主页 / dba / 问题 / 270521
Accepted
tony
Asked: 2020-07-07 19:23:47 +0800 CST

总结以前日期的所有唯一值

  • 772

假设，为简单起见，我有下表：

id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00 
3       15         2        2020-01-01T01:20:00 
4       10         3        2020-01-01T03:30:00 
5       10         4        2020-01-01T03:50:00 
6       20         1        2020-01-01T03:40:00

这是我想要的示例响应：

{
 "2020-01-01T01:00:00": 25, -- this is from adding records with ids: 2 and 3
 "2020-01-01T03:00:00": 55  -- this is from adding records with ids: 3,4,5 and 6
}

我想得到按小时分组sum(amount)的所有 unique 的总数（ ） 。p_id

每个选择的行p_id是最新的date。因此，例如，上述响应中的第一个值不包括在内id 1，因为记录id 2具有相同p_id且该date行上的记录较晚。

一件棘手的事情是我想包括所有amount每个人的总和，p_id如果他们date在提出的时间之前。因此，例如，在响应的第二个值（键为“2020-01-01T03:00:00”）中，即使id 3时间戳在不同的时间，它也是最新的，p_id 2因此包含在总和中“2020-01-01T03:00:00”。但是具有相同id 6覆盖的行。id 2p_id 1

换句话说：到目前为止，总是取amount每个最新的，并计算表中每个不同小时的总和。p_id

postgresql greatest-n-per-group

2 个回答

  1. 寻找

    WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY p_id ORDER BY date DESC) rn
              FROM source_table
              WHERE date_trunc('hour', date) = @needed_datetime_truncated_to_hour )
SELECT @needed_datetime_truncated_to_hour, SUM(amount)
FROM cte
WHERE rn = 1;
    • 1
  2. Best Answer

    简单案例

    获取单个给定小时的总和相对简单：

    SELECT timestamp '2020-01-01 03:00:00', sum(amount)
FROM  (
   SELECT DISTINCT ON (p_id) amount
   FROM   tbl
   WHERE  date < timestamp '2020-01-01 03:00:00' + interval '1h'
   ORDER  BY p_id, date DESC
   ) sub;

    DISTINCT ON通常比使用row_number(). 看：

    对于每行的许多行p_id，有可能通过智能索引和相应的查询进行更多优化。看：

    每小时特别流水

    您似乎想要表中每个不同小时的总和。

    您可以简单地提取不同的时间，并将简单的解决方案应用于LATERAL子查询中的任何一个：

    WITH grid AS (  -- or some other source?
   SELECT DISTINCT date_trunc('hour', date) AS hour
   FROM   tbl
   )
SELECT *
FROM   grid
CROSS  JOIN LATERAL (
   SELECT sum(amount)
   FROM  (
      SELECT DISTINCT ON (p_id) amount
      FROM   tbl
      WHERE  date < grid.hour + interval '1h'
      ORDER  BY p_id, date DESC
      ) sub1
   ) sub2
ORDER  BY grid.hour;

    应该没问题，虽然没有很多不同的时间。但是查询的扩展性不是很好。每增加一小时以全部（稳步增加）成本计算amount每一个的最新值。p_id

    优化运行时间数小时

    p_id我希望这个递归 CTE 能够更好地扩展（虽然最初有相当大的开销），因为它只需要每隔一小时加入一行：

    WITH RECURSIVE
  values AS (
   SELECT DISTINCT ON (1, 2)
          date_trunc('hour', date) AS hour, p_id, amount
   FROM   tbl
   ORDER  BY 2, 1, date DESC  -- !
   )
, hours AS (
   SELECT hour, row_number() OVER (ORDER BY hour) AS hnr
   FROM  (SELECT DISTINCT hour FROM values) sub
   )
, cte AS (
   SELECT p_id, h.hour, v.amount, 2 AS next_hnr
   FROM  (SELECT DISTINCT p_id FROM values) i  -- all IDs
   JOIN   hours       h ON hnr = 1             -- first hour
   LEFT   JOIN values v USING (p_id, hour)

   UNION ALL
   SELECT c.p_id, h.hour, COALESCE(v.amount, c.amount), c.next_hnr + 1
   FROM   cte         c
   JOIN   hours       h ON h.hnr  = c.next_hnr
   LEFT   JOIN values v ON v.p_id = c.p_id
                       AND v.hour = h.hour
   )
SELECT hour, sum(amount)
FROM   cte
GROUP  BY 1
ORDER  BY 1;

    此外，根据价值分布，可能还有更多的优化潜力，如顶部的简单案例所示。

    db<>在这里摆弄

    另外：将您的timestamp列称为“日期”会产生误导，因为这是一种不同的基本数据类型。

    • 0

相关问题