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

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

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}

 

转载于:https://www.cnblogs.com/chenzechao/p/9449933.html