2

I currently have a table called quantities with the following data:

+------+----------+----------+
| item | end_date | quantity |
+------+----------+----------+
| 1    | 26/11/17 | 100      |
+------+----------+----------+
| 2    | 28/11/17 | 300      |
+------+----------+----------+
| 3    | 30/11/17 | 500      |
+------+----------+----------+

I want to query it so I can get this result:

+--------+-------+
| date   | total |
+--------+-------+
| 26-Nov | 900   |
+--------+-------+
| 27-Nov | 800   |
+--------+-------+
| 28-Nov | 800   |
+--------+-------+
| 29-Nov | 500   |
+--------+-------+
| 30-Nov | 500   |
+--------+-------+

How would this query look like? Many thanks!

Please note:

The above output is the result of suming the "quantity" field of each item when today's date is =< end_date. See below that today is the 26th of november.

+--------+-----+-----+-----+-------+
| date   | 1   | 2   | 3   | total |
+--------+-----+-----+-----+-------+
| 26-Nov | 100 | 300 | 500 | 900   |
+--------+-----+-----+-----+-------+
| 27-Nov | -   | 300 | 500 | 800   |
+--------+-----+-----+-----+-------+
| 28-Nov | -   | 300 | 500 | 800   |
+--------+-----+-----+-----+-------+
| 29-Nov | -   | -   | 500 | 500   |
+--------+-----+-----+-----+-------+
| 30-Nov | -   | -   | 500 | 500   |
+--------+-----+-----+-----+-------+

I'd need the query to always give an output with the following date range:

  • start_date=current_date
  • end_date=the latest end_date from the list of items

I am using PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1499

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Henry
  • 697
  • 1
  • 6
  • 16

1 Answers1

2
t=# create table so (item serial, end_date date, quantity int);
CREATE TABLE
t=# set datestyle TO DMY;
SET
t=# insert into so(end_date,quantity) values('26-11-2017',100),('28-11-2017', 300), ('30-11-2017', 500);
INSERT 0 3

then, we create a view with a date series that goes into the future starting from today by using a table with a sequential id column:

CREATE VIEW future_dates AS
SELECT (getdate()::date -1 + id)::date as future_dates
FROM app_data.table_with_sequential_id
ORDER BY  future_dates

N.b. We need to create the above view because generate_series is not fully supported in Redshift.

and lastly select itself:

t=# select gs::date,  sum(quantity) over (order by gs desc rows unbounded preceding)
from future_dates gs
left outer join so on so.end_date = gs
order by gs ;
     gs     | sum
------------+-----
 2017-11-26 | 900
 2017-11-27 | 800
 2017-11-28 | 800
 2017-11-29 | 500
 2017-11-30 | 500
(5 rows)
Henry
  • 697
  • 1
  • 6
  • 16
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • thanks very much for your prompt response. Your answer works except for the `generate_series` function which in Redshift is not fully supported. Are there any alternatives to generate a series that goes into the future? Thanks @Vao Tsun! – Henry Nov 26 '17 at 17:20
  • just found an alternative to generate_series in Redshift: The simplest is to use a table with a sequential id column, and then select the id column from that table to generate a date series. On the other hand, this is the other error I am currently getting: `Aggregate window functions with an ORDER BY clause require a frame clause` Any ideas @VaoTsun? – Henry Nov 26 '17 at 17:34
  • ah - I missed the redshift tag. I believe it is based on ancient postgres version, so window functions will be with limitations. alas I did not practice with postgres 8 for 10years, so won't guess working code for redshift – Vao Tsun Nov 26 '17 at 17:53
  • so to solve the above error the query needs to have the following: `(order by gs desc rows unbounded preceding)`. That way it will work; don't know why though. Thanks @VaoTsun! – Henry Nov 26 '17 at 17:54
  • Not sure why this is marked as the correct answer as it does no twork on redshift! please unmark it as correct or update it to be correct? – Jon Scott Nov 27 '17 at 22:07
  • @Henry please post your redshift version and working query – Vao Tsun Nov 28 '17 at 07:08