如何计算大于或等于上一个值的值

Question

我有一个数据集，放在图表中时会产生正弦波。让我们想象一下，每天上午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不是指行，而是指数据的源。

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

Answer 1

尝试滞后：

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

Answer 2

您可以使用LAG添加具有来自精确行的值的新列。

使用它可以对当前值为<或>=值。

-- Dummy table
WITH table0 AS (
SELECT 4 AS value, 1 AS createdOn
UNION ALL SELECT 4 AS value, 2 AS createdOn
UNION ALL SELECT 5 AS value, 3 AS createdOn
UNION ALL SELECT 6 AS value, 4 AS createdOn
UNION ALL SELECT 5 AS value, 5 AS createdOn
UNION ALL SELECT 6 AS value, 6 AS createdOn
UNION ALL SELECT 5 AS value, 7 AS createdOn
UNION ALL SELECT 6 AS value, 8 AS createdOn
UNION ALL SELECT 7 AS value, 9 AS createdOn
UNION ALL SELECT 7 AS value, 10 AS createdOn
UNION ALL SELECT 8 AS value, 11 AS createdOn
)
-- SQL Query
SELECT
COUNTIF(value >= prevValue) AS total_increase,
COUNTIF(value < prevValue) AS total_decrease,
FROM (
SELECT
value, LAG(value) OVER (ORDER BY createdOn) AS prevValue
FROM table0
)