Ken Vernaillen Ken Vernaillen - 6 months ago 33
SQL Question

query saved mqtt messages in database between start and end row

I have a program that directly saves my MQTT messages into a mySQL database.
I save the payload, topic and create time.
I have different topics that follow a certain sequence.

I have 'topic:start, topic:phase1 with payload:A, topic:phase2 with payload:B and topic:stop'. In between I have a lot of messages with a constant feed of temperature data.

I looks like this:

| Topic | Payload | Create_time |
| 'start' | | '2016-05-17 10:00:00' |
| 'temperature' | 5 | '2016-05-17 10:00:20' |
| 'temperature' | 6 | '2016-05-17 10:01:20' |
| 'temperature' | 6 | '2016-05-17 10:02:20' |
| 'temperature' | 7 | '2016-05-17 10:03:20' |
| 'temperature' | 8 | '2016-05-17 10:04:20' |
| 'phase1' | 'A' | '2016-05-17 10:04:30' |
| 'temperature' | 8 | '2016-05-17 10:05:20' |
| 'temperature' | 9 | '2016-05-17 10:06:20' |
| 'temperature' | 10 | '2016-05-17 10:07:20' |
| 'phase2' | 'B' | '2016-05-17 10:08:30' |
| 'temperature' | 10 | '2016-05-17 10:08:50' |
| 'stop' | | '2016-05-17 10:08:30' |
... more sequences like this where A and B are sequence specific


Now I want to query this data to get it into this format:

| Date | Phase1 | Start sequence | End sequence | Phase2 |
| '2016-05-17' | 'A' | '2016-05-17 10:00:00' | '2016-05-17 10:08:30' | 'B' |
...
for every sequence where
- date is the date of that sequence (without time).
- the var A of that sequence
- the start create_time of that sequence
- the end create_time of that sequence
- the var B of that sequence.


How do I group different rows into sequences, with the variables that show up as payload between the start and end times?

Answer

you can use user defined variable to tag the sequence (by incrementing whenever you see Topic='start'):

select date(min(Create_time)) `Date`, group_concat(if(Topic='phase1',Payload,null)) Phase1, 
  min(Create_time) `Start sequence`, max(Create_time) `End sequence`, 
  group_concat(if(Topic='phase2',Payload,null)) Phase2
from (
  select if(Topic='start',@id:=@id+1,@id) id, a.*
  from (
    select *
    from Table1
    order by Create_time) a
  join (select @id:=0) b) a
group by id;

sqlfiddle