oracle 查询中的每周/每月/每季度分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9769127/
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
Weekly/monthly/quarterly grouping in query
提问by nkukhar
Lets say I have table with following columns
假设我有包含以下列的表格
1. Client - string.
2. Profit - integer.
3. Deal_Date - date.
I need query that will retrieve sum of profit breakdown by week/month/quater etc.
我需要按周/月/季度等检索利润明细总和的查询。
Expected output for weeks
数周的预期产出
1 row, sum (profit) of all deals that registered from (03.19.2012 - 03.12.2012).
2 row, sum (profit) of all deals that registered from (03.12.2012 - 03.05.2012).
...
n row, sum (profit) of all deals that registered from (05.17.2011 - 05.10.2011).
NOTE (dates set just for example)
注意（设置的日期只是举例）
The same for month, years, etc.
月、年等相同。
Could someone help me with such query?
有人可以帮助我进行此类查询吗？
Btw performance is very important.
顺便说一句，性能非常重要。
回答by APC
This query uses simple date formats to extract the various elements you want to track and analytics to get the sums.
此查询使用简单的日期格式来提取您想要跟踪和分析的各种元素以获取总和。
select client
, yr
, qtr
, wk
, sum ( profit ) over ( partition by client, yr) as yr_profit
, sum ( profit ) over ( partition by client, yr, qtr) as qtr_profit
, sum ( profit ) over ( partition by client, yr, wk) as wk_profit
from (
select client
, profit
, to_char(deal_date, 'yyyy') as yr
, to_char(deal_date, 'q') as qt
, to_char(deal_date, 'ww') as wk
from your_table )
/
This will produce one row for each row in the current table. So you probebly will want to wrap it in a further outer query which only returns only distinct rows.
这将为当前表中的每一行生成一行。因此，您可能希望将其包装在仅返回不同行的进一步外部查询中。
A variant would be to use rollup instead. I'm not sure how well that works when the grouping criteria aren't perfectly hierarchical (weeks don't fit neatly into quarters).
一个变体是使用 rollup 来代替。我不确定当分组标准不是完美的分层时（周不适合季度）效果如何。
select client
, yr
, qtr
, wk
, sum ( profit ) as profit
from (
select client
, profit
, to_char(deal_date, 'yyyy') as yr
, to_char(deal_date, 'q') as qt
, to_char(deal_date, 'ww') as wk
from your_table )
group by rollup ( client, yr, qtr, wk )
/
回答by Bhabani Sankar Mishra
Just make an SP and loop the code for each week or month or year as you wish.
只需制作一个 SP 并根据需要循环每周、每月或每年的代码。
