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.
CodePudding user response：
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