在此處輸入影像描述
我很難在所有半小時間隔之間安排兩個日期并以秒為單位分配持續時間。
附加表格資料和附加預期輸出。
我嘗試了層次結構功能，但對我來說失敗了。任何幫助表示贊賞。
uj5u.com熱心網友回復：
您可以使用分層查詢或遞回 CTE 在您需要的任何范圍內生成半小時時間段串列：
with p (start_time, stop_time) as (
select cast(timestamp '2022-10-04 09:00:00' as date),
cast(timestamp '2022-10-04 09:00:00' as date) interval '30' minute
from dual
union all
select p.stop_time, p.stop_time interval '30' minute
from p
where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select * from p
| 開始時間 | STOP_TIME |
|---|---|
| 2022-10-04 09:00:00 | 2022-10-04 09:30:00 |
| 2022-10-04 09:30:00 | 2022-10-04 10:00:00 |
| 2022-10-04 10:00:00 | 2022-10-04 10:30:00 |
| 2022-10-04 10:30:00 | 2022-10-04 11:00:00 |
| 2022-10-04 11:00:00 | 2022-10-04 11:30:00 |
| 2022-10-04 11:30:00 | 2022-10-04 12:00:00 |
| 2022-10-04 12:00:00 | 2022-10-04 12:30:00 |
| 2022-10-04 12:30:00 | 2022-10-04 13:00:00 |
然后，您可以（外部）將其連接到您的實際資料中以尋找重疊范圍，并計算在該時間段內有多少重疊 - 這里我使用最大/最小，并減去這些。這給出了天數的差異，您可以將其乘以 24x24x60 以獲得以秒為單位的值。然后對每個時間段進行總結。
with p (start_time, stop_time) as (
select cast(timestamp '2022-10-04 09:00:00' as date),
cast(timestamp '2022-10-04 09:00:00' as date) interval '30' minute
from dual
union all
select p.stop_time, p.stop_time interval '30' minute
from p
where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select p.start_time,
sum(round(
(least(p.stop_time, t.stop_time) - greatest(p.start_time, t.start_time))
* 86400
)) as total_secs
from p
left join your_table t
on t.start_time <= p.stop_time and t.stop_time >= p.start_time
group by p.start_time
order by p.start_time
| 開始時間 | TOTAL_SECS |
|---|---|
| 2022-10-04 09:00:00 | 1800 |
| 2022-10-04 09:30:00 | 891 |
| 2022-10-04 10:00:00 | 1800 |
| 2022-10-04 10:30:00 | 1800 |
| 2022-10-04 11:00:00 | 900 |
| 2022-10-04 11:30:00 | 1800 |
| 2022-10-04 12:00:00 | 1800 |
| 2022-10-04 12:30:00 | 1754 |
小提琴包括中間計算。
在兩個時期內，我的數字與您的數字不同- 891 而不是 971，以及 1754 而不是 1726。但這似乎是您的資料應該給出的：
- 對于 09:30 時段，第一行在 09:37:31 停止，因此該時段包括該行的 7:31；第二行從 09:52:40 開始，因此該時段包括 7:20；總計 14:51，即 891 秒。
- 對于 12:30 時段，第三行結束于 12:59:14，到該時段包括第三行的 29:14，即 1754 秒。
uj5u.com熱心網友回復：
您可以計算每個時間的最小和最大時間id，然后生成一個以半小時為間隔的日歷并將其連接回您的表：
WITH bounds (id, min_start, max_stop) AS (
SELECT id,
TRUNC(CAST(MIN(start_dt) AS TIMESTAMP), 'HH')
CASE
WHEN EXTRACT(MINUTE FROM CAST(MIN(start_dt) AS TIMESTAMP)) >= 30
THEN INTERVAL '30' MINUTE
ELSE INTERVAL '0' MINUTE
END,
TRUNC(CAST(MAX(stop_dt) AS TIMESTAMP), 'HH')
CASE
WHEN EXTRACT(MINUTE FROM CAST(MAX(stop_dt) AS TIMESTAMP)) >= 30
THEN INTERVAL '60' MINUTE
ELSE INTERVAL '30' MINUTE
END
FROM table_name
GROUP BY id
),
half_hours (id, start_dt) AS (
SELECT b.id, t.start_dt
FROM bounds b
CROSS JOIN LATERAL(
SELECT min_start (LEVEL - 1) * INTERVAL '30' MINUTE AS start_dt
FROM DUAL
CONNECT BY min_start LEVEL * INTERVAL '30' MINUTE <= max_stop
) t
)
SELECT h.id,
h.start_dt,
h.start_dt INTERVAL '30' MINUTE AS stop_dt,
ROUND(
SUM(
LEAST(h.start_dt INTERVAL '30' MINUTE, t.stop_dt)
- GREATEST(h.start_dt, t.start_dt)
) * 24 * 60 * 60
) AS seconds
FROM half_hours h
LEFT OUTER JOIN table_name t
ON ( h.id = t.id
AND t.start_dt < h.start_dt INTERVAL '30' MINUTE
AND h.start_dt < t.stop_dt)
GROUP BY
h.id,
h.start_dt
其中，對于樣本資料：
CREATE TABLE table_name (ID, start_dt, stop_dt) AS
SELECT 5, DATE '2022-04-10' INTERVAL '09:00:00' HOUR TO SECOND, DATE '2022-04-10' INTERVAL '09:37:31' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' INTERVAL '09:52:40' HOUR TO SECOND, DATE '2022-04-10' INTERVAL '11:15:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' INTERVAL '11:30:00' HOUR TO SECOND, DATE '2022-04-10' INTERVAL '12:59:14' HOUR TO SECOND FROM DUAL;;
輸出：
| ID | START_DT | STOP_DT | 秒 |
|---|---|---|---|
| 5 | 2022-04-10 09:00:00 | 2022-04-10 09:30:00 | 1800 |
| 5 | 2022-04-10 09:30:00 | 2022-04-10 10:00:00 | 891 |
| 5 | 2022-04-10 10:00:00 | 2022-04-10 10:30:00 | 1800 |
| 5 | 2022-04-10 10:30:00 | 2022-04-10 11:00:00 | 1800 |
| 5 | 2022-04-10 11:00:00 | 2022-04-10 11:30:00 | 900 |
| 5 | 2022-04-10 11:30:00 | 2022-04-10 12:00:00 | 1800 |
| 5 | 2022-04-10 12:00:00 | 2022-04-10 12:30:00 | 1800 |
| 5 | 2022-04-10 12:30:00 | 2022-04-10 13:00:00 | 1754 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/530919.html