sql - 按月、日、小时+间隙和孤岛问题分组
问题描述
我需要计算(百分比)状态在一天、几小时或一个月(工作时间)中的真实时间。
我将我的表格简化为这个:
| date | status |
|-------------------------- |-------- |
| 2018-11-05T19:04:21.125Z | true |
| 2018-11-05T19:04:22.125Z | true |
| 2018-11-05T19:04:23.125Z | true |
| 2018-11-05T19:04:24.125Z | false |
| 2018-11-05T19:04:25.125Z | true |
....
我需要得到这个结果(取决于参数):
用了几个小时:
| date | working_time |
|-------------------------- |--------------|
| 2018-11-05T00:00:00.000Z | 14 |
| 2018-11-05T01:00:00.000Z | 15 |
| 2018-11-05T02:00:00.000Z | 32 |
|... | ... |
| 2018-11-05T23:00:00.000Z | 13 |
几个月:
| date | working_time |
|-------------------------- |--------------|
| 2018-01-01T00:00:00.000Z | 14 |
| 2018-02-01T00:00:00.000Z | 15 |
| 2018-03-01T00:00:00.000Z | 32 |
|... | ... |
| 2018-12-01T00:00:00.000Z | 13 |
我的 SQL 查询如下所示:
SELECT date_trunc('month', date) as date,
round((EXTRACT(epoch from sum(time_diff)) / 25920) :: numeric, 2) as working_time
FROM (SELECT date,
status as current_status,
(lag(status, 1) OVER (ORDER BY date)) AS previous_status,
(date -(lag(date, 1) OVER (ORDER BY date))) AS time_diff
FROM table
) as raw_data
WHERE current_status = TRUE AND previous_status = TRUE
GROUP BY date_trunc('month', date)
ORDER BY date;
它工作正常,但真的很慢。关于优化的任何想法?Maybse 使用 Row_Number() 函数?
解决方案
尝试这个:
SELECT t.month_reference as date,
round( sum(if(t_aux.status,1,0)) / 25920) :: numeric, 2) as working_time
#我假设你使用这个数字是因为系统的正常运行时间是 60*18*24,
#如果我想要当月的总秒数 60*60*24*day(Last_day(t.month_reference)),我会使用它
FROM (SELECT date_trunc('month', t.date) as month_reference
FROM table
) as t
left join table t_aux
on t.month_reference=date_trunc('month', t_aux.date)
因此,当我们按月份分组时, sum() 只会找到为真且具有引用月份的行
and t_aux.date <
(select t1.date
from table t1
where t.month_reference=date_trunc('month', t1.date)
and t1.status=false
order by t1.date asc limit 1 )
我添加了这个,所以它只选择为真的行,直到它在同月参考中找到状态为假的行
GROUP BY t.month_reference
ORDER BY t.month_reference;
推荐阅读
- python - 将信息从 dict 传递到 snakemake 会导致缺少输入文件错误
- python - 在 Tkinter 中更改标签和同时更改具有相同内部函数的变量有什么区别?
- pine-script - 使用变量设置直线的 x 点
- git - Github 分叉存储库搞砸了
- r - 如何识别函数输出,以便我可以做出“漂亮的输出”和/或减少输出量
- python - 启用 MFA 的 Office 365 SharePoint 列表上的 Python 读/写
- ios - SwiftUI .sheet() 的主体在解雇后被评估并且不呈现
- node.js - Supervisord 可以通过调用 node.js 进程的 Health API 或执行 shell 命令来进行健康检查吗?
- java - Flyway 迁移仅在 linux 服务器上失败
- python - 如何在geodjango中转换英里公里