sql把一段时间分割成周，月，季度，年的时间段

 
--本周 
select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,  
          TO_CHAR(trunc(CREATE_DATE, 'd')+ 1,'yyyy-MM-dd')  as START_DATE ,  
          TO_CHAR(trunc(CREATE_DATE, 'd')+ 7,'yyyy-MM-dd') as END_DATE 
          from BG_PROJECT_INFO;
--本月
select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,  
          TO_CHAR(trunc(CREATE_DATE, 'mm'),'yyyy-MM-dd')  as START_DATE ,  
          TO_CHAR(last_day(trunc(CREATE_DATE, 'mm')),'yyyy-MM-dd') as END_DATE   
          from BG_PROJECT_INFO;
--本季 
select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,  
          TO_CHAR(trunc(CREATE_DATE, 'Q'),'yyyy-MM-dd')  as START_DATE ,  
          TO_CHAR(add_months(trunc(CREATE_DATE, 'Q'),3)-1,'yyyy-MM-dd') as END_DATE   
          from BG_PROJECT_INFO;
--本年
select    TO_CHAR(CREATE_DATE   ,'yyyy-MM-dd')as NEW_DATE   ,  
          TO_CHAR(trunc(CREATE_DATE, 'yyyy'),'yyyy-MM-dd')  as START_DATE ,  
          TO_CHAR(add_months(trunc(CREATE_DATE, 'yyyy'),12)-1,'yyyy-MM-dd') as END_DATE   
          from BG_PROJECT_INFO;

select to_char(t.CREATE_DATE, 'YYYY-MM-DD') as 天,  t.PLAN_HOURS  
from BG_PROJECT_INFO t


---按天统计 
select to_char(t.CREATE_DATE+15/24, 'YYYY-MM-DD') as 天 ,sum(t.PLAN_HOURS) as 数量
from BG_PROJECT_INFO t
WHERE
    1=1
group by to_char(t.CREATE_DATE+15/24, 'YYYY-MM-DD') 
ORDER by 天 NULLS  LAST;

--按周统计 
select to_char(next_day(t.CREATE_DATE+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(t.PLAN_HOURS) as 数量
from BG_PROJECT_INFO t
WHERE
     1=1
group by to_char(next_day(t.CREATE_DATE+15/24 - 7,2),'YYYY-MM-DD')
ORDER BY 周;

--按月统计 
select to_char(t.CREATE_DATE,'YYYY-MM') as 月份,sum(t.PLAN_HOURS) as 数量
from BG_PROJECT_INFO t
WHERE
      1=1
GROUP BY
    to_char(t.CREATE_DATE,'YYYY-MM')  
ORDER BY 月份;

-- 按季统计 
select to_char(t.CREATE_DATE,'q') 季度,sum(t.PLAN_HOURS) as 数量
from BG_PROJECT_INFO t
WHERE
   1=1
group by to_char(t.CREATE_DATE,'q')
ORDER BY 季度 NULLS  LAST;

--按年统计 
select to_char(t.CREATE_DATE,'yyyy') AS 年度,sum(t.PLAN_HOURS) as 数量
from BG_PROJECT_INFO t
WHERE
    1=1
group by to_char(t.CREATE_DATE,'yyyy')
ORDER BY 年度;




--按照周一到周日为一周算周的开始时间和结束时间（IW）自然周
WITH PARAMS AS (SELECT TRUNC(TO_DATE('2009-01-01','YYYY-MM-DD'),'YYYY') AS SD FROM DUAL)
SELECT LEVEL 周次,
DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,
NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2))
 当周第一天,
