在Snowflake中使用SQL计算首年单位面积基准租金

需要计算首年每单位面积（如平方英尺）的租金，已知多个租金周期：

• 周期可能年中开始，租赁期内多次变更条款（示例1）
• 条款可能存在重叠（示例2）
  最终仅使用绿色标注的条款进行计算，需在Snowflake中实现。

样本数据 #

CREATE TEMPORARY TABLE RENTDATA
(
    EXAMPLE_DEAL INT,
    AMOUNT DECIMAL (15,2),
    SURFACE DOUBLE,
    TERM_START DATE,
    TERM_END DATE
);    

INSERT INTO RENTDATA VALUES (1,1300.00, 1600, '2018-08-01', '2019-05-31');
INSERT INTO RENTDATA VALUES (1,1177.60, 1600, '2019-06-01', '2020-05-31');
INSERT INTO RENTDATA VALUES (1,1212.92, 1600, '2020-06-01', '2021-05-31');
INSERT INTO RENTDATA VALUES (1,1249.32, 1600, '2021-06-01', '2022-05-31');
INSERT INTO RENTDATA VALUES (2,3000.00, 2465, '2023-11-01', '2024-10-31');
INSERT INTO RENTDATA VALUES (2,-3000.00,2465, '2023-11-01', '2024-04-30');
INSERT INTO RENTDATA VALUES (2,3090.00, 2465, '2024-11-01', '2025-10-31');
INSERT INTO RENTDATA VALUES (2,3183.00, 2465, '2025-11-01', '2026-10-31');

Snowflake实现方案 #

步骤1：确定首年时间范围 #

首年指从交易最早TERM_START日期起的12个月周期，比如示例1首年为2018-08-01至2019-07-31，示例2为2023-11-01至2024-10-31。

步骤2：处理重叠条款并计算首年单位面积租金 #

通过日期拆分合并重叠区间，按实际天数折算首年总租金，最终计算单位面积租金：

WITH DEAL_START AS (
    -- 获取每个交易的首年起止日期
    SELECT 
        EXAMPLE_DEAL,
        MIN(TERM_START) AS FIRST_TERM_START,
        DATEADD(MONTH, 12, MIN(TERM_START)) AS FIRST_YEAR_END
    FROM RENTDATA
    GROUP BY EXAMPLE_DEAL
),
DATE_SPLITS AS (
    -- 生成所有需要拆分的日期点（含首年结束日）
    SELECT EXAMPLE_DEAL, TERM_START AS SPLIT_DATE FROM RENTDATA
    UNION
    SELECT EXAMPLE_DEAL, DATEADD(DAY, 1, TERM_END) AS SPLIT_DATE FROM RENTDATA
    UNION
    SELECT EXAMPLE_DEAL, FIRST_YEAR_END AS SPLIT_DATE FROM DEAL_START
),
ORDERED_SPLITS AS (
    -- 排序日期点，生成连续的日期区间
    SELECT 
        EXAMPLE_DEAL,
        SPLIT_DATE AS PERIOD_START,
        LEAD(SPLIT_DATE) OVER (PARTITION BY EXAMPLE_DEAL ORDER BY SPLIT_DATE) AS PERIOD_END
    FROM DATE_SPLITS
),
VALID_PERIODS AS (
    -- 筛选首年范围内的有效区间
    SELECT 
        os.EXAMPLE_DEAL,
        os.PERIOD_START,
        LEAST(os.PERIOD_END, ds.FIRST_YEAR_END) AS PERIOD_END
    FROM ORDERED_SPLITS os
    JOIN DEAL_START ds ON os.EXAMPLE_DEAL = ds.EXAMPLE_DEAL
    WHERE os.PERIOD_END IS NOT NULL 
      AND os.PERIOD_START < ds.FIRST_YEAR_END
),
PERIOD_RENT AS (
    -- 合并重叠区间的租金金额
    SELECT 
        vp.EXAMPLE_DEAL,
        vp.PERIOD_START,
        vp.PERIOD_END,
        SUM(rd.AMOUNT) AS TOTAL_PERIOD_RENT,
        MAX(rd.SURFACE) AS SURFACE
    FROM VALID_PERIODS vp
    JOIN RENTDATA rd ON vp.EXAMPLE_DEAL = rd.EXAMPLE_DEAL
        AND rd.TERM_START < vp.PERIOD_END
        AND rd.TERM_END >= vp.PERIOD_START
    GROUP BY vp.EXAMPLE_DEAL, vp.PERIOD_START, vp.PERIOD_END
),
FIRST_YEAR_TOTAL AS (
    -- 按天数比例折算首年总租金
    SELECT 
        EXAMPLE_DEAL,
        SURFACE,
        SUM(TOTAL_PERIOD_RENT * DATEDIFF(DAY, PERIOD_START, PERIOD_END) / 365) AS FIRST_YEAR_EQUIVALENT_RENT
    FROM PERIOD_RENT
    GROUP BY EXAMPLE_DEAL, SURFACE
)
-- 计算单位面积首年租金
SELECT 
    EXAMPLE_DEAL,
    SURFACE,
    ROUND(FIRST_YEAR_EQUIVALENT_RENT / SURFACE, 4) AS INITIAL_SQFT_RENT
FROM FIRST_YEAR_TOTAL;

方案说明 #

• 先锁定每个交易的首年时间范围，确保覆盖租赁启动后的完整12个月
• 通过日期拆分处理重叠条款，合并同一时间段的租金，避免重复计算
• 按实际租赁天数比例折算首年总租金，再除以面积得到单位面积租金

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