Try adding the cust_name to the PARTITION clause! From the docs:
... the window_partition_clause divides the rows in the input based on user-provided expressions, such as aggregation functions like SUM(x). Window partitioning is similar to the GROUP BY clause except that it returns only one result row per input row.
Example:
Code: Select all
dbadmin=> SELECT * FROM avg_test ORDER BY 1, 2;
cust_name | rpt_dt | demand | supply
-----------+------------+--------+--------
AAA | 2014-01-01 | 5 | 10
AAA | 2014-02-01 | 4 | 20
AAA | 2014-03-01 | 5 | 15
AAA | 2014-04-01 | 1 | 5
AAA | 2014-05-01 | 2 | 10
AAA | 2014-06-01 | 6 | 12
BBB | 2014-02-01 | 5 | 20
BBB | 2014-03-01 | 3 | 15
BBB | 2014-04-01 | 2 | 4
BBB | 2014-05-01 | 2 | 10
BBB | 2014-06-01 | 3 | 9
BBB | 2014-07-01 | 3 | 9
(12 rows)
Code: Select all
dbadmin=> SELECT cust_name,
dbadmin-> rpt_dt,
dbadmin-> demand,
dbadmin-> supply,
dbadmin-> supply/demand month_avg,
dbadmin-> SUM(supply) OVER (PARTITION BY cust_name, EXTRACT(YEAR FROM rpt_dt ) ORDER BY rpt_dt) /
dbadmin-> DECODE(SUM(demand) OVER (PARTITION BY cust_name, EXTRACT(YEAR FROM rpt_dt) ORDER BY rpt_dt), 0, 1, SUM(demand) OVER (PARTITION BY cust_name, EXTRACT(YEAR FROM rpt_dt) ORDER BY rpt_dt)) ytd_avg,
dbadmin-> SUM(supply) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) /
dbadmin-> DECODE(SUM(demand) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0, 1, SUM(demand) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) rolling3mnth,
dbadmin-> SUM(supply) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) /
dbadmin-> DECODE(SUM(demand) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 5 PRECEDING AND CURRENT ROW), 0, 1, SUM(demand) OVER (ORDER BY cust_name, rpt_dt ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)) rolling6mnth
dbadmin-> FROM avg_test
dbadmin-> ORDER
dbadmin-> BY cust_name, rpt_dt;
cust_name | rpt_dt | demand | supply | month_avg | ytd_avg | rolling3mnth | rolling6mnth
-----------+------------+--------+--------+----------------------+----------------------+----------------------+----------------------
AAA | 2014-01-01 | 5 | 10 | 2.000000000000000000 | 2.000000000000000000 | 2.000000000000000000 | 2.000000000000000000
AAA | 2014-02-01 | 4 | 20 | 5.000000000000000000 | 3.333333333333333333 | 3.333333333333333333 | 3.333333333333333333
AAA | 2014-03-01 | 5 | 15 | 3.000000000000000000 | 3.214285714285714286 | 3.214285714285714286 | 3.214285714285714286
AAA | 2014-04-01 | 1 | 5 | 5.000000000000000000 | 3.333333333333333333 | 4.000000000000000000 | 3.333333333333333333
AAA | 2014-05-01 | 2 | 10 | 5.000000000000000000 | 3.529411764705882353 | 3.750000000000000000 | 3.529411764705882353
AAA | 2014-06-01 | 6 | 12 | 2.000000000000000000 | 3.130434782608695652 | 3.000000000000000000 | 3.130434782608695652
BBB | 2014-02-01 | 5 | 20 | 4.000000000000000000 | 4.000000000000000000 | 3.230769230769230769 | 3.565217391304347826
BBB | 2014-03-01 | 3 | 15 | 5.000000000000000000 | 4.375000000000000000 | 3.357142857142857143 | 3.500000000000000000
BBB | 2014-04-01 | 2 | 4 | 2.000000000000000000 | 3.900000000000000000 | 3.900000000000000000 | 3.473684210526315789
BBB | 2014-05-01 | 2 | 10 | 5.000000000000000000 | 4.083333333333333333 | 4.142857142857142857 | 3.550000000000000000
BBB | 2014-06-01 | 3 | 9 | 3.000000000000000000 | 3.866666666666666667 | 3.285714285714285714 | 3.333333333333333333
BBB | 2014-07-01 | 3 | 9 | 3.000000000000000000 | 3.722222222222222222 | 3.500000000000000000 | 3.722222222222222222
(12 rows)