oracle 如何使用 pl/sql 按周汇总记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4806348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I sum records by week using pl/sql?
提问by Gary.Ray
I have a query in Oracle for a report that looks like this:
我在 Oracle 中查询了如下所示的报告：
SELECT TRUNC (created_dt) created_dt
, COUNT ( * ) AllClaims
, SUM(CASE WHEN filmth_cd in ('T', 'C') THEN 1 ELSE 0 END ) CT
, SUM(CASE WHEN filmth_cd = 'W' THEN 1 ELSE 0 END ) Web
, SUM(CASE WHEN filmth_cd = 'I' THEN 1 ELSE 0 END ) Icon
FROM claims c
WHERE c.clsts_cd NOT IN ('IN', 'WD')
AND TRUNC (created_dt) between
to_date('1/1/2006', 'dd/mm/yyyy') AND
to_date('1/1/2100', 'dd/mm/yyyy')
GROUP BY TRUNC (created_dt)
ORDER BY TRUNC (created_dt) DESC;
It returns data like this:
它返回这样的数据：
Create_Dt AllClaims CT Web Icon
1/26/2011 675 356 285 34
1/25/2011 740 322 379 39
...
What I need is a result set that sums all of the daily values into a weekly value. I am pretty new to PL/SQL and not sure where to begin.
我需要的是一个结果集，它将所有每日值汇总为每周值。我对 PL/SQL 很陌生，不知道从哪里开始。
回答by Justin Cave
Something like
就像是
SELECT TRUNC (created_dt, 'IW') created_dt
, COUNT ( * ) AllClaims
, SUM(CASE WHEN filmth_cd in ('T', 'C') THEN 1 ELSE 0 END ) CT
, SUM(CASE WHEN filmth_cd = 'W' THEN 1 ELSE 0 END ) Web
, SUM(CASE WHEN filmth_cd = 'I' THEN 1 ELSE 0 END ) Icon
FROM claims c
WHERE c.clsts_cd NOT IN ('IN', 'WD')
AND TRUNC (created_dt) between
to_date('1/1/2006', 'dd/mm/yyyy') AND
to_date('1/1/2100', 'dd/mm/yyyy')
GROUP BY TRUNC (created_dt, 'IW')
ORDER BY TRUNC (created_dt, 'IW') DESC;
will aggregate the data based on the first day of the ISO week.
将根据 ISO 周的第一天聚合数据。
回答by eamo
Here is a simple example that I quite often use:
这是我经常使用的一个简单示例：
SELECT
TO_CHAR(created_dt,'WW'),
max(created_dt),
COUNT(*)
from MY_TABLE
group by
TO_CHAR(created_dt,'WW');
The to_char(created_dt,'WW') create the groups, and the max(created_dt) displays the last day of the week. Use min(created_dt) if you want to display the first day of the week.
to_char(created_dt,'WW') 创建组，max(created_dt) 显示一周的最后一天。如果要显示一周的第一天，请使用 min(created_dt)。
