获取全年/淡季/旺季

全年/淡季/旺季

-- 全年
with x1 as (
select
    months_sub(now(),1) as month1,
    months_sub(now(),2) as month2,
    months_sub(now(),3) as month3,
    months_sub(now(),4) as month4,
    months_sub(now(),5) as month5,
    months_sub(now(),6) as month6,
    months_sub(now(),7) as month7,
    months_sub(now(),8) as month8,
    months_sub(now(),9) as month9,
    months_sub(now(),10) as month10,
    months_sub(now(),11) as month11,
    months_sub(now(),12) as month12
)
, x2 as (
select month1 as season1 from x1
union all
select month2 as season1 from x1
union all
select month3 as season1 from x1
union all
select month4 as season1 from x1
union all
select month5 as season1 from x1
union all
select month6 as season1 from x1
union all
select month7 as season1 from x1
union all
select month8 as season1 from x1
union all
select month9 as season1 from x1
union all
select month10 as season1 from x1
union all
select month11 as season1 from x1
union all
select month12 as season1 from x1
)
, x3 as (
select 'all' as style, from_timestamp(season1, 'yyyyMM') as season1 from x2
)
-- select * from x3
-- 淡季
-- with y1 as (
, y1 as (
select
    months_sub(now(),1) as month1,
    months_sub(now(),2) as month2,
    months_sub(now(),3) as month3,
    months_sub(now(),4) as month4,
    months_sub(now(),5) as month5,
    months_sub(now(),6) as month6,
    months_sub(now(),7) as month7,
    months_sub(now(),8) as month8,
    months_sub(now(),9) as month9,
    months_sub(now(),10) as month10,
    months_sub(now(),11) as month11,
    months_sub(now(),12) as month12
)
, y2 as (
select case when month(month1) in (1,2,5,6,7,8) then month1 else null end as season2 from y1
union all
select case when month(month2) in (1,2,5,6,7,8) then month2 else null end as season2 from y1
union all
select case when month(month3) in (1,2,5,6,7,8) then month3 else null end as season2 from y1
union all
select case when month(month4) in (1,2,5,6,7,8) then month4 else null end as season2 from y1
union all
select case when month(month5) in (1,2,5,6,7,8) then month5 else null end as season2 from y1
union all
select case when month(month6) in (1,2,5,6,7,8) then month6 else null end as season2 from y1
union all
select case when month(month7) in (1,2,5,6,7,8) then month7 else null end as season2 from y1
union all
select case when month(month8) in (1,2,5,6,7,8) then month8 else null end as season2 from y1
union all
select case when month(month9) in (1,2,5,6,7,8) then month9 else null end as season2 from y1
union all
select case when month(month10) in (1,2,5,6,7,8) then month10 else null end as season2 from y1
union all
select case when month(month11) in (1,2,5,6,7,8) then month11 else null end as season2 from y1
union all
select case when month(month12) in (1,2,5,6,7,8) then month12 else null end as season2 from y1
)
, y3 as (
select 'off' as style, from_timestamp(season2, 'yyyyMM') as season2 from y2
where season2 is not null
order by from_timestamp(season2, 'yyyyMM')
)
-- select * from y3
-- 旺季
-- with z1 as (
, z1 as (
select
    months_sub(now(),1) as month1,
    months_sub(now(),2) as month2,
    months_sub(now(),3) as month3,
    months_sub(now(),4) as month4,
    months_sub(now(),5) as month5,
    months_sub(now(),6) as month6,
    months_sub(now(),7) as month7,
    months_sub(now(),8) as month8,
    months_sub(now(),9) as month9,
    months_sub(now(),10) as month10,
    months_sub(now(),11) as month11,
    months_sub(now(),12) as month12
)
, z2 as(
select case when month(month1) in (3,4,9,10,11,12) then month1 else null end as season3 from z1
union all
select case when month(month2) in (3,4,9,10,11,12) then month2 else null end as season3 from z1
union all
select case when month(month3) in (3,4,9,10,11,12) then month3 else null end as season3 from z1
union all
select case when month(month4) in (3,4,9,10,11,12) then month4 else null end as season3 from z1
union all
select case when month(month5) in (3,4,9,10,11,12) then month5 else null end as season3 from z1
union all
select case when month(month6) in (3,4,9,10,11,12) then month6 else null end as season3 from z1
union all
select case when month(month7) in (3,4,9,10,11,12) then month7 else null end as season3 from z1
union all
select case when month(month8) in (3,4,9,10,11,12) then month8 else null end as season3 from z1
union all
select case when month(month9) in (3,4,9,10,11,12) then month9 else null end as season3 from z1
union all
select case when month(month10) in (3,4,9,10,11,12) then month10 else null end as season3 from z1
union all
select case when month(month11) in (3,4,9,10,11,12) then month11 else null end as season3 from z1
union all
select case when month(month12) in (3,4,9,10,11,12) then month12 else null end as season3 from z1
)
, z3 as (
select 'peak' as style, from_timestamp(season3, 'yyyyMM') as season3 from z2
where season3 is not null
order by from_timestamp(season3, 'yyyyMM')
)
-- select * from z3
select * from x3 union all
select * from y3 union all
select * from z3