Azure SQL Server多维度表月环比空置房产统计问题求助

原代码存在以下核心问题：

• 仅统计生效当月的空置记录：通过RowEffectiveDate分组，只能捕捉当月新增的空置房产，无法覆盖上月空置且本月仍未恢复的持续空置场景——这类记录没有新的RowEffectiveDate产生，会被遗漏。
• 多户/单户逻辑混淆：多户房产要求以单元状态为准，但原代码关联了tblPWDimBuilding的Active状态，可能错误过滤掉建筑非活跃但单元仍空置的情况；单户建筑的统计也未考虑记录的时间跨度，比如2023年3月生效的空置建筑，在4、5月仍空置的话，原代码不会计入这两个月份。
• 未利用过期日期判断状态周期：没有结合RowExpirationDate（活跃记录为null）来判断空置状态的持续时间，无法确定某房产是否在整个月度或部分月度处于空置状态。

步骤1：构建月度维度表 #

先生成需要统计的月度范围（示例为2023年4-5月），用于匹配每个月度的空置状态：

WITH MonthDim AS (
    SELECT 
        DATEFROMPARTS(YEAR(MonthStart), MONTH(MonthStart), 1) AS MonthStart,
        EOMONTH(MonthStart) AS MonthEnd
    FROM (
        VALUES 
            ('2023-04-01'), ('2023-05-01')
    ) AS Dates(MonthStart)
)

步骤2：处理单户建筑的空置时间范围 #

提取单户建筑的空置时间段，将活跃记录（RowExpirationDate IS NULL）的过期日期设为当前最大统计月的月底：

, SingleFamilyVacancy AS (
    SELECT 
        b.BuildingID,
        b.OrganizationName,
        b.RowEffectiveDate AS VacancyStart,
        ISNULL(b.RowExpirationDate, (SELECT MAX(MonthEnd) FROM MonthDim)) AS VacancyEnd
    FROM curated.tblPWDimBuilding b
    WHERE b.Status = 'Vacant'
        AND b.RowIsActive IN (0, 1) -- 包含历史和活跃记录，覆盖完整时间跨度
)

步骤3：处理多户单元的空置时间范围 #

多户房产仅以单元状态为准，忽略建筑状态：

, MultiFamilyVacancy AS (
    SELECT 
        u.UnitID,
        u.OrganizationName,
        u.RowEffectiveDate AS VacancyStart,
        ISNULL(u.RowExpirationDate, (SELECT MAX(MonthEnd) FROM MonthDim)) AS VacancyEnd
    FROM curated.tblPWDimUnit u
    WHERE u.Status = 'Vacant'
        AND u.RowIsActive IN (0, 1)
)

步骤4：关联月度维度统计各月空置数 #

将单户和多户的空置时间段与月度维度匹配，统计每个月的有效空置房产：

, MonthlyVacancyCounts AS (
    -- 单户统计
    SELECT 
        DATEPART(YEAR, md.MonthStart) AS Year,
        DATEPART(MONTH, md.MonthStart) AS Month,
        sf.OrganizationName,
        COUNT(DISTINCT sf.BuildingID) AS VacantCount,
        'SingleFamily' AS PropertyType
    FROM MonthDim md
    JOIN SingleFamilyVacancy sf
        ON sf.VacancyStart <= md.MonthEnd
        AND sf.VacancyEnd >= md.MonthStart
    GROUP BY DATEPART(YEAR, md.MonthStart), DATEPART(MONTH, md.MonthStart), sf.OrganizationName

    UNION ALL

    -- 多户统计
    SELECT 
        DATEPART(YEAR, md.MonthStart) AS Year,
        DATEPART(MONTH, md.MonthStart) AS Month,
        mf.OrganizationName,
        COUNT(DISTINCT mf.UnitID) AS VacantCount,
        'MultiFamily' AS PropertyType
    FROM MonthDim md
    JOIN MultiFamilyVacancy mf
        ON mf.VacancyStart <= md.MonthEnd
        AND mf.VacancyEnd >= md.MonthStart
    GROUP BY DATEPART(YEAR, md.MonthStart), DATEPART(MONTH, md.MonthStart), mf.OrganizationName
)

步骤5：计算月环比 #

通过窗口函数LAG获取上月空置数，计算环比增长率：

SELECT 
    Year,
    Month,
    OrganizationName,
    PropertyType,
    VacantCount,
    LAG(VacantCount) OVER (
        PARTITION BY OrganizationName, PropertyType 
        ORDER BY Year, Month
    ) AS PreviousMonthVacantCount,
    CASE 
        WHEN LAG(VacantCount) OVER (
            PARTITION BY OrganizationName, PropertyType 
            ORDER BY Year, Month
        ) IS NULL THEN NULL
        ELSE ROUND(
            (VacantCount - LAG(VacantCount) OVER (
                PARTITION BY OrganizationName, PropertyType 
                ORDER BY Year, Month
            )) * 100.0 / LAG(VacantCount) OVER (
                PARTITION BY OrganizationName, PropertyType 
                ORDER BY Year, Month
            ), 2
        )
    END AS MonthOverMonthGrowthRate
FROM MonthlyVacancyCounts
ORDER BY OrganizationName, PropertyType, Year, Month;

关键说明 #

• 时间匹配逻辑：通过VacancyStart <= MonthEnd AND VacancyEnd >= MonthStart判断空置状态是否覆盖当月的任意时间段，确保持续空置的房产被计入每个有效月度。
• 活跃记录处理：将RowExpirationDate IS NULL的活跃记录过期日期设为统计周期的最后一天，保证当前仍空置的房产被计入后续月度。
• 环比计算：使用LAG窗口函数按区域和房产类型分组，避免跨区域/类型的错误对比，确保环比数据的准确性。

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