Oracle segmentation in the query-CodePudding

How months according to custom in oracle data, such as monthly checkout time for last month to the 25th of this month, 26 statistical turnover per month

CodePudding user response:

To date processing, 25 days after adjustment for normal date style to do processing

 with mon_yye 
As 
(
The select to_date (' 20160125 ', 'yyyymmdd') date1, 100 yye from dual union all 
The select to_date (' 20160126 ', 'yyyymmdd'), 200 from dual union all 
The select to_date (' 20160131 ', 'yyyymmdd'), 200 from dual union all 
The select to_date (' 20160225 ', 'yyyymmdd'), 300 from dual union all 
The select to_date (' 20160226 ', 'yyyymmdd'), 400 from dual union all 
The select to_date (' 20160227 ', 'yyyymmdd'), 500 from dual union all 
The select to_date (' 20160228 ', 'yyyymmdd'), 500 from dual union all 
The select to_date (' 20160229 ', 'yyyymmdd'), 500 from dual union all 
The select to_date (' 20160326 ', 'yyyymmdd'), 600 from dual) 
The select to_char (add_months (a. d. ate1-25, 1), 'YYYYMM') date2, sum (a.y ye) yye 
The from mon_yye a 
Group by to_char (add_months (a. d. ate1-25, 1), 'YYYYMM') 
The order by to_char (add_months (a. d. ate1-25, 1), 'YYYYMM')

CodePudding user response:

Oracle has a function, add_months, increase specified date month

 select add_months (to_date (' 2017-01-25 ', '- dd yyyy - mm), 1) from dual

Back to the 2017-02-25

According to your demand, that is, the statistical date between to_date (' 2017-01-25 ', '- dd yyyy - mm) and add_months (to_date (' 2017-01-25', '- dd yyyy - mm), 1)

CodePudding user response:

Borrow # 1 data a

With mon_yye
As
(
The select to_date (' 20160125 ', 'yyyymmdd') date1, 100 yye from dual union all
The select to_date (' 20160126 ', 'yyyymmdd'), 200 from dual union all
The select to_date (' 20160131 ', 'yyyymmdd'), 200 from dual union all
The select to_date (' 20160225 ', 'yyyymmdd'), 300 from dual union all
The select to_date (' 20160226 ', 'yyyymmdd'), 400 from dual union all
The select to_date (' 20160227 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160228 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160229 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160326 ', 'yyyymmdd'), 600 from dual)
The select t.m onth_s, SUM (t.y Lin ye) from (
Select
The case when a. d. ate1 between a. d. ate1 and add_months (a. d. ate1, 1) + 1
Then to_char (add_months (a. d. ate1, 1), 'YYYYMM')
End month_s - if time in 26 to 25 next month interval, then take out in
T, a.y ye from mon_yye a)
Group by t.m onth_s
The order by t.m onth_s;

CodePudding user response:

I'm sorry, just logic is a little problem, refer to the

With mon_yye
As
(
The select to_date (' 20160120 ', 'yyyymmdd') date1, 100 yye from dual union all
The select to_date (' 20160125 ', 'yyyymmdd') date1, 100 from dual union all
The select to_date (' 20160126 ', 'yyyymmdd'), 200 from dual union all
The select to_date (' 20160131 ', 'yyyymmdd'), 200 from dual union all
The select to_date (' 20160225 ', 'yyyymmdd'), 300 from dual union all
The select to_date (' 20160226 ', 'yyyymmdd'), 400 from dual union all
The select to_date (' 20160227 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160228 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160229 ', 'yyyymmdd'), 500 from dual union all
The select to_date (' 20160326 ', 'yyyymmdd'), 600 from dual)

The select t.m onths, sum (t.y Lin ye) from (
Select a case when substr (to_char (a. d. ate1, 'YYYYMMDD'), 7, 2) & gt; 26 then=
To_char (add_months (a. d. ate1, 1), 'YYYYMM')
The else to_char (a. d. ate1, 'YYYYMM')
End up - if the date is greater than or equal to 26 months plus 1, whereas unchanged
The substr (to_char (a. d. ate1, 'YYYYMMDD'), 7, 2), a. d. ate1, a.y ye from mon_yye a
) t
Group by t.m onths
The order by t.m onths

CodePudding user response:

Studied, and the top