Sql query for calculating room rate

Question

Hi, I have a problem that I have been working on for some time, let's say I have a view that lets you call it room_price like this:

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

I am looking for the lowest price in this hotel for x number of people

for 1, I expect that I will have:

 hotel | price A | 200 B | 90 

for 2 i:

 hotel | price A | 99 

this is because there are no rooms in Hotel B that can exactly match 2 persons. 6 cannot be used for less (or more) 6 people.

for hotel Price - 99 because I use room 2

for result 6 should be:

 hotel | price A | 394 B | 300 

so for hotel A I take rooms 1,2,3, and for hotel B the lowest price will be for one room 5 for 300

I did it with the restrictions that I can put people in a maximum of 3 rooms, and that’s acceptable, but my request is to slow down :( it looks something like this:

 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 

after that I made a group at the hotel and took a minimum (price), and it worked ... but after completing 3 times the request that room_price receives for me, and not the potato product, which took a lot of time. There are about 5000 elements in room_price, and this is a rather complicated sql that generates this data (accepts the start dates of several prices, currency exchange ...)

I can use sql, user-defined functions ... or anything that will make this work fast, but I would prefer to stay at the database level without having to process this data in the application (I use java), as I will expand it additionally adds additional data per request.

I would be grateful for any help.

Answer 1

The request itself:

 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; 

Tested in this dataset:

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

Please note that it will become much slower if you add more rooms to your base.

Ah, to adjust how many people you want to get results, change the installation part.

Wrote a detailed explanation of how this works.

Answer 2

It looks like your request as entered incorrectly with the FROM clause ... it looks like the aliases are sloppy

 from room_price a, room_price,a1 room_price,room_price a2 

and should be

 from room_price a, room_price a1, room_price a2 

This MAY give the query a false alias / extra table giving some kind of Cartesian product that causes it to hang.

--- ok in the FROM clause ...

In addition, and just a thought ... Since the "Room" seems to be an internal column of the auto-increment identifier, it will never be duplicated, for example, number 100 in hotel A and number 100 in hotel B. Your request to do <> in the room, so that you never compare across all three tables ...

Why not make a1 and a2 join only the room MORE than the "room". Otherwise, you will retest the same conditions again and again. From your example data, just at Hotel A, you have the IDs of rooms 1, 2, and 3. Thus, you compare

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

It would only help to compare where "a1" is always greater than "a" and "a2" is always greater than "a1", thereby doing tests

 a a1 a2 1 2 3 

will give the same results as everything else, and thus inflate your result to one record in this case ... but then, as you can compare with the location of only two types of rooms "hotel B". You will never get an answer, as your qualifications for rooms

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

You might want to try to reduce only one independent union for a1, a2 and keep the comparison with only two, for example

 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

The implementation of <> will have a pretty big impact when you start looking at large datasets. Especially if the previous filtering does not significantly reduce its size. Using this, you can potentially deny the possibility of optimizing the optimized query and implement indexing, but it may also not implement indexing, since SQL will try to run filters for querying and presenting against tables in as many statements as possible (for example, the expected optimizations performed by the engine).

Ideally, I would start with a presentation and optimize it correctly. Just by looking at the request itself, it is more likely to be optimized;

 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 

It seems to return the same results, but I'm not sure how you implement this query in your overall solution. Therefore, we consider only the nature of the changes to the existing request and what you have already done.

Hope this helps. If not, you may need to consider what the view does, and how it works on a view that returns results from a temp table or variable, also cannot implement indexing. In this case, perhaps creating an indexed pace table would be better for you.