计算指定时间所在的季度数及当季的第一天和最后一天

-- 指定时间所在的季度的第一天

-- oracle写法
select trunc(to_date('2018-08-02'), 'Q') from dual;

SQL实现:

-- hive写法
select
     day
    ,floor(month(day)/3) + 1                                                         as season_error
    ,ceil(month(day)/3)                                                              as season_num
    ,lpad(ceil(month(day)/3),2,0)                                                    as season_num
    ,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01'))        as season_of_first_day
    ,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_of_last_day
    ,case
        when month(day) <= 3 then to_date(concat(year(day),'-01-01'))
        when month(day) <= 6 then to_date(concat(year(day),'-04-01'))
        when month(day) <= 9 then to_date(concat(year(day),'-07-01'))
        when month(day) <=12 then to_date(concat(year(day),'-10-01'))
    end as season_of_first_day
from (
    select '2018-01-02' as day union all
    select '2018-02-02' as day union all
    select '2018-03-02' as day union all
    select '2018-04-02' as day union all
    select '2018-05-02' as day union all
    select '2018-06-02' as day union all
    select '2018-07-02' as day union all
    select '2018-08-02' as day union all
    select '2018-09-02' as day union all
    select '2018-10-02' as day union all
    select '2018-11-02' as day union all
    select '2018-12-02' as day
) t1
;

+-------------+---------------+-------------+-------------+----------------------+---------------------+----------------------+--+
|     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}