R语言为数据框NA行迭代生成唯一ID及对应SQL实现咨询

错误原因说明 #

你之前的代码未达到预期的核心原因是：

• mutate为向量化运算，计算max(id, na.rm = TRUE)时取的是ID列初始状态的全局最大值，不会随ID更新动态调整，所以所有NA行得到的是同一个值
• 映射函数写法中，你传入map_dbl的是单个id值，对NA行来说入参就是NA，计算max(.)结果仍为NA，所以最终ID没有变化

R 实现代码 #

不需要逐行迭代更新最大值，直接通过累计NA行的序号叠加初始最大值即可，不修改原有有效ID，也不需要重排表顺序：

library(tidyverse)

df <- tibble(id = c(1, 2, 3, NA, NA),
             descriptions = c("dodgers", "yankees","giants", "orioles", "mets"))

# 先取现有ID的最大值
max_exist_id <- max(df$id, na.rm = TRUE)

df <- df %>%
  mutate(id = ifelse(
    is.na(id),
    # 累计NA的出现次数作为增量，依次加1
    max_exist_id + cumsum(is.na(id)),
    id
  ))

运行后即可得到你预期的结果。

SQL 实现代码 #

支持标准窗口函数的数据库（MySQL 8.0+、PostgreSQL、Spark SQL等）都可以使用以下逻辑，PySpark场景也可直接复用：

查询场景（生成新ID不修改原表） #

-- 假设你的表名为team_data
WITH global_max AS (
  SELECT COALESCE(MAX(id), 0) AS max_id FROM team_data
)
SELECT
  CASE
    WHEN t.id IS NULL THEN g.max_id + ROW_NUMBER() OVER(PARTITION BY t.id IS NULL ORDER BY 1)
    ELSE t.id
  END AS id,
  t.descriptions
FROM team_data t, global_max g

更新场景（直接修改原表空ID） #

以PostgreSQL为例：

WITH global_max AS (
  SELECT COALESCE(MAX(id), 0) AS max_id FROM team_data
),
na_row_seq AS (
  SELECT
    descriptions,
    ROW_NUMBER() OVER() AS seq
  FROM team_data
  WHERE id IS NULL
)
UPDATE team_data t
SET id = g.max_id + s.seq
FROM global_max g, na_row_seq s
WHERE t.id IS NULL AND t.descriptions = s.descriptions

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