grzegorz grzegorz - 6 months ago 23
MySQL Question

GROUP BY and interleaved values

I have a table which stores consumed energy measurements. If I want to know the cost of energy I can select rows for given period, then select first and last row, calculate difference and multiply by price. The problem is if there are two tariffs and they switch from time to time:

kWh | tariff | time_stamp |
123 | G11 | t+0 |
130 | G11 | t+1 |
132 | G11 | t+2 |
140 | G12 | t+3 |
143 | G12 | t+4 |
150 | G11 | t+5 |
161 | G11 | t+6 |


I need to somehow group by tariff but I need new group every time the tariff changes. So in the above example there would be three groups, not two. There are only two tariffs possible, G11 and G12. Can I use GROUP BY or something else?

Answer

You can use a user-defined variable that increments every time the tariff changes.

SELECT tariff, MIN(time_stamp) AS mintime, MAX(time_stamp) AS maxtime
FROM (
    SELECT t1.*, @counter := IF(tariff = @last_tariff, @counter, @counter+1) AS counter, @last_tariff := tariff
    FROM (SELECT *
          FROM yourTable
          ORDER BY time_stamp) AS t1
    JOIN (SELECT @last_tariff := null, @counter := 0) AS vars) AS grouped
GROUP BY tariff, counter

To get the corresponding rows of the table you can join this with the original table, as explained in SQL Select only rows with Max Value on a Column