CODEWITHSUNDEEP
sql
shenhengbin
shenhengbin

Reputation: 4294

How to compute the diff between records?

My table records is like below 

ym  cnt
200901  57
200902  62
200903  67
...
201001  84
201002  75
201003  75
...
201101  79
201102  77
201103  80
...

I want to computer the diff between current month and per month . the result would like below ...

ym  cnt       diff
200901  57        57   
200902  62        5  (62 - 57)
200903  67        5  (67 - 62) 
...
201001  84       ...
201002  75
201003  75
...
201101  79
201102  77
201103  80
...

Can anyone told me how to wrote a sql to got the result and with a good performance ?

UPDATE:

sorry for simple words

my solution is 

step1: input the currentmonth data into temp table1

step2: input the permonth data into temp table2

step3: left join 2 tables to compute the result

Temp_Table1

SELECT  (ym - 1) as ym , COUNT( item_cnt ) as cnt
FROM _table 
GROUP BY (ym - 1 ) 
order by ym

Temp_Table2

SELECT  ym ,  COUNT( item_cnt ) as cnt
FROM _table
GROUP BY ym 
order by ym



select ym , (b.cnt - a.cnt) as diff from Temp_Table2 a 
           left join Temp_Table1 b
                on a.ym = b.ym

*If i want to compare the diff between the month in this year and last year I can only change the ym - 1 to ym - 100*

but , actually , the group by key is not only ym

there is max 15 keys and max 100 millions records

so , I wonder a good solution can easy to manager the source

and good performance.

Upvotes: 0

Views: 106

Answers (2)

Alexey
Alexey

Reputation: 919

For MSSQL, this has one reference to the table, so potentially it can be faster (maybe not) than left join which has two references to the table:

-- ================
-- sample data
-- ================
declare @t table
(
    ym varchar(6),
    cnt int
)

insert into @t values ('200901', 57)
insert into @t values ('200902', 62)
insert into @t values ('200903', 67)
insert into @t values ('201001', 84)
insert into @t values ('201002', 75)
insert into @t values ('201003', 75)

-- ===========================
-- solution
-- ===========================
select
    ym2,
    diff = case when cnt1 is null then cnt2
        when cnt2 is null then cnt1
        else cnt2 - cnt1
        end
from
(
    select
        ym1 = max(case when k = 2 then ym end),
        cnt1 = max(case when k = 2 then cnt end),
        ym2 = max(case when k = 1 then ym end),
        cnt2 = max(case when k = 1 then cnt end)
    from
    (
        select 
            *, 
            rn = row_number() over(order by ym)
        from @t
    ) t1
    cross join
    (
        select k = 1 union all select k = 2
    ) t2
    group by rn + k
) t
where ym2 is not null

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799290

Can anyone told me how to wrote a sql to got the result

Absolutely. Simply get the row with the next highest date, and subtract.

and with a good performance ?

No. Relational databases are not really meant to be traversed linearly, and even using indexes appropriately would require a virtual linear traversal.

Upvotes: 0

Related Questions