SQL Server计算合格客户注册占比及开户注册间隔中位数方法

实现说明 #

以下为SQL Server环境下的完整实现代码，两个指标可以合并在同一段查询中返回：

WITH eligible_cust AS (
    -- 筛选合格客户，关联注册表计算间隔天数
    SELECT 
        c.id,
        -- 转换源日期格式dd/mm/yyyy为标准日期类型
        CONVERT(DATE, c.open_date, 103) AS open_date,
        CONVERT(DATE, r.reg_date, 103) AS reg_date,
        DATEDIFF(DAY, CONVERT(DATE, c.open_date, 103), CONVERT(DATE, r.reg_date, 103)) AS days_gap
    FROM customers c
    LEFT JOIN registered_customers r ON c.id = r.id
    WHERE c.eligible = 'True'
),
gap_median AS (
    -- 计算间隔天数中位数
    SELECT DISTINCT
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_gap) OVER () AS median_days_gap
    FROM eligible_cust
)
-- 最终返回两个指标结果
SELECT
    -- 乘以1.0规避整数除法自动取整问题
    COUNT(CASE WHEN reg_date IS NOT NULL THEN 1 END) * 1.0 / COUNT(*) AS eligible_reg_rate,
    MAX(median_days_gap) AS median_open_to_reg_days
FROM eligible_cust, gap_median

逻辑说明 #

• 合格客户注册占比：先过滤所有eligible为True的客户，左关联注册表后，非空reg_date的数量除以合格客户总数量即为所求占比
• 开户注册间隔中位数：你的思路完全正确，先关联两张表用DATEDIFF计算间隔天数，再用PERCENTILE_CONT(0.5)计算连续型中位数，如果需要取数据集里实际存在的间隔值作为中位数，可以将PERCENTILE_CONT替换为PERCENTILE_DISC

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