如何在Hive时间维度中计算季度首末日及cutoff对应季度起止日

字段计算逻辑 #

• quarter_last_date：当前季度最后一天，基于已算出的当前季度第一天，加3个月再减1天即可
• Firstday_quartercutoff：日期减7天后对应季度的第一天，对减7天之后的日期套用原有季度第一天的计算逻辑即可
• last_date_quarter_cutoff：日期减7天后对应季度的最后一天，基于减7天后的季度第一天加3个月再减1天即可

如果你的Hive版本在2.1及以上，可以直接用trunc(日期, 'Q')直接获取日期所属季度的第一天，写法更简洁。

修改后完整查询代码 #

with dates as (
    select date_add("2010-01-01", a.pos) as d 
    from (select posexplode(split(repeat("o", datediff(from_unixtime(unix_timestamp()), "2010-01-01")), "o"))) a
)
select 
    d as date_today,
    year(d) as year,
    CONCAT('Q',(INT((MONTH(d)-1)/3)+1)) AS QUARTER,
    YEAR(date_add(d, -7)) AS year_cutoff,
    CONCAT('Q',(INT((MONTH(date_add(d, -7))-1)/3)+1)) AS QUARTER_cutoff,
    trunc(add_months(d,-(month(d)-1)%3),'MM')  as Firstday_quarter,
    -- 新增3个字段
    date_add(add_months(trunc(add_months(d,-(month(d)-1)%3),'MM'),3),-1) as quarter_last_date,
    trunc(add_months(date_add(d, -7),-(month(date_add(d, -7))-1)%3),'MM') as Firstday_quartercutoff,
    date_add(add_months(trunc(add_months(date_add(d, -7),-(month(date_add(d, -7))-1)%3),'MM'),3),-1) as last_date_quarter_cutoff
from dates

简化写法（Hive 2.1+支持） #

with dates as (
    select date_add("2010-01-01", a.pos) as d 
    from (select posexplode(split(repeat("o", datediff(from_unixtime(unix_timestamp()), "2010-01-01")), "o"))) a
)
select 
    d as date_today,
    year(d) as year,
    CONCAT('Q',(INT((MONTH(d)-1)/3)+1)) AS QUARTER,
    YEAR(date_add(d, -7)) AS year_cutoff,
    CONCAT('Q',(INT((MONTH(date_add(d, -7))-1)/3)+1)) AS QUARTER_cutoff,
    trunc(d, 'Q')  as Firstday_quarter,
    date_add(add_months(trunc(d, 'Q'),3),-1) as quarter_last_date,
    trunc(date_add(d, -7), 'Q') as Firstday_quartercutoff,
    date_add(add_months(trunc(date_add(d, -7), 'Q'),3),-1) as last_date_quarter_cutoff
from dates

效果验证 #

以示例中的2010-01-07为例：

• 当前季度第一天是2010-01-01，加3个月是2010-04-01，减1天得到季度最后一天2010-03-31
• 日期减7天为2009-12-31，对应季度第一天为2009-10-01，对应季度最后一天为2009-12-31，完全符合预期效果。

内容的提问来源于stack exchange，提问作者karthik adiga