I am looking to calculate an average (over number of occurences) 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
db<>fiddle here