I have a list of datapoints associated with a meter. The datapoints are contiguous but over time the meter name changes, and occasionally overlaps.
meter table:
| MeterID | DataID | MeterName | ValidFrom | ValidTo |
|---|---|---|---|---|
| 1 | 1 | Meter A | 2010-09-21 | 2015-09-17 |
| 2 | 1 | Meter B | 2015-09-15 | 2020-02-04 |
| 3 | 1 | Meter C | 2016-05-02 | 2020-09-01 |
data table:
| DataID | Value | Timestamp |
|---|---|---|
| 1 | 0.9 | 2010-09-21 00:00:00 |
| 1 | ... | ... |
| 1 | 3.4 | 2020-09-01 00:00:00 |
What I'm looking for:
| Timestamp | DataID | MeterName | Value |
|---|---|---|---|
| … | 1 | Meter A | … |
| 2015-09-14 23:00:00 | 1 | Meter A | 7.9 |
| 2015-09-15 00:00:00 | 1 | Meter A,Meter B | 3.0 |
| … | 1 | Meter A,Meter B | 6.3 |
| 2015-09-16 23:00:00 | 1 | Meter A,Meter B | 0.4 |
| 2015-09-17 00:00:00 | 1 | Meter B | 7.5 |
| … | 1 | Meter B | … |
| 2016-05-01 23:00:00 | 1 | Meter B | 0.6 |
| 2016-05-02 00:00:00 | 1 | Meter B,Meter C | 2.0 |
| … | 1 | Meter B,Meter C | … |
| 2020-02-03 23:00:00 | 1 | Meter B,Meter C | 3.6 |
| 2020-02-04 00:00:00 | 1 | Meter C | 9.7 |
| … | 1 | Meter C | … |
My query below results in duplicate rows where the meter timestamps overlap. Some of these overlaps can last years. There will likely never be more than 2, maybe 3 overlaps at any given point.
With over 1000 meters active at a time and 20 years worth of hourly data, the meter table is 2200 rows and the data table is almost 190M rows. My query below is very fast based on how the indexes are set up.
What is a subquery that transposes the meter names by timestamp value, similar to a PIVOT function in MySQL (v8.0.33)?
SELECT
d.Timestamp,
d.DataID,
m.MeterName,
d.Value
FROM
data d
LEFT JOIN
meter m
ON
m.DataID = d.DataID
AND d.Timestamp >= m.ValidFrom
AND d.Timestamp <= m.ValidTo
WHERE
d.DataID=1
Can you try with GROUP_CONCAT() function:
SELECT
d.Timestamp,
d.DataID,
GROUP_CONCAT(m.MeterName ORDER BY m.ValidFrom ASC SEPARATOR ',') AS MeterNames,
d.Value
FROM
data d
LEFT JOIN
meter m
ON
m.DataID = d.DataID
AND d.Timestamp >= m.ValidFrom
AND d.Timestamp <= m.ValidTo
WHERE
d.DataID=1
GROUP BY
d.Timestamp, d.DataID, d.Value
Let me know what you get..
You can read more about GROUP_CONCAT() function here: