Этот запрос работает должным образом под 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;
извините за это, я редактирую свой пост
Почему нельзя просто обновить сервер Mariadb? Альтернативные решения были бы чрезвычайно подробными, возможно, с использованием пользовательских переменных.
Насколько мне известно, последняя версия MariaDB (10.4 в альфа-версии) еще не реализовала его.
Вы можете выразить это с помощью коррелированного подзапроса. Думаю, это эквивалентная логика:
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. Но просто спасибо.
Извините за мой энтузиазм, быстрый ответ, но я понимаю, что есть проблема, если вы увеличите количество дополнительных строк INTERVAL 50
, которые оконная функция mysql не
@ klesk44. . . Это потому, что ваше внешнее предложение WHERE
будет применяться к вашей исходной версии. Если вам нужны такие же общие ограничения, вам необходимо применить их в подзапросе.
Можно ли обновить свой запрос, чтобы объяснить свой ответ? @Гордон
Хорошо, я сделал это, но я думаю, что не понимаю, потому что результаты совпадают с этим подзапросом where или без него. с INTERVAL 50 DAY
кажется актуальным. Но с INTERVAL 100 DAY
этого не происходит. С интервалом в 100 дней результат должен быть таким же, как и с 50 (это то, что возвращает Mysql8).
Итак, единственное решение - перейти на MySQL 8?
Примеры данных и желаемые результаты помогут.