Lost at SQL游戏Search关卡SQL查询求解请求

我在学习SQL时发现了一款SQL教学游戏《Lost at SQL》，目前卡在了「Search」关卡，想求助解决该关卡的SQL编写问题。

需求说明 #

• 返回包含path、diff_total_clicks、diff_unique_keywords三列的表；
• 针对每个path，计算最近2天与之前2天的点击量差值、唯一关键词差值；
• 按点击量变化（diff_total_clicks）降序排序。

现有代码 #

我认为自己编写的JOIN语句存在问题，以下是目前完成的代码：

SELECT
  S.path,
  coalesce(sum(clicks) - XDtotal_clicks, sum(clicks)) as diff_total_clicks,
  coalesce(Count(distinct query) - XDunique_query,Count(distinct query))  as diff_unique_keywords

from
  search_data as S
  LEFT JOIN (
    SELECT
      path,
      sum(clicks) as XDtotal_clicks,
      Count(distinct query) as XDunique_query
    from
      search_data
    where
      pt < '2022-04-31'
      and pt > '2022-04-29'
    GROUP BY
      path
  ) as XD on XD.path = S.path
where
  pt > '2022-04-31'
GROUP BY
  S.path

问题分析 #

现有代码存在几个核心问题：

1. 日期逻辑错误：4月没有31号，'2022-04-31'是无效日期，导致时间范围过滤完全错误——子表仅能匹配到2022-04-30一天（而非两天），主表的pt > '2022-04-31'则无任何数据返回；
2. JOIN逻辑冗余且易出错：使用LEFT JOIN拆分两个时间区间的统计，不仅代码繁琐，还容易出现匹配不到的NULL值处理问题；
3. 聚合范围不准确：主表的聚合操作没有限定时间范围，若search_data表存在其他日期数据，会导致统计结果错误。

修正后的代码 #

推荐使用条件聚合的方式实现需求，逻辑更清晰且高效：

SELECT
  path,
  -- 计算最近2天与之前2天的点击量差值
  SUM(CASE WHEN pt BETWEEN '2022-04-30' AND '2022-05-01' THEN clicks ELSE 0 END)
  - SUM(CASE WHEN pt BETWEEN '2022-04-28' AND '2022-04-29' THEN clicks ELSE 0 END) AS diff_total_clicks,
  -- 计算最近2天与之前2天的唯一关键词差值
  COUNT(DISTINCT CASE WHEN pt BETWEEN '2022-04-30' AND '2022-05-01' THEN query END)
  - COUNT(DISTINCT CASE WHEN pt BETWEEN '2022-04-28' AND '2022-04-29' THEN query END) AS diff_unique_keywords
FROM search_data
-- 仅筛选需要的4天数据，提升查询效率
WHERE pt BETWEEN '2022-04-28' AND '2022-05-01'
GROUP BY path
-- 按点击量变化降序排序
ORDER BY diff_total_clicks DESC;

修正说明 #

1. 日期修正：明确指定最近2天为2022-04-30、2022-05-01，之前2天为2022-04-28、2022-04-29，避免无效日期；
2. 条件聚合替代JOIN：通过CASE WHEN在同一聚合查询中分别统计两个时间区间的数据，无需拆分表再JOIN，逻辑更直观；
3. 自动处理空值：若某个时间区间内某path无数据，对应的聚合结果为0，差值计算自然正确，无需额外用COALESCE处理；
4. 范围过滤优化：仅筛选需要的4天数据，减少不必要的计算。

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