Oracle运行总计

Question

Oracle运行总计

3

寻求使用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方面并不强大，但如果有人能给我一些方向 - 即使只是一些需要搜索的关键词，我会感激帮助。

谢谢

- Chains

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Chandu · Accepted Answer

试试这个：

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