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
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
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;;
2条答案
按热度按时间chhqkbe11#
您 可以 使用 分层 查询 或 递归 CTE 生成 所 需 范围 内 的 半 小时 时段 列表 :
中 的 每 一 个
| 开始 时间|停止 时间|
| - -| - -|
| 2022 年 10 月 4 日 09 : 00 : 00| 2022 年 10 月 4 日 09 时 30 分|
| 2022 年 10 月 4 日 09 时 30 分|2022 年 10 月 4 日 10 时 00 分|
| 2022 年 10 月 4 日 10 时 00 分|2022 年 10 月 4 日 10 时 30 分|
| 2022 年 10 月 4 日 10 时 30 分|2022 年 10 月 4 日 11 时 00 分|
| 2022 年 10 月 4 日 11 时 00 分|2022 年 10 月 4 日 11 时 30 分|
| 2022 年 10 月 4 日 11 时 30 分|2022 年 10 月 4 日 12 时 00 分|
| 2022 年 10 月 4 日 12 时 00 分|2022 年 10 月 4 日 12 时 30 分|
| 2022 年 10 月 4 日 12 时 30 分|2022 年 10 月 4 日 13 时 00 分|
然后 , 你 可以 将 其 与 实际 数据 ( 外部 ) 连接 起来 , 寻找 重叠 的 范围 , 并 计算 重叠 的 范围 有 多少 落 在 时间 段 内 - - 这里 我 使用 最 大/最 小 , 然后 减去 它们 。 这样 就 得到 了 以 天 为 单位 的 差值 , 你 可以 将 其 乘以 24x24x60 , 得到 以 秒 为 单位 的 值 。 然后 , 将 每个 时间 段 的 差值 相加 。
格式
| 开始 时间|总计 _ 秒|
| - -| - -|
| 2022 年 10 月 4 日 09 : 00 : 00|一八零零 年|
| 2022 年 10 月 4 日 09 时 30 分|八九一|
| 2022 年 10 月 4 日 10 时 00 分|一八零零 年|
| 2022 年 10 月 4 日 10 时 30 分|一八零零 年|
| 2022 年 10 月 4 日 11 时 00 分|九百|
| 2022 年 10 月 4 日 11 时 30 分|一八零零 年|
| 2022 年 10 月 4 日 12 时 00 分|一八零零 年|
| 2022 年 10 月 4 日 12 时 30 分|小 行星 1754|
fiddle , 包括 中间 计算 。
我 在 其中 两 个 时期 得到 的 数字 与 你 不同 - - 891 而 不是 971 , 1754 而 不是 1726 。 这 似乎 是 你 的 数据 应该 给出 的 :
wydwbb8l2#
您可以计算每个
id
的最小和最大时间,然后生成一个半小时间隔的日历并将其联接回表中:其中,对于示例数据:
输出:
| 识别码|开始日期|停止日期|秒数|
| - -|- -|- -|- -|
| 五个|2022年4月10日09:00:00| 2022年4月10日09时30分|一八零零年|
| 五个|2022年4月10日09时30分|2022年4月10日10时00分|八九一|
| 五个|2022年4月10日10时00分|2022年4月10日10时30分|一八零零年|
| 五个|2022年4月10日10时30分|2022年4月10日11时00分|一八零零年|
| 五个|2022年4月10日11时00分|2022年4月10日11时30分|九百|
| 五个|2022年4月10日11时30分|2022年4月10日12时00分|一八零零年|
| 五个|2022年4月10日12时00分|2022年4月10日12时30分|一八零零年|
| 五个|2022年4月10日12时30分|2022年4月10日13时00分|小行星1754|
fiddle