從範圍數據中獲取聚合的最佳方法
我遇到的問題如下：
我有“班次”表，其中包含我想要匯總的以下範圍數據：
ID | started_at | ended_at | costs 1 | 2022-08-23 08:00 | 2022-08-23 11:00 | 150 2 | 2022-08-23 08:30 | 2022-08-23 09:30 | 50目前使用MySQL，如何獲取以下數據：
time | total_costs | total_count 2022-08-23 08:00 | 75 | 2 2022-08-23 09:00 | 75 | 2 2022-08-23 10:00 | 50 | 1或者您是否建議使用專門為此類數據建構的其他數據庫？
**編輯：**很抱歉給您帶來不便。時間是 2022-08-23 08:00-09:00、2022-08-23 09:00-10:00 等間隔。因此，屬於該
total_costs區間sum(calculated_costs)的班次。calculated_costs = (cost / total_duration) * duration_in_interval (150 / 3 hours) * 1 hour = 50total_count = 在目前時間/前後具有started_at 和ended_at 的班次的計數（*）（間隔）
希望有人能指出我正確的方向。謝謝！
為了回答您的問題，我執行了以下操作（下面的所有程式碼都可以在此處的小提琴中找到）：
我決定使用兩種方法——一種是“經典”，另一種是使用 PostgreSQL 的範圍類型。
方法 1 - 開始時間/結束時間
CREATE TABLE test ( t_id INTEGER NOT NULL, started_at TIMESTAMP NOT NULL, ended_at TIMESTAMP NOT NULL, costs INTEGER NOT NULL );
- 值得注意的是，最好養成
TIMESTAMPTZ隨時使用的習慣——我只是TIMESTAMP在這裡使用過，因為它更易讀，時區不是問題的一部分。填充：
INSERT INTO test VALUES (1, '2022-08-23 08:00', '2022-08-23 11:00', 150), (2, '2022-08-23 08:30', '2022-08-23 09:30', 50);第一步：
SELECT t_id, GENERATE_SERIES(started_at, ended_at - INTERVAL '1 HOUR', '1 HOUR') AS st, GENERATE_SERIES(started_at + INTERVAL '1 HOUR', ended_at, '1 HOUR') AS et, costs FROM test; t_id st et costs 1 2022-08-23 08:00:00 2022-08-23 09:00:00 150 1 2022-08-23 09:00:00 2022-08-23 10:00:00 150 1 2022-08-23 10:00:00 2022-08-23 11:00:00 150 2 2022-08-23 08:30:00 2022-08-23 09:30:00 50
- 我們使用了無與倫比的 PostgreSQL 功能
GENERATE_SERIES（手冊）——為什麼其他供應商還沒有實現它，這超出了我的理解。- 增加和減少小時數涉及到一些玩弄遊戲，這樣我們就不會超出
started_at和ended_at時間戳的界限。第二步：
我留下了額外的欄位，以便您可以輕鬆查看發生了什麼 - 根據需要刪除。
WITH hours AS ( SELECT t_id, GENERATE_SERIES(started_at, ended_at - INTERVAL '1 HOUR', '1 HOUR') AS st, GENERATE_SERIES(started_at + INTERVAL '1 HOUR', ended_at, '1 HOUR') AS et, costs FROM test ) SELECT *, COUNT(*) OVER (PARTITION BY t_id), costs/COUNT(*) OVER (PARTITION BY t_id) AS hourly_cost FROM hours;結果：
t_id st et costs count hourly_cost 1 2022-08-23 08:00:00 2022-08-23 09:00:00 150 3 50 1 2022-08-23 09:00:00 2022-08-23 10:00:00 150 3 50 1 2022-08-23 10:00:00 2022-08-23 11:00:00 150 3 50 2 2022-08-23 08:30:00 2022-08-23 09:30:00 50 1 50因此，根據數據，我們可以看到每個班次的每小時成本為 50。我檢查了不同的成本（600），得到的 hourly_cost 為 200，所以它似乎可以正常工作。
方法 2 - PostgreSQL 範圍類型
CREATE TABLE test2 ( t_id INTEGER NOT NULL, st_et TSRANGE NOT NULL, costs INTEGER NOT NULL );填充：
INSERT INTO test2 VALUES (1, '[2022-08-23 08:00, 2022-08-23 11:00)', 600), -- <<<=== Note 600! (2, '[2022-08-23 08:30, 2022-08-23 09:30)', 50);
- 注意時間戳範圍的
[和)部分INSERT。這些是INCLUSIVE 和 EXCLUSIVE bounds，因此第一個範圍從 2022-08-23 08:00 延伸到 2022-08-23 11:00 之前的最後一刻，但不是11:00 本身。這將其與上述方法區分開來，其中班次在範圍的小時邊界處有效重疊。這一點，再加上廣泛的範圍/多範圍函式和運算符（是的，還有一個多範圍類型！）可以對日期/時間/整數範圍進行一些非常複雜的分析……世界就是你的牡蠣！我們不會在這裡使用它們（好吧，幾乎沒有），但請查看一些線上範例/網站 - 值得花時間！
- 與許多複雜的系統一樣，有時很難做簡單的事情——只有當我們更深入地研究時，工具的有用性和復雜性才會變得明顯。多範圍類型也是如此——這種情況下的 SQL 有點“毛茸茸”……
SQL：
SELECT t_id, ( '[' || GENERATE_SERIES(LOWER(st_et), UPPER(st_et) - INTERVAL '1 HOUR', '1 HOUR')::TEXT || ', ' || GENERATE_SERIES(LOWER(st_et) + INTERVAL '1 HOUR', UPPER(st_et), '1 HOUR')::TEXT || ')' )::TSRANGE AS s_range, -- shift range costs FROM test2;結果：
t_id s_range costs 1 ["2022-08-23 08:00:00","2022-08-23 09:00:00") 600 1 ["2022-08-23 09:00:00","2022-08-23 10:00:00") 600 1 ["2022-08-23 10:00:00","2022-08-23 11:00:00") 600 2 ["2022-08-23 08:30:00","2022-08-23 09:30:00") 50然後它只是重複方法 1 中採用的方法 - 見 fiddle。
Tobias Oetiker
Round Robin Database用於各種時間序列數據監控/繪圖系統，如 MRTG、Nagios、Munin 等。它具有一組非常強大的嵌入式聚合函式以及用於自定義計算的反向波蘭符號解釋器。它不是通常意義上的數據庫，因為它的目的不是儲存提供給它的確切數據，而是基於提供的數據進行聚合。https://oss.oetiker.ch/rrdtool/
我不確定它是否適合您的目的，但它是整個概念的良好起點。