Cumulative average and count over occurrences increasing in time

Hans Roggeman 0
Viewed 38

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:

input_data

the output should look like:

output_data

oracle sql
1 Answers

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

db<>fiddle here

MT00
Related