sql - Oracle 运行总计

Question

使用 PLSQL 寻找具有 2 种不同类型小计的建议。

我需要提取一个数据集，其中包含 1) 唯一的人数和 2) 学分总数，作为一段时间内的运行总数。

原始数据:
这是交易数据——每次学生注册或类(class)时，都会插入一条记录，其中包含日期、学生 ID 和学分(以及类(class)编号和一堆其他相关数据)。每个学生每门类(class)一条记录。

STUDENT_ID   CREDITS   DATE
1            3         01-JAN-12
1            2         02-JAN-12
57           1         03-JAN-12
1            1         03-JAN-12

处理数据:
这是老板需要看到的——它将用于稍后的趋势(例如，查看今年的 Jan-01 与去年的 Jan-01 的对比情况等)。

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 时它似乎只是一个日/月/年的值，但实际上也包含时间，所以我得到的结果集不仅仅是简单地在日期上求和，而且随着时间的推移——因此，我处理的数据不是每天一条记录，而是每天返回数百条记录(每个单独的类(class)注册一条)。例如:

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 方面并不强，但如果有人能给我一些指导——即使只是对谷歌的话，我会很感激你的帮助。

谢谢

Answer 1

试试这个:

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