Postgres酒店预订数据每周PACE报表查询开发需求

核心统计规则 #

• 财年周期：每年4月1日至次年3月31日
• 周周期：每周三至下周二（以给定日期所在的周三作为周起始）
• 收入统计逻辑：
  • 历史入住月份：入住日期早于目标周起始日的月份，仅统计未取消（取消日期为空或晚于入住日期）的实际收入
  • 未来入住月份：入住日期晚于目标周结束日的月份，统计截至目标周结束日仍未取消（取消日期为空或晚于目标周结束日）的账面收入
  • 当周入住月份：入住日期在目标周内的订单，按实际是否取消（取消日期晚于入住日期则计入）统计收入

步骤1：计算目标周的时间边界 #

先通过查询确定任意给定日期所在的周三起始周的起止日期：

WITH target_week AS (
    -- 替换这里的input_date为目标周的任意日期（如2023-08-06）
    SELECT
        input_date - ((extract(dow FROM input_date) - 3)::int + 7) % 7 AS week_start,
        input_date - ((extract(dow FROM input_date) - 3)::int + 7) % 7 + 6 AS week_end
    FROM (SELECT '2023-08-06'::date AS input_date) AS t
)
SELECT week_start, week_end FROM target_week;

逻辑说明 #

extract(dow FROM date)返回星期几（0=周日，3=周三），通过偏移计算得到当前日期所属的周三起始周：

• 若给定日期是周三及以后，返回本周三到下周二
• 若给定日期是周二及以前，返回上周三到本周二

步骤2：单目标周的收入统计 #

结合目标周边界，按财年月份分组统计收入：

WITH target_week AS (
    SELECT
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 AS week_start,
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 + 6 AS week_end
),
booking_stats AS (
    SELECT
        -- 生成财年月份标识（如FY2023-M04代表2023财年4月）
        TO_CHAR(arrival_date, '"FY"YYYY-"M"MM') AS fy_month,
        CASE
            WHEN arrival_date < (SELECT week_start FROM target_week) THEN '历史入住'
            WHEN arrival_date > (SELECT week_end FROM target_week) THEN '未来入住'
            ELSE '当周入住'
        END AS period_type,
        SUM(
            CASE
                -- 历史入住：仅保留实际未取消的订单收入
                WHEN arrival_date < (SELECT week_start FROM target_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
                -- 未来入住：仅保留截至目标周未取消的订单收入
                WHEN arrival_date > (SELECT week_end FROM target_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > (SELECT week_end FROM target_week) THEN revenue ELSE 0 END
                -- 当周入住：按实际入住状态统计
                ELSE
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
            END
        ) AS total_revenue
    FROM bookings
    GROUP BY fy_month, period_type
)
SELECT fy_month, period_type, total_revenue
FROM booking_stats
ORDER BY fy_month, period_type;

步骤3：回溯历史周的快照数据 #

由于只有实时快照，需通过过滤模拟历史周的数据状态（仅保留历史周及之前的预订和取消记录）：

WITH target_week AS (
    SELECT
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 AS week_start,
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 + 6 AS week_end
),
historical_snapshot AS (
    -- 模拟目标周结束时的数据快照：仅保留该周及之前创建的订单，且取消记录不晚于该周结束日
    SELECT arrival_date, cancel_date, revenue
    FROM bookings
    WHERE
        input_date <= (SELECT week_end FROM target_week)
        AND (cancel_date IS NULL OR cancel_date <= (SELECT week_end FROM target_week))
),
booking_stats AS (
    SELECT
        TO_CHAR(arrival_date, '"FY"YYYY-"M"MM') AS fy_month,
        CASE
            WHEN arrival_date < (SELECT week_start FROM target_week) THEN '历史入住'
            WHEN arrival_date > (SELECT week_end FROM target_week) THEN '未来入住'
            ELSE '当周入住'
        END AS period_type,
        SUM(
            CASE
                WHEN arrival_date < (SELECT week_start FROM target_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
                WHEN arrival_date > (SELECT week_end FROM target_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > (SELECT week_end FROM target_week) THEN revenue ELSE 0 END
                ELSE
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
            END
        ) AS total_revenue
    FROM historical_snapshot
    GROUP BY fy_month, period_type
)
SELECT fy_month, period_type, total_revenue
FROM booking_stats
ORDER BY fy_month, period_type;

步骤4：生成周同比报表 #

对比当前周与去年同期周的收入数据，计算同比增长率：

WITH current_week AS (
    SELECT
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 AS week_start,
        '2023-08-06'::date - ((extract(dow FROM '2023-08-06'::date) - 3)::int + 7) % 7 + 6 AS week_end
),
prior_year_week AS (
    SELECT
        ('2023-08-06'::date - interval '1 year')::date - ((extract(dow FROM ('2023-08-06'::date - interval '1 year')::date) - 3)::int + 7) % 7 AS week_start,
        ('2023-08-06'::date - interval '1 year')::date - ((extract(dow FROM ('2023-08-06'::date - interval '1 year')::date) - 3)::int + 7) % 7 + 6 AS week_end
),
current_stats AS (
    SELECT
        TO_CHAR(arrival_date, '"FY"YYYY-"M"MM') AS fy_month,
        SUM(
            CASE
                WHEN arrival_date < (SELECT week_start FROM current_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
                WHEN arrival_date > (SELECT week_end FROM current_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > (SELECT week_end FROM current_week) THEN revenue ELSE 0 END
                ELSE
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
            END
        ) AS current_revenue
    FROM bookings
    WHERE input_date <= (SELECT week_end FROM current_week)
        AND (cancel_date IS NULL OR cancel_date <= (SELECT week_end FROM current_week))
    GROUP BY fy_month
),
prior_stats AS (
    SELECT
        -- 将去年财年月份映射到当前财年，方便同比对比
        REPLACE(TO_CHAR(arrival_date, '"FY"YYYY-"M"MM'), 'FY' || EXTRACT(YEAR FROM ('2023-08-06'::date - interval '1 year')), 'FY' || EXTRACT(YEAR FROM '2023-08-06'::date)) AS fy_month,
        SUM(
            CASE
                WHEN arrival_date < (SELECT week_start FROM prior_year_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
                WHEN arrival_date > (SELECT week_end FROM prior_year_week) THEN
                    CASE WHEN cancel_date IS NULL OR cancel_date > (SELECT week_end FROM prior_year_week) THEN revenue ELSE 0 END
                ELSE
                    CASE WHEN cancel_date IS NULL OR cancel_date > arrival_date THEN revenue ELSE 0 END
            END
        ) AS prior_revenue
    FROM bookings
    WHERE input_date <= (SELECT week_end FROM prior_year_week)
        AND (cancel_date IS NULL OR cancel_date <= (SELECT week_end FROM prior_year_week))
    GROUP BY fy_month
)
SELECT
    c.fy_month,
    c.current_revenue,
    p.prior_revenue,
    ROUND(((c.current_revenue - p.prior_revenue) / NULLIF(p.prior_revenue, 0)) * 100, 2) AS yoy_growth_pct
FROM current_stats c
LEFT JOIN prior_stats p ON c.fy_month = p.fy_month
ORDER BY c.fy_month;

逻辑说明 #

• 通过- interval '1 year'得到去年同期的周边界
• 动态替换财年标识，确保去年同期的财月与当前财月对应
• 使用NULLIF避免除数为0导致的报错，同比增长率保留两位小数

内容的提问来源于stack exchange，提问作者T J