SQL分组查询首尾行值不一致，请求修正查询语句

问题根源 #

你当前查询使用groupArray函数获取每组的首尾时间和value，但groupArray是按照数据的物理存储顺序收集元素的，不会自动按time排序。由于ClickHouse的存储顺序可能因数据写入、合并等操作变化，每次查询返回的数组元素顺序不固定，导致取到的groupArray(time)[1]/groupArray(time)[-1]并非真正的最早/最晚时间，对应的value自然也不一致。

解决方案 #

以下是几种可靠的修正方法，按简洁高效性排序：

方案1：使用窗口函数（推荐） #

利用first_value和last_value窗口函数，直接按time排序后取分组内的首尾value：

SELECT DISTINCT
    name,
    last_value(value) OVER (
        PARTITION BY name 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) - first_value(value) OVER (
        PARTITION BY name 
        ORDER BY time 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS delta
FROM stage.table1
WHERE time >= '2023-01-16 08:08:15'
  AND time <= '2023-01-16 08:54:00'
  AND name LIKE '%apple%'

• 逻辑：按name分组，time升序排序，取分组内第一个和最后一个value计算差值，DISTINCT确保每个name只返回一行结果。

方案2：对groupArray结果强制排序 #

如果坚持使用groupArray，需先对数据按time排序，确保数组元素按时间顺序排列：

SELECT
    name,
    (last_value - first_value) AS delta
FROM (
    SELECT
        name,
        groupArray(value)[1] AS first_value,
        groupArray(value)[-1] AS last_value
    FROM (
        -- 先按name分组、time升序排序，确保每组内数据顺序固定
        SELECT name, value, time
        FROM stage.table1
        WHERE time >= '2023-01-16 08:08:15'
          AND time <= '2023-01-16 08:54:00'
          AND name LIKE '%apple%'
        ORDER BY name, time
    )
    GROUP BY name
)

或者用sortArray函数直接对数组排序：

SELECT
    name,
    (arrayElement(sorted_values, -1) - arrayElement(sorted_values, 1)) AS delta
FROM (
    SELECT
        name,
        -- 按time升序排序(time, value)对，再提取value数组
        sortArray(groupArray((time, value)), x -> x.1) AS sorted_pairs,
        arrayMap(x -> x.2, sorted_pairs) AS sorted_values
    FROM stage.table1
    WHERE time >= '2023-01-16 08:08:15'
      AND time <= '2023-01-16 08:54:00'
      AND name LIKE '%apple%'
    GROUP BY name
)

方案3：通过min/max时间关联获取对应value #

先分组获取每组的最早、最晚时间，再关联原表拿到对应value：

SELECT
    t.name,
    t_last.value - t_first.value AS delta
FROM (
    SELECT 
        name, 
        MIN(time) AS first_time, 
        MAX(time) AS last_time
    FROM stage.table1
    WHERE time >= '2023-01-16 08:08:15'
      AND time <= '2023-01-16 08:54:00'
      AND name LIKE '%apple%'
    GROUP BY name
) t
JOIN stage.table1 t_first ON t.name = t_first.name AND t.first_time = t_first.time
JOIN stage.table1 t_last ON t.name = t_last.name AND t.last_time = t_last.time

内容的提问来源于stack exchange，提问作者ditrauth