Calculate a ratio using SQL incorporating lag values-CodePudding

I have a table like this

For each day, I need to calculate the ratio of (sum of val for that day when cat=4) / (sum of val for the previous day when cat=3).

Could you tell me how to calculate that?

I can calculate the numerator and denominator separately like this:

select Date, sum(val) as bal1 from performance  
       where cat=4 group by Date 

select Date, sum(val) as bal2 from performance  
       where cat=3 group by Date

But I am not sure how to combine those to get the ratio like I mentioned above. May be I need to join the two results by Date.

Any guidance would be highly appreciate.

CodePudding user response：

We can try using LAG() as follows:

WITH cte AS (
    SELECT *, LAG(cat) OVER (PARTITION BY Date ORDER BY Date) lag_cat,
              LAG(val) OVER (PARTITION BY Date ORDER BY Date) lag_val
    FROM performance
)

SELECT
    Date,
    SUM(CASE WHEN cat = 4 THEN val ELSE 0 END) /
    SUM(CASE WHEN lag_cat = 3 THEN lag_val ELSE 0 END) AS ratio
FROM cte
GROUP BY Date;

CodePudding user response：

You can combine aggregation with LAG() window function:

SELECT Date,
       SUM(CASE WHEN cat = 4 THEN val END) /
       LAG(SUM(CASE WHEN cat = 3 THEN val END)) OVER (ORDER BY Date) ratio  
FROM performance
GROUP BY Date;

See the demo.