CODEWITHSUNDEEP
hivehiveql
cs_guy
cs_guy

Reputation: 373

Subtracting previous row value from current row

I'm doing an aggregation like this:

select
    date,
    product,
    count(*) as cnt
from
    t1
where
    yyyy_mm_dd in ('2020-03-31', '2020-07-31', '2020-09-30', '2020-12-31')
group by
    1,2
    
order by
    product asc, date asc

This produces data which looks like this:

| date       | product | cnt  | difference |
|------------|---------|------|------------|
| 2020-03-31 | p1      | 100  | null       |
| 2020-07-31 | p1      | 1000 | 900        |
| 2020-09-30 | p1      | 900  | -100       |
| 2020-12-31 | p1      | 1100 | 200        |
| 2020-03-31 | p2      | 200  | null       |
| 2020-07-31 | p2      | 210  | 10         |
| ...        | ...     | ...  | x          |

But without the difference column. How could I make such a calculation? I could pivot the date column and subtract that way but maybe there's a better way

Upvotes: 0

Views: 253

Answers (1)

cs_guy
cs_guy

Reputation: 373

Was able to use lag with partition by and order by to get this to work:

select
    date,
    product,
    count,
    count - lag(count) over (partition by product order by date, product) as difference
from(
    select
        date,
        product,
        count(*) as count
    from
        t1
    where
        yyyy_mm_dd in ('2020-03-31', '2020-07-31', '2020-09-30', '2020-12-31')
    group by
        1,2
) t

Upvotes: 1

Related Questions