如何在SQL Server中实现R的factor()与complete()缺失分组填充功能？

原数据表 #

期望输出表 #

实现思路与SQL代码 #

在SQL Server里可以通过构造全量组合+左连接补0的方式实现，逻辑和R中factor()指定levels、complete()补全配对的思路一致：

1. 先定义所有可能的score取值集合；
2. 提取原表中所有不重复的player；
3. 交叉连接两者得到所有player与score的完整组合；
4. 左连接原表，将未匹配到的count值替换为0。

假设你的原表名为score_data，具体SQL代码如下：

WITH all_scores AS (
    -- 列出所有已知的score取值
    SELECT 'eagle' AS score UNION ALL
    SELECT 'birdie' UNION ALL
    SELECT 'par' UNION ALL
    SELECT 'bogey' UNION ALL
    SELECT 'dbogey'
),
all_players AS (
    -- 获取原表中所有唯一的player
    SELECT DISTINCT player FROM score_data
)
-- 生成全量组合并补全count
SELECT 
    ap.player,
    ascore.score,
    ISNULL(sd.count, 0) AS count
FROM all_players ap
CROSS JOIN all_scores ascore
LEFT JOIN score_data sd 
    ON ap.player = sd.player 
    AND ascore.score = sd.score
ORDER BY ap.player, ascore.score;

代码说明 #

• all_scores 公共表表达式（CTE）：相当于R中factor(score, levels=c(...))指定的所有可能取值；
• all_players CTE：提取原表中所有不重复的玩家，确保每个玩家都能覆盖所有score；
• CROSS JOIN：生成每个玩家和每个score的笛卡尔积，对应complete(player, score)生成的完整配对；
• ISNULL(sd.count, 0)：左连接后，原表中不存在的配对会返回NULL，用ISNULL将其替换为0。

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