CODEWITHSUNDEEP
sqlamazon-redshift
Axis
Axis

Reputation: 2132

RedShift: Join a table with generate series

I have a table store items level so if you upgrade your item save in the item upgrade day, eg, 05/10/2019

|     day       |  customer |  items  |levels|
| -------       | -------   | ------- | -----|
| 2019-10-02    |    a      |  item1  |  0   |
| 2019-10-05    |    a      |  item1  |  1   |
| 2019-10-09    |    a      |  item1  |  2   |

So my goal is to find how many different levels item exist in my table day by day. So in the above table, I cannot tell I have one level-1 item between 05/10/2019 - 09/10/2019

So I needed to count all levels and show in the other days as well. (cumulative sum)

So finally I found something to calculate

    SELECT *
FROM (
       SELECT date date_d,
              d.cust_id,
              item_id,
              item_level,
              last_value(item_level) IGNORE NULLS OVER (PARTITION BY d.cust_id ORDER BY date_d
                ROWS UNBOUNDED PRECEDING ) level_d
       FROM daily d
              LEFT JOIN (
           SELECT cust_id,
                  date date_u,
                  item_id
                  item_level,
                  RANK()
                  OVER (PARTITION BY cust_id,date_u,item_id ORDER BY TIMESTAMP DESC ) rank
           FROM update
            where item_id = '1'
         ) u ON u.cust_id= d.cust_id AND u.date_u = d.date_d AND rank = 1
       GROUP BY
         1, 2, 3, 4, 5)

The result is:

|     date       |  customer |  items  |levels|
| -------       | -------   | ------- | -----|
| 2019-10-02    |    a      |  item1  |  0   |
| 2019-10-03    |    a      |  item1  |  0   |
| 2019-10-04    |    a      |  item1  |  0   |
| 2019-10-05    |    a      |  item1  |  1   |
| 2019-10-06    |    a      |  item1  |  1   |
| 2019-10-07    |    a      |  item1  |  1   |
| 2019-10-08    |    a      |  item1  |  1   |
| 2019-10-09    |    a      |  item1  |  2   |
| 2019-10-10    |    a      |  item1  |  2   |

but the problem is if the customer is not in the second table we cannot see further so I need a current date for all customers item

What I was thinking to create a generate series like this and join my table

SELECT (getdate() - (i * interval '1 day'))::date as date_datetime
FROM generate_series(1,DATEDIFF(day, '2019/10/01', getdate())) i
ORDER BY 1

enter image description here

but I got a redshift error!

Any idea how can I join a generate_series data to my table or any different idea

Upvotes: 2

Views: 904

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

You can use the original table to generate dates:

with dates as (
      select '2010-01-01'::date + row_number() over () * interval '1 day' as dte
      from daily d
      limit 5000
     )
. . .

You can then use this in your query as you would generate_series().

Note: This assumes that you have a sufficient number of rows in your daily table.

Upvotes: 1

Related Questions