时间戳

首页 > 解决方案 > 我想在接下来的 30 分钟内输出 MAX（值）如果当前行值 > 10 并且前一行是 <10

sql - 我想在接下来的 30 分钟内输出 MAX（值）如果当前行值 > 10 并且前一行是 <10

问题描述

使用数据 mytable

date value 2019-07-11 02:20:00 UTC 14.99 2019-07-11 02:30:00 UTC 12.53 2019-07-11 02:40:00 UTC 12.53 2019-07-11 02:50:00 UTC 14.99 2019-07-11 03:00:00 UTC 10.07 2019-07-11 03:10:00 UTC 7.61 2019-07-11 03:20:00 UTC 7.61 2019-07-11 03:30:00 UTC 10.07 2019-07-11 03:40:00 UTC 10.07 2019-07-11 03:50:00 UTC 7.61 2019-07-11 04:00:00 UTC 7.61 2019-07-11 04:10:00 UTC 7.61：

如果当前行值> 10且前一行< 10，我想在接下来的 30 分钟内输出 MAX（值）。

例如，如果值 >10，则检查上一行的值是否 <10。如果为真，则在当前行之后的 30 分钟内输出 MAX(value)。对于上表，这将输出的第一个值应该是 10.07

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT *, 
  CASE value > 10 AND prev_value < 10
    WHEN TRUE THEN 
      MAX(value) OVER(ORDER BY UNIX_SECONDS(ts) RANGE BETWEEN CURRENT ROW AND 1800 FOLLOWING)
    ELSE NULL
  END max_value_next_30_min
FROM (
  SELECT *, LAG(value) OVER(ORDER BY ts) prev_value
  FROM `project.dataset.table`
)
-- ORDER BY ts

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT TIMESTAMP '2019-07-11 02:20:00 UTC' ts, 14.99 value UNION ALL
  SELECT '2019-07-11 02:30:00 UTC', 12.53 UNION ALL
  SELECT '2019-07-11 02:40:00 UTC', 12.53 UNION ALL
  SELECT '2019-07-11 02:50:00 UTC', 14.99 UNION ALL
  SELECT '2019-07-11 03:00:00 UTC', 10.07 UNION ALL
  SELECT '2019-07-11 03:10:00 UTC', 7.61 UNION ALL
  SELECT '2019-07-11 03:20:00 UTC', 7.61 UNION ALL
  SELECT '2019-07-11 03:30:00 UTC', 10.07 UNION ALL
  SELECT '2019-07-11 03:40:00 UTC', 10.07 UNION ALL
  SELECT '2019-07-11 03:50:00 UTC', 17.61 UNION ALL
  SELECT '2019-07-11 04:00:00 UTC', 7.61 UNION ALL 
  SELECT '2019-07-11 04:10:00 UTC', 7.61
)
SELECT *, 
  CASE value > 10 AND prev_value < 10
    WHEN TRUE THEN 
      MAX(value) OVER(ORDER BY UNIX_SECONDS(ts) RANGE BETWEEN CURRENT ROW AND 1800 FOLLOWING)
    ELSE NULL
  END max_value_next_30_min
FROM (
  SELECT *, LAG(value) OVER(ORDER BY ts) prev_value
  FROM `project.dataset.table`
)
-- ORDER BY ts

带输出 

Row ts                      value   prev_value  max_value_next_30_min    
1   2019-07-11 02:20:00 UTC 14.99   null        null     
2   2019-07-11 02:30:00 UTC 12.53   14.99       null     
3   2019-07-11 02:40:00 UTC 12.53   12.53       null     
4   2019-07-11 02:50:00 UTC 14.99   12.53       null     
5   2019-07-11 03:00:00 UTC 10.07   14.99       null     
6   2019-07-11 03:10:00 UTC 7.61    10.07       null     
7   2019-07-11 03:20:00 UTC 7.61    7.61        null     
8   2019-07-11 03:30:00 UTC 10.07   7.61        17.61    
9   2019-07-11 03:40:00 UTC 10.07   10.07       null     
10  2019-07-11 03:50:00 UTC 17.61   10.07       null     
11  2019-07-11 04:00:00 UTC 7.61    17.61       null     
12  2019-07-11 04:10:00 UTC 7.61    7.61        null

推荐阅读