sql - 根据数据汇总今天和前一天

在组合查询以提取给定时间戳的聚合值及其之前的时间戳时遇到问题。给定以下架构：

name TEXT,
ts TIMESTAMP,
X NUMERIC,
Y NUMERIC

当ts列中由于数据中的间隙而出现间隙时，我试图构造一个查询来生成

name,
date_trunc('day' q1.ts),
avg(q1.X),
sum(q2.Y),
date_trunc('day', q2.ts),
avg(q2.X),
sum(q2.Y)

上半场很简单：

SELECT q1.name, date_trunc('day', q1.ts), avg(q1.X), sum(q1.Y)
FROM data as q1
GROUP BY 1, 2
ORDER BY 1, 2;

但不知道如何生成关系以查找每行的“前一天”。我正在尝试这样的内部连接：

SELECT q1.name, q1.day, q1.avg, q1.sum, q2.day, q2.avg, q2.sum
FROM (
    SELECT name, date_trunc('day', ts) AS day, avg(X) AS avg, sum(Y) as sum
    FROM data
    GROUP BY 1,2
    ORDER BY 1,2
) q1 INNER JOIN (
    SELECT name, date_trunc('day', ts) AS day, avg(X) AS avg, sum(Y) as sum
    FROM data
    GROUP BY 1,2
    ORDER BY 1,2
) q2 ON (
    q1.name = q2.name
    AND  q2.day = q1.day - interval '1 day'
);

问题是，当下一个“日”比当前日期早1天以上时，它不包括这些情况。

最佳答案

这里的特殊困难是，您需要在聚合行之后计算天数。使用窗口函数row_number()可以在单个查询级别执行此操作，因为窗口函数是在通过GROUP BY聚合后应用的。
另外，使用CTE可避免多次执行同一子查询：

WITH q AS (
    SELECT name, ts::date AS day
          ,avg(x) AS avg_x, sum(y) AS sum_y
          ,row_number() OVER (PARTITION BY name ORDER BY ts::date) AS rn
    FROM   data
    GROUP  BY 1,2
   )
SELECT q1.name, q1.day, q1.avg_x, q1.sum_y
      ,q2.day AS day2, q2.avg_x AS avg_x2, q2.sum_y AS sum_y2
FROM   q      q1
LEFT   JOIN q q2 ON q1.name = q2.name
                AND q1.rn   = q2.rn + 1
ORDER  BY 1,2;

使用更简单的cast to date（ts::date）而不是date_trunc('day', ts)来获取“天”。
LEFT [OUTER] JOIN（与[INNER] JOIN相反）有助于保留第一行的角情况，因为没有前一天。
并且ORDER BY应该应用于外部查询。

关于sql - 根据数据汇总今天和前一天，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20746360/