CODEWITHSUNDEEP
postgresqlconditional-statementscase-when
tlswpsl
tlswpsl

Reputation: 97

indicator for increase over time

I'm trying to create an indicator for value increase over time within a group. In particular, I'm trying to flag certain grp if value ever increases by 50% over time.

I have a raw data that looks like:

id    grp     value_dt    value
--------------------------------
1      1      11/20/20     1.4
1      1      11/21/20     0.8
1      1      11/24/20     2.8
1      1      11/25/20     2.5
1      2      11/29/20     1.5
1      2      12/1/20      1.6
2      1      11/21/20     0.8
2      2      11/26/20     0.9
2      3      12/1/20      0.9
2      3      12/3/20      2.8

You can see that for id = 1 and grp = 1, the value fluctuates as it increases and decreases over time, but because it had increase over time between 11/21/20 and 11/24/20 from 0.8 to 2.8 (greater than 50% increase), I want to flag the whole grp 1. I want my output to look like:

id    grp     val_ind 
-----------------------
1      1         1
1      2         0 
2      1         0 
2      2         0 
2      3         1

I can only think of using min and max (something like below), which doesn't include the 'over the time' factor in...

select id, 
grp, 
min(value) as min_grp, 
max(value) as max_grp, 
(max_grp - min_grp) as val_diff, 
case when val_diff >= min_grp * 1.5 then 1 else 0 end as val_ind

If anyone can offer their advice, I will greatly appreciate it!

Upvotes: 0

Views: 37

Answers (1)

eshirvana
eshirvana

Reputation: 24568

I think you want to know if at any point at time there is an increase of 50% , you flag that group. if yes , here is how you can do it, you need to use cte and window functions :

; WITH cte AS (
SELECT *
, CASE WHEN  COALESCE(LEAD(value) OVER (PARTITION BY id, grp ORDER BY value_dt),0)  >= value* 2 THEN 1 ELSE 0 END val_ind
FROM  ttt
)

SELECT 
id , grp , MAX(val_ind) val_ind
FROM cte 
GROUP BY 
id , grp

id | grp | val_ind
-: | --: | ------:
 1 |   1 |       1
 1 |   2 |       0
 2 |   1 |       0
 2 |   2 |       0
 2 |   3 |       1

db<>fiddle here

Upvotes: 1

Related Questions