1。聚合
SELECT
storeid
, prodid
, sales
,SUM(sales) OVER (ORDER BY sales DESC)
FROM salestbl;
结果如下:
storeid prodid sales Group Sum(sales)
----------- ------ -------------- --------------------------
1001 F 150000.00 610000.00
1001 A 100000.00 610000.00
1003 B 65000.00 610000.00
1001 C 60000.00 610000.00
1003 D 50000.00 610000.00
1002 A 40000.00 610000.00
1001 D 35000.00 610000.00
1002 C 35000.00 610000.00
1003 A 30000.00 610000.00
1002 D 25000.00 610000.00
1003 C 20000.00 610000.00
2,分组聚合
SELECT
storeid
,prodid
,sales
,SUM(sales) OVER (PARTITION BY prodid ORDER BY sales DESC)
FROM salestbl ;
结果如下:
storeid prodid sales Group Sum(sales)
------ ---------- -------------- --------------------------
1001 A 100000.00 170000.00
1002 A 40000.00 170000.00
1003 A 30000.00 170000.00
1003 B 65000.00 65000.00
1001 C 60000.00 115000.00
1002 C 35000.00 115000.00
1003 C 20000.00 115000.00
1003 D 50000.00 110000.00
1001 D 35000.00 110000.00
1002 D 25000.00 110000.00
1001 F 150000.00 150000.00
PARTITION :分组依据
ORDER BY :排序