最火手游排行榜2021,西安seo外包价格,实名认证域名可以做电影网站吗,电子商务做网站实训体会一、问题
按每年的1月1日算当年的第一个自然周 (遇到跨年也不管#xff0c;如果1月1日是周三#xff0c;那么到1月5号#xff08;周日#xff09;算是本年的第一个自然周, 如果按周一是一周的第一天) 计算是本年的第几周#xff0c;那么 spark sql 如何写 ?
二、分析
…一、问题
按每年的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 1ELSE 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当1月1日是周一时DAYOFWEEK(your_date_column) 返回 2Spark SQL 中周一是2。
当1月1日是周日时DAYOFWEEK(your_date_column) 返回 1。但是 1月1日都是当年的第一天即 1月1日在 DAYOFYEAR(your_date_column) 永远都返回 1。当1月1日是周日时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 1 0 第二周第一天 1月2日 2 - 1 1
当1月1日是周一时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 2 -1 第二周第一天 1月8日 8 - 1 7
当1月1日是周二时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 3 -2 第二周第一天 1月7日 7 - 1 6
当1月1日是周三时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 4 -3 第二周第一天 1月6日 6 - 1 5
当1月1日是周四时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 5 -4 第二周第一天 1月5日 5 - 1 4
当1月1日是周五时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 6 -5 第二周第一天 1月4日 4 - 1 3
当1月1日是周六时DAYOFYEAR(your_date_column) - DAYOFWEEK(your_date_column) 1 - 7 -6 第二周第一天 1月3日 3 - 1 2除了第一行每一行都相差 88 可以拆解为 7 17 代表第二周 1 代表第二周的第一天定义偏移了 1。举个例子如果 your_date_column 是 2024-01-08这是一个周二DAYOFYEAR 为 8DAYOFWEEK 为 3周二
那么计算将是 CEIL((8 - 3 8) / 7.0)即 CEIL(13 / 7.0)结果为 2意味着这是第二周。 还有一种思考方式去理解 7 1 中的 1 DAYOFWEEK(your_date_column) 返回 1~7 , 如果除以 7 的话为了确保不进位 1那么需要把 1~7 向左偏移一位。 1~7 - 1 0~6
DAYOFYEAR(your_date_column) - ( DAYOFWEEK(your_date_column) - 1 ) / 7 都是在同一周 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;SELECTyour_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 1ELSE 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并向上取整。
