寻求使用PLSQL实现两种不同类型的小计的建议。
我需要提取一个数据集,其中包括1)唯一的员工总数和2)学分的总数,作为随时间变化的累计总数。
原始数据:
这是交易数据--每次学生注册课程时,都会插入一条记录,其中包括日期、学生ID和学分(以及课程号和其他相关数据)。每个学生每门课程只有一条记录。
STUDENT_ID CREDITS DATE
1 3 01-JAN-12
1 2 02-JAN-12
57 1 03-JAN-12
1 1 03-JAN-12
处理后的数据:
这是老板需要查看的内容 - 它将用于后续趋势分析(例如,查看今年的1月1日与去年的1月1日相比如何表现等)。
UniqueHeadcount SumCredits Date
1 3 01-JAN-12
1 5 02-JAN-12
2 7 03-JAN-12
对此的一种粗暴方法是编写一堆单独的SELECT(每个查询一个日期),并将它们联合在一起。例如:
SELECT
COUNT(DISTINCT STUDENT_ID) as "UniqueHeadcount",
SUM(CREDIT_HR) as "SumCredits",
'01-JAN-12' as "DATE"
FROM
REGISTRATIONS
WHERE
TO_CHAR(DATE,'yyyymmdd') <= '20120101'
GROUP BY
'01-JAN-12'
UNION
SELECT
COUNT(DISTINCT STUDENT_ID) as "UniqueHeadcount",
SUM(CREDIT_HR) as "SumCredits",
'02-JAN-12' as "DATE"
FROM
REGISTRATIONS
WHERE
TO_CHAR(DATE,'yyyymmdd') <= '20120102'
GROUP BY
'02-JAN-12'
UNION
...
这样做是可以的——结果是准确的——但是你可以看到,这远非优雅。如果你必须在365天内完成它,那么...这是一项艰巨的任务。必须有更好的方法。
在我的搜索过程中,我了解到了一个可以使用的“OVER”子句,就像这样:
SELECT
COUNT(DISTINCT STUDENT_ID) OVER(ORDER BY TRUNC(RSTS_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "UniqueHeadcount",
SUM(CREDIT_HR) OVER(ORDER BY TRUNC(RSTS_DATE) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "SumCredits",
TRUNC(RSTS_DATE) as "DATE"
FROM
REGISTRATIONS
这个查询要短得多(耶)--但有两个重大问题,我还找不到解决方法。首先是它不能与COUNT DISTINCT一起使用(显然是设计上的问题?)。所以我暂时注释掉了它,但接着遇到了第二个问题:它忽略了TRUNC()函数。虽然在运行SELECT时RSTS_DATE似乎只是一个日/月/年值,但实际上它也包含时间,因此我得到的结果集不仅按日期简单求和,还按时间求和--因此,我的处理数据每天返回数百条记录(每个课程注册的记录都有一个)。例如:
UniqueHeadcount SumCredits Date
1 3 01-JAN-12
1 5 02-JAN-12
2 6 03-JAN-12 (hidden time: 07:32:27)
2 7 03-JAN-12 (hidden time: 08:01:33)
不是我要找的。
所以我正在寻找专业知识 - 如果到目前为止我解释的有意义 - 是否有另一种使用OVER子句的方法,或者也许完全应该使用PLSQL的另一个功能?如果你无法告诉我,我在PLSQL方面并不强大,但如果有人能给我一些方向 - 即使只是一些需要搜索的关键词,我会感激帮助。
谢谢