SQL: пара прийомів в SELECT-запиті


Автор: Юрій Циганенко, Senior QA

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

Для вибору цікавих нам користувальницьких акаунтів потрібно зіставити максимальні інтервали між покупками в різних користувачів.

Від тестувальника потрібно побудувати SQL-запит, видає N користувачів, у яких інтервали між датами замовлень будуть найбільшими.

Аналогічні завдання і їх розбір — під катом.

Мова йде про пошук послідовностей записів/інтервалів стандартними засобами SQL. Агрегатні функції обробляють всі дані, які потрапляють в умову вибірки, і тому тільки ними обійтися не можна.

В якості прикладу візьмемо датчик погоди, періодично видає стан «ясно» чи «похмуро» і силу вітру. Розглянемо завдання:

1. Перша частина даних виведена в таблицю 'Погода', містить поля:

• time // Містить час вимірювання;
• clear // Містить оцінку чистоти неба: нехай 0 — похмуро; 1 — ясно.

Нам потрібен запит, видає кілька (скажімо, три або менше) найбільш довгих періоду (інтервалу) з ясною погодою. Іншими словами, пари записів з ясною погодою, між якими не буде періодів щільної хмарності. Рівномірність вимірювань при цьому не мається на увазі.

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



2. В рамках другого завдання у нас є схожа на першу таблиця 'Wind', що включає записи сили вітру в окремі моменти часу. Вона має два поля:

• Time
• Speed

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



На графіку локальні максимуми відповідають 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!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.