Custom function inside order by

Question

I'm trying to make a pretty complex query. I have a database with blocks. Each block has a start date, an end date and the module to which it belongs. I have to calculate the turnover, which would be the difference between consecutive blocks (for the block[i]):

block[i].start - block[i - 1].end

Let's put the following example, I have these data:

create table blocks (start datetime, end datetime, module integer);
insert into blocks (start, end, module)
values 
('2016-04-13 09:00:00',  '2016-04-13 10:00:00', 1), -- diff: null or 0
('2016-04-13 11:00:00',  '2016-04-13 12:00:00', 1), -- diff: 1hour
('2016-04-13 12:30:00',  '2016-04-13 14:00:00', 1), -- diff: 30minutes
                                                    -- turnoverAvg: 45min = (1h + 30min) / 2

('2016-04-13 09:00:00',  '2016-04-13 10:00:00', 2), -- diff: null or 0
('2016-04-13 12:00:00',  '2016-04-13 12:30:00', 2), -- diff: 2hour
('2016-04-13 13:30:00',  '2016-04-13 14:30:00', 2), -- diff: 1hour
                                                    -- turnoverAvg: 90min = (2h + 1h) / 2

('2016-04-14 14:30:00',  '2016-04-14 16:00:00', 2), -- diff: null or 0
('2016-04-14 17:00:00',  '2016-04-14 18:00:00', 2), -- diff: 1hour
                                                    -- turnoverAvg: 60min = 1h/1

('2016-04-13 09:00:00',  '2016-04-13 10:00:00', 3), -- diff: null or 0
('2016-04-13 10:00:00',  '2016-04-13 11:00:00', 3), -- diff: 0
('2016-04-13 12:00:00',  '2016-04-13 13:00:00', 3), -- diff: 1hour
('2016-04-13 14:00:00',  '2016-04-13 15:00:00', 3), -- diff: 1hour
('2016-04-13 16:00:00',  '2016-04-13 17:00:00', 3), -- diff: 1hour
                                                    -- turnoverAvg: 45min = (0 + 1h + 1h + 1h) / 4

('2016-04-13 09:00:00',  '2016-04-13 10:00:00', 4), -- diff: null or 0
                                                    -- turnoverAvg: null

('2016-04-13 09:00:00',  '2016-04-13 15:00:00', 5), -- diff: null or 0
('2016-04-13 19:00:00',  '2016-04-13 20:00:00', 5); -- diff: 4hour
                                                    -- turnoverAvg: 240min = 4h/1

I should make the following query (pseudo-code):

SELECT turnoverAVG (rows of each group by)
FROM blocks
GROUP BY DATE (start), module

Where turnoverAvg would be a function like this (pseudo-code):

function turnoverAVG(rows):
  acc = 0.0
  for(i=1; i < rows.length; i++)
    d = row[i].start - rows[i - 1].end
    acc += d
  return acc/(rows.length - 1)

Actually I have tried many things, but I do not know where to start ... If someone has an idea, I would greatly appreciate it.

EDIT:

The output would be similar to:

turnoverAVG, module, day
45min, 1, 2016-04-13
1:30hour, 2, 2016-04-13
1hour, 2, 2016-04-14 -- different day but same module
45min, 3, 2016-04-13
4hour, 5, 2016-04-13

The turnoverAVG would be fine if it was in minutes, but I've written it that way to make it better understood. As you can see it never computes the first block because it can not be subtracted with the previous one (there is no previous block).

Can you just provide the desired result as a block of formatted text. — Strawberry, Jan 26 '18 at 14:21
I'm struggling to understand this entry: `'2016-04-13 12:30:00', '2016-04-13 10:00:00'` — Strawberry, Jan 26 '18 at 14:47
Sorry it was a mistake to write the example. If the blocks are ordered by time (start, end), each block corresponds to a time interval. The turnover looks for the time that exists between each interval, I try to give you a simple example: Suppose I have two blocks (I have added a name to make it simpler): Start, end, name, module 2017-01-01 00:00, 2017-01-01 08:00, 'Sleeping', 1 2017-01-01 09:00, 2017-01-01 15:00, 'Work', 1 The turnover would be indicating the time it takes from when you wake up until you get to work. Before sleeping there is no block, so I do not have turnover. — Adria Ciurana, Jan 26 '18 at 14:50
@Strawberry I updated the dataset. the only solution I have found for now is to create an added function in C / C ++ ... And that is a huge problem because I have to put it in a portable system that goes to different servers that I can not manipulate it. Another solution that occurs to me that is very rudimentary is to generate for each pair (module, day) a mysql query from PHP (What would make a lot of queries ...) — Adria Ciurana, Jan 28 '18 at 09:33

score 1 · Accepted Answer · answered Jan 28 '18 at 11:04

Functions like this are called window functions. They are only available starting with MySQL 8.

Until then, you will have to find an alternative way to write do your query, see e.g. this question. Most times, you will do it by using variables, although the sql way is to use joins.

But in your specific case, you do not actually need these: the turnovertime is not only the sum between the modules (for which you need to know the previous row), but also the time between start and end of your day (for which you only need min and max) minus the time the modules where running (for which you do not need the previous row).

So try this:

select 
  module,
  date(start),
  case when count(module) > 1
    then (TIMESTAMPDIFF(Minute,min(start),max(end)) -
           sum(TIMESTAMPDIFF(Minute,start, end))) 
         / (count(module) - 1)
    else null 
  end as turnoverAVG,
  -- details, just for information:
  TIMESTAMPDIFF(Minute,min(start),max(end)) as total_day,
  sum(TIMESTAMPDIFF(Minute,start, end)) as module_duration,
  TIMESTAMPDIFF(Minute,min(start),max(end)) -
    sum(TIMESTAMPDIFF(Minute,start, end)) as turnover,
  count(module) as cnt
from blocks
group by date(start), module;

The 4 additional columns are just there to so you can see the different termsn used in the calculation, and you can remove them.

All modules are required to start and end on the same date (although you can simply modify it to support overnight modules). It also does not correct the times if the modules overlap (but so doesn't your pseudocode).

It's not entirely clear if you want to include days with only one module (as suggested in the comment for module 4) or not (as suggested in your sample output). If you want to exclude these, you can add e.g. having count(module) > 1 at the end of the query.

Thank you very much! It is a very good solution, I had not noticed this property. I also tried at the time to use ROW_NUMBER, the problem is that a self-join to a temporary table must be done to calculate the difference between adjacent rows and I did not get an optimal solution without making many calculations. No doubt, this is very efficient, I appreciate your code and explanation, it is just what I was looking for. — Adria Ciurana, Jan 29 '18 at 09:35

Custom function inside order by

1 Answers1