Aggregate multiple rows between two numbers

August 18, 2022

Let’s say we have this table:
table1
sensor_id | start_time_index | end_time_index
—:|—:|—:
1 | 1 | 4
1 | 2 | 6
2 | 1 | 3
2 | 2 | 4

And another table with:
table2
sensor_id | time_index | value
—:|—:|—:
1 | 1 | ‘A’
1 | 2 | ‘B’
1 | 3 | ‘A’
1 | 4 | ‘C’
1 | 5 | ‘D’
1 | 6 | ‘B’
2 | 1 | ‘B’
2 | 2 | ‘C’
2 | 3 | ‘D’
2 | 4 | ‘A’

Desired table is:
sensor_id | start_time_index | end_time_index | values_concatenated
—:|—:|—:|—:
1 | 1 | 4 | "ABAC"
1 | 2 | 6 | "BACDB"
2 | 1 | 3 | "BCD"
2 | 2 | 4 | "CDA"

I didn’t know how to aggregate between a range that’s specified between two values that are in two columns.

>Solution :

Using "range join" ON col BETWEEN ... AND ... and LISTAGG:

SELECT tab1.sensor_id, tab1.start_time_index, tab1.end_time_index,
  LISTAGG(tab2.value) WITHIN GROUP(ORDER BY tab2.time_index) AS values_contatenated
FROM tab1
JOIN tab2
  ON tab1.sensor_id = tab2.sensor_id
 AND tab2.time_index BETWEEN tab1.start_time_index = tab1.end_time_index
GROUP BY tab1.sensor_id, tab1.start_time_index, tab1.end_time_index