SQL: task to find the latest price
Hello! Radio SQL is on the air again.
It was a long time since they went on the air, but then the humanoid brothers from the nearby Little McGelan Cloud threw up a problem. Immediately in one sitting, the problem did not dare, I had to think. This means that in the Western arm of the Galaxy there may also be those who want to break their minds about a problem. Now I will state the condition, and the answer will go away with the next message.
The condition is as follows. There is a dataset with prices for goods (prod_id) in warehouses (stock_id). Moreover, prices are real (R= Real), and there are advertising (P= Promo). Each price has a start date. It is necessary to pull out the real price for each line of the set, which is the latest real price (price1) with type ‘R‘for this item in the corresponding warehouse.
Here is the start of a query with test data as CTEwhere you can practice:
with price(stock_id, prod_id, start_date, kind, price1, cost1, bonus1) as (
values (1,1,to_date('2000-01-01','YYYY-MM-DD'),'R',100.0,32.12,6.49),
(1,1,'2000-01-02','P', 80.0, 0, 0),
(1,1,'2000-01-03','P', 70.0, 0, 0),
(1,1,'2000-01-04','R',110.0,33.48,6.19),
(1,1,'2000-01-05','P', 90.0, 0, 0),
(1,1,'2000-01-06','R',120.0,41.22,6.19),
(1,1,'2000-01-07','P', 80.0, 0, 0),
(1,1,'2000-01-08','P', 90.0, 0, 0),
(1,1,'2000-01-09','R', 93.0,36.87,6.49),
(1,1,'2000-01-10','R', 94.0,36.85,6.99),
(1,2,'2000-01-01','R',101.0,52.06,9.00),
(1,2,'2000-01-02','P', 81.0, 0, 0),
(1,2,'2000-01-03','P', 71.0, 0, 0),
(1,3,'2000-01-04','R',111.0,64.96,4.50),
(1,3,'2000-01-05','P', 92.0, 0, 0),
(1,3,'2000-01-06','R',122.0,66.83,4.60),
(1,3,'2000-01-07','P', 82.0, 0, 0),
(1,3,'2000-01-08','P', 92.0, 0, 0))
select ...You should get something like this:
stock_id | prod_id | start_date | kind | price1 | cost1 | bonus1 | price1x
----------+---------+------------+------+--------+-------+--------+---------
1 | 1 | 2000-01-01 | R | 100.0 | 32.12 | 6.49 | 100.0
1 | 1 | 2000-01-02 | P | 80.0 | 0 | 0 | 100.0
1 | 1 | 2000-01-03 | P | 70.0 | 0 | 0 | 100.0
1 | 1 | 2000-01-04 | R | 110.0 | 33.48 | 6.19 | 110.0
1 | 1 | 2000-01-05 | P | 90.0 | 0 | 0 | 110.0
1 | 1 | 2000-01-06 | R | 120.0 | 41.22 | 6.19 | 120.0
1 | 1 | 2000-01-07 | P | 80.0 | 0 | 0 | 120.0
1 | 1 | 2000-01-08 | P | 90.0 | 0 | 0 | 120.0
...The peculiarities here are as follows. I radioed higher for a reason “data source“, Because we have not a table here, but a view, collected from a variety of and often completely unexpected sources, from where all sorts of promotional prices come from. That is, there is not only no primary key for lines, but even a surrogate key on the fly will not be immediately obtained, since there is no CTID (or ROWID there) … The second caveat is that I left only the columns here. price1, cost1 and bonus1, and in the real data source, many characteristics had to be pulled from the last ‘R’ line, since this data is not on the advertising lines. And don’t ask why – business knows better. Think of it as an advanced condition of the problem – to select all of these fields from the last R-record.
Well, please go to kamenty with ideas and solutions! Through the mythical man-month, there will be a summing up and analysis of the decision. The solutions in the comments will be tested against PostgreSQL, so you can use (but not abuse!) Platform-specific features. We ask you to clean up the SQL code under spoilers, so as not to shine it on those who would like to try their hand at solving. For those who do not have postgres at hand, there are many opportunities to launch a request online, so it does not count as an excuse. And you can literally put a working postgres from the standard OS repositories under any not too exotic Linux in one command.
PS Since it was published in the Postgres Pro corporate blog, we will use corporate privileges. To stimulate activity, for the most interesting decision I will give out some goodies on behalf of the company, a promotional code for PGConf or for passing certification tests …
