Amazon Redshift中基于日期范围拆分住宿记录单行至多行

要把存储为单行的多晚住宿记录拆分为每晚一行，同时按住宿天数均分房费收入，可以用以下两种方法实现：

原表示例 #

期望结果 #

方法一：递归CTE（兼容所有Redshift版本） #

递归CTE是通用方案，不管你的Redshift版本新旧都能使用：

WITH recursive_stays AS (
    -- 初始行：取入住日期作为第一天，计算每日均分收入
    SELECT
        stayid,
        arrivaldate AS staydate,
        lengthofstay AS remaining_days,
        roomrevenueusd / lengthofstay AS daily_revenue
    FROM
        your_stay_table
    WHERE
        lengthofstay > 0 -- 过滤掉无住宿天数的记录，避免除以0
    UNION ALL
    -- 递归生成后续每晚的记录
    SELECT
        stayid,
        DATEADD(day, 1, staydate) AS staydate,
        remaining_days - 1 AS remaining_days,
        daily_revenue
    FROM
        recursive_stays
    WHERE
        remaining_days > 1 -- 直到生成完所有住宿天数的行
)
SELECT
    stayid,
    staydate,
    daily_revenue AS roomrevenueusd
FROM
    recursive_stays
ORDER BY
    stayid,
    staydate;

方法二：使用generate_series（Redshift 1.0.2367+支持） #

如果你的Redshift版本在1.0.2367及以上，支持generate_series函数，写法会更简洁：

SELECT
    s.stayid,
    DATEADD(day, gs.n, s.arrivaldate) AS staydate,
    -- 可选：用ROUND控制小数精度，比如保留两位
    ROUND(s.roomrevenueusd / s.lengthofstay, 2) AS roomrevenueusd
FROM
    your_stay_table s
JOIN
    generate_series(0, s.lengthofstay - 1) gs(n)
ON
    s.lengthofstay > 0
ORDER BY
    s.stayid,
    staydate;

关键说明 #

• 除以0防护：必须过滤lengthofstay = 0的记录，否则会触发除以0的错误
• 日期处理：DATEADD会保留原timestamp的时间部分，和示例格式匹配
• 精度调整：如果房费均分后有多位小数，用ROUND函数可以控制显示的小数位数

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