一、问题
按每年的1月1日算当年的第一个自然周
(遇到跨年也不管，如果1月1日是周三，那么到1月5号（周日）算是本年的第一个自然周, 如果按周一是一周的第一天)
计算是本年的第几周，那么 spark sql 如何写 ?
二、分析
难点 :
- Spark SQL 的 DAYOFWEEK 函数返回的每周第一天是周日。
- 边界值的处理，即第一周如何判定、第二周从哪天开始计算。
先给出 sql 关键逻辑
CASE
WHEN DAYOFYEAR(your_date_column) <= 7 AND DAYOFWEEK(your_date_column) <> 2 THEN 1
ELSE CEIL((DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) + 8) / 7.0)
END AS week_number
多找一些边界值测试一下。
首先先校准每周从周一开始计算
DAYOFWEEK(your_date_column)分别返回
周日 周一 周二 周三 周四 周五 周六
1 2 3 4 5 6 7
这个表达式部分 CEIL((DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) + 8) / 7.0) 用于计算给定日期所在的周数，
特别是当年的1月1日始终作为第一周的开始，并且下一个周一开始第二周的情况下。
这里 "+8" 的部分是关键，它的作用是确保计算逻辑符合这个特殊的周定义。
解释如下：
DAYOFYEAR(your_date_column) 计算年中的天数。
DAYOFWEEK(your_date_column) 返回一周中的某一天（以周日为第一天）。
从 DAYOFYEAR 的结果中减去 DAYOFWEEK 的结果，是为了根据周日作为一周开始的标准进行调整。
在这个基础上加上 8 的目的是确保每年的1月1日到1月7日（不论1月1日是周几）都计算为第一周。
之后，除以 7 是为了将天数转换为周数，并且使用 CEIL 函数向上取整，以确保任何部分周都算作完整的一周。
举个例子，如果 your_date_column 是 2024-01-08（这是一个周二），DAYOFYEAR 为 8，DAYOFWEEK 为 3（周二），
那么计算将是 CEIL((8 - 3 + 8) / 7.0)，即 CEIL(13 / 7.0)，结果为 2，意味着这是第二周。
2023-01-01 年是周日,
那么 DAYOFWEEK(your_date_column) 返回的是 1，即本周第一天。
WEEKOFYEAR(your_date_column) 返回的是 52, 即 2022 年最后一周。
但实际上我们要求的结果应该是 2023 年的第一周。
2023-01-02 年是周一,
那么 DAYOFWEEK(your_date_column) 返回的是 2，即本周第二天。
WEEKOFYEAR(your_date_column) 返回的是 1, 即 2023 年第一周。
但实际上我们要求的结果应该是 2023 年的第二周。
三、验证
drop table your_table;
CREATE TABLE your_table (
id INT,
your_date_column DATE
);
CREATE OR REPLACE TEMPORARY VIEW temp_view AS
SELECT 1 as id, to_date('2023-01-01', 'yyyy-MM-dd') as your_date_column
UNION ALL
SELECT 2, to_date('2023-01-02', 'yyyy-MM-dd')
UNION ALL
SELECT 3, to_date('2023-02-15', 'yyyy-MM-dd')
UNION ALL
SELECT 4, to_date('2023-12-31', 'yyyy-MM-dd')
UNION ALL
SELECT 5, to_date('2024-01-01', 'yyyy-MM-dd')
UNION ALL
SELECT 6, to_date('2024-01-02', 'yyyy-MM-dd')
UNION ALL
SELECT 5, to_date('2024-01-07', 'yyyy-MM-dd')
UNION ALL
SELECT 6, to_date('2024-01-08', 'yyyy-MM-dd');
INSERT INTO your_table
SELECT * FROM temp_view;
SELECT
your_date_column,
YEAR(your_date_column) AS year,
date_format(your_date_column, 'EEEE') as WEEK,
WEEKOFYEAR(your_date_column) as WEEK_OF_YEAR,
DAYOFYEAR(your_date_column) as DAYOFYEAR,
DAYOFWEEK(your_date_column) as DAYOFWEEK,
CEIL((DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) + 8) / 7.0) as CEIL,
CASE
WHEN DAYOFYEAR(your_date_column) <= 7 AND DAYOFWEEK(your_date_column) <> 2 THEN 1
ELSE CEIL((DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) + 8) / 7.0)
END AS week_number
from your_table;
your_date_column year WEEK WEEK_OF_YEAR DAYOFYEAR DAYOFWEEK CEIL WEEK_NUMBER
2023-01-01 2023 Sunday 52 1 1 2 1
2023-01-02 2023 Monday 1 2 2 2 2
2023-02-15 2023 Wednesday 7 46 4 8 8
2023-12-31 2023 Sunday 52 365 1 54 54
2024-01-01 2024 Monday 1 1 2 1 1
2024-01-02 2024 Tuesday 1 2 3 1 1
2024-01-07 2024 Sunday 1 7 1 2 1
2024-01-08 2024 Monday 2 8 2 2 2
在这个查询中：
date_format 函数的第二个参数 'EEEE' 指定返回完整的星期名称（如 Monday, Tuesday 等）。
DAYOFYEAR(your_date_column) 计算出年中的天数。
DAYOFWEEK(your_date_column) 返回一周中的某天（以周日为一周的第一天）。
当 DAYOFYEAR 小于或等于7且 DAYOFWEEK 不等于2（不是周一）时，日期属于第一周。
否则，使用调整后的公式计算周数：减去 DAYOFWEEK 的结果，加上8，然后除以7，并向上取整。
