Oracle PL/SQL •MySQL •SQL Server. Select в sql
пара приемов в SELECT-запросах / Блог компании DataArt / Хабр
Автор: Юрий Цыганенко, Senior QAТестирование новых функций часто проводят на данных, взятых с уже функционирующей системы. В этом случае тестировщикам порою приходится строить запросы для хитрых случаев. Например, нужно протестировать новую функциональность интернет-магазина, причём играют роль интервалы между покупками. Нам доступны данные с работающей версии — можно загрузить их на тестовый стенд и проверить работу новой версии продукта. (NB!: конечно, имея дело с «живыми» данными, нужно исключить из них приватную информацию и обеспечить возможность логина интересующим нас пользователям).
Для выбора интересных нам пользовательских аккаунтов нужно сопоставить максимальные интервалы между покупками у разных пользователей.
От тестировщика требуется построить SQL-запрос, выдающий N пользователей, у которых интервалы между датами заказов будут наибольшими.
Аналогичные задачи и их разбор — под катом. Речь идет о поиске последовательностей записей/интервалов стандартными средствами SQL. Агрегатные функции обрабатывают все данные, попадающие в условие выборки, и поэтому только ими обойтись нельзя.
В качестве примера возьмем датчик погоды, периодически выдающий состояние «ясно» или «пасмурно» и силу ветра. Рассмотрим задачи:
1. Первая часть данных выведена в таблицу 'Weather', включающую поля:
• 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; Нюансы: в разных СУБД возможно, потребуются:Проблемы при таком решении:
• Границы периода наблюдений: если все записи в таблице только с ясной погодой, ответа мы не получим. Как, впрочем, не получим и периоды ясной погоды в начале и конце наблюдений; • В ответе отсутствуют точные данных о первой и финальной записи с хорошей погодой. Строго говоря, такое решение не отвечает условию задачи («пары записей с ясной погодой, между которыми не будет иной...»).
Обе проблемы лечатся этими же приемами.
Оттачиваем решение: ищем пары записей с хорошей погодой, между которыми нет записей с плохой погодой. Причем так, чтобы у первой записи в паре не было бы предшествующей записи с хорошей погодой (т. е. это была бы запись, начинающая ясный период). Соответственно, у второй записи в паре не должно быть последующей записи с хорошей погодой (т. е. запись замыкала бы ясный период).
Полагаю, достаточно идеи запроса.
Решение второй задачи: ищем тройки последовательных записей, в которых скорость ветра в средней точке больше, чем в каждой из соседних. Соседство проверяется функцией exists аналогично первой задаче: not exists(...). Порядок точек проверяется сравнением значений времени.
На этом я статью завершаю и еще раз рекомендую Стэндфордский курс!
Enjoy!
habr.com
Команда SELECT Раздел ORDER BY - в возрастающем порядке и в обратном порядке
Раздел ORDER BY
Фраза ORDER BY используется для того, чтобы упорядочить строки, извлекаемые запросом.
В предложении ORDER BY SQL можно задавать несколько выражений. Сначала сортируются строки, основываясь на их значениях для первого выражения. Строки с одним и тем же значением для первого выражения затем сортируются по второму выражению и так далее. NULL-значения располагает после всех других при упорядочивании в порядке возрастания и перед всеми другими при сортировке в убывающем порядке.
Вместо имени столбца можно указать его позицию для сокращения записи длинного выражения.
Кроме того, при составлении сложных запросов, содержащих множественные операторы UNION, INTERSECT, MINUS, или UNION ALL, в предложении ORDER BY лучше использовать позиции, чем непосредственно сами выражения. Предложение ORDER BY может появляться только в последнем составляющем запросе и сортирует строки, полученные всем составным запросом в целом.
Предложение ORDER BY подчинено следующим ограничениям:
- Если в утверждении SELECT используются и оператор ORDER BY и оператор DISTINCT, то предложение ORDER BY не может ссылаться на столбцы, не упоминаемые в списке выбора выбираемых столбцов.
- Предложение ORDER BY не может появляться в подзапросах внутри других утверждений.
ORDER BY в обратном порядке
Выбрать из EMP записи по всем продавцам, и упорядочить результаты по размерам комиссионных в обратном порядке (убывающем порядке):
SELECT * FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm DESC;
ORDER BY в возрастающем порядке
Выбрать из EMP записи по всем сотрудникам, и упорядочить результаты по размерам комиссионных в возрастающем порядке:
SELECT * FROM emp WHERE job = ‘SALESMAN’ ORDER BY comm ASC;
ORDER BY в возрастающем и убывающем порядке
Выбрать из EMP записи по служащим, упорядоченные сначала по возрастанию номера отдела а затем по убыванию размера оклада:
SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
sql-language.ru
SELECT TOP SQL Server — Oracle PL/SQL •MySQL •SQL Server
В этом учебном пособии вы узнаете, как использовать оператор SELECT TOP в SQL Server (Transact-SQL) с синтаксисом и примерами.
Описание
Оператор SELECT TOP SQL Server (Transact-SQL) используется для извлечения записей из одной или нескольких таблиц в SQL Server и ограничения количества возвращаемых записей на основе фиксированного значения или процента.
Синтаксис
Синтаксис оператора SELECT TOP в SQL Server (Transact-SQL):
SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]expressionsFROM tables[WHERE conditions][ORDER BY expression [ ASC | DESC ]];
Параметры или аргументы
TOP (top_value) — возвращает верхнее число строк в результирующем наборе на основе top_value. Например, TOP (10) вернет первые 10 строк из полного набора результатов.PERCENT — необязательный. Если задан PERCENT, то верхние строки основаны на проценте от общего набора результатов (как указано в верхнем значении). Например, TOP (10) PERCENT вернет верхние 10% полного набора результатов.WITH TIES — необязательный. Если указано условие WITH TIES, то возвращаются строки, связанные со строкой на последнем месте в ограниченном результирующем наборе. Это может привести к возврату большего количества строк, чем позволяет параметр TOP.expressions — столбцы или вычисления, которые вы хотите получить.tables — таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в предложении FROM.WHERE conditions — необязательный. Условия, которые должны быть выполнены для выбранных записей.ORDER BY expression — необязательный. Он используется в операторе SELECT TOP для сортировки записей, которые вы хотите вернуть. ASC в порядке возрастания, а DESC — в порядке убывания.
Пример использования ключевого слова TOP
Давайте посмотрим на пример SQL Server, где мы используем ключевое слово TOP в операторе SELECT.Например:
SELECT TOP(5) employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;
SELECT TOP(5) employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id; |
В этом примере SQL Server SELECT TOP были выбраны первые 5 записей из таблицы employees, где last_name – ‘Samson’. Если в таблице employee есть другие записи с last_name ‘Samson’, то они не будут возвращены оператором SELECT.
Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:
SELECT TOP(5) WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;
SELECT TOP(5) WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id; |
Предложение WITH TIES будет включать строки, которые могут быть связаны со строкой на последнем месте в ограниченном наборе результатов. Поэтому, если с 5-й строкой верхнего набора связаны еще две строки, то все эти связанные строки будут возвращены оператором SELECT TOP. Это приведет к возврату более 7 записей.
Пример использования ключевого слова TOP PERCENT
Рассмотрим пример SQL Server, в котором мы используем ключевое слово TOP PERCENT в операторе SELECT.Например:
SELECT TOP(10) PERCENT employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;
SELECT TOP(10) PERCENT employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id; |
Этот пример SQL Server SELECT TOP выберет первые 10% записей из полного набора результатов. Поэтому в этом примере оператора SELECT вернет 10% записей из таблицы employees, где last_name — ‘Samson’. Остальные 90% набора результатов не будут возвращены оператором SELECT.Вы можете изменить этот пример, включив предложение WITH TIES следующим образом:
SELECT TOP(10) PERCENT WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id;
SELECT TOP(10) PERCENT WITH TIES employee_id, last_name, first_name FROM employees WHERE last_name = 'Samson' ORDER BY employee_id; |
Предложение WITH TIES будет включать строки, которые могут быть связаны со строкой которая на последнем месте в ограниченном наборе результатов. Поэтому, если есть такие строки в наборе записей SELECT TOP (10) PERCENT, то эти связанные записи будут возвращены оператором SELECT TOP. Это приведет к возврату более 10% от полного набора записей.
oracleplsql.ru
