如何按两列指定的时间索引范围聚合多行并拼接字段值

现有数据表table1，记录每个传感器的时间范围：

另有数据表table2，记录每个传感器各时间点的数值：

需要生成目标表，将table1中每个时间范围内的table2数值按时间顺序拼接：

核心逻辑是先关联两张表筛选时间范围内的有效数据，再按table1的分组维度聚合拼接字符串，以下是主流SQL方言的实现：

MySQL/MariaDB #

使用GROUP_CONCAT函数，指定排序保证拼接顺序：

SELECT
    t1.sensor_id,
    t1.start_time_index,
    t1.end_time_index,
    CONCAT('"', GROUP_CONCAT(t2.value ORDER BY t2.time_index SEPARATOR ''), '"') AS values_concatenated
FROM table1 t1
JOIN table2 t2 
    ON t1.sensor_id = t2.sensor_id
    AND t2.time_index BETWEEN t1.start_time_index AND t1.end_time_index
GROUP BY t1.sensor_id, t1.start_time_index, t1.end_time_index;

PostgreSQL #

使用STRING_AGG函数：

SELECT
    t1.sensor_id,
    t1.start_time_index,
    t1.end_time_index,
    '"' || STRING_AGG(t2.value, '' ORDER BY t2.time_index) || '"' AS values_concatenated
FROM table1 t1
JOIN table2 t2 
    ON t1.sensor_id = t2.sensor_id
    AND t2.time_index BETWEEN t1.start_time_index AND t1.end_time_index
GROUP BY t1.sensor_id, t1.start_time_index, t1.end_time_index;

Spark SQL #

使用STRING_AGG函数：

SELECT
    t1.sensor_id,
    t1.start_time_index,
    t1.end_time_index,
    CONCAT('"', STRING_AGG(t2.value, '' ORDER BY t2.time_index), '"') AS values_concatenated
FROM table1 t1
JOIN table2 t2 
    ON t1.sensor_id = t2.sensor_id
    AND t2.time_index BETWEEN t1.start_time_index AND t1.end_time_index
GROUP BY t1.sensor_id, t1.start_time_index, t1.end_time_index;

关键点说明 #

• 通过sensor_id关联两张表，确保同一传感器的数据匹配
• 用BETWEEN或t2.time_index >= t1.start_time_index AND t2.time_index <= t1.end_time_index筛选时间范围
• 聚合时必须指定ORDER BY t2.time_index，保证拼接顺序与时间顺序一致
• 外层的双引号拼接是为了匹配目标表格式，不需要可直接去掉

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