时间戳

首页 > 解决方案 > 选择从第一个坏值到最后一个坏值范围内的数据

clickhouse - 选择从第一个坏值到最后一个坏值范围内的数据

问题描述

有这样的表格和数据：

create table sensor_values(
    dt DateTime default now(),
    value UInt32
)
engine MergeTree()
partition by toYYYYMM(dt)
order by tuple();

insert into sensor_values(value) values (1), (2), (11), (13), (4), (17), (5), (8);

数据：

value
-----
1
2
11
13
4
17
5
8

我想选择从第一个坏值（11）到最后一个坏值（17）的数据。错误值超过 10。

选择后所需的范围：

value
-----
11
13
4
17

我的第一个想法是定义值是否坏，然后计算（以某种方式）累积总和：

value  isBad  cumSum
--------------------
1      0      0
2      0      0
11     1      1
13     1      2
4      0      2
17     1      3
5      0      3
8      0      3

然后我会从 min(cumSum) 到 max(cumSum) - 1 中选择，但我错过了最后一个错误值。

如何获得选择结果中包含的最后一个值？

标签： clickhouse

解决方案

您可以尝试使用窗口函数（请参阅：runningDifference，neighbor）或数组函数：

SELECT arrayJoin(slice) as result
FROM (
    SELECT
        groupArray(data) AS arr,
        arrayFirstIndex(x -> (x > 10), arr) AS first_index,
        (length(arr) - arrayFirstIndex(x -> (x > 10), arrayReverse(arr)) + 1) AS last_index,
        arraySlice(arr, first_index, last_index - first_index + 1) AS slice    
    FROM 
    (
        /* test dataset */
        SELECT arrayJoin([1, 2, 11, 13, 4, 17, 5, 8]) AS data
    )
)
/*
┌─result─┐
│     11 │
│     13 │
│      4 │
│     17 │
└────────┘
*/

推荐阅读