时间戳

首页 > 解决方案 > 如何计算大于或等于前一个的值

google-bigquery - 如何计算大于或等于前一个的值

问题描述

我有一个数据集，当放入图表时会产生正弦波。让我们想象一下，每天早上 9 点左右，我的图表都会达到当天的最低点并开始上涨。下午 6 点左右到达最高点。我需要计算（从上午 9 点到下午 6 点，当我的值增加时）我的值大于或等于前一个值的多少倍，以及我的值小于前一个值的多少倍。

让我们取 11 个值：[4-4-5-6-5-6-5-6-7-7-8]。我有 8 个值大于或等于前一个值。并且仅比前一个少 2 个。这个想法是能够说，在那段时间里，我有 8 个正值，用于 4 的级数（从 4 到 8）。这意味着平均而言，正值给我 0.5。

重要提示：我使用的是 BigQuery

到目前为止，这是我的查询：

select created_at,id,value,
(SELECT Count(value) from `dataset` where id LIKE "A1" AND value >= ?previous?) as positive,
(SELECT Count(value) from `dataset` where id LIKE "A1" AND value < ?previous?) as negative
from `dataset` 
where id LIKE "A1" 
AND value != 0 
AND DATETIME(created_at) BETWEEN PARSE_DATETIME("%Y%m%d:%H:%M:%S", "20210505:09:00:00") AND PARSE_DATETIME("%Y%m%d:%H:%M:%S", "20210505:17:59:59") 
order by created_at desc

这是一个数据示例。Id 不是指行，而是指数据源。

编号 | created_at | 价值

A1 | 2021-05-05 09:00:01 | 20

A1 | 2021-05-05 09:30:12 | 26

A1 | 2021-05-05 10:00:44 | 28

A1 | 2021-05-05 10:30:44 | 33

A1 | 2021-05-05 11:00:44 | 32

A1 | 2021-05-05 11:30:44 | 38

A1 | 2021-05-05 12:00:44 | 44

A1 | 2021-05-05 12:30:44 | 43

A1 | 2021-05-05 13:00:44 | 55

A1 | 2021-05-05 13:30:44 | 60

A1 | 2021-05-05 14:00:44 | 64

A1 | 2021-05-05 14:30:44 | 66

A1 | 2021-05-05 15:00:44 | 65

A1 | 2021-05-05 15:30:44 | 71

A1 | 2021-05-05 16:00:44 | 76

A1 | 2021-05-05 16:30:44 | 82

A1 | 2021-05-05 17:00:44 | 87

A1 | 2021-05-05 17:30:44 | 93

标签： google-bigquery

解决方案

尝试滞后：

select id, sum(positive), sum(negative)
from (
  select
    created_at,
    id,
    value,
    if(value >= lag(value) over (order by created_at), 1, 0) as positive,
    if(value < lag(value) over (order by created_at), 1, 0) as negative
  from `dataset` 
  where id LIKE "A1" 
    AND value != 0 
    AND DATETIME(created_at) BETWEEN PARSE_DATETIME("%Y%m%d:%H:%M:%S", "20210505:09:00:00") AND PARSE_DATETIME("%Y%m%d:%H:%M:%S", "20210505:17:59:59") 
  order by created_at desc
)
group by id

推荐阅读