用過去 1 小時的時間值減去目前時間值

表名：數據

我想要一個 PostgreSQL 查詢，其中Result帶有公式的列（目前時間值 - （一小時前的值））如果減去 (7:45-6:45) 的數據，您將得到 7:45 的結果值。

• 時間四捨五入到最接近的分鐘。
• 測量次數可能會因小時而異。
• 這裡有一個小提琴。

這很棘手。如果每小時的測量次數是恆定的，那麼LAG()orLEAD()函式對於這項任務來說是完美的，但事實並非如此，所以我們必須以不同的方式進行。

我們將不得不使用SELF JOIN.

我將舉一個我用來獲得答案並展示我的構想的“探索性”SQL 的範例。下面的所有程式碼都可以在 fiddle here上找到。

桌子：

create table data
(
 timetable timestamp, 
 information NUMERIC,
 answer NUMERIC 
);

數據可以在小提琴中找到。

--
-- "Exploratory" SQL
--

SELECT 
 DATE_TRUNC('minute', d1.timetable), d1.information, d2.information,
 date_trunc('minute', d1.timetable) - date_trunc('minute', d2.timetable),
 d1.information, d2.information,
 d1.information -  d2.information
FROM 
 data d1
LEFT join
 data d2    -- <<==== Note! JOINing the table back on to itself!
ON 
 date_trunc('minute', d2.timetable) = 
 date_trunc('minute', d1.timetable - INTERVAL '1 hour')
ORDER BY d1.timetable DESC;

JOIN將表重新置於自身 = SELF JOIN. 我們使用 a 是LEFT JOIN因為，正如我們從下面的結果中看到的，結果集中有相當數量的NULLs，我們需要返回（至少其中一些）這些來獲得所需的結果。

結果：

        date_trunc information information  ?column? information information   ?column?
2022-09-05 07:45:00       12.0          3.0  01:00:00       12.0          3.0   9.0
2022-09-05 07:44:00       10.0          2.0  01:00:00       10.0          2.0   8.0
2022-09-05 07:43:00       13.00         1.0  01:00:00       13.00         1.0   12.00
2022-09-05 07:42:00       31.0          2.0  01:00:00       31.0          2.0   29.0
2022-09-05 07:41:00       13.0          1.0  01:00:00       13.0          1.0   12.0
2022-09-05 07:40:00      123.0         null      null      123.0         null   null
2022-09-05 07:39:00        2.0          3.0  01:00:00        2.0          3.0   -1.0
2022-09-05 06:45:00        3.0         null      null        3.0         null   null
2022-09-05 06:44:00        2.0         null      null        2.0         null   null
2022-09-05 06:43:00        1.0         null      null        1.0         null   null
2022-09-05 06:42:00        2.0         null      null        2.0         null   null
2022-09-05 06:41:00        1.0         null      null        1.0         null   null
2022-09-05 06:39:00        3.0         null      null        3.0         null   null

因此，我們擁有所有必需的欄位，現在我們整理它們並產生所需的結果。我已經決定了使用 PostgreSQL 的雙冒號 ( ) 運算符的CAST所有差異。我還使用該函式將s 變為零。INT``::COALESCE()NULL

SELECT 
 DATE_TRUNC('minute', d1.timetable) AS "Timetable",
 d1.information::INTEGER,
 COALESCE(d1.information - d2.information, 0)::INTEGER  AS answer
FROM 
 data d1
LEFT JOIN data d2 
 ON DATE_TRUNC('MINUTE', d2.timetable) = 
    DATE_TRUNC('MINUTE', d1.timetable - INTERVAL '1 HOUR')
ORDER BY d1.timetable DESC;

結果：

Timetable           information answer
2022-09-05 07:45:00          12      9
2022-09-05 07:44:00          10      8
2022-09-05 07:43:00          13     12
2022-09-05 07:42:00          31     29
2022-09-05 07:41:00          13     12
2022-09-05 07:40:00         123      0
2022-09-05 07:39:00           2     -1
2022-09-05 06:45:00           3      0
2022-09-05 06:44:00           2      0
2022-09-05 06:43:00           1      0
2022-09-05 06:42:00           2      0
2022-09-05 06:41:00           1      0
2022-09-05 06:39:00           3      0

需要注意的幾點：

• 在您的表中，您可以將informationandanswer欄位設置為INTEGERs 而不是NUMERICs - 使結果更清晰並使用更少的資源 - 但也許您有其他記錄確實有小數部分？

• 對於結果

  • 2022-09-05 07:40:00 123 0你自找的

  • 2022-09-05 07:40:00 123 123這對我來說毫無意義——我把它和其他人一樣留下了COALESCE()，即顯示為零。該列中的所有其他零answer也必須顯示該information列中的數據。此外，它非常令人困惑——人們會將原始數據與結果混淆。

• 將來，當問這樣的問題時，請始終包括小提琴。這是對那些試圖幫助你的人的一種禮貌（它消除了我們/他們的重複努力），它為問題提供了一個單一的事實

• 最後，在回複評論時，請將所要求的額外資訊放回問題本身。您可以通過在他們下方的評論中@加上 + 他們的句柄（即如果回复我，您會輸入）來通知提出請求的人@Vérace

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