PostgreSQL查询学习路径层级计数错误的修复求助

问题分析 #

原查询中count(learning_path_levels.id)统计的是关联后所有行中层级ID的出现次数——由于每个层级对应多个节点，每个节点又可能关联多条用户记录，导致统计结果等于节点总数（甚至更多），而非实际的层级数量。同时原查询未按层级拆分统计，无法满足“用户每个层级的待完成与已完成节点数”的需求。

修复后的查询 #

针对单个用户的层级统计 #

SELECT
  lp.name AS learning_path_name,
  lpl.name AS level_name,
  COUNT(DISTINCT lpl.id) OVER (PARTITION BY lp.id) AS total_levels_of_path,
  COUNT(lpln.id) AS total_nodes_of_level,
  SUM(CASE WHEN lpn.user_id = [指定用户ID] AND lpn.is_successful THEN 1 ELSE 0 END) AS completed_nodes,
  COUNT(lpln.id) - SUM(CASE WHEN lpn.user_id = [指定用户ID] AND lpn.is_successful THEN 1 ELSE 0 END) AS pending_nodes
FROM learning_paths lp
JOIN learning_path_levels lpl ON lp.id = lpl.learning_path_id
JOIN learning_path_level_nodes lpln ON lpl.id = lpln.learning_path_level_id
LEFT JOIN learning_path_node_users lpn ON lpln.id = lpn.learning_path_level_node_id
WHERE lpn.user_id = [指定用户ID] OR lpn.user_id IS NULL
GROUP BY lp.id, lp.name, lpl.id, lpl.name
ORDER BY lp.name, lpl."order";

针对所有用户的层级统计（按用户+层级拆分） #

SELECT
  lp.name AS learning_path_name,
  lpl.name AS level_name,
  COUNT(DISTINCT lpl.id) OVER (PARTITION BY lp.id) AS total_levels_of_path,
  COUNT(lpln.id) AS total_nodes_of_level,
  lpn.user_id,
  SUM(CASE WHEN lpn.is_successful THEN 1 ELSE 0 END) AS completed_nodes,
  COUNT(lpln.id) - SUM(CASE WHEN lpn.is_successful THEN 1 ELSE 0 END) AS pending_nodes
FROM learning_paths lp
JOIN learning_path_levels lpl ON lp.id = lpl.learning_path_id
JOIN learning_path_level_nodes lpln ON lpl.id = lpln.learning_path_level_id
LEFT JOIN learning_path_node_users lpn ON lpln.id = lpn.learning_path_level_node_id
GROUP BY lp.id, lp.name, lpl.id, lpl.name, lpn.user_id
ORDER BY lp.name, lpl."order", lpn.user_id;

关键修复点 #

1. 层级数统计：使用COUNT(DISTINCT lpl.id) OVER (PARTITION BY lp.id)，通过窗口函数在不改变分组粒度的前提下，精准计算每个学习路径下的唯一层级数量。
2. 层级拆分统计：按learning_path_levels.id分组，确保每个层级单独统计节点完成情况，匹配“每个层级”的统计需求。
3. 完成/待完成节点逻辑：已完成节点数基于用户的is_successful标记统计，待完成节点数通过总节点数减去已完成数得到，逻辑更直观准确。

内容的提问来源于stack exchange，提问作者Jarliev Pérez