SQL:上行流量和下行流量

| id|         start_time|           end_time|flow|
+---+-------------------+-------------------+----+
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  20|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  40|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  50|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  60|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  30|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  40|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  50|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  20|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  30|
+---+-------------------+-------------------+----+
需求,start_time-上一个end_time如2020-02-18 14:47:20-2020-02-18 14:46:30 > 10的话就让flow相加.
如果<10的话就不相加,以此类推
 SELECT
    id,
    start_time,
    end_time,
    lag(end_time,1,start_time) over(partition by id order by start_time) as flag,
    flow
    FROM
    v_flow
先将end_time下压
+---+-------------------+-------------------+-------------------+----+
| id|         start_time|           end_time|               flag|flow|
+---+-------------------+-------------------+-------------------+----+
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|2020-02-18 14:39:58|  20|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|2020-02-18 15:35:53|  30|
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|2020-02-18 14:20:30|  20|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|2020-02-18 14:46:30|  30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|2020-02-18 15:20:30|  40|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|2020-02-18 16:05:26|  50|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|2020-02-18 17:20:49|  60|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|2020-02-18 14:18:24|  20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|2020-02-18 15:01:40|  30|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|2020-02-18 15:30:24|  40|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|2020-02-18 16:40:32|  50|
+---+-------------------+-------------------+-------------------+----+

 SELECT
  id,
  start_time,
  end_time,
  IF((UNIX_TIMESTAMP(start_time)-UNIX_TIMESTAMP(flag))) as fid,
  flow
  FROM
    (
    SELECT
    id,
    start_time,
    end_time,
    lag(end_time,1,start_time) over(partition by id order by start_time) as flag,
    flow
    FROM
    v_flow
    )
 
将start_time-end_time.这里用到了UNIX_TIMESTAMP转为秒.注意是秒不是毫秒
+---+-------------------+-------------------+---+----+
| id|         start_time|           end_time|fid|flow|
+---+-------------------+-------------------+---+----+
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  0|  20|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  0|  30|
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  0|  20|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  0|  30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  1|  40|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  0|  50|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  0|  60|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  0|  20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  1|  30|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  1|  40|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  0|  50|
+---+-------------------+-------------------+---+----+


 如果大于10分钟就为1,不大于就为0,那么我们可以让他们相加
+---+-------------------+-------------------+----+----+
| id|         start_time|           end_time|fids|flow|
+---+-------------------+-------------------+----+----+
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|   0|  20|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|   0|  30|
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|   0|  20|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|   0|  30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|   1|  40|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|   1|  50|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|   1|  60|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|   0|  20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|   1|  30|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|   2|  40|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|   2|  50|
+---+-------------------+-------------------+----+----+
SELECT
id,
min(start_time),
max(end_time),
sum(flow)
FROM
(
SELECT
id,
start_time,
end_time,
sum(fid) over(partition by id order by start_time) as fids,
flow
FROM
  (
  SELECT
  id,
  start_time,
  end_time,
  flow,
  IF((unix_timestamp(start_time)-unix_timestamp(flag)) / 60 > 10 ,1,0) as fid
  FROM
    (
     SELECT
     id,
     start_time,
     lag(end_time,1,start_time) over(partition by id order by start_time) flag,
     end_time,
     flow
     from
     v_time
    )t
  )
)group by id ,fids


然后分组,相加求出最小的时间,最大的时间.和流量总和
+---+-------------------+-------------------+-----+
| id|    min(start_time)|      max(end_time)|flows|
+---+-------------------+-------------------+-----+
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53| 50.0|
|  1|2020-02-18 14:20:30|2020-02-18 15:20:30| 50.0|
|  1|2020-02-18 15:37:23|2020-02-18 18:03:27|150.0|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40| 20.0|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24| 30.0|
|  2|2020-02-18 16:01:23|2020-02-18 17:40:52| 90.0|
+---+-------------------+-------------------+-----+