计算房价的SQL查询

Question

您好，我现在正在解决一个问题，假设我有一个视图，我们可以称之为 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

之后我在酒店做了一个团体，花了最少的钱（价格），它起作用了..但执行3获取 room_price 的 times 查询，而不是笛卡尔积， room_price 中有大约 5000 个元素，它是一个相当复杂的 sql，它生成此数据（需要日期开始结束多个价格，货币兑换......）

我 。可以使用sql，自定义函数...或任何可以使这项工作快速进行的东西，但我更愿意停留在数据库级别，而不需要在应用程序中处理这些数据（我正在使用java），因为我将进一步将其扩展到向查询添加一些附加数据。

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

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
\.

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

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

写了详细说明< /a> 它是如何工作的。

Answer 2

看起来您输入的查询与 FROM 子句不正确...看起来别名不正常

from room_price a, room_price,a1 room_price,room_price a2 

，应该是

from room_price a, room_price a1, room_price a2 

这可能会给查询一个错误的别名/额外的表，从而给出某种笛卡尔积，使其挂起。 ..

--- 好的 FROM 子句...

另外，只是一个想法...由于“Room”似乎是内部自动递增 ID 列，因此它永远不会重复，例如酒店的 Room 100酒店A、100室B. 您要执行的查询<>在房间上是有道理的，所以你永远不会在所有 3 个表上进行全面比较...

为什么不强制 a1 和 a2 连接只符合比“a”房间更大的房间。否则，您将一遍又一遍地重新测试相同的条件。从您的示例数据来看，就在酒店 A，您的房间 ID 为 1、2 和 3。因此，您正在比较

a     a1    a2
1     2     3
1     3     2
2     1     3
2     3     1
3     1     2
3     2     1

仅比较“a1”始终大于“a”和“a2”始终大于“a”是否有帮助？因此，对“a1”进行测试

a   a1   a2
1   2    3 

将得到与其他所有房间相同的结果，从而在这种情况下将您的结果膨胀到一条记录......但是，您如何才能真正与只有两种房型的位置“酒店”进行比较B”。您永远不会得到答案，因为您的房间资格是

a <> a1 AND
a <> a2 AND
a1 <> a2

您可能想尝试将 a1、a2 减少到只有一个自连接，并仅对两者进行比较，例如

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


For hotel "A", you would thus have final result comparisons of 
a1   a2
1    2
1    3
2    3

and for hotel "B"
a1   a2
4    5

Answer 3

实施<>当您开始查看更大的数据集时，将会产生相当大的影响。特别是如果先前的过滤没有大幅减小其大小。通过使用此功能，您可能会否定直接查询被优化和实现索引的可能性，但视图也可能不会实现索引，因为 SQL 将尝试以尽可能少的语句对表运行查询和视图的过滤器（等待引擎完成的优化）。

理想情况下，我会从视图开始并确认它已适当优化。仅查看查询本身，就有更好的机会进行优化；

SELECT
    a.hotel, a.price + a1.price + a2.price  
FROM    
    room_price a, 
    room_price, 
    room_price a1,
    room_price a2  
WHERE   
    (a.room > a1.room OR a.room < a1.room) AND 
    (a1.room > a2.room OR a1.room < a2.room) AND 
    (a.room > a2.room OR a.room < a2.room) AND 
    a.hotel = a1.hotel AND 
    a.hotel = a2.hotel 

它似乎返回相同的结果，但我不确定您如何在整体解决方案中实现此查询。因此，请仅考虑对现有查询所做的更改的性质以及您已经完成的操作。

希望这有帮助。如果不是，您可能需要考虑视图正在做什么以及它如何工作从临时表或变量返回结果的视图也无法实现索引。在这种情况下，生成索引临时表可能更适合您。