sql - Oracle运行总计

使用PLSQL寻找2种不同小计的建议。

我需要提取一个数据集，其中包括：1）唯一的人数，以及2）总学分，作为一段时间内的总运行时间。

原始数据：
这是交易数据-每次学生注册或课程时，都会插入一条记录，其中包含日期，学生证和学分（以及课程号和许多其他相关数据）。每个学生每门课程一项记录。

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

粗暴的方法是编写一堆单独的SELECTS（每天一个），然后将它们联合在一起。例如：

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（）函数。 RSTS_DATE虽然在您执行SELECT时似乎只是一天/月/年的值，但实际上也保存着时间，因此我得到的结果集不仅是整个日期的总和，而且还不是时间的总和-因此，我每天处理的数据不是每天一条记录，而是返回数百条记录（每个单独的课程注册一个）。例如：

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方面并不强壮，但是如果有人可以给我一些指导-甚至只是给Google留言，我将不胜感激。

谢谢

最佳答案

试试这个：

WITH CRdata AS
(
    SELECT COUNT(DISTINCT STUDENT_ID) AS UniqueHeadcount,
    SUM(CREDIT_HR)                     AS SumCredits,
    TRUNC(RSTS_DATE) RSTS_DATE
     FROM REGISTRATIONS
    GROUP BY TRUNC(RSTS_DATE)
)
 SELECT SUM(UniqueHeadcount) OVER(ORDER BY RSTS_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS UniqueHeadcount,
  SUM(SumCredits) OVER(ORDER BY RSTS_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS  SumCredits,
  RSTS_DATE
  FROM CRdata

plsql

sql - Oracle运行总计