如何无需手动枚举年份统计各年份有记录的用户数？

在表table_b的日期最小年份到最大年份范围内，统计每一年有多少不同用户存在记录（即该用户的记录覆盖了这一年，也就是date_2 ≤ 当年最后一天 且 date_3 ≥ 当年第一天）。

建表与插入数据的SQL语句：

CREATE TABLE table_b (
  name VARCHAR(255),
  date_2 DATE,
  date_3 DATE,
  var CHAR(1)
);

INSERT INTO table_b (name, date_2, date_3, var) VALUES
('john', '2001-01-01', '2015-01-01', 'b'),
('sara', '2000-01-01', '2015-01-01', 'c'),
('sara', '2015-01-02', '2022-01-01', 'a'),
('tim', '2020-01-01', '2021-01-01', 'a'),
('john', '1998-01-01', '1999-01-01', 'd');

表数据展示：

通过手动枚举年份的CTE生成年份列表，再关联原表统计每年的不同用户数：

WITH years AS (
    SELECT 1998 AS year
    UNION ALL 
    SELECT 1999
    UNION ALL
    -- 此处需手动补充所有年份
),
counts AS (
    SELECT 
        y.year,
        COUNT(DISTINCT t.name) AS user_count
    FROM table_b t
    JOIN years y
        ON t.date_2 <= DATE(y.year || '-12-31') 
        AND t.date_3 >= DATE(y.year || '-01-01')
    GROUP BY y.year
)
SELECT * FROM counts;

可以通过递归CTE自动生成从表中最小年份到最大年份的所有年份序列，无需手动逐个添加：

WITH year_range AS (
    -- 先算出表中所有记录涉及的最小年份和最大年份
    SELECT 
        MIN(EXTRACT(YEAR FROM date_2)) AS min_year,
        MAX(EXTRACT(YEAR FROM date_3)) AS max_year
    FROM table_b
),
recursive_years AS (
    -- 递归起始：从最小年份开始
    SELECT min_year AS year
    FROM year_range
    UNION ALL
    -- 递归生成后续年份，直到达到最大年份
    SELECT year + 1
    FROM recursive_years
    CROSS JOIN year_range
    WHERE year < max_year
),
user_counts AS (
    -- 关联原表统计每年的不同用户数
    SELECT 
        ry.year,
        COUNT(DISTINCT tb.name) AS user_count
    FROM recursive_years ry
    LEFT JOIN table_b tb
        ON tb.date_2 <= DATE(ry.year || '-12-31')
        AND tb.date_3 >= DATE(ry.year || '-01-01')
    GROUP BY ry.year
)
SELECT * FROM user_counts ORDER BY year;

关键说明： #

• year_range：自动获取表中记录的年份边界，不用手动指定起始和结束年份。
• recursive_years：通过递归逻辑自动生成完整的年份序列，年份范围随表中数据自动调整。
• LEFT JOIN：保证即使某一年没有任何用户记录，也会显示该年份并将用户数记为0，不会遗漏年份。

内容的提问来源于stack exchange，提问作者Uk rain troll