选择每组的第一行和最后一行

Question

我想在我的SQL查询中找到第一行和最后一行value之间的delta，但是每次运行的子查询总是在time_last和last_value列中返回不同的值。请帮我解决这个问题。

table1包含唯一的时间值和重复的名称和值。像这样:

time                    name        value
2023-01-16 08:52:51.965 apple       1100.0
2023-01-16 08:52:23.665 apple       691.3
2023-01-16 08:52:01.915 apple       107.0
2023-01-16 08:51:33.621 apple       1000.0
2023-01-16 08:51:11.815 apple_two   50.0
2023-01-16 08:50:51.574 apple_two   61.9
2023-01-16 08:50:42.575 apple_two   69.0
2023-01-16 08:50:21.800 apple_two   94.0

问题子查询:

SELECT groupArray(time)[-1] as time_last, name , (groupArray(value)[-1])  as last_value
FROM stage.table1 il 
WHERE     time >= '2023-01-16 08:08:15'
AND   time <= '2023-01-16 08:54:00'
AND name like '%apple%'
GROUP BY name 
ORDER BY time_last

我想使用的合计查询:

SELECT name, (last_value - first_value) as delta
FROM 
(SELECT groupArray(time)[1] as time_first, name , (groupArray(value)[1])  as first_value
FROM stage.table1 il 
WHERE     time >= '2023-01-16 08:08:15'
AND   time <= '2023-01-16 08:54:00'
AND name like '%apple%'
GROUP BY name 
ORDER BY time_first
) as frst  
JOIN 
(SELECT groupArray(time)[-1] as time_last, name , (groupArray(value)[-1])  as last_value
FROM stage.table1 il 
WHERE     time >= '2023-01-16 08:08:15'
AND   time <= '2023-01-16 08:54:00'
AND name like '%apple%'
GROUP BY name 
ORDER BY time_last ) lst on frst.name = lst.name 
having name like '%apple%'

返回值:第一次运行:

time_first          name        first_value             time_last               `lst.name`  last_value  delta
2023-01-16 08:08:15.010 apple   1100                    2023-01-16 08:29:04.804 apple       1000        -100

第二运行:

time_first          name        first_value             time_last                           `lst.name`  last_value  delta
2023-01-16 08:10:44.813 apple   200                     2023-01-16 08:53:59.782 apple       254         54

Answer 1

create table t(time DateTime64(3), name String, value Float64) Engine=Memory as 
select * from values(
('2023-01-16 08:52:51.965','apple',       1100.0),
('2023-01-16 08:52:23.665','apple',       691.3),
('2023-01-16 08:52:01.915','apple',       107.0),
('2023-01-16 08:51:33.621','apple',       1000.0),
('2023-01-16 08:51:11.815','apple_two',   50.0),
('2023-01-16 08:50:51.574','apple_two',   61.9),
('2023-01-16 08:50:42.575','apple_two',   69.0),
('2023-01-16 08:50:21.800','apple_two',   94.0));

SELECT
name,
max(time),
min(time),
argMax(value, time) AS last,
argMin(value, time) AS first
FROM t
GROUP BY name
┌─name──────┬───────────────max(time)─┬───────────────min(time)─┬─last─┬─first─┐
│ apple     │ 2023-01-16 08:52:51.965 │ 2023-01-16 08:51:33.621 │ 1100 │  1000 │
│ apple_two │ 2023-01-16 08:51:11.815 │ 2023-01-16 08:50:21.800 │   50 │    94 │
└───────────┴─────────────────────────┴─────────────────────────┴──────┴───────┘