SQL: пара приемов в SELECT-запросах

в 17:07, , рубрики: sql, Блог компании DataArt, запросы, тестирование

SQL: пара приемов в SELECT-запросах - 1
Автор: Юрий Цыганенко, Senior QA

Тестирование новых функций часто проводят на данных, взятых с уже функционирующей системы. В этом случае тестировщикам порою приходится строить запросы для хитрых случаев. Например, нужно протестировать новую функциональность интернет-магазина, причём играют роль интервалы между покупками. Нам доступны данные с работающей версии — можно загрузить их на тестовый стенд и проверить работу новой версии продукта. (NB!: конечно, имея дело с «живыми» данными, нужно исключить из них приватную информацию и обеспечить возможность логина интересующим нас пользователям).

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

От тестировщика требуется построить SQL-запрос, выдающий N пользователей, у которых интервалы между датами заказов будут наибольшими.

Аналогичные задачи и их разбор — под катом.

Речь идет о поиске последовательностей записей/интервалов стандартными средствами SQL. Агрегатные функции обрабатывают все данные, попадающие в условие выборки, и поэтому только ими обойтись нельзя.

В качестве примера возьмем датчик погоды, периодически выдающий состояние «ясно» или «пасмурно» и силу ветра. Рассмотрим задачи:

1. Первая часть данных выведена в таблицу 'Weather', включающую поля:

• time // Содержит время измерения;
• clear // Содержит оценку чистоты неба: пусть 0 — пасмурно; 1 — ясно.

Нам нужен запрос, выдающий несколько (допустим, три или менее) наиболее долгих периода (интервала) с ясной погодой. Иными словами, пары записей с ясной погодой, между которыми не будет периодов плотной облачности. Равномерность измерений при этом не подразумевается.

То есть задача сводится к поиску наборов трех или менее значений Period_1…Period_N в порядке убывания.

SQL: пара приемов в SELECT-запросах - 2

2. В рамках второй задачи у нас есть похожая на первую таблица 'Wind', включающая записи силы ветра в отдельные моменты времени. Она имеет два поля:

• Time
• Speed

Требуется найти все «локальные максимумы» скорости — т. е. моменты времени и значения скорости, по сравнению с которыми предшествующая и последующая (по времени) записи имеют меньшую скорость.

SQL: пара приемов в SELECT-запросах - 3

На графике локальные максимумы соответствуют 3, 10, 14, 17. Для упрощения не будем считать граничную точку 19.

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

1. Неявный Join таблицы с ней же самой: в поле FROM через запятую перечислим записи и нашу таблицу (например, FROM Weather w1, Weather w2 ).

На всякий случай: при выборке SELECT w1.*, w2.* FROM Weather w1, Weather w2; выведутся все пары записей, включая совпадающие и повторяющиеся в обратном порядке пары. Т. е. при 10 записях в таблице выведутся 100.

2. Функция exists(), внутри которой пишем вложенный запрос.

Оба эти приема показаны в бесплатном курсе по SQL (видео): тут и тут.

Грубое решение первой задачи: выбираем все пары записей с плохой погодой, между которыми все записи с хорошей погодой, причем не менее 1 (то есть нет плохой поды в промежуточных записях):

SELECT (w2.time - w1.time) as duration
FROM Weather w1, Weather w2 
WHERE w2.time > w1.time         # вторая точка позде первой
  AND w2.clear=0                # в первой точке плохая погода
  AND w1.clear=0                # во второй точке плохая погода
  AND exists (                  # Существуют точки
     SELECT * FROM Weather wg 
     WHERE wg.clear = 1         # с ясной погодой
       AND wg.time > w1.time    # в промежутке от первой точки
       AND wg.time < w2.time    # до второй 
  )
  AND not exists (              # Нет точек
     SELECT * FROM Weather w3 
     WHERE w3.clear=0           # с плохой погодой
       AND w3.time>w1.time      # в промежутке от первой точки
       AND w3.time<w2.time      # до второй
  )
Order by duration DESC
LIMIT 3;

Нюансы: в разных СУБД возможно, потребуются:

• конвертации для типа timestamp, чтобы можно было вычитать w1.time и w2.time;
• ограничение на количество выводимых строк — limit или top.

Проблемы при таком решении:

• Границы периода наблюдений: если все записи в таблице только с ясной погодой, ответа мы не получим. Как, впрочем, не получим и периоды ясной погоды в начале и конце наблюдений;
• В ответе отсутствуют точные данных о первой и финальной записи с хорошей погодой.
Строго говоря, такое решение не отвечает условию задачи («пары записей с ясной погодой, между которыми не будет иной...»).

Обе проблемы лечатся этими же приемами.

Оттачиваем решение: ищем пары записей с хорошей погодой, между которыми нет записей с плохой погодой. Причем так, чтобы у первой записи в паре не было бы предшествующей записи с хорошей погодой (т. е. это была бы запись, начинающая ясный период). Соответственно, у второй записи в паре не должно быть последующей записи с хорошей погодой (т. е. запись замыкала бы ясный период).

Полагаю, достаточно идеи запроса.

Решение второй задачи: ищем тройки последовательных записей, в которых скорость ветра в средней точке больше, чем в каждой из соседних. Соседство проверяется функцией exists аналогично первой задаче: not exists(...). Порядок точек проверяется сравнением значений времени.

На этом я статью завершаю и еще раз рекомендую Стэндфордский курс!

Enjoy!

Автор: DataArt

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js