在R/SQL中按组计算累计缺失年份及年度差值

问题描述 #

现有存储在R/SQL Server中的数据集：

name year
1  john 2010
2  john 2011
3  john 2013
4  jack 2015
5  jack 2018
6 henry 2010
7 henry 2011
8 henry 2012

需要为数据集添加两列：

• missing_years：计算每个人连续行之间的缺失年数
• cumulative_missing_years：计算每个人的累计缺失年数

示例结果：

# note: in this specific example that I have created, "missing_years" is the same as the "cumulative_missing_years"

   name year missing_years cumulative_missing_years
1  john 2010             0                        0
2  john 2011             0                        0
3  john 2013             1                        1
4  jack 2015             0                        0
5  jack 2018             3                        3
6 henry 2010             0                        0
7 henry 2011             0                        0
8 henry 2012             0                        0

用户尝试的dplyr代码存在语法与逻辑错误，以下是正确的实现方案：

一、R（dplyr/dbplyr）实现方法 #

本地数据处理（dplyr） #

核心逻辑：分组排序后，计算当前年份与前一行年份的差值，差值减1得到缺失年数；再对缺失年数做累计求和。

library(dplyr)

# 假设my_data是本地数据集
final <- my_data %>%
  group_by(name) %>%
  arrange(year) %>% # 确保每组内年份按升序排列
  mutate(
    # 计算缺失年数：当前年-前一年-1，不足0则取0
    missing_years = pmax(0, year - lag(year, default = first(year)) - 1),
    # 累计求和缺失年数
    cumulative_missing_years = cumsum(missing_years)
  ) %>%
  ungroup() # 可选，取消分组状态

数据库数据处理（dbplyr） #

适配SQL Server的代码，dbplyr会自动将R语法转换为SQL：

library(dbplyr)
library(DBI)
library(odbc)

# 连接SQL Server（替换为你的连接参数）
con <- dbConnect(
  odbc(),
  Driver = "SQL Server",
  Server = "你的服务器地址",
  Database = "你的数据库名",
  UID = "用户名",
  PWD = "密码"
)

# 从数据库读取目标表
my_data_db <- tbl(con, "你的表名")

# 执行计算
final_db <- my_data_db %>%
  group_by(name) %>%
  arrange(year) %>%
  mutate(
    missing_years = pmax(0, year - lag(year, default = first(year)) - 1),
    cumulative_missing_years = cumsum(missing_years)
  ) %>%
  ungroup()

# 可查看生成的SQL语句
show_query(final_db)

# 将结果拉取到本地或写入数据库
collect(final_db)

二、SQL Server实现方法 #

使用窗口函数LAG()获取前一行年份，再通过SUM() OVER()实现累计求和：

WITH ranked_data AS (
  SELECT
    name,
    year,
    -- 获取当前行的前一行年份，第一行默认取自身年份
    LAG(year, 1, year) OVER (PARTITION BY name ORDER BY year) AS prev_year,
    -- 计算原始缺失年数
    year - LAG(year, 1, year) OVER (PARTITION BY name ORDER BY year) - 1 AS missing_years_raw
  FROM 你的表名
)
SELECT
  name,
  year,
  -- 处理负数情况，确保缺失年数非负
  CASE WHEN missing_years_raw < 0 THEN 0 ELSE missing_years_raw END AS missing_years,
  -- 按分组累计求和缺失年数
  SUM(CASE WHEN missing_years_raw < 0 THEN 0 ELSE missing_years_raw END) OVER (
    PARTITION BY name 
    ORDER BY year 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_missing_years
FROM ranked_data
ORDER BY name, year;

核心实现思路 #

1. 分组排序：按name分组，每组内按year升序排列，保证年份顺序正确
2. 计算单步缺失年数：当前年份与前一行年份的差值减1，即为中间缺失的年数（如2013-2011=2，减1得1，对应缺失2012年）；第一行无前置行，默认缺失0年
3. 处理边界值：通过pmax()或CASE确保缺失年数不会出现负数
4. 累计缺失年数：对每组的missing_years做累加求和，得到累计值

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