昨晚SP跑完之后,发现部分数据有问题,看了下过程,是在做数据累计时出现的问题。于是在网上找到了一种好方法,以下简要说明:
比如多个工厂(comp),每个工厂每天有多条流水线(line)进行生产,现在按工厂及日期(crtdate)进行统计生产数量(qty),并对数量按工厂及日期进行累计
原表查询信息
SELECT COMP, CREDATE, LINE, QTY FROM PRO_LOG ORDER BY COMP, CREDATE;
结果:
COMP CRTDATE LINE QTY
----- -------------- ---- ----
SSS 2008-01-01 1 4
SSS 2008-01-02 1 4
SSS 2008-01-03 1 4
SSS 2008-01-03 2 4
TTT 2008-01-01 1 1
TTT 2008-01-01 2 2
TTT 2008-01-02 1 3
XXX 2008-01-01 5 9
XXX 2008-02-01 1 7
9 rows selected
一、当不需要对生产数量(qty)按工厂及日期进行累计时,可以简单的用group by处理
查询SQL:
SELECT COMP, CREDATE, SUM(QTY) FROM PRO_LOG ORDER BY COMP, CREDATE;
结果:
COMP CRTDATE QTY
----- ----------- -----
SSS 2008-01-01 4
SSS 2008-01-02 4
SSS 2008-01-03 8
TTT 2008-01-01 3
TTT 2008-01-02 3
XXX 2008-01-01 9
XXX 2008-02-01 7
7 rows selected
二、当需要对生产数量(qty)按工厂及日期(月为单位)进行累计时,可以使用SUM() OVER()
查询SQL:
SELECT COMP,
CRTDATE,
QTY,
SUM(QTY) OVER(PARTITION BY COMP, TO_CHAR(CRTDATE, 'YYYYMM') ORDER BY COMP, CRTDATE) TOTAL
FROM (SELECT COMP, CRTDATE, SUM(QTY) QTY
FROM PRO_LOG
GROUP BY COMP, CRTDATE) T;
/*TO_CHAR(CRTDATE, 'YYYYMM') 以月为单位,故做此处理*/
结果:
COMP CRTDATE QTY TOTAL
------- ----------- ------- -----
SSS 2008-01-01 4 4
SSS 2008-01-02 4 8
SSS 2008-01-03 8 16
TTT 2008-01-01 3 3
TTT 2008-01-02 3 6
XXX 2008-01-01 9 9
XXX 2008-02-01 7 7
7 rows selected