如何在 PostgreSQL 中取得每分鐘運行的行數？

PostgreSQL：以分鐘計算運行行數

本文示範如何取得查詢的運行行數（在 PostgreSQL 中按分鐘分組）。 挑戰在於準確計算每分鐘的行數，即使是那些沒有活動的行。

利用視窗函數

最有效的解決方案是利用 PostgreSQL 強大的視窗函數。 這些函數對行集進行操作，從而實現跨多行的計算。 在這裡，我們在按分鐘分區的視窗中使用 COUNT 函數。

使用 SELECT DISTINCT 和 ORDER BY

進行高效查詢

此查詢使用 SELECT DISTINCT 確保唯一的分鐘條目，並使用帶有視窗函數的 COUNT 來產生運行計數。 ORDER BY 確保每分鐘間隔的正確累積和。

SELECT DISTINCT
       date_trunc('minute', "when") AS minute,
       count(*) OVER (ORDER BY date_trunc('minute', "when")) AS running_ct
FROM   mytable
ORDER  BY 1;

理解視窗函數語法

讓我們分解一下視窗函數：

• COUNT(*)：計算每個分鐘分區內的行數。
• OVER (ORDER BY date_trunc('minute', "when"))：將視窗定義為一系列行，按分鐘排序，從頭開始累積計數。

零活動處理分鐘

要包含沒有活動的分鐘（零行），我們使用 generate_series 建立一系列分鐘，然後執行 LEFT JOIN 來包含這些分鐘，即使它們在表中缺少相應的條目：

SELECT DISTINCT
       minute, count(c.minute) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT generate_series(date_trunc('minute', min("when")), max("when"), interval '1 min')
   FROM   mytable
   ) m(minute)
LEFT   JOIN (SELECT date_trunc('minute', "when") AS minute FROM mytable) c USING (minute)
ORDER  BY 1;

效能注意事項

雖然視窗函數通常最適合大型資料集，但對於較小的資料集，使用 SUM() 的替代方案可能會提供更好的效能。此方法首先將每分鐘的行分組和計數，然後使用 SUM() 計算運行總計：

SELECT minute, sum(minute_ct) OVER (ORDER BY minute) AS running_ct
FROM  (
   SELECT date_trunc('minute', "when") AS minute,
        count(*) AS minute_ct
   FROM   tbl
   GROUP  BY 1
   ) sub
ORDER  BY 1;

最佳方法取決於您的資料集大小和效能要求。 建議測試這兩種方法，以確定適合您的特定用例的最有效的解決方案。

以上是如何在 PostgreSQL 中取得每分鐘運行的行數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！