選擇間隔前的第一個時間戳，如果沒有合適的間隔，則選擇當天的最後一個時間戳

我有一個 TIMESTAMP 列：

dates
2021-06-24 05:47:05
2021-06-24 09:47:05
2021-06-24 13:47:05
2021-06-24 17:47:05

我想選擇在同一天的下一個時間戳之前 3 小時或更長時間的給定日期的第一個時間戳。

expected output:

2021-06-24 05:47:05

但是，如果沒有時間戳比任何其他時間戳（在給定日期）早 3 小時以上，則應返回當天的最後一個時間戳。

這是一個完全修改過的答案，比前一個更有效。舊答案可以通過查看編輯歷史或作為本文底部的腳註來查看。

下面的所有程式碼的小提琴都可以在這裡的小提琴中找到。

所以，我們有我們的測試表：

CREATE TABLE test
(
 the_date TIMESTAMP NOT NULL
);

填充它 - 添加一天的記錄，沒有間隔 > 3 小時：

INSERT INTO test VALUES


('2021-06-23 05:47:05'::TIMESTAMPTZ),     -- NO gaps > 3 hours on this date!
('2021-06-23 07:47:05'::TIMESTAMPTZ),
('2021-06-23 09:47:05'::TIMESTAMPTZ),
('2021-06-23 11:47:05'::TIMESTAMPTZ),
('2021-06-23 13:47:05'::TIMESTAMPTZ),
('2021-06-23 14:47:05'::TIMESTAMPTZ),  
('2021-06-23 16:47:05'::TIMESTAMPTZ),  
('2021-06-23 17:47:05'::TIMESTAMPTZ),
   

('2021-06-24 05:47:05'::TIMESTAMPTZ),  -- TWO gaps > 3 hours on this date

                                     -- 1st gap > 3 hours

('2021-06-24 09:47:05'::TIMESTAMPTZ),

                                     -- 2nd gap > 3 hours

('2021-06-24 13:47:05'::TIMESTAMPTZ),

('2021-06-24 14:47:05'::TIMESTAMPTZ),  -- added for testing
('2021-06-24 16:47:05'::TIMESTAMPTZ),  -- added for testing


('2021-06-24 17:47:05'::TIMESTAMPTZ);

並且（展示邏輯）然後執行以下 SQL：

SELECT
 the_date::DATE AS dat, 
 the_date AS td, 
 LEAD(the_date) 
   OVER (PARTITION BY the_date::DATE 
          ORDER BY the_date ASC) AS l_td,
 LEAD(the_date) 
   OVER (PARTITION BY the_date::DATE 
           ORDER BY the_date ASC) - the_date AS diff  -- for demonstration
FROM                                                   -- purposes - see diffs
 test                                                 -- > 3 HOUR - 2 on 24/06
ORDER BY dat, td;

結果：

      dat                      td                   l_td   diff
2021-06-23  2021-06-23 05:47:05+01  2021-06-23 07:47:05+01  02:00:00
2021-06-23  2021-06-23 07:47:05+01  2021-06-23 09:47:05+01  02:00:00
2021-06-23  2021-06-23 09:47:05+01  2021-06-23 11:47:05+01  02:00:00
2021-06-23  2021-06-23 11:47:05+01  2021-06-23 13:47:05+01  02:00:00
2021-06-23  2021-06-23 13:47:05+01  2021-06-23 14:47:05+01  01:00:00
2021-06-23  2021-06-23 14:47:05+01  2021-06-23 16:47:05+01  02:00:00
2021-06-23  2021-06-23 16:47:05+01  2021-06-23 17:47:05+01  01:00:00
2021-06-23  2021-06-23 17:47:05+01  NULL                    NULL        
2021-06-24  2021-06-24 05:47:05+01  2021-06-24 09:47:05+01  04:00:00
2021-06-24  2021-06-24 09:47:05+01  2021-06-24 13:47:05+01  04:00:00
2021-06-24  2021-06-24 13:47:05+01  2021-06-24 14:47:05+01  01:00:00
2021-06-24  2021-06-24 14:47:05+01  2021-06-24 16:47:05+01  02:00:00
2021-06-24  2021-06-24 16:47:05+01  2021-06-24 17:47:05+01  01:00:00
2021-06-24  2021-06-24 17:47:05+01  NULL                    NULL        
14 rows

我們已經使用了LEAD()視窗函式。視窗功能非常強大，我強烈建議您花一些精力來學習如何使用它們 - 他們會多次回報您的努力！

• the_date它根據中的標準提供了值和它後面的值之間的比較-您可以通過更改函式本身的子句ORDER BY來做很多聰明的事情-可以在此處看到更改其他參數。ORDER BY``LEAD()
• 該PARTITION BY the_date::DATE子句是為數據集中的每個日期提供單獨的結果。特別注意 NULL - 由於分區，您不能擁有跨越數天的 LEAD，因此任何給定日期的最後一個時間戳的 LEAD 值將始終是NULL- 這與要求有關 - 見下文。

