CODEWITHSUNDEEP
sqlpostgresql
user882134
user882134

Reputation: 309

Detect a series of declining values in a postgres table

I have a table with a series of dates, customer ID's, and integers that updates each week with a new date and integer values for all ID's.

I'd like to be able to write a query that will detect if the most recent three values are in decreasing order, and return TRUE or FALSE, for each ID.

So, for the table:

ID | Weekending | Value
1  | 2015-07-15 | 5
2  | 2015-07-15 | 23
1  | 2015-07-08 | 7
2  | 2015-07-08 | 21
1  | 2015-07-01 | 9
2  | 2015-07-01 | 24

I'd want a query that would return TRUE for ID:1 because three consecutive values declined, but FALSE for ID:2 because the value increased.

Upvotes: 4

Views: 743

Answers (3)

jpw
jpw

Reputation: 44871

Not sure about performance but using the lag window function like this should work:

select *, 
    case when 
        value < lag(value, 1) over (partition by id order by weekending) 
    and value < lag(value, 2) over (partition by id order by weekending) 
    then 'True' else 'False' end
from t
order by id, weekending desc;

This would mark the row 1 | 2015-07-15 | 5 as true, and all other rows as false.

Sample SQL Fiddle

To only get the last row per id you could do this:

select ID, Weekending, Value, decrease 
from (
    select *, 
       case when 
           value < lag(value, 1) over (partition by id order by weekending) 
       and value < lag(value, 2) over (partition by id order by weekending) 
       then 'True' else 'False' end as decrease,
       row_number() over (partition by id order by weekending desc) as r
    from t
) src 
where r = 1;

which would give you a result like:

ID  Weekending  Value   decrease
1   2015-07-15  5       True
2   2015-07-15  23      False

Upvotes: 4

leonbloy
leonbloy

Reputation: 75896

SELECT DISTINCT ON (id) 
id, week,val,val1,val2, val< val1 AND val1 < val2 as decreases
FROM (
 SELECT id,week,val, 
 lag(val,-1,10000) OVER w1 AS val1, 
 lag(val,-2,10001) OVER w1 AS val2 
 FROM TAB 
 WINDOW w1 AS (PARTITION BY id ORDER BY week DESC)
 ORDER BY ID,week  DESC) 
AS X

This assumes that 10000 and 10001 are "very large values" (true values will always be below that), and that you are interested in strict decreasing sequences.

For example, for the data

       id    week        val
        1  2015-07-15      5
        1  2015-07-08      7
        1  2015-07-01      9
        1  2015-06-28    121
        1  2015-06-15      2 
        2  2015-07-15     23
        2  2015-07-08     21
        2  2015-07-01     24
        3  2015-07-15     20
        3  2015-07-08     21
        4  2015-07-15     30
        4  2015-07-08     21
        5  2015-07-08    210

this gives

   id     week        val     val1     val2   decreases
    1   2015-07-15      5        7        9      t
    2   2015-07-15     23       21       24      f
    3   2015-07-15     20       21    10001      t
    4   2015-07-15     30       21    10001      f
    5   2015-07-08    210    10000    10001      t

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

If you can select the relevant records based only on the weekending values (so assuming that all ids have records for every recent week), you could select the values into an array and use array methods to detect whether the values are descending or not:

with cte_values as (
  select id,
         array_agg(value order by weekending) value_array
  from   my_table
  where  weekending >= <add something to select the relevant weeks>)
select id
       case
         when value_array[2] < value_array[1] and
              value_array[3] < value_array[2]
         then true
         else false
       end is_ascending
from cte_values

Not syntax checked

Upvotes: 0

Related Questions