總結以前日期的所有唯一值

假設，為簡單起見，我有下表：

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 2``p_id 1

換句話說：到目前為止，總是取amount每個最新的，併計算表中每個不同小時的總和。p_id

簡單案例

獲取單個給定小時的總和相對簡單：

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(). 看：

• SELECT DISTINCT ON，按另一列排序

對於每行的許多行p_id，有可能通過智能索引和相應的查詢進行更多優化。看：

• 優化 GROUP BY 查詢以檢索每個使用者的最新行
• 在每個 GROUP BY 組中選擇第一行？

每小時特別流水

您似乎想要表中每個不同小時的總和。

您可以簡單地提取不同的時間，並將簡單的解決方案應用於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列稱為“日期”會產生誤導，因為這是一種不同的基本數據類型。

引用自：https://dba.stackexchange.com/questions/270521