SQL重叠周期划分：V3计数与占比计算的SQL代码修正求助

表结构与数据 #

现有SQL表df，结构及数据如下：

-- 表结构
CREATE TABLE df (
    v1 DATE,
    v2 DATE,
    v3 INT,
    v4 DATE,
    name VARCHAR(10)
);

-- 插入数据
INSERT INTO df (v1, v2, v3, v4, name) VALUES
('2015-06-23', '2024-06-09', 2013, '2015-03-31', 'red'),
('2015-06-23', '2024-06-09', 2014, '2015-03-31', 'red'),
('2015-06-23', '2024-06-09', 2018, '2019-03-18', 'red'),
('2015-06-23', '2024-06-09', 2020, '2021-02-21', 'red'),
('2015-06-23', '2024-06-09', 2023, '2024-03-15', 'red'),
('2015-06-23', '2024-06-09', 2013, '2015-03-31', 'blue'),
('2015-06-23', '2024-06-09', 2014, '2015-03-31', 'blue'),
('2015-06-23', '2024-06-09', 2018, '2019-03-18', 'blue');

样本数据预览：

需求说明 #

需按name（颜色）分组执行以下计算：

1. 计算总年数：[MAX(v2)年份 - MIN(v1)年份] + 1；
2. 筛选v3值落在区间[MAX(v2)年份-1, MIN(v1)年份-1]内的记录；
3. 统计该区间内不重复的v3值数量；
4. 计算第三步结果与第一步总年数的比值。

以red为例：

• 总年数：2024-2015+1=10；
• 目标区间：2024-1=2023 至 2015-1=2014；
• 符合条件的不重复v3：2014、2018、2020、2023，共4个；
• 比值：4/10=0.4。

我的尝试代码 #

我用CTE分步骤编写了SQL，但不确定第三步的V3计数逻辑是否正确，代码如下：

WITH step_1 AS (
    SELECT
        name,
        YEAR(MAX(v2)) - YEAR(MIN(v1)) + 1 AS YearRange
    FROM df
    GROUP BY name
),
step_2_part1 AS (
    SELECT
        name,
        YEAR(MAX(v2)) - 1 AS MaxYear,
        YEAR(MIN(v1)) - 1 AS MinYear
    FROM df
    GROUP BY name
),
step_2_part2 AS (
    SELECT
        d.name,
        d.v3
    FROM df d
    JOIN step_2_part1 s2p1 ON d.name = s2p1.name
    WHERE d.v3 BETWEEN s2p1.MinYear AND s2p1.MaxYear
),
step_3 AS (
    SELECT
        name,
        COUNT(v3) AS V3Count
    FROM step_2_part2
    GROUP BY name
),
step_4 AS (
    SELECT
        s1.name,
        CASE
            WHEN s1.YearRange = 0 THEN 'Error: denominator is 0'
            ELSE CAST(s3.V3Count AS FLOAT) / s1.YearRange
        END AS Result
    FROM step_1 s1
    JOIN step_3 s3 ON s1.name = s3.name
)
SELECT * FROM step_4;

代码修正与优化 #

核心问题修正 #

你的代码中第三步的COUNT(v3)会统计所有符合条件的记录（包括重复的v3值），但需求是统计不重复的v3值数量，因此需要改为COUNT(DISTINCT v3)。

效率优化 #

step_1和step_2_part1都对name分组计算了MAX(v2)和MIN(v1)的年份，重复执行了分组逻辑，可以合并为一个CTE，减少一次全表扫描。

修正后的代码 #

WITH year_params AS (
    SELECT
        name,
        -- 总年数
        YEAR(MAX(v2)) - YEAR(MIN(v1)) + 1 AS YearRange,
        -- 目标区间的上下限
        YEAR(MAX(v2)) - 1 AS MaxYear,
        YEAR(MIN(v1)) - 1 AS MinYear
    FROM df
    GROUP BY name
),
filtered_v3 AS (
    SELECT
        d.name,
        d.v3
    FROM df d
    JOIN year_params yp ON d.name = yp.name
    WHERE d.v3 BETWEEN yp.MinYear AND yp.MaxYear
),
distinct_v3_count AS (
    SELECT
        name,
        COUNT(DISTINCT v3) AS V3Count
    FROM filtered_v3
    GROUP BY name
)
SELECT
    yp.name,
    CASE
        WHEN yp.YearRange = 0 THEN 'Error: denominator is 0'
        ELSE CAST(dvc.V3Count AS FLOAT) / yp.YearRange
    END AS Result
FROM year_params yp
JOIN distinct_v3_count dvc ON yp.name = dvc.name;

简化版本（可选） #

如果不需要分步的CTE，可以进一步简化为单查询：

SELECT
    name,
    CASE
        WHEN YEAR(MAX(v2)) - YEAR(MIN(v1)) + 1 = 0 THEN 'Error: denominator is 0'
        ELSE CAST(COUNT(DISTINCT CASE WHEN v3 BETWEEN YEAR(MIN(v1))-1 AND YEAR(MAX(v2))-1 THEN v3 END) AS FLOAT) / (YEAR(MAX(v2)) - YEAR(MIN(v1)) + 1)
    END AS Result
FROM df
GROUP BY name;

运行结果 #

执行修正后的代码，会得到如下结果：

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