Альтернатива этому запросу для запуска под mariadb 10.1

Этот запрос работает должным образом под Mysql 8, но на моем сервере используется MariaDB 10.1. Вы знаете, существует ли этому альтернатива? И как этого добиться?

SELECT * FROM (
SELECT
  *,
  SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(hs.`ending_hour`, hs.`starting_hour`))) OVER (ORDER BY hs.starting_hour RANGE BETWEEN INTERVAL '12' HOUR PRECEDING AND INTERVAL '12' HOUR following)) AS tot
FROM
  time_table hs
  WHERE hs.`starting_hour` > DATE_SUB(NOW(), INTERVAL 50 DAY) AND hs.`ending_hour` <= NOW()
 ORDER BY hs.`starting_hour` ASC
 ) t1
  HAVING tot >= '14:00:00' 
 ;

рабочий пример

Проблема в том, что RANGE BETWEEN INTERVAL в оконной функции OVER в данный момент не существует в MariaDB.

Спасибо

Образец данных:

        id  starting_hour        ending_hour          
------  -------------------  ---------------------
     1  2018-09-02 06:00:00  2018-09-02 08:30:00  
     2  2018-09-02 08:30:00  2018-09-02 10:00:00  
     4  2018-09-03 11:00:00  2018-09-03 15:00:00  
     5  2018-09-04 15:30:00  2018-09-04 16:00:00  
     6  2018-09-04 16:15:00  2018-09-04 17:00:00  
     7  2018-09-19 00:00:00  2018-09-19 03:00:00  
     8  2018-09-19 04:00:00  2018-09-19 15:00:00  
     9  2018-09-20 00:00:00  2018-09-20 22:01:00  
    10  2018-10-21 12:00:00  2018-10-21 11:00:00  
    11  2018-10-29 09:09:00  2018-10-29 10:10:00  
    12  2018-10-09 02:10:00  2018-10-09 14:00:00  

В моем случае id 7, 8 и 9 - это результаты.

РЕДАКТИРОВАТЬ

Благодаря ответу @Gordon Linoff это исправленный запрос. Но, наконец, не работает так, как ожидалось. Увеличение INTERVAL 50 DAY возвращает ненужные строки, которые не выполняет оконная функция MySQL.

SELECT hs.*,
       (
       SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(hs2.ending_hour, hs2.starting_hour))))
        FROM hours_sailor hs2
        WHERE hs2.starting_hour >= DATE_SUB(hs.starting_hour, INTERVAL 12 HOUR) AND hs2.starting_hour <= DATE_SUB(NOW(), INTERVAL 12 HOUR)
      ) AS duration
FROM `time_table` hs
WHERE hs.`starting_hour` > DATE_SUB(NOW(), INTERVAL 50 DAY) AND hs.`ending_hour` <= NOW()
HAVING duration >= '14:00:00' 
ORDER BY hs.starting_hour ASC;

Примеры данных и желаемые результаты помогут.

Gordon Linoff 31.10.2018 12:22

извините за это, я редактирую свой пост

kl3sk 31.10.2018 12:28

Почему нельзя просто обновить сервер Mariadb? Альтернативные решения были бы чрезвычайно подробными, возможно, с использованием пользовательских переменных.

Madhur Bhaiya 31.10.2018 12:34

Насколько мне известно, последняя версия MariaDB (10.4 в альфа-версии) еще не реализовала его.

kl3sk 31.10.2018 12:35
0
4
135
1

Ответы 1

Вы можете выразить это с помощью коррелированного подзапроса. Думаю, это эквивалентная логика:

SELECT hs.*,
       (SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(hs2.ending_hour, hs2.starting_hour)))
        FROM time_table hs2
        WHERE hs2.starting_hour >= hs.starting_hour - INTERVAL '12' HOUR AND
              hs2.starting_hour <= hs.starting_hour + INTERVAL '12' HOUR
      ) AS tot
FROM time_table hs
WHERE hs.starting_hour > DATE_SUB(NOW(), INTERVAL 50 DAY) AND
      hs.ending_hour <= NOW()
HAVING tot >= '14:00:00' 
ORDER BY hs.starting_hour ASC;

Обновлено:

Если вы также хотите установить временное ограничение для вычисления «диапазона», вам необходимо включить его в подзапрос. Эта фильтрация встроена в оконную функцию, но чаще является помехой, чем функцией:

SELECT hs.*,
       (SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(hs2.ending_hour, hs2.starting_hour)))
        FROM time_table hs2
        WHERE hs2.starting_hour >= hs.starting_hour - INTERVAL '12' HOUR AND
              hs2.starting_hour <= hs.starting_hour + INTERVAL '12' HOUR AND
              hs2.starting_hour > DATE_SUB(NOW(), INTERVAL 50 DAY) AND
              hs2.ending_hour <= NOW()
      ) AS tot
FROM time_table hs
WHERE hs.starting_hour > DATE_SUB(NOW(), INTERVAL 50 DAY) AND
      hs.ending_hour <= NOW()
HAVING tot >= '14:00:00' 
ORDER BY hs.starting_hour ASC;

Ты мой спаситель, похоже, это именно то, что я ищу. Для ясности я обновлю свой вопрос исправленным запросом, потому что он не на 100% совместим с Mysql / MariaDb. Но просто спасибо.

kl3sk 31.10.2018 14:05

Извините за мой энтузиазм, быстрый ответ, но я понимаю, что есть проблема, если вы увеличите количество дополнительных строк INTERVAL 50, которые оконная функция mysql не

kl3sk 31.10.2018 14:54

@ klesk44. . . Это потому, что ваше внешнее предложение WHERE будет применяться к вашей исходной версии. Если вам нужны такие же общие ограничения, вам необходимо применить их в подзапросе.

Gordon Linoff 31.10.2018 14:59

Можно ли обновить свой запрос, чтобы объяснить свой ответ? @Гордон

kl3sk 31.10.2018 15:50

Хорошо, я сделал это, но я думаю, что не понимаю, потому что результаты совпадают с этим подзапросом where или без него. с INTERVAL 50 DAY кажется актуальным. Но с INTERVAL 100 DAY этого не происходит. С интервалом в 100 дней результат должен быть таким же, как и с 50 (это то, что возвращает Mysql8).

kl3sk 31.10.2018 16:58

Итак, единственное решение - перейти на MySQL 8?

kl3sk 02.11.2018 14:37

Другие вопросы по теме