DECODE(SIGN(5-DECODE(TO_CHAR(PM.SD,'D'),'1','7',TO_CHAR(PM.SD,'D'))),-1,
NEXT_DAY(PM.SD+(LEVEL-1)*7,2),NEXT_DAY(PM.SD+(LEVEL-1)*7-7,2)) + 6
当周最后一天
FROM DUAL D
LEFT JOIN PARAMS PM ON 1=1
CONNECT BY LEVEL<=53
--按照周日到周六为一周算周的开始时间和结束时间（D）
SELECT LEVEL 周次,(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7  当周第一天,
    (TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY')-7) + (7-TO_CHAR(TRUNC(TO_DATE('2011-01-01','YYYY-MM-DD'),'YYYY'),'D')+1)+(LEVEL-1)*7+6 当周最后一天
FROM DUAL CONNECT BY LEVEL<=53
--按照ORACLE标准（WW）
SELECT LEVEL 周次,TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7  当周第一天,
    TO_DATE('2013-01-01','YYYY-MM-DD')+(LEVEL-1)*7+
    DECODE((TO_CHAR(TO_DATE('2013-12-31','YYYY-MM-DD'),'DDD')-(LEVEL-1)*7),1,0,2,1,6) 当周最后一天
FROM DUAL CONNECT BY LEVEL<=53









WITH src
     AS (SELECT TRUNC (DATE '2018-08-01') fromdate,
                TRUNC (DATE '2019-09-01') todate
           FROM DUAL),
     dataset
     AS (    SELECT LEVEL AS seq
               FROM DUAL
         CONNECT BY LEVEL < (SELECT todate - fromdate FROM src)),
     datawithindices
     AS (SELECT src.*,
                dataset.*,
                CASE TO_CHAR (fromdate + seq - 1, 'DY')
                   WHEN 'SAT' THEN 1
                   WHEN 'SUN' THEN 2
                   WHEN 'MON' THEN 3
                   WHEN 'TUE' THEN 4
                   WHEN 'WED' THEN 5
                   WHEN 'THU' THEN 6
                   WHEN 'FRI' THEN 7
                END
                   indice
           FROM src, dataset),
     filt
     AS (SELECT seq, fromdate + seq - 1 nowaday, indice FROM datawithindices),
     alldata
     AS (SELECT filt.*, CEIL (indice / 7) + FLOOR (seq / 7) weekno FROM filt)
SELECT 'WEEK '||weekno week_seq, begindate, enddate
  FROM (SELECT weekno,
               FIRST_VALUE (nowaday)
                  OVER (PARTITION BY weekno ORDER BY weekno)
                  begindate,
               LAST_VALUE (nowaday)
                  OVER (PARTITION BY weekno ORDER BY weekno)
                  enddate,
               ROW_NUMBER () OVER (PARTITION BY weekno ORDER BY weekno) rn
          FROM alldata)
 WHERE rn = 1















  

 SQL ----周

-----周
SELECT   NUMS ,
     (case when TO_DATE(WEEK_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else WEEK_START_DATE end)   as    WEEKS_START_DATE ,
     (case when TO_DATE(WEEK_END_DATE, 'yyyy-mm-dd') > TO_DATE('2018-09-15', 'yyyy-mm-dd') then '2018-09-15' else WEEK_END_DATE end)   as    WEEK_END_DATE 
       
 from (
SELECT  ROWNUM as NUMS,WEEK_START_DATE,WEEK_END_DATE from(
select  WEEK_START_DATE, WEEK_END_DATE from (
select   
  ROWNUM as NUM,
  to_char(日期,'yyyy-mm-dd')   DATE_ID,
  to_char(日期,'yyyy')   YEAR_ID,
  to_char(日期,'q')   QUARTERID_ID,  
  to_char(日期,'mm')   MONTH_ID, 
  TO_CHAR(trunc(日期, 'd')+ 1,'yyyy-MM-dd') as WEEK_START_DATE ,  
  TO_CHAR(trunc(日期, 'd')+ 7,'yyyy-MM-dd') as WEEK_END_DATE,   
  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,  
  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE, 
  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'Q'),3)-1,'yyyy-MM-dd') as QUARTERID_END_DATE,   
  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'yyyy'),12)-1,'yyyy-MM-dd') as YEAR_END_DATE 
  from(   
     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)   日期  
     from   ALL_OBJECTS   
     where   rownum<9999  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)<=to_date('2018-09-15','yyyy-mm-dd')   
  )  
) WHERE
      1=1
GROUP BY  WEEK_START_DATE,WEEK_END_DATE
ORDER BY WEEK_START_DATE
)
)

 SQL ----月

-----月
SELECT   NUMS ,
     (case when TO_DATE(MONTH_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else MONTH_START_DATE end)   as    MONTH_START_DATE ,
     (case when TO_DATE(MONTH_END_DATE, 'yyyy-mm-dd') > TO_DATE('2018-09-15', 'yyyy-mm-dd') then '2018-09-15' else MONTH_END_DATE end)   as    MONTH_END_DATE 
       
 from (
SELECT  ROWNUM as NUMS,MONTH_START_DATE,MONTH_END_DATE from(
select  MONTH_START_DATE, MONTH_END_DATE from (
select   
  ROWNUM as NUM,
  to_char(日期,'yyyy-mm-dd')   DATE_ID,
  to_char(日期,'yyyy')   YEAR_ID,
  to_char(日期,'q')   QUARTERID_ID,  
  to_char(日期,'mm')   MONTH_ID, 
  TO_CHAR(trunc(日期, 'd')+ 1,'yyyy-MM-dd') as WEEK_START_DATE ,  
  TO_CHAR(trunc(日期, 'd')+ 7,'yyyy-MM-dd') as WEEK_END_DATE,   
  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,  
  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE, 
  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'Q'),3)-1,'yyyy-MM-dd') as QUARTERID_END_DATE,   
  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'yyyy'),12)-1,'yyyy-MM-dd') as YEAR_END_DATE 
  from(   
     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)   日期  
     from   ALL_OBJECTS   
     where   rownum<9999  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)<=to_date('2018-09-15','yyyy-mm-dd')   
  )  
) WHERE
      1=1
