主页 / dba / 问题 / 280744
Accepted
Sociopath
Asked: 2020-12-03 06:11:56 +0800 CST

如何从列中查找连续的非零值

  • 772

我有一张如下表

user  timestamp            counts   
xyz   01-01-2020 00:05:00   12
xyz   01-01-2020 00:10:00   11
xyz   01-01-2020 00:15:00   45
xyz   01-01-2020 00:20:00   0
xyz   01-01-2020 00:25:00   0
xyz   01-01-2020 00:30:00   13
xyz   01-01-2020 00:35:00   12
xyz   01-01-2020 00:40:00   0

我想根据计数列中的最大连续值（非零）值对其进行切片，并找出该持续时间内的开始时间戳、结束时间戳和总计数

starttime            endtime               total_count
01-01-2020 00:05:00  01-01-2020 00:15:00   68

如何使用 BigQuery 实现这一目标

google-bigquery

1 个回答

  1. Best Answer

    测试类似的东西

    WITH cte AS ( SELECT *, SUM(CASE WHEN counts=0 THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) grp
              FROM sourcetable )
SELECT MIN(timestamp) start_time, MAX(timestamp) end_time, SUM(counts) total_count
FROM cte
WHERE grp = 0

    演示小提琴（使用 MySQL 8）

    • 1

相关问题