sql - SQL Joining/merging两个事件表,对对应时间敏感
问题描述
表 1 有开放时间。
id | OpenTime
1 | 2019-12-02 16:52:42.9130000
1 | 2019-12-02 16:55:57.5560000
1 | NULL
1 | 2019-12-02 16:59:09.5640000
1 | 2019-12-02 17:01:35.3510000
2 | 2019-12-02 17:02:55.0270000
2 | 2019-12-02 17:05:41.3930000
2 | 2019-12-02 17:07:41.7870000
表 2 有关闭时间。
id | CloseTime
1 | NULL
1 | 2019-12-02 16:56:19.2560000
1 | 2019-12-02 16:57:47.5790000
1 | 2019-12-02 16:59:33.5390000
1 | 2019-12-02 17:01:55.6040000
2 | 2019-12-02 17:04:00.7780000
2 | 2019-12-02 17:06:04.4830000
我需要DATEDIFF
计算每个打开到关闭的时间。
它一次只能打开一次,但是,我们可能会错过该活动。
我们可能还没有关闭事件。
一个 OpenTime 通常会有一个对应的 CloseTime,其中 CloseTime 大于 OpenTime,但小于下一个 OpenTime。
id | OpenTime | CloseTime | Datedif
1 | 2019-12-02 16:52:42.9130000 | NULL | NULL
1 | 2019-12-02 16:55:57.5560000 | 2019-12-02 16:56:19.2560000 |
1 | NULL | 2019-12-02 16:57:47.5790000 | NULL
1 | 2019-12-02 16:59:09.5640000 | 2019-12-02 16:59:33.5390000 |
1 | 2019-12-02 17:01:35.3510000 | 2019-12-02 17:01:55.6040000 |
2 | 2019-12-02 17:02:55.0270000 | 2019-12-02 17:04:00.7780000 |
2 | 2019-12-02 17:05:41.3930000 | 2019-12-02 17:06:04.4830000 |
2 | 2019-12-02 17:07:41.7870000 | NULL | NULL
Datediff 将仅以 seconds 为单位DATEDIFF(SECOND,OpenTime,CloseTime)
。
DROP TABLE IF EXISTS #NEWTABLE;
SELECT
a.ID,
MAX(a.OpenTime) as OpenTime,
MIN(b.CloseTime) as CloseTime ,
DATEDIFF(SECOND, a.OpenTime, b.CloseTime) AS diffSeconds
INTO #NEWTABLE
FROM Table1 a
JOIN Table2 b
ON a.Id= b.Id
WHERE
a.Id = b.ID
and b.CloseTime> = a.OpenTime
Group by a.Id,DATEDIFF(SECOND, a.OpenTime, b.CloseTime)
order by diffSeconds desc
这不起作用,但我试图确保每个 OpenTime 都获得正确的对应 CloseTime。我的代码为 8 个打开和关闭事件提供了 26 行输出。
解决方案
您可以使用它LEAD
来查找下一个开放时间并从 table2 中获取相应的记录
with cte as(
select id, opentime, lead(opentime,1) over (order by opentime) nextopentime
from table1
where opentime is not null)
select a.id, a.opentime, b.closetime
from cte a
left join table2 b on a.id = b.id and b.closetime > a.opentime and b.closetime < a.nextopentime
order by a.opentime
推荐阅读
- angular - Angular,接收数据但获取 CORS
- javascript - 在 JavaScript 中分配给数组的动画对象
- java - Java字符串中重音字母的Unicode的完整参考
- sql-server - 相同的查询给出不同的结果
- php - 选中复选框时使用身份验证用户信息填充字段
- python - sqlalchemy session.query 奇怪 - 双重转义
- python - Python3 现在从 JSON 文件加载数据时抛出 ASCII 错误
- python - Django:NoReverseMatch -> 未找到“password_reset_done”的反向。“password_reset_done”不是有效的视图函数或模式名称
- java - 在纯 java 中调用 rest 调用
- magento2 - 废弃的购物车电子邮件未第二次发送