如何用SQL实现所有(year, year+1)组合的球员数据统计？

我有一个名为「players」的足球球员表，数据如下：

year  name var1 year2
 2000 Name 5    0    NA
 2000 Name 1    0  2002
 2000 Name 3    1  2004
 2000 Name 2    1    NA
 2000 Name 4    0    NA
 2001 Name 4    1    NA
 2001 Name 2    1  2002
 2001 Name 1    0    NA
 2001 Name 3    1    NA
 2001 Name 5    1    NA
 2002 Name 5    0  2001
 2002 Name 3    1    NA
 2002 Name 1    0    NA
 2002 Name 4    0  2004
 2002 Name 2    1  2003
 2003 Name 1    1    NA
 2003 Name 5    0  2004
 2003 Name 3    0    NA
 2003 Name 2    0  2000
 2003 Name 4    0    NA
 2004 Name 2    0    NA
 2004 Name 1    0    NA
 2004 Name 4    0  2004
 2004 Name 5    0    NA
 2004 Name 3    1    NA

我的需求是：对每一年，选取该年var1=1的所有球员，统计以下指标：

• 该年份var1=1的球员数量
• 下一年这些球员中仍满足var1=1且year2不为空的数量
• 下一年这些球员中var1≠1且year2不为空的数量
• 下一年这些球员未出现在表中的数量（nf）
• 下一年这些球员中year2为空的数量

我已经写了针对2000和2001年组合的SQL：

WITH names_2000 AS (
  SELECT name
  FROM players
  WHERE year = 2000 AND var1 = 1
),

names_2001 AS (
  SELECT name, var1, year2
  FROM players
  WHERE year = 2001
)

SELECT 
  (SELECT COUNT(*) FROM names_2000) AS count_names_2000_var1,
  COUNT(DISTINCT CASE WHEN n1.var1 = 1 AND n1.year2 IS NOT NULL THEN n1.name END) AS count_names_2001_var1_year2_not_null,
  COUNT(DISTINCT CASE WHEN n1.var1 != 1 AND n1.year2 IS NOT NULL THEN n1.name END) AS count_names_2001_var_not_1_year2_not_null,
  COUNT(DISTINCT CASE WHEN n1.name IS NULL THEN n0.name END) AS count_names_not_in_2001,
  COUNT(DISTINCT CASE WHEN n1.year2 IS NULL THEN n1.name END) AS count_names_2001_year2_null
FROM names_2000 n0
LEFT JOIN names_2001 n1 ON n0.name = n1.name;

目前我是通过Python脚本循环执行这段代码来统计所有(year, year+1)组合，但希望直接用SQL一次性完成，求协助。

可以通过自连接结合分组统计的方式，一次性生成所有年份组合的统计结果，无需循环执行。以下是通用SQL：

WITH current_year_players AS (
  -- 筛选出每年var1=1的球员
  SELECT year, name
  FROM players
  WHERE var1 = 1
),
next_year_players AS (
  -- 获取所有年份的球员全量数据，用于匹配下一年状态
  SELECT year, name, var1, year2
  FROM players
)
SELECT
  cy.year AS current_year,
  COUNT(DISTINCT cy.name) AS count_current_var1,
  -- 下一年var1=1且year2非空的球员数量
  COUNT(DISTINCT CASE WHEN ny.year = cy.year + 1 AND ny.var1 = 1 AND ny.year2 IS NOT NULL THEN ny.name END) AS count_next_var1_year2_not_null,
  -- 下一年var1≠1且year2非空的球员数量
  COUNT(DISTINCT CASE WHEN ny.year = cy.year + 1 AND ny.var1 != 1 AND ny.year2 IS NOT NULL THEN ny.name END) AS count_next_var_not1_year2_not_null,
  -- 下一年未出现在表中的球员数量
  COUNT(DISTINCT CASE WHEN ny.name IS NULL THEN cy.name END) AS count_next_not_appear,
  -- 下一年year2为空的球员数量
  COUNT(DISTINCT CASE WHEN ny.year = cy.year + 1 AND ny.year2 IS NULL THEN ny.name END) AS count_next_year2_null
FROM current_year_players cy
LEFT JOIN next_year_players ny 
  ON cy.name = ny.name AND ny.year = cy.year + 1
GROUP BY cy.year
ORDER BY cy.year;

逻辑说明 #

1. current_year_players CTE：提取每一年中var1=1的球员名单，作为分组统计的基础数据。
2. next_year_players CTE：保留全量球员数据，用于匹配当前年份球员的下一年状态。
3. 主查询通过LEFT JOIN将当前年份的球员与下一年的球员关联，使用CASE语句分别统计各类指标，最后按当前年份分组，一次性输出所有(year, year+1)组合的统计结果。

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