本月的第几周。一周应从周一开始，周日结束

Question

我需要在我的dim_date中添加新的业务wee，实际上我有一个月中的星期，如下表所示，每7天计算一次。

但是对于这个商业周专栏，一周应该从星期一开始，到星期日结束，即使第一周只有一天（当一个月从星期日开始时），但如果月份从星期五开始，我应该将其视为该月的第一周。

你有什么建议来计算这个吗？谢谢

Answer 1

我希望我想出的这个解决方案可以在未来帮助你，或者我们可以讨论并改进这个解决方案:

WITH sample_data AS (SELECT
                     date,
                     EXTRACT(WEEK FROM date) AS week_of_year,
                     DATE_TRUNC(date, MONTH) AS FIRST_DAY_OF_MONTH
                 FROM (SELECT date
                       FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2022-04-01', '2022-04-19', INTERVAL 1 DAY)) AS date))
SELECT
date,
FORMAT_DATE('%A', date) as day_name,
CASE
    WHEN CAST(EXTRACT(WEEK(MONDAY) FROM d.date) AS INT) = 0 AND d.WEEK_OF_YEAR IN (52, 53) THEN 1
    WHEN CAST(EXTRACT(WEEK(MONDAY) FROM d.FIRST_DAY_OF_MONTH) AS INT) = 0 THEN EXTRACT(WEEK(MONDAY) FROM d.date) + 1
    ELSE (d.WEEK_OF_YEAR - CAST(EXTRACT(WEEK(MONDAY) FROM d.FIRST_DAY_OF_MONTH) AS INT)) + 1
END AS BUSINESS_WEEK_OF_MONTH
FROM sample_data d;

Answer 2

此查询获取从2002-04-01到2002-04-19的周数。本周从周一开始，周日结束。您可以看到以下示例：

with sample_data as (
    SELECT date FROM UNNEST(generate_timestamp_array('2022-04-01', '2022-04-19', INTERVAL 1 DAY)) as date
)

select
    date 
    , extract(week(MONDAY) from  date)-(extract(MONTh from  date)-1)*4 AS days_diff
    ,  FORMAT_DATE('%A', date)
from sample_data

你可以看到结果。

您需要使用EXTRACT函数。