Hive和MySQL中自然周保持一致的方法

跨年周：

如果一周中某几天在前一年，而剩下几天在后一年，那么这周是哪一年的周？

 

• Hive中： weekofyear (string date) 返回值为 int

从周一到周日为一个完整的周

若一周7天中，有4天及以上在某年，那么这一周就作为这一年的周。

在hive中需要获取 yyyyww 这样的年周时，采用case when方式生成

-- 返回形式: yyyyww
select case when weekofyear('2021-01-01') < 10 and month('2021-01-01') > 11 
                 then cast((year('2021-01-01')+1)*100 + weekofyear('2021-01-01') as string)
            when weekofyear('2021-01-01') > 10 and month('2021-01-01') < 2 
                 then cast((year('2021-01-01')-1)*100 + weekofyear('2021-01-01') as string)
            else cast(year('2021-01-01')*100 + weekofyear('2021-01-01') as string)
            end as create_date

在hive中需要获取 yyyy-ww 这样的年周时，采用case when方式生成

-- 周期:周(在Spark中没有yearweek函数, 返回值, 如201950, 只有weekofyear,返回值:周, 如:50)   
-- 返回形式: yyyy-ww, 解决weekofyear返回值小于9时, 前面不补0问题
-- CASE WHEN写法
select case when weekofyear('2019-12-31') < 10 and month('2019-12-31') > 11 
                 then concat_ws('-', cast(year('2019-12-31') + 1 as string), substr(concat('0', cast(weekofyear('2019-12-31') as string)), -2))
            when weekofyear('2019-12-31') > 10 and month('2019-12-31') < 2 
                 then concat_ws('-', cast(year('2019-12-31') - 1 as string), substr(concat('0', cast(weekofyear('2019-12-31') as string)), -2))
            else concat_ws('-', cast(year('2019-12-31') as string), substr(concat('0', cast(weekofyear('2019-12-31') as string)), -2)) 
           end as create_date

• MySQL中：WEEK(date[, mode])；WEEKOFYEAR(date)；YEARWEEK(date[, mode])

mode参数决定了，使用星期几作为一周的开始，以及用什么样的逻辑，来计算一年里的第一周。

WEEKOFYEAR(date) 相当于 WEEK(date, 3)

YEARWEEK(date, 3) 在week之前加上了年份，输出格式与以上hive的case when 输出结果一致。

-- 等价于 MySQL中的: YEARWEEK(substring('2020-11-16', 1, 10), 3);
-- MySQL中:: WEEK(date[,mode]); WEEKOFYEAR(date); YEARWEEK(date[,mode])
-- mode参数决定了, 使用星期几作为一周的开始, 以及用什么样的逻辑, 来计算一年里的第一周
-- 返回形式: yyyyww
select YEARWEEK(substring('2020-11-16', 1, 10), 3); 

其中第二个参数是 mode ，具体指的意思如下：

模式	一周的第一天	范围	第1周是第一周......
0	星期日	0-53	星期的第一天是星期日
1	星期一	0-53	一周的第一天是星期一，第一周超过3天
2	星期日	1-53	星期的第一天是星期日
3	星期一	1-53	一周的第一天是星期一，第一周超过3天
4	星期日	0-53	一周的第一天是星期日，第一周超过3天
5	星期一	0-53	一周的第一天是星期一
6	星期日	1-53	一周的第一天是星期日，并且第一周超过3天
7	星期一	1-53	一周的第一天是星期一

具体描述如下：

参数值 每周第一天是星期几 返回值 范围 第一周是怎么计算的？

• 0 星期天 0-53 遇到本年的第一个星期天开始，是第一周。前面的计算为第0周。
• 1 星期一 0-53 假如第一周能超过3天，那么计算为本年的第一周。否则为第0周可以理解为如果1月1号～4号是星期一，那么本年第一周开始。（之前的是第零周）如果1月5号以后是星期一，那么现在开始，是本年的第二周了，之前的是第一周。
• 2 星期天 1-53 遇到本年的第一个星期天开始，是第一周。前面的计算为上年度的第5x周。
• 3 星期一 1-53 假如第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周。可以理解为如果1月1号～4号是星期一，那么本年第一周开始。（之前的是第5x周）如果1月5号以后是星期一，那么现在开始，是本年的第二周了，之前的是第一周。
• 4 星期天 0-53 假如第一周能超过3天，那么计算为本年的第一周。否则为第0周可以理解为如果1月1号～4号是星期天，那么本年第一周开始。（之前的是第零周）如果1月5号以后是星期天，那么现在开始，是本年的第二周了，之前的是第一周。
• 5 星期一 0-53 遇到本年的第一个星期一开始，是第一周。前面的计算为第0周。
• 6 星期天 1-53 假如第一周能超过3天，那么计算为本年的第一周。否则为上年度的第5x周。可以理解为如果1月1号～4号是星期天，那么本年第一周开始。（之前的是第5x周）如果1月5号以后是星期天，那么现在开始，是本年的第二周了，之前的是第一周。
• 7 星期一 1-53 遇到本年的第一个星期一开始，是第一周。前面的计算为上年度的第5x周。