GROUP BY  MONTH_START_DATE,MONTH_END_DATE
ORDER BY MONTH_START_DATE
)
)

 SQL ----季节

SELECT   NUMS ,
     (case when TO_DATE(QUARTERID_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else QUARTERID_START_DATE end)   as    QUARTERID_START_DATE ,
     (case when TO_DATE(QUARTERID_END_DATE, 'yyyy-mm-dd') > TO_DATE('2019-09-15', 'yyyy-mm-dd') then '2019-09-15' else QUARTERID_END_DATE end)   as    QUARTERID_END_DATE 
       
 from (
SELECT  ROWNUM as NUMS,QUARTERID_START_DATE,QUARTERID_END_DATE from(
select  QUARTERID_START_DATE, QUARTERID_END_DATE from (
select   
  ROWNUM as NUM,
  to_char(日期,'yyyy-mm-dd')   DATE_ID,
  to_char(日期,'yyyy')   YEAR_ID,
  to_char(日期,'q')   QUARTERID_ID,  
  to_char(日期,'mm')   MONTH_ID, 
  TO_CHAR(trunc(日期, 'd')+ 1,'yyyy-MM-dd') as WEEK_START_DATE ,  
  TO_CHAR(trunc(日期, 'd')+ 7,'yyyy-MM-dd') as WEEK_END_DATE,   
  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,  
  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE, 
  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'Q'),3)-1,'yyyy-MM-dd') as QUARTERID_END_DATE,   
  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'yyyy'),12)-1,'yyyy-MM-dd') as YEAR_END_DATE 
  from(   
     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)   日期  
     from   ALL_OBJECTS   
     where   rownum<9999  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)<=to_date('2019-09-15','yyyy-mm-dd')   
  )  
) WHERE
      1=1
GROUP BY  QUARTERID_START_DATE,QUARTERID_END_DATE
ORDER BY QUARTERID_START_DATE
)
)

SQL ----年

-----年
SELECT   NUMS ,
     (case when TO_DATE(YEAR_START_DATE, 'yyyy-mm-dd') < TO_DATE('2018-08-01', 'yyyy-mm-dd') then '2018-08-01' else YEAR_START_DATE end)   as    YEAR_START_DATE ,
     (case when TO_DATE(YEAR_END_DATE, 'yyyy-mm-dd') > TO_DATE('2019-09-15', 'yyyy-mm-dd') then '2019-09-15' else YEAR_END_DATE end)   as    YEAR_END_DATE 
       
 from (
SELECT  ROWNUM as NUMS,YEAR_START_DATE,YEAR_END_DATE from(
select  YEAR_START_DATE, YEAR_END_DATE from (
select   
  ROWNUM as NUM,
  to_char(日期,'yyyy-mm-dd')   DATE_ID,
  to_char(日期,'yyyy')   YEAR_ID,
  to_char(日期,'q')   QUARTERID_ID,  
  to_char(日期,'mm')   MONTH_ID, 
  TO_CHAR(trunc(日期, 'd')+ 1,'yyyy-MM-dd') as WEEK_START_DATE ,  
  TO_CHAR(trunc(日期, 'd')+ 7,'yyyy-MM-dd') as WEEK_END_DATE,   
  TO_CHAR(trunc(日期, 'mm'),'yyyy-MM-dd')  as MONTH_START_DATE ,  
  TO_CHAR(last_day(trunc(日期, 'mm')),'yyyy-MM-dd') as MONTH_END_DATE, 
  TO_CHAR(trunc(日期, 'Q'),'yyyy-MM-dd')  as QUARTERID_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'Q'),3)-1,'yyyy-MM-dd') as QUARTERID_END_DATE,   
  TO_CHAR(trunc(日期, 'yyyy'),'yyyy-MM-dd')  as YEAR_START_DATE ,  
  TO_CHAR(add_months(trunc(日期, 'yyyy'),12)-1,'yyyy-MM-dd') as YEAR_END_DATE 
  from(   
     select   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)   日期  
     from   ALL_OBJECTS   
     where   rownum<9999  and   to_date('2018-08-01','yyyy-mm-dd')+(rownum-1)<=to_date('2019-09-15','yyyy-mm-dd')   
  )  
) WHERE
      1=1
GROUP BY  YEAR_START_DATE,YEAR_END_DATE
ORDER BY YEAR_START_DATE
)
)