Можно ли собрать такой запрос?

Question

[Антон Шелестов]

Всем привет!
Может подсказать как составить sql запрос чтобы можно было получить следующее(2й блок "Рекорды"):


Т.е. есть таблица с полями:
id, done(boolean), date(YYYY-MM-DD)

Хочу составить запрос который сможет выдать что-то вроде такого:
date_start, count, date_end
limit 10
Ну как все в блоке "Рекорды"

Возможно ли такое?
Спасибо!

Answer 1

[Дмитрий]

SELECT pairs.startDate, pairs.endDate, pairs.endDate - pairs.startDate + 1 as count FROM
(SELECT t1.date as startDate, MIN(t3.date) as endDate FROM statistics as t1
   LEFT JOIN statistics t2 ON t1.date - t2.date = 1 and t2.done = 1
   LEFT JOIN statistics as t3 ON t1.date <= t3.date and t3.done = 1
     LEFT JOIN statistics as t4 ON t3.date - t4.date = -1 and t4.done = 1
WHERE t1.done = 1 AND ISNULL(t2.date) AND ISNULL(t4.date)
GROUP BY t1.date) as pairs
ORDER BY pairs.startDate

Объяснение:
1) Находим все записи t1, у которых нет записей с предыдущим днем t2
2) Соединяем с записями t3, у которых нет записей с последующим днем t4 и дата которых больше t1
3) Считаем для записей из t1 минимум даты из t3

Comments

[Антон Шелестов]

К сожалению нужного результата не выдает(

[Дмитрий]

Антон Шелестов, Странно, у меня работает, дайте мне входные данные я проверю

[Антон Шелестов]

Дмитрий, файл нельзя тут добавить

-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Хост: localhost:3306
-- Время создания: Июн 17 2020 г., 08:36
-- Версия сервера: 5.7.30-0ubuntu0.18.04.1
-- Версия PHP: 7.2.24-0ubuntu0.18.04.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- База данных: `test`
--

-- --------------------------------------------------------

--
-- Структура таблицы `statistics`
--

CREATE TABLE `statistics` (
  `id` int(10) UNSIGNED NOT NULL,
  `done` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Дамп данных таблицы `statistics`
--

INSERT INTO `statistics` (`id`, `done`, `date`) VALUES
(1, 0, '2020-06-19'),
(2, 1, '2020-06-03'),
(3, 1, '2020-06-04'),
(4, 0, '2020-06-05'),
(5, 0, '2020-06-10'),
(6, 1, '2020-06-07'),
(7, 0, '2020-06-22'),
(8, 1, '2020-06-09'),
(9, 1, '2020-06-06'),
(10, 1, '2020-06-11'),
(11, 1, '2020-06-12'),
(12, 1, '2020-06-13'),
(13, 1, '2020-06-14'),
(14, 1, '2020-06-15'),
(15, 0, '2020-06-16'),
(16, 1, '2020-06-17'),
(17, 1, '2020-06-18'),
(18, 0, '2020-06-02'),
(19, 1, '2020-06-20'),
(20, 0, '2020-06-21'),
(21, 1, '2020-06-08'),
(22, 0, '2020-06-23'),
(23, 1, '2020-06-24'),
(24, 1, '2020-06-25'),
(25, 1, '2020-06-26'),
(26, 1, '2020-06-27'),
(27, 1, '2020-06-28'),
(28, 1, '2020-06-29'),
(29, 0, '2020-07-20'),
(30, 0, '2020-07-21'),
(31, 0, '2020-07-19'),
(32, 0, '2020-07-25');

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `statistics`
--
ALTER TABLE `statistics`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT для сохранённых таблиц
--

--
-- AUTO_INCREMENT для таблицы `statistics`
--
ALTER TABLE `statistics`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[Дмитрий]

Антон Шелестов, Чуть чуть поправил текст, там была маленькая ошибка, но ваших результатов он все равно не должен был выдать. Сейчас буду тестить с вашими данными. Замените в моем коде visits на statistics и попробуйте еще раз

[Дмитрий]

Антон Шелестов, Да, с вашими данными и у меня не работает отчего то, буду разбираться, потому что запрос верный

[Антон Шелестов]

Дмитрий, возможно причина в том что порядок записей может быть случайным а не по возрастанию дат... хотя ничего не изменилось, результат тот же.
Пробую немного свой код создать, пока получилось такое:

теперь нужно как-то это дело сжать)

[Дмитрий]

Антон Шелестов, А, ну ясно, я думал по id надо группировать, если нет то вот так

SELECT pairs.startDate, pairs.endDate, DATEDIFF(pairs.endDate, pairs.startDate) + 1 as count FROM
(SELECT t1.date as startDate, MIN(t3.date) as endDate FROM statistics as t1
   LEFT JOIN statistics t2 ON t1.date - t2.date = 1 and t2.done = 1
    AND DATEDIFF(t1.date, t2.date) = 1
   LEFT JOIN statistics as t3 ON t1.date <= t3.date and t3.done = 1
     LEFT JOIN statistics as t4 ON DATEDIFF(t3.date, t4.date) = -1 and t4.done = 1
WHERE t1.done = 1 AND ISNULL(t2.date) AND ISNULL(t4.date)
GROUP BY t1.date) as pairs
ORDER BY pairs.startDate

[Антон Шелестов]

Дмитрий, круто!!! спасибо большое!! Буду изучать код)

[Дмитрий]

Антон Шелестов, Прежде чем изучать возьмите последнюю версию из моего ответа, обнаружил что можно без DATEDIFF обойтись, обычное вычитание дат дает разницу в днях

[Антон Шелестов]

Ммм... думаю оставлю datediff т.к. этот sql будет использоваться в sqllite android

[Дмитрий]

Антон Шелестов, окей, спасибо и вам за интересную задачку

[Антон Шелестов]

Дмитрий, и Вам спасибо за помощь в решении!)

Answer 2

[d-stream]

Судя по https://www.sqlitetutorial.net/sqlite-window-funct... sqlite уже умеет lag/lead - тогда все должно получиться вполне изящно.

Comments

[Антон Шелестов]

Ммм... спасибо, по изучаю, но мне кажется это не то

[d-stream]

Это просто облегчит конструкцию. Можно и без них, но конструкция получится более монстроватая.

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

[Дмитрий]

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

[Дмитрий]

d-stream, Даже без подзапросов можно обойтись