计算指定时间所在的季度数及当季的第一天和最后一天
-- 指定时间所在的季度的第一天
1 -- oracle写法 2 select trunc(to_date('2018-08-02'), 'Q') from dual;
SQL实现:
1 -- hive写法 2 select 3 day 4 ,floor(month(day)/3) + 1 as season_error 5 ,ceil(month(day)/3) as season_num 6 ,lpad(ceil(month(day)/3),2,0) as season_num 7 ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01')) as season_of_first_day 8 ,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_of_last_day 9 ,case 10 when month(day) <= 3 then to_date(concat(year(day),'-01-01')) 11 when month(day) <= 6 then to_date(concat(year(day),'-04-01')) 12 when month(day) <= 9 then to_date(concat(year(day),'-07-01')) 13 when month(day) <=12 then to_date(concat(year(day),'-10-01')) 14 end as season_of_first_day 15 from ( 16 select '2018-01-02' as day union all 17 select '2018-02-02' as day union all 18 select '2018-03-02' as day union all 19 select '2018-04-02' as day union all 20 select '2018-05-02' as day union all 21 select '2018-06-02' as day union all 22 select '2018-07-02' as day union all 23 select '2018-08-02' as day union all 24 select '2018-09-02' as day union all 25 select '2018-10-02' as day union all 26 select '2018-11-02' as day union all 27 select '2018-12-02' as day 28 ) t1 29 ;
+-------------+---------------+-------------+-------------+----------------------+---------------------+----------------------+--+ | day | season_error | season_num | season_num | season_of_first_day | season_of_last_day | season_of_first_day | +-------------+---------------+-------------+-------------+----------------------+---------------------+----------------------+--+ | 2018-01-02 | 1 | 1 | 01 | 2018-01-01 | 2018-03-31 | 2018-01-01 | | 2018-02-02 | 1 | 1 | 01 | 2018-01-01 | 2018-03-31 | 2018-01-01 | | 2018-03-02 | 2 | 1 | 01 | 2018-01-01 | 2018-03-31 | 2018-01-01 | | 2018-04-02 | 2 | 2 | 02 | 2018-04-01 | 2018-06-30 | 2018-04-01 | | 2018-05-02 | 2 | 2 | 02 | 2018-04-01 | 2018-06-30 | 2018-04-01 | | 2018-06-02 | 3 | 2 | 02 | 2018-04-01 | 2018-06-30 | 2018-04-01 | | 2018-07-02 | 3 | 3 | 03 | 2018-07-01 | 2018-09-30 | 2018-07-01 | | 2018-08-02 | 3 | 3 | 03 | 2018-07-01 | 2018-09-30 | 2018-07-01 | | 2018-09-02 | 4 | 3 | 03 | 2018-07-01 | 2018-09-30 | 2018-07-01 | | 2018-10-02 | 4 | 4 | 04 | 2018-10-01 | 2018-12-31 | 2018-10-01 | | 2018-11-02 | 4 | 4 | 04 | 2018-10-01 | 2018-12-31 | 2018-10-01 | | 2018-12-02 | 5 | 4 | 04 | 2018-10-01 | 2018-12-31 | 2018-10-01 | +-------------+---------------+-------------+-------------+----------------------+---------------------+----------------------+--+
2. 创建相应的日期维表可以实现
3. 通过shell可以实现
inc_start='20181112' IncStart=$inc_start IncStartYear=`echo ${IncStart:0:4}`; IncStartMonth=`echo ${IncStart:4:2}`; IncStartQuarter=${IncStartYear}"Q"$(((10#${IncStartMonth}-1)/3+1)); echo ${IncStartQuarter}