另外，請注意NULL減去任何東西NULL（NULL加號相同） - 我們說NULLs “傳播”。

所以，現在我們執行這個 SQL：

WITH leads AS
(
   SELECT
     the_date::DATE AS dat, the_date AS td, LEAD(the_date)
         OVER (PARTITION BY the_date::DATE) AS l_td
   FROM
     test
)
SELECT DISTINCT ON(dat)
   dat AS "The date", td AS "Gap start or last ts"
FROM leads
WHERE l_td - td > INTERVAL '3 HOUR'
  OR l_td IS NULL
ORDER BY dat, td;

結果：

The date    Gap start or last ts
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01

想要的結果！但是，這是怎麼回事？從這裡：

PostgreSQL 有一個非常有趣且強大的構造，稱為 SELECT DISTINCT ON。不，這不是典型的 DISTINCT。這是不同的。當您擁有相似的數據組並希望根據特定順序從每個組中提取一條記錄時，這是完美的選擇。

或者，換一種方式（來自同一個連結）：

使用 DISTINCT ON，您告訴 PostgreSQL 為 ON 子句定義的每個不同組返回一行。返回該組中的哪一行由 ORDER BY 子句指定。

或者從這裡的 PostgreSQL 文件：

SELECT DISTINCT ON ( 表達式

$$ , … $$) 只保留給定表達式計算結果為相等的每組行的第一行。DISTINCT ON 表達式使用與 ORDER BY 相同的規則進行解釋（見上文）。請注意，除非使用 ORDER BY 來確保所需的行首先出現，否則每組的“第一行”是不可預測的。例如：

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

檢索每個位置的最新天氣報告。但是，如果我們沒有使用 ORDER BY 來強制每個位置的時間值降序排列，我們就會從每個位置的不可預測的時間得到一個報告。

DISTINCT ON 表達式必須匹配最左邊的 ORDER BY 表達式。ORDER BY 子句通常包含附加表達式，這些表達式確定每個 DISTINCT ON 組中行的所需優先級。

如您所見，這（如視窗函式）顯然是 PostgreSQL 程序員武器庫中非常強大的工具，非常值得花時間和精力去學習。

一個有趣的替代方法是使用ROW_NUMBER()視窗函式，如果你想要前兩個間隙或最後一個記錄，如下所示：

WITH leads AS
(
   SELECT
     the_date::DATE AS dat, the_date AS td,
     LEAD(the_date)
         OVER (PARTITION BY the_date::DATE) AS l_td
   FROM
     test
),
gaps AS
(
   SELECT
     dat, td,
     ROW_NUMBER()
         OVER (PARTITION BY dat ORDER BY td) AS rn
   FROM leads
   WHERE (l_td - td > INTERVAL '3 HOUR')
     OR (l_td IS NULL)
)
SELECT
   dat, td
FROM gaps
WHERE rn <= 2  -- NOTE 2!
ORDER BY dat, td;

結果：

      dat                      td
2021-06-23  2021-06-23 17:47:05+01
2021-06-24  2021-06-24 05:47:05+01
2021-06-24  2021-06-24 09:47:05+01

請注意，我們現在有 2021-06-24 的兩條記錄。

最後，僅作記錄，原始解決方案：

WITH long_gaps AS
(
 SELECT dat, MIN(td) AS gap
 FROM
 (
   SELECT
     the_date::DATE AS dat, the_date AS td, LEAD(the_date) OVER (PARTITIION BY the_date::DATE) AS l_td
   FROM
     test
 ) AS t1
 WHERE l_td - td > INTERVAL '3 HOUR'
 GROUP BY dat
),
short_gaps AS
(
 SELECT the_date::DATE AS dat2, MAX(the_date)
 FROM test
 WHERE the_date::DATE NOT IN (SELECT dat FROM long_gaps)
 GROUP BY dat2

)
SELECT dat AS "The date", gap AS "Gap start or last ts" FROM long_gaps
UNION 
SELECT * FROM short_gaps
ORDER BY 1;  -- parameter 1 which ORDERs BY the first field in the query

結果：

 The date     Gap start or last ts
2021-06-23   2021-06-23 17:47:05+01
2021-06-24   2021-06-24 05:47:05+01

小提琴底部給出了 3 種解決方案的性能分析 - 它表明該DISTINCT ON解決方案的性能明顯優於其他解決方案 - 但是ROW_NUMBER()有可能更加靈活！然而，一個警告 - 在我們無法控制的伺服器上對非常小的數據集進行性能分析，也不知道其他地方發生了什麼可能存在缺陷 - 我建議您在自己的硬體上使用合理的數據集進行基準測試。

將來，當您提出這種性質的問題時，您能否提供一個涵蓋您所有案例的樣本數據 - 即在這種情況下，哪些地方存在差距，哪些地方沒有。這減少了出錯的可能性並消除了重複勞動——幫助我們為您提供幫助。另外，請始終包含您的 PostgreSQL 版本。

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