重疊範圍的最大 sum()

本質上我的問題是：如何在 PostgreSQL 9.3（或 9.4）中進行涉及重疊範圍的聚合操作？我手頭的具體問題是給定一個範圍，我想找到適用重疊範圍的最大 sum() 。一個簡單的例子：

create table event (
 event_id int primary key,
 event_type_id int not null,
 period tstzrange not null,
 quantity int not null
);

insert into event (event_id, event_type_id, period, quantity) values
(1, 1,'[2016-01-06 09:00:00+00,2016-01-08 17:00:00+00]',1),
(2, 1,'[2016-01-07 09:00:00+00,2016-01-07 11:00:00+00]',1),
(3, 1,'[2016-01-07 13:00:00+00,2016-01-07 17:00:00+00]',1),
(4, 2,'[2016-01-07 12:00:00+00,2016-01-07 17:00:00+00]',1);

給定帶有以下子句的查詢：

select ... 
where event_type_id = 1
and period && '[2016-01-07 00:00:00+00,2016-01-07 23:59:00+00]'::tstzrange 
group by event_type_id

期望的結果將是：3，即sum(quantity)相同的範圍event_type_id在給定時間戳範圍內重疊的最大值。

我理解的任務

從表中選擇period與給定時間範圍重疊的行。確定該集合內重疊時段的不同範圍，並sum(quantity)從任何範圍返回最大值。

需要 Postgres 9.2 +，因為舊版本中沒有範圍類型。

假設

• “重疊”是指層疊的方式，就像傳統屋頂上的瓦片一樣：這些是“重疊的”（防雨），儘管最高的瓦片不直接與最低的瓦片重疊。
• 中的所有值period都有包含範圍 ( [])。否則，您必須調整排他邊界。（輸入參數的範圍仍然可以有任意範圍。）
• 我們過濾了一個 event_type_id。否則，您必須添加PARTITION BY event_type_id到視窗定義中。
• quantity是一個integer。否則，您必須針對計算類型進行調整。
• 重疊期間的數量將被完全計算在內，即使該期間的某些部分超出了您的給定時間範圍。
• 甚至適用於(event_type_id, period).

單個子查詢的最佳性能

這應該是炸藥。

SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
FROM  (
  SELECT lower(period) AS p_start
       ,(sum(quantity)                      OVER w)::int AS running_sum
       , lead(lower(period), 1, 'infinity') OVER w
                       > max(upper(period)) OVER w AS range_end
  FROM   event
  WHERE  event_type_id = 1
  AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange 
  WINDOW w AS (ORDER BY lower(period))
  ) sub
WHERE  range_end
ORDER  BY 1 DESC
LIMIT  1;

子查詢中的所有三個視窗函式都可以使用同一個視窗。這避免了額外的排序操作，應該是最快的。

帶有更多解釋的詳細 CTE 變體

相同的查詢，只是更冗長和更慢，因為 CTE 實現派生表並構成優化障礙。

WITH cte1 AS (
  SELECT quantity
       , lower(period) AS p_start
       , upper(period) AS p_end
  FROM   event
  WHERE  event_type_id = 1
  AND    period && '[2016-01-01 0:0+0,2016-01-10 0:0+0]'::tstzrange
  )
,    cte2 AS (
  SELECT (sum(quantity)               OVER w)::int AS running_sum
       , lead(p_start, 1, 'infinity') OVER w               -- next start ..
                         > max(p_end) OVER w AS range_end  -- .. after last end
       , p_start, p_end
  FROM   cte1
  WINDOW w AS (ORDER BY p_start)
  )
SELECT running_sum - lag(running_sum, 1, 0) OVER (ORDER BY p_start) AS sum_quantity
                   -- subtract the previous sum to get the sum of this range
    , p_end::text
FROM   cte2
WHERE  range_end    -- only rows at the end of each range
ORDER  BY 1 DESC    -- biggest sum first
LIMIT  1;           -- only return the winner

sqlfiddle for Postgres 9.3

db<>fiddle here for Postgres 12

您需要一個索引才能快速處理大表。最好的選擇是在(event_type_id, period). 細節：

• 優化對一系列時間戳（兩列）的查詢

解釋

篩選符合條件的行，然後按時間範圍的開始 ( lower(period)) 排序併計算：

1. 數量的執行總和 ( running_sum)。
2. 下一個時期的開始：( lead(lower(period), 1, 'infinity'))。最後一行預設為“無窮大”以包括最後一個範圍。
3. 迄今為止任何時期的最晚結束max(upper(period))。

如果*2.晚於3.*它是（子）範圍 ( range_end) 的結尾。

在外部SELECT過濾器行中，使用range_end並減去先前的總數以獲得範圍的總和。ORDER BY該結果並返回第一個 ( LIMIT 1) 最大的sum_quantity。瞧。

在旁邊

要選擇 2016 年 1 月 7 日的所有內容，簡潔的表達式是：

'[2016-01-07 00:00:00+00,2016-01-**08 00:00:00+00)**'::tstzrange

不是：

'[2016-01-07 00:00:00+00,2016-01-**07 23:59:00+00]**'::tstzrange

細節：

• PostgreSQL 將時間範圍拆分為天

由於時間戳值的預設精度是 6 位十進制數字（微秒解析度），您還可以使用：

'[2016-01-07 00:00:00+00,2016-01-**07 23:59:59.999999+00]**'::tstzrange

但這很混亂，並且取決於可能會更改（即使不太可能）的實現細節。它不受舍入錯誤的影響，因為時間戳在現代 Postgres 中儲存為整數值：

• 在 Rails 和 PostgreSQL 中完全忽略時區

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