我有一张下表(view)
±------±------------±------------+
| Data | Date | Time |
±------±------------±------------+
| Data1 | 2020-08-19 | 13:00:00 |
| Data1 | 2020-08-19 | 13:30:00 |
| Data1 | 2020-08-19 | 14:00:00 |
| Data1 | 2020-08-21 | 07:00:00 |
| Data1 | 2020-08-21 | 07:30:00 |
| Data2 | 2020-08-20 | 08:00:00 |
| Data2 | 2020-08-20 | 08:30:00 |
±------±------------±------------+
我试图找出一种创建SQL语句的方法,该方法将执行以下操作:合并具有连续日期和时间(30分钟间隔)的数据
结果应为:
±------±------------±------------+
| Data | Date | Time |
±------±------------±------------+
| Data1 | 2020-08-19 | 13:00:00 |
| Data1 | 2020-08-21 | 07:00:00 |
| Data2 | 2020-08-20 | 08:00:00 |
±------±------------±------------+
分析解答
具有LAG()窗口功能:
select Data, Date, Time
from (
select *,
concat(Date, ’ ', Time) - interval 30 minute <=
lag(concat(Date, ’ ', Time)) over (partition by Data order by Date, Time) flag
from tablename
) t
where coalesce(flag, 0) = 0
order by Data, Date, Time
请参阅demo。
结果:
Data Date Time Data1 2020-08-19 13:00:00 Data1 2020-08-21 07:00:00 Data2 2020-08-20 08:00:00