SQL to get the distinct rolling sum over a growing window-CodePudding

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