0

I have a table. i need to get the median value based on time interval from this table.
Table data is like this : 

    date_time       type        value
2018-07-01 00:00:04 bottom#3    93.17
2018-07-01 00:00:04 top#45      100.28
2018-07-01 00:00:09 bottom#3    93.41
2018-07-01 00:00:09 top#45      100.28
2018-07-01 00:00:19 bottom#3    93.41
2018-07-01 00:00:19 top#45      100.85
2018-07-01 00:00:24 bottom#3    93.11
2018-07-01 00:00:24 top#45      101.13
2018-07-01 00:00:29 bottom#3    92.85
2018-07-01 00:00:29 top#45      101.13
2018-07-01 00:00:44 bottom#3    93.22
2018-07-01 00:00:44 top#45      101.13

we need to group the data based on time interval like 15 min or 30 min and also on type column after a time interval.

Expected output is something like :

date        interval    type        median(value)
2018-07-01  00:00:15    top#45      --
2018-07-01  00:00:15    bottom#3    --
2018-07-01  00:00:30    top#45      --
2018-07-01  00:00:30    bottom#3    --
2018-07-01  00:00:45    top#45      --
2018-07-01  00:00:45    bottom#3    --

I don't have much more knowledge in MySQL and trying to get a time interval but not getting the result.

please help me to get this solution. Thanks

shiva
  • 339
  • 4
  • 13
  • Medians are a bit tricky. Luckily, they're widely discussed. – Strawberry Mar 04 '19 at 11:31
  • Possible duplicate of [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) – Raymond Nijland Mar 04 '19 at 11:44
  • Also fill in the blanks (median(value)) column in the resultset. We need to be able to verivy our query generated the excepted results – Raymond Nijland Mar 04 '19 at 11:56
  • @Strawberry : can u write the query if I replace the median with avg(value)? but the expected output will be the same. – shiva Mar 04 '19 at 12:10
  • "can u write the query if I replace the median with avg(value)? but the expected output will be the same" median "calculation" is **not** the same as avg calculaton by the way.. Only when the list contains a even number the middle two items will use a "avg" calculaton to get a median, with a odd list number the middle item is choosen as median. – Raymond Nijland Mar 04 '19 at 12:31
  • @RaymondNijland : Yes I know median is not the same as avg calculation but my main problem is to get the time interval of 15 min from above sample. – shiva Mar 04 '19 at 15:31
  • "but my main problem is to get the time interval of 15 min from above sample" well you can use something like [this](https://www.db-fiddle.com/f/sEbLau6nynCCRmJuCJjGPW/0) to generate the intervals or use offcource a fixed list. `SELECT * FROM (SELECT '00:00:15' UNION SELECT '00:00:30' ...) as intervals` – Raymond Nijland Mar 04 '19 at 15:37

0 Answers0