Рассчитать частоту в SQL по определённой формуле

Question

Доброго времени суток программисты, встала задача посчитать такую вещь, имеется таблица с двумя полями по которым нужно вычислить "частоту", в которой находится айди события (просто айди для наглядности) и дата по возрастанию (01-01-2022, 01-02-2022, 02-02-2022, 15-03-2022), интересует возможность посчитать это всё средствами базы данных по формуле, (date[i + 1] - date[i]) + (date[i + 2] - date[i + 1]) + (date[i + 3] - date[i + 2]) и потом эту всю сумму поделить на кол-во слагаемых. И при этом всё это вывести вместе с событием. Вывод например такой: 1 - 10,5 ; 2 - 3,7; Код для генерации таблиц вставлю ниже. Заранее благодарен за уделённое время и помощь. Работаю на python 3.10, если что могу подкорректировать что-то с помощью него, и MariaDb 10-ой версии. Данных будет очень много и весь запрос изначально большой, поэтому нет смысла на питоне его обрабатывать ибо думаю что в базе данных есть функционал уже для такого.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for Test_table
-- ----------------------------
DROP TABLE IF EXISTS `Test_table`;
CREATE TABLE `Test_table` (
  `case` int(255) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of Test_table
-- ----------------------------
BEGIN;
INSERT INTO `Test_table` VALUES (1, '2022-06-01');
INSERT INTO `Test_table` VALUES (2, '2022-06-03');
INSERT INTO `Test_table` VALUES (1, '2022-06-03');
INSERT INTO `Test_table` VALUES (2, '2022-06-04');
INSERT INTO `Test_table` VALUES (1, '2022-06-08');
INSERT INTO `Test_table` VALUES (2, '2022-06-07');
INSERT INTO `Test_table` VALUES (1, '2022-06-10');
INSERT INTO `Test_table` VALUES (2, '2022-06-12');
INSERT INTO `Test_table` VALUES (1, '2022-06-16');
INSERT INTO `Test_table` VALUES (2, '2022-06-14');
INSERT INTO `Test_table` VALUES (2, '2022-06-16');
INSERT INTO `Test_table` VALUES (2, '2022-06-17');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

Answer 1

я так понял, что у вас есть типы события 1 и 2 которые наступают в какие то даты. вам нужно вычислить среднее число дней между наступлением событий для каждого типа. Единственная загвоздка в том, что у меня получаются другие значения нежели вы привели.

В общем первое, что надо сделать - навсегда запомнить, что именовать поля, используя ключевые слова SQL, это плохая идея. Делать это дважды в одной таблице - очень плохая идея.

Начать можно с того, чтобы помимо строк таблицы выбрать еще и предыдущую дату. Сделать это можно с помощью оконной функции LAG. Пример:

SELECT c, d
   , LAG(d, 1) OVER (PARTITION BY c ORDER BY d ASC) AS pd 
FROM test

Получим нечто вида

1   2022-06-01  null
1   2022-06-03  2022-06-01
1   2022-06-08  2022-06-03
....
2   2022-06-03  null
2   2022-06-04  2022-06-03
....

самые первые строки где pd пусто можно отбросить и далее посчитать число дней между датами. Так что заворачиваем сие в CTE и выичсляем

WITH data AS (
   ...
)
SELECT c, d, pd, DATEDIFF(d, pd) AS dif
FROM DATA

на выходе имеем

1   2022-06-03  2022-06-01  2
1   2022-06-08  2022-06-03  5
...

2   2022-06-04  2022-06-03  1
...

Теперь еще раз заворачиваем в CTE, группируем и считаем среднее

WITH data AS  (...)
   , data2 AS (...)
SELECT c, AVG(diff)
FROM data2
GROUP BY c

получим

1   3.7500
2   2.3333

Answer 2

В итоге я решил просто создать буферную таблицу в которой буду хранить все значения вычитаний дат, рекомендую такой способ.

БЛОГ НА HUSL

Рассчитать частоту в SQL по определённой формуле

Ответы (2 шт):