Revisions to MySQL: speed query that uses the time intervals

added fields in query

Source Link

edit approved Sep 6, 2016 at 12:38

1

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,
      MIN(my_date) as minDate,
      MAX(my_date) as maxDate,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,
      MIN(my_date) as minDate,
      MAX(my_date) as maxDate,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

minor adjustment a1.my_dare => a1.my_date

Source Link

edited Sep 5, 2016 at 18:29

Lennart - Slava Ukraini

23.6k
3
33
71

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_daremy_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_dare = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_date = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

Source Link

created Sep 4, 2016 at 20:52

dnoeth

4.2k
12
14

There's no need for joining a1 and sub1, this is a variation of your previous question, just adjust it to the 5 minute interval instead of weeks, probably:

SELECT 
   a2.field_a1,
   fieldValue_a2,
   fieldValue_a3,
   a3.field_a4,
   fieldValue_a5,
   maxDate,
   maxTime
FROM
 (
   SELECT
      my_date,
      floor(timestampdiff(minute,time '09:00:00', my_time) /5) AS timeInterval,
      MIN(my_time) as minTime,
      MAX(my_time) as maxTime,

      MAX(field_a2) as fieldValue_a2,
      MIN(field_a3) as fieldValue_a3, 
      SUM(field_a5) as fieldValue_a5
   FROM tableTen 
   WHERE my_time BETWEEN TIME '09:00:00' AND TIME '17:30:00'
   GROUP BY my_date, timeInterval
 ) as a1
left join tableTen a2
  on a1.my_date = a2.my_date
 and a1.minTime = a2.my_time

left join tableTen a3
  on a1.my_dare = a3.my_date
 and a1.maxTime = a3.my_time

ORDER BY minDate DESC, minTime DESC

Stack Exchange Network

Return to Answer