Oracle SQL - 按周汇总和分组数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25259633/
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
Oracle SQL - Sum and group data by week
提问by royskatt
I have records related to dates:
我有与日期相关的记录：
DATE AMOUNT
16.03.2013 3
16.03.2013 4
16.03.2013 1
16.03.2013 3
17.03.2013 4
17.03.2014 3
I know how to sum them up for each day, but how could I sum them up by week?`
我知道如何每天总结它们，但我怎么能按周总结它们呢？`
回答by ntalbs
You can use TRUNCfunction to truncate date to the first day of week. There are a few ways of defining week. For example, if you want to treat that the first day of week is Monday, you can IWformat, like this:
您可以使用TRUNC函数将日期截断为一周的第一天。有几种定义周的方法。例如，如果您想将一周的第一天视为星期一，则可以IW格式化，如下所示：
select trunc(date, 'IW') week, sum(amount)
from YourTable
group by trunc(date, 'IW');
You can also TO_CHARfunction as the "@Vignesh Kumer"'s answer.
您还TO_CHAR可以充当“@Vignesh Kumer”的答案。
The point is that you should truncate the date in the same week into one value. Then group by the value. That's it.
关键是您应该将同一周的日期截断为一个值。然后按值分组。就是这样。
回答by Vignesh Kumar A
Try this
尝试这个
SELECT to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW'),SUM(AMOUNT)
FROM YourTable
GROUP BY to_char(DATE - 7/24,'IYYY'), to_char(DATE - 7/24,'IW')
Output would be:
输出将是：
+-----+-------+--------+
|YEAR | WEEK | AMOUNT |
+-----+-------+--------+
|2013 | 11 | 18 |
|2013 | 13 | 3 |
+-----+-------+--------+
回答by Manoj Kumar
I guess this would help as well....
我想这也会有所帮助......
/* Weekly sum of values */
SELECT SUM( Amount ) as Sum_Amt,
DATEPART (wk, Date) as WeekNum
FROM databse_name.table_name
GROUP BY DATEPART (wk, Date)
ORDER BY WeekNum
/* Monthly sum of values */
SELECT SUM( Amount ) as Sum_Amt,
DATEPART (mm, Date) as MonNum
FROM databse_name.table_name
GROUP BY DATEPART (mm, Date)
ORDER BY MonNum
