计算房价的sql查询

Question

您好，我有一个问题我正在处理一段时间，假设我有一个 View ，我们称它为 room_price 看起来像这样:

room | people | price   | hotel
 1   |    1   |   200   |   A
 2   |    2   |   99    |   A
 3   |    3   |   95    |   A
 4   |    1   |   90    |   B
 5   |    6   |   300   |   B

我正在为 x 人数寻找给定酒店的最低价格

对于 1，我希望我会有:

hotel | price
  A   |  200
  B   |  90

对于 2 我会:

hotel | price
  A   |  99

因为B酒店没有正好可以容纳2人的房间。 6 不能用于少于(或多于)6 人。

酒店A的价格是99是因为我用的是2号房

对于 6 结果应该是:

hotel | price
  A   |  394
  B   |  300

所以对于酒店 A，我选择 1、2、3 号房间，对于酒店 B，最低价格是 5 号房 300 美元

我这样做是有限制的，我最多只能容纳 3 个房间，这是可以接受的，但我的查询速度很慢 :( 它看起来像这样 :

select a.hotel,a.price+a1.price+a2.price 
from room_price a, room_price a1, room_price a2 
where 
    a.room<> a1.room
and a1.room<> a2.room
and a.room<> a2.room
and a.hotel = a1.hotel
and a.hotel = a2.hotel

之后我按酒店做了一个组并取了 min(price) 并且它起作用了......但是执行了 3 次查询得到了 room_price 并且比笛卡尔乘积花费了很多时间。 room_price 中有大约 5000 个元素，它是一个相当复杂的 sql 生成此数据(需要日期开始结束多个价格，货币兑换......)

我可以使用 sql、自定义函数 ...这进一步向查询添加了一些额外的数据。

如有任何帮助，我将不胜感激。

Answer 1

查询本身:

WITH RECURSIVE
setup as (
    SELECT 3::INT4 as people
),  
room_sets AS (
    SELECT
        n.hotel,
        array[ n.room ] as rooms,
        n.price,
        n.people
    FROM
        setup s,
        room_price n
    WHERE
        n.people <= s.people
    UNION ALL
    SELECT
        rs.hotel,
        rs.rooms || n.room,
        rs.price + n.price as price,
        rs.people + n.people as people
    FROM
        setup s,
        room_sets rs
        join room_price n using (hotel)
    WHERE
        n.room > rs.rooms[ array_upper( rs.rooms, 1 )]
        AND rs.people + n.people <= s.people
),
results AS (
    SELECT
        DISTINCT ON (rs.hotel)
        rs.*
    FROM
        room_sets rs,
        setup s
    WHERE
        rs.people = s.people
    ORDER BY
        rs.hotel, rs.price
)   
SELECT * FROM results;

在这个数据集上测试它:

CREATE TABLE room_price (
    room INT4 NOT NULL,
    people INT4 NOT NULL,
    price INT4 NOT NULL,
    hotel TEXT NOT NULL,
    PRIMARY KEY (hotel, room)
);  
copy room_price FROM stdin WITH DELIMITER ',';
1,1,200,A
2,2,99,A
3,3,95,A
4,1,90,B
5,6,300,B
\.

请注意，当您向基地添加更多房间时，它会变得更慢。

啊，要为多少人定制您想要的结果 - 更改设置部分。

写了detailed explanation关于它是如何工作的。