Home > Back-end >  SQL to get the distinct rolling sum over a growing window
SQL to get the distinct rolling sum over a growing window

Time:08-26

I have a table that shows the inventory records per day. I need to get the rolling distinct and its total. How can I get it? recursive SQL?

Here I have a simplified table like this:

SELECT timestamp, item FROM inventory;
timestamp item
20210101 A
20210101 B
20210101 C
20210102 A
20210103 B
20210103 D

The [timestamp] is the timestamp when we receive the inventory stock. The [item] is the item name. for the first row: on 2021 Jan 1st, we have A,B and C. on 2021-01-02, we got A. on 2021-02-03, we got B and C.

Expected results: Table1:

Time distinct_item_count(rolling)
20210101 3
20210102 3
20210103 4

Table2:

Time distinct_item
20210101 A,B,C
20210102 A,B,C
20210103 A,B,C,D

This means, up to 20210101, we have (A,B and C), 20210102 -> (A,B,C), 20210103 -> (A,B,C,D)

Thanks.

CodePudding user response:

It depends on dialect/DBMS. Try this example It is partially ANSI SQL, partially MySQL

CodePudding user response:

your example data not match with the results... sql server solution below

CREATE TABLE inventory (
  timestamp varchar(8),
  item varchar(1)
 );
 
 insert into inventory(timestamp, item) values
('20210101','A'),
('20210101','A'),
('20210101','B'),
('20210101','C'),
('20210102','A'),
('20210103','B'),
('20210103','D');


select timestamp,  sum(qtd) as qtd, string_agg(item,',') items
from(
select 
 timestamp, item, 1 qtd
 from inventory
 group by timestamp, item) s
 group by timestamp
  • Related