Cumulative average and count over occurrences increasing in time-CodePudding

I am looking to calculate an average (over number of occurrences) and observation count over increasing dates per instance (take customer as an example instance) in Oracle SQL.

So the count will increase as date goes up, the average could go up or down.

I can do it for an individual case and a fixed time interval, but I would like to see a series for every customer, with every row a separate date where a sale occurred. Right now, I have a single row per customer. Here is the SQL summarizing the average and count for a fixed time interval:

SELECT AVG(bought_usd) as avg_bought
     , COUNT(*) as num_of_interactions
     , cust_id
FROM   salesTable
WHERE  obsdate >= DATE('2000-01-01')
AND    obsdate <= DATE('2022-01-01')
GROUP BY cust_id

So for an input of:

the output should look like:

CodePudding user response：

Use analytic functions:

SELECT "DATE",
       cust,
       AVG(bought_usd) OVER (PARTITION BY cust ORDER BY "DATE") AS avg,
       COUNT(*) OVER (PARTITION BY cust ORDER BY "DATE") AS cnt
FROM   salestable
ORDER BY cust, "DATE"

Note: DATE is a reserved word. You should not use it as an identifier.

Which, for the sample data:

CREATE TABLE salestable ("DATE", cust, bought_usd) AS
SELECT DATE '2010-10-01', 'Cust A', 100 FROM DUAL UNION ALL
SELECT DATE '2010-12-18', 'Cust A',  50 FROM DUAL UNION ALL
SELECT DATE '2010-12-18', 'Cust B', 120 FROM DUAL UNION ALL
SELECT DATE '2011-10-01', 'Cust B', 180 FROM DUAL;

Outputs:

DATE CUST AVG CNT

2010-10-01 00:00:00 Cust A 100 1

2010-12-18 00:00:00 Cust A 75 2

2010-12-18 00:00:00 Cust B 120 1

2011-10-01 00:00:00 Cust B 150 2

DATE	CUST	AVG	CNT
2010-10-01 00:00:00	Cust A	100	1
2010-12-18 00:00:00	Cust A	75	2
2010-12-18 00:00:00	Cust B	120	1
2011-10-01 00:00:00	Cust B	150	2

db<>fiddle here