PGSQL提取本月跨越的周数及每周的开始结束日期

最近一个比较奇葩的需求,针对数据分析方面,需要针对月度跨的所有周数来进行分析,即使不是一个整周,也按照整周去进行提取.

例如: 2024年9月份,跨越了6周, 第一周和最后一周,只有1天.但是统计的时候需要将对应的数据处理出来.然后进行分析.这样就需要先将本月的周数处理一下,才可以与业务数据进行关联提取.

pgsql数据库的具体代码如下:

-- 获取录入月份的月开始和结束日期
WITH input_month AS (
    SELECT 
        DATE_TRUNC('month', '2024-07-01'::DATE) AS month_start, 
        DATE_TRUNC('month', '2024-07-01'::DATE) + INTERVAL '1 month - 1 day' AS month_end
),
-- 获取提取月份,每日对应的周的开始和结束日期,包含跨越.
weekly_boundaries AS (
    SELECT 
        date_trunc('week', day)::date AS week_start, -- 周一作为周的开始
        (date_trunc('week', day) + INTERVAL '6 days')::date AS week_end -- 周日作为周的结束
    FROM generate_series(
        date_trunc('month', (select month_start from input_month)::DATE), -- 当月第一天
        date_trunc('month', (select month_end from input_month)::DATE) + INTERVAL '1 month' - INTERVAL '1 day', -- 下个月第一天减一天，即当月最后一天
        INTERVAL '1 day'
    ) AS day
),
-- 处理第一周和最后一周的数据.
adjusted_weeks AS (
    SELECT 
        CASE 
            WHEN week_end > (SELECT month_end FROM input_month) THEN (SELECT month_end FROM input_month) ELSE week_end 
        END AS adjusted_week_end, -- 当结束日期大于本月最后一天.那么提取本月最后一天.
        CASE 
	        when week_start < (SELECT month_start FROM input_month) then (SELECT month_start FROM input_month) else week_start
        END AS adjusted_week_start  -- 当开始日期小于本月第一天,那么提取本月第一天.
    FROM 
        weekly_boundaries
    group by week_start, week_end
)
-- 提取周数据.
SELECT 
	ROW_NUMBER() over (order by adjusted_week_start) as in_week,  --所属本月周数
    EXTRACT(day from adjusted_week_start) as start_day, --周的开始日期,单位: 日
    EXTRACT(day from adjusted_week_end) as end_day, -- 周的结束日期, 单位: 日
    EXTRACT(day from adjusted_week_end)-EXTRACT(day from adjusted_week_start)+1 as days_num -- 本周的天数
FROM 
    adjusted_weeks
ORDER BY 
    adjusted_week_start;

代码解析: 首先创建CTE,以供缓存使用.

input_month: 获取录入日期对应的月份的第一天和最后一天

weekly_boundaries: 获取提取月份,每日对应的周的开始和结束日期,包含跨月.

adjusted_weeks: 处理第一周和最后一周的数据.

然后通过，提取周数据.首先通过开始日期进行排序，然后通过ROW_NUMBER(),生成序号,以便提取属于第几周. 为方便后续提取.将每日的数据都抽取出日来与订单的日进行关联提取即可.

其中: inweek: 所属周数, start_day: 开始日, end_day: 结束日, days_num: 周在本月的天数

为方便后续确认天数,增加了本月每周的天数,以便确认销量等数据是否合理.