学习 SQL - 迷失在 SQL 游戏中

SELECT
    a.path,
    (b.total_clicks - a.total_clicks) AS diff_total_clicks,
    (b.unique_keywords - a.unique_keywords) AS diff_unique_keywords
FROM
    (SELECT path, total_clicks, unique_keywords FROM search_data WHERE date = 'most_recent_date') a
JOIN
    (SELECT path, total_clicks, unique_keywords FROM search_data WHERE date = 'most_recent_date_minus_two_days') b
ON a.path = b.path;

每个路径都有一个最近的日期：

select path, max(pt)
from search_data
group by path;

您应该选择每个路径的最大日期和前一天的数据，然后聚合数据。然后，您将在前两天执行相同的操作，然后比较结果。

因此，对于每条路径，我们感兴趣的是需要路径最大日期的四天。我们可以在聚合查询中获取它，如上所示，也可以使用窗口函数即时获取它： .MAX(pt) OVER (PARTITION BY path)

with
  data as
  (
    select sd.*, max(pt) over (partition by path) as max_pt
    from search_data sd
  )
select
  path,
  sum(case when pt >= date(max_pt, '-1 day') then clicks else -clicks end)
    as diff_total_clicks,
  count(distinct case when pt >= date(max_pt, '-1 day') then query end) -
  count(distinct case when pt <  date(max_pt, '-1 day') then query end)
    as diff_unique_keywords
from data
where pt >= date(max_pt, '-3 days')
group by path
order by diff_total_clicks desc;

不知道表格，我使用了与您相同的列。我希望这个查询是不言自明的。在聚合函数调用中使用表达式称为“条件聚合”。它可以帮助两个人同时处理不同的聚合（过去两天与此处的前两天）。CASE

我的查询未通过测试。我已经试过了，但收到错误消息：

结果是缺少 1 个预期的行。

结果中有 1 行不正确。

我看不出怎么会缺少一行。我按路径分组，并选择每个路径的所有行，其中 pt 大于或等于其最大 pt 减去三天。因此，我至少应该为每个路径获得一个 maximum-pt 行。一定是SQLite奇怪的日期/时间处理，其中不存在正确的数据类型或类似的东西。不幸的是，我在该站点上没有看到表定义。我们也没有看到选定的数据和预期的数据来得出结论。也许SQLite专业人士可以在这里提供帮助。