LeetCode Investments in 2016问题SQL查询错误排查求助

题目要求 #

统计满足以下两个条件的投保人2016年总投资金额tiv_2016的总和，结果需保留两位小数：

• tiv_2015值与至少一位其他投保人相同
• (lat, lon)坐标对唯一（所在位置无重复）

我的查询语句 #

WITH lattu AS (
  SELECT CONCAT_WS('_', lat, lon) AS lat_lon
  FROM insurance
  GROUP BY CONCAT_WS('_', lat, lon)
  HAVING COUNT(CONCAT_WS('_', lat, lon)) < 2
)

SELECT Round(sum(tiv_2016),2) as tiv_2016 
FROM insurance
WHERE CONCAT_WS('_', lat, lon) IN (SELECT lat_lon FROM lattu)
group by tiv_2015
HAVING COUNT(tiv_2015) > 1;

问题情况 #

上述查询在测试用例中未得到正确输出：

• 测试用例数据示例：

  pid	tiv_2015	tiv_2016	lat	lon
  1	10	5	10	10
  2	20	20	20	20
  3	10	30	20	20
  4	10	45	40	40

• 输出对比：我的输出为45.00，预期输出为90.00
• 结果格式要求：输出的tiv_2016必须保留两位小数（如123.45）

错误分析 #

你的查询存在两个核心问题：

1. 分组逻辑错误：题目要求的是所有符合条件的记录的tiv_2016总和，不需要按tiv_2015分组。你用GROUP BY tiv_2015后，只会对每个tiv_2015值单独求和，导致遗漏同一tiv_2015下其他符合条件的记录。
2. 条件筛选逻辑偏差：先筛选坐标唯一的记录再按tiv_2015分组统计，会把符合条件的记录拆分到不同分组中，无法得到整体的总和。

正确解法 #

方法1：子查询筛选交集 #

通过子查询分别找出符合两个条件的tiv_2015和坐标对，再取交集后求和：

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM insurance
WHERE 
  -- 条件1：tiv_2015存在至少一个其他相同值
  tiv_2015 IN (
    SELECT tiv_2015
    FROM insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
  )
  -- 条件2：(lat, lon)坐标唯一
  AND (lat, lon) IN (
    SELECT lat, lon
    FROM insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
  );

方法2：窗口函数高效计算 #

用窗口函数一次性计算每个记录对应的tiv_2015出现次数和坐标出现次数，再过滤条件求和：

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM (
  SELECT 
    tiv_2016,
    -- 统计当前tiv_2015的总出现次数
    COUNT(*) OVER (PARTITION BY tiv_2015) AS cnt_tiv2015,
    -- 统计当前坐标对的总出现次数
    COUNT(*) OVER (PARTITION BY lat, lon) AS cnt_coord
  FROM insurance
) AS sub
WHERE cnt_tiv2015 > 1 AND cnt_coord = 1;

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