weekofyear跨年问题

南风知我意丿

已于 2023-03-02 16:24:52 修改

阅读量1.6k

点赞数 3

分类专栏： SQL Data Works 文章标签： sql 阿里云

于 2023-02-08 10:39:00 首次发布

本文链接：https://blog.csdn.net/Lzx116/article/details/128931012

版权

SQL 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

Data Works

6 篇文章 0 订阅

订阅专栏

文章目录

函数说明

命令格式

bigint weekofyear (datetime <date>)

参数说明
date：必填。DATETIME类型日期值。格式为yyyy-mm-dd hh:mi:ss。如果输入为STRING类型，且MaxCompute项目的数据类型版本是1.0，则会隐式转换为DATETIME类型后参与运算。
注意事项
这一周算上一年还是下一年，取决于这一周的大多数日期（4天以上）在哪一年。算在前一年，就是前一年的最后一周；算在后一年就是后一年的第一周。

Bug现象

在这里插入图片描述

SELECT  WEEKOFYEAR(DATE '2021-01-01');
-- 53

上面的53 实际上是2020年的第53周

一般我们算一年的周数都是按照下面的sql处理的

SELECT CONCAT('2021',WEEKOFYEAR(DATE '2021-01-01'));
-- 202153

这样的话问题就来了，他其实是202053才对。因此我们不能简单的使用concat拼上年份，而是动态拼上year

解决

SELECT YEAR(date_sub(next_day('2021-01-01','monday'),4))*100 + WEEKOFYEAR(DATE '2021-01-01');
-- 202053

next_day函数

NEXT_DAY(date,char)
date参数为日期型，
char:为1–7或Monday/Mon~Sunday/

指定时间的下一个星期几（由char指定）所在的日期，
char也可用1～7替代，1表示星期日，2代表星期一

获取当前周、周的开始、结束时间

[0-6]
//周的开始时间
DATE_SUB(TO_DATE(ds,'yyyymmdd'),WEEKDAY(TO_DATE(ds,'yyyymmdd'))) as week_start,
//周的结束时间
DATE_SUB(TO_DATE(ds,'yyyymmdd'),WEEKDAY(TO_DATE(ds,'yyyymmdd')) - 6) as week_end

示例：

需求：按周求uid的pv

SELECT  
    COUNT(DISTINCT uid),
    weekdate,
    collect_set(week_start)[0] as week_start,
    collect_set(week_end)[0] as week_end
FROM (
    SELECT 
        uid,
        TO_DATE(ds,'yyyymmdd'),
        CONCAT(YEAR(date_sub(next_day(TO_DATE(ds,'yyyymmdd'),'monday'),4)) ,'年第' ,WEEKOFYEAR(TO_DATE(ds,'yyyymmdd')),'周' ) AS weekdate,
        DATE_SUB(TO_DATE(ds,'yyyymmdd'),WEEKDAY(TO_DATE(ds,'yyyymmdd'))) as week_start,
        DATE_SUB(TO_DATE(ds,'yyyymmdd'),WEEKDAY(TO_DATE(ds,'yyyymmdd')) - 6) as week_end
        FROM huorong_bigdata.base_huorongsoftware_source01_detail
    WHERE fid IN (1,2,3,4,41,46) AND recname = 'Ransom/LockFile.ii'
    AND  ds >= '20221221' AND ds <= '20230301'
)t1
GROUP BY weekdate;