sql - 使用生成系列
问题描述
我正在编写一个 psql 过程来读取源表,然后聚合并写入聚合表。我的表源包含 2 列 beg,end 是指客户端连接到网站,客户端断开连接。我想为每个客户计算他花费的时间。使用生成系列的目的是当事件超过一天时。
我的伪代码如下
execute $$SELECT MAX(date_) FROM $$||aggregate_table INTO max_date;
IF max_date is not NULL THEN
execute $$DELETE FROM $$||aggregate_table||$$ WHERE date_ >= $$||quote_literal(max_date);
ELSE
max_date := 'XXXXXXX';
end if;
SELECT * from (
select
Id, gs.due_date,
(case
When TRIM(set) ~ '^OPT[0-9]{3}/MINUTE/$'
Then 'minute'
When TRIM(set) ~ '^OPT[0-9]{3}/SECOND/$'
Then 'second'
as TIME,
sum(extract(epoch from (least(s.end, gs.date_ + interval '1 day') -
greatest(s.beg, gs.date_)
)
) / 60) as Timing
from source s cross join lateral
generate_series(date_trunc(‘day’, s.beg), date_trunc('day',
least(s.end,
CASE WHEN $$||quote_literal(max_date)||$$ = ‘XXXXXXX’
THEN (current_date)
ELSE $$||quote_literal(max_date)||$$
END)
), interval '1 day’) gs(date_)
where ( (beg, end) overlaps ($$||quote_literal(max_date)||$$'00:00:00', $$||quote_literal(max_date)||$$'23:59:59’))
group by id, gs.date_, TIME
) as X
where ($$||quote_literal(max_date)||$$ = X.date_ and $$||quote_literal(max_date)||$$ != ‘XXXXXXX’)
OR ($$||quote_literal(max_date)||$$ ='XXXXXXX')
表源数据
number, beg, end, id, set
(10, '2019-10-25 13:00:00', '2019-10-25 13:30:00', 1234, 'OPT111/MINUTE/'),
(11, '2019-10-25 13:00:00', '2019-10-25 14:00:00', 1234, 'OPT111/MINUTE/'),
(12, '2019-11-04 09:19:00', '2019-11-04 09:29:00', 1124, 'OPT111/SECOND/'),
(13, '2019-11-04 22:00:00', '2019-11-05 02:00:00', 1124, 'OPT111/MINUTE/')
Expected_output 聚合表
2019-10-25, 1234, MINUTE, 90(1h30)
2019-11-04, 1124, SECOND, 10
2019-11-04, 1124, MINUTE, 120
2019-11-05, 1124, MINUTE, 120
我的代码的问题是,如果我明天将添加新行,例如 (14, '2019-11-06 12:00:00', '2019-11-06 13: 00:00'、1124、'OPT111/MINUTE/')。
请问有大神可以帮忙吗?
谢谢你
解决方案
这是我的解决方案。为了避免保留字,我更改了列名。您可能需要触摸duration
.
with mycte as
(
select -- the first / first and only days
id, col_beg,
case when col_beg::date = col_end::date then col_end else date_trunc('day', col_end) end as col_end
from mytable
union all
select -- the last days of multi-day periods
id, date_trunc('day', col_end) as col_beg, col_end
from mytable
where col_end::date > col_beg::date
union all
select -- the middle days of multi-day periods
id, rd as col_beg, rd::date + 1 as col_end
from mytable
cross join lateral generate_series(col_beg::date + 1, col_end::date - 1, interval '1 day') g(rd)
where col_end::date > col_beg::date + 1
)
select
col_beg::date as start_time, id, sum(col_end - col_beg) as duration
from mycte group by 1, 2 order by 1;
推荐阅读
- java - 构建 java 项目时 build.gradle 中的 Groovy 语法
- matlab - 在 Matlab 上为 GPU 并行化值函数迭代编写内核
- jquery - 如何根据前面定义的变量选择器选择具有属性的元素?
- wordpress - Wordpress 多个 IIS 绑定
- flutter - 如何响应地更改 Flutter 中 FontAwesomeIcons 中图标的高度?
- elasticsearch - 当数据架构可能随时间变化时,最佳插入弹性
- python - 用于测试包的基本毒物设置
- mysql - 如何输入动态密钥到 json_extract mysql?
- google-apps-script - 谷歌表格。脚本。根据单元格值删除值,然后再次重新组织
- bash - Bash - 很多 elif,有最佳实践吗?