HQL实现从每年第一天开始计算周数

最新推荐文章于 2022-11-18 16:47:28 发布

一个数据大开发

最新推荐文章于 2022-11-18 16:47:28 发布

阅读量220

点赞数

文章标签： sql hive 数据仓库

本文链接：https://blog.csdn.net/weixin_41026747/article/details/123311846

版权

该博客详细介绍了如何对日期进行处理，包括季度和季节的划分、周编号的计算以及日期转换。内容涵盖SQL中的日期函数，如DATE_FORMAT、WEEK_OF_YEAR等，并展示了如何利用窗口函数LAG来处理年末周编号的特殊情况。此外，还讨论了时间序列分析的基础概念，为数据分析和报告提供基础工具。

摘要由CSDN通过智能技术生成

select 
    `date`                                as date
    ,date_ds                              as date_ds 
    ,year_mth                             as month   
    ,year                                 as year 
    ,month                                as month_id
    ,day                                  as day   
    ,case when month = 1 or month = 2 or month = 3 then "Q1"
          when month = 4 or month = 5 or month = 6 then "Q2"
          when month = 7 or month = 8 or month = 9  then "Q3"
     else "Q4"   end     
                                          as quarter       
    ,case when month = 1 or month = 2 or month = 3 then "春季"
          when month = 4 or month = 5 or month = 6 then "夏季"
          when month = 7 or month = 8 or month = 9 then "秋季"
      else "冬季" end
                                          as season
    ,day_of_week                          as week  
    --年末最后一周周编号不能为1，应延续上周编号累加
    ,case when month(`date`)=12 and weekofyear(`date`)=1 
          then lag(week_of_year,7) over(order by `date`)+1 
          else week_of_year 
     end week_of_year
    ,CURRENT_DATE()                       as etl_time   
from (
  select
    `date`,
    regexp_replace(`date`, '-', '') as date_ds,
    SUBSTR(`date`,1,7) as year_mth,
    year(`date`) as year,
    month(`date`) as month,
    day(`date`) as day,
    -- 请参看代码拆析 2   date_sub(next_day(`date`, 'Mon'), 7) as theMonday,
    if(datediff(next_day(`date`, 'Mon'), `date`) == 7, date_sub(`date`, 6), date_sub(next_day(`date`, 'Mon'), 7)) as theMonday,
    -- 版本支持date_format，可以使用： date_format(`date`, 'u') as day_of_week 
    from_unixtime(unix_timestamp(`date`, "yyyy-MM-dd"), "u") as day_of_week ,
    --年第一天周编号总是从1开始，后面累加
    case when weekofyear(concat(SUBSTR(`date`,1,4),'-01-01'))<>1 
        then 
            case when weekofyear(`date`)=weekofyear(concat(SUBSTR(`date`,1,4),'-01-01')) and month(`date`)=1
            then 1 else weekofyear(`date`)+1
        end 
    else   
        weekofyear(`date`)
    end week_of_year
  from (
   
        select date_add('2010-01-01',pos) as `date`
        from (
            select '2010-01-01'
        ) t 
        lateral view posexplode(split(repeat(", ", 9495), ",")) tf as pos,val
      
  ) dates
) dates_expanded
sort by `date`
;