Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – How can I find the COUNT of lesser and equal to values UNTIL the next highest value in the same column in Redshift SQL?

This might be a bit elaborate so please bear with me. I’m looking to create a new column free_duration in a table that currently looks like this, when grouped by country and product in ASCENDING order of month-

month   country product         stock
7/1/10  Wakanda Vibranium   7166
8/1/10  Wakanda Vibranium   6189
9/1/10  Wakanda Vibranium   1987
10/1/10 Wakanda Vibranium   98
11/1/10 Wakanda Vibranium   23
12/1/10 Wakanda Vibranium   1
1/1/11  Wakanda Vibranium   29999
2/1/11  Wakanda Vibranium   2314
3/1/11  Wakanda Vibranium   19

The new column free_duration, for a given row x1, is basically the count of number of months where stock column
values are lesser than or equal to the stock column value in that row x1, until more stock comes up (that is, the stock value increases next).

In the example, for row 1, arranged in ascending order of month, stock in July ’10 is 7166 – free_duration value for July ’10 should therefore be 5 because for 5 consecutive months, the stock column value is lesser than 7166 in row 1, until the next higher value is hit. Same way, for row 6, Dec ’10, stock available is 1, and given the next immediate month the stock is greater than 1, free_duration should be 0.

To summarize, I need to take one value in a column, count down that same column all the less than or equal values, UNTIL the next immediate greater value is reached.

My query almost does it but with one logic flaw- Within each group, it ends up counting all lesser values and not setting the upper cap of limiting the count to until the next higher value is reached.

WITH ranking AS (SELECT month,
                        country,
                        product,
                        stock,
                        ROW_NUMBER() OVER (PARTITION BY country, product ORDER BY month) AS rn
                 FROM data_table),
     free_duration_cte AS (SELECT r1.month,
                               r1.country,
                               r1.product,
                               r1.stock,
                               COALESCE((SELECT COUNT(*)
                                         FROM ranking r2
                                         WHERE r1.country = r2.country
                                           AND r1.product = r2.product
                                           AND r2.rn > r1.rn
                                           AND r2.stock <=
                                               r1.stock), 0) AS free_duration
                        FROM ranking r1)
SELECT *
FROM free_duration_cte
ORDER BY country, product, month;

I’ve tried multiple other things to merely set the upper cap, and the simplest would involve FIRST_VALUE() which is not accepted in Redshift. Any help to make the change needed will really be appreciated.

Tags:

2

Answers


  1. 0

    Is this what you are looking for?

    create table table_1 (
  month   date,
  country varchar(16),
  product varchar(16),        
  stock int);

insert into table_1 values 
('2010-7-1', 'Wakanda', 'Vibranium', 7166),
('2010-8-1', 'Wakanda', 'Vibranium', 6189),
('2010-9-1', 'Wakanda', 'Vibranium', 1987),
('2010-10-1', 'Wakanda', 'Vibranium', 98),
('2010-11-1', 'Wakanda', 'Vibranium', 23),
('2010-12-1', 'Wakanda', 'Vibranium', 1),
('2011-1-1', 'Wakanda', 'Vibranium', 29999),
('2011-2-1', 'Wakanda', 'Vibranium', 2314),
('2011-3-1', 'Wakanda', 'Vibranium', 19);

with min_local as (
select *,
  nvl(min(stock) over(partition by country, product order by month rows between current row and unbounded following), stock) local_min
from table_1),
change as (
select *,
  decode(lag(local_min) over(partition by country, product order by month), local_min, 0, 1) change
from min_local),
groups as (
select *,
  sum(change) over(partition by country, product order by month rows unbounded preceding) grp
from change)
select month, country, product, stock,
  count(*) over(partition by country, product, grp order by month desc rows unbounded preceding) - 1 as free_duration
from groups
order by month;
    Login or Signup to reply.
  2. 0

    One of the options to do it is to:

    1. find the "breaking points" – rows that has stock bigger than any stock before for the same country and product (innermost query in the SQL below).
    2. find the first month in future rows that is a "breaking point" (stock = max_stock in inner query)
    3. calculate months before next bigger stock (TIMESTAMPDIFF() in inner query)
    4. select columns of interest handling negative month_diff
    --    S a m p l e    D a t a :
Create Table tbl ( month   date, country varchar(16), product varchar(16), stock int);
Insert Into tbl values 
('2010-07-01', 'Wakanda', 'Vibranium', 7166),
('2010-08-01', 'Wakanda', 'Vibranium', 6189),
('2010-09-01', 'Wakanda', 'Vibranium', 1987),
('2010-10-01', 'Wakanda', 'Vibranium', 98),
('2010-11-01', 'Wakanda', 'Vibranium', 23),
('2010-12-01', 'Wakanda', 'Vibranium', 1),
('2011-01-01', 'Wakanda', 'Vibranium', 29999),
('2011-02-01', 'Wakanda', 'Vibranium', 2314),
('2011-03-01', 'Wakanda', 'Vibranium', 19);

    SELECT  x.month, x.country, x.product, x.stock,
        Case When month_diff < 0 Then 0 Else month_diff End as month_diff
FROM   ( Select     g.*, 
               TIMESTAMPDIFF( MONTH,
                              g.month, 
                              Coalesce(Min(Case When g.stock = g.max_stock Then g.month End) 
                                           Over(Partition By g.country, g.product Order By g.month
                                           Rows Between 1 Following And Unbounded Following), 
                                       g.month
                                      )
                            ) - 1 as month_diff
         From ( Select  t.month, t.country, t.product, t.stock, 
                        Max(t.stock)  
                            Over(Partition By t.country, t.product Order By t.month
                            Rows Between Unbounded Preceding And Current Row) as max_stock  
               From     tbl t 
             ) g
       ) x
ORDER BY  x.country, x.product, x.month

    /*    R e s u l t : 
month       country  product       stock  month_diff
----------  -------  ----------  -------  ----------
2010-07-01  Wakanda  Vibranium      7166           5
2010-08-01  Wakanda  Vibranium      6189           4
2010-09-01  Wakanda  Vibranium      1987           3
2010-10-01  Wakanda  Vibranium        98           2
2010-11-01  Wakanda  Vibranium        23           1
2010-12-01  Wakanda  Vibranium         1           0
2011-01-01  Wakanda  Vibranium     29999           0
2011-02-01  Wakanda  Vibranium      2314           0
2011-03-01  Wakanda  Vibranium        19           0      */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search