如何在几个月内划分月份并在一个季度中分组月份 [英] How to partition over months and group months in a quarter

问题描述

我想在某些月份的列上应用分区，并将其分组为一个季度.

I want to apply partition over on some months columns and group them in a quarter.

Months

Jan-18
Feb-18
Mar-18
Apr-18
May-18
Jun-18
Jul-18
Aug-18
Sep-18
Oct-18
Nov-18
Dec-18

我的日历从6月开始

所以

Jun,Jul,Aug is considered as 1st quarter .
Sep,Oct,Nov is considered as 2nd quarter .
Dec,Jan,Feb is considered as 3rd quarter .
Mar,Apr,May is considered as 4th quarter .

trunc(Jan-18,'q')returns 01-Jan-18

whereas expected result is '01-Nov-18' .

我尝试过

select ADD_MONTHS(trunc(to_date('01-APR-18'), 'Q'),2) from dual;

也尝试过

select ADD_MONTHS(trunc(to_date('01-JUN-18'), 'Q'),-1) from dual;
output is '01-Mar-18',whereas expected reuslt is '01-JUN-18' .

预期结果集:

 Months     Expected result 

02-Jan-18      01-Dec-17
02-Feb-18      01-Dec-17
05-Mar-18      01-Mar-18
08-Apr-18      01-Mar-18
05-May-18      01-Mar-18
05-Jun-18      01-Jun-18
05-Jul-18      01-Jun-18
09-Aug-18      01-Jun-18
10-Sep-18      01-Sep-18
11-Oct-18      01-Sep-18
11-Nov-18      01-Sep-18
11-Dec-18      01-Dec-18

有人可以帮忙吗

推荐答案

您尝试过的解决方案的问题是您缺少一个步骤.

The problem with the solution you tried is that you are missing a step.

如果您的宿舍与标准"宿舍相差一个月，则给定日期，任何日期，您必须将日期提前一个月，然后然后截断到该季度，然后后退一个月.您执行了第2步和第3步，但缺少第1步.

If your quarters are off by one month from "standard" quarters, then - given a date, any date - you must move the date forward by one month, then truncate to the quarter and then pull back by one month. You did steps 2 and 3 but you are missing step 1.

with
  inputs ( dt ) as (
    select to_date('02-Jan-18', 'dd-Mon-yy') from dual union all
    select to_date('02-Feb-18', 'dd-Mon-yy') from dual union all
    select to_date('05-Mar-18', 'dd-Mon-yy') from dual union all
    select to_date('08-Apr-18', 'dd-Mon-yy') from dual union all
    select to_date('05-May-18', 'dd-Mon-yy') from dual union all
    select to_date('05-Jun-18', 'dd-Mon-yy') from dual union all
    select to_date('05-Jul-18', 'dd-Mon-yy') from dual union all
    select to_date('09-Aug-18', 'dd-Mon-yy') from dual union all
    select to_date('10-Sep-18', 'dd-Mon-yy') from dual union all
    select to_date('11-Oct-18', 'dd-Mon-yy') from dual union all
    select to_date('11-Nov-18', 'dd-Mon-yy') from dual union all
    select to_date('11-Dec-18', 'dd-Mon-yy') from dual
  )
select dt,
       add_months(trunc(add_months(dt, 1), 'q'), -1) as qtr
from   inputs;

DT          QTR     
---------   ---------
02-Jan-18   01-Dec-17
02-Feb-18   01-Dec-17
05-Mar-18   01-Mar-18
08-Apr-18   01-Mar-18
05-May-18   01-Mar-18
05-Jun-18   01-Jun-18
05-Jul-18   01-Jun-18
09-Aug-18   01-Jun-18
10-Sep-18   01-Sep-18
11-Oct-18   01-Sep-18
11-Nov-18   01-Sep-18
11-Dec-18   01-Dec-18

这篇关于如何在几个月内划分月份并在一个季度中分组月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！