Учебно-практическое пособие москва 2017 ббк
жүктеу/скачать 3,05 Mb. Pdf көрінісі
|
Язык SQL. Базовый курс
- Бұл бет үшін навигация:
- SELECT departure_city, count( * ) FROM routes GROUP BY departure_city HAVING count( * ) >= 15 ORDER BY count DESC;
- SELECT city, count( * ) FROM airports GROUP BY city HAVING count( * ) > 1;
| предложении HAVING
. Важно помнить, что предложение WHERE ра- ботает с отдельными строками еще до выполнения группировки с помощью GROUP BY, а предложение HAVING — уже после выполнения группировки. В качестве примера приведем такой запрос: определить, сколько существует марш- рутов из каждого города в другие города, и вывести названия городов, из которых в другие города существует не менее 15 маршрутов. SELECT departure_city, count( * ) FROM routes GROUP BY departure_city HAVING count( * ) >= 15 ORDER BY count DESC; departure_city | count -----------------+------- Москва | 154 Санкт-Петербург | 35 Новосибирск | 19 Екатеринбург | 15 (4 строки) В подавляющем большинстве городов только один аэропорт, но есть и такие города, в которых более одного аэропорта. Давайте их выявим. SELECT city, count( * ) FROM airports GROUP BY city HAVING count( * ) > 1; city | count -----------+------- Ульяновск | 2 Москва | 3 (2 строки) Кроме обычных агрегатных функций существуют и так называемые оконные функ- ции (window functions) , технология использования которых описана в документа- ции в разделе 3.5 «Оконные функции». Эти функции предоставляют возможность производить вычисления на множестве строк, логически связанных с текущей стро- кой, т. е. имеющих то или иное отношение к ней. При работе с оконными функциями используются концепции раздела (partition) и оконного кадра (window frame). Сначала объясним эти понятия на примере. Предпо- ложим, что руководство нашей компании хочет усовершенствовать тарифную поли- тику и с этой целью просит нас предоставить сведения о распределении количества проданных билетов на некоторые рейсы во времени. Количество проданных билетов должно выводиться в виде накопленного показателя, суммирование должно произ- водиться в пределах каждого календарного месяца. Форма отчета предположительно должна быть такой: 130 book_ref | book_date | month | day | count ----------+------------------------+-------+-----+------- A60039 | 2016-08-22 12:02:00+08 | 8 | 22 | 1 554340 | 2016-08-23 23:04:00+08 | 8 | 23 | 2 854C4C | 2016-08-24 10:52:00+08 | 8 | 24 | 5 854C4C | 2016-08-24 10:52:00+08 | 8 | 24 | 5 854C4C | 2016-08-24 10:52:00+08 | 8 | 24 | 5 81D8AF | 2016-08-25 10:22:00+08 | 8 | 25 | 6 ... 8D6873 | 2016-08-31 17:09:00+08 | 8 | 31 | 59 E82829 | 2016-08-31 20:56:00+08 | 8 | 31 | 60 ECA0D7 | 2016-09-01 00:48:00+08 | 9 | 1 | 1 E3BD32 | 2016-09-01 04:44:00+08 | 9 | 1 | 2 ... EB11BB | 2016-09-03 12:02:00+08 | 9 | 3 | 14 19FE38 | 2016-09-03 17:42:00+08 | 9 | 3 | 16 19FE38 | 2016-09-03 17:42:00+08 | 9 | 3 | 16 536A3D | 2016-09-03 19:19:00+08 | 9 | 3 | 18 536A3D | 2016-09-03 19:19:00+08 | 9 | 3 | 18 02E6B6 | 2016-09-04 01:39:00+08 | 9 | 4 | 19 (79 строк) Для примера был выбран рейс с идентификатором 1. В столбцах book_ref и book_date приводятся номер бронирования и момент времени, когда оно было произведено. В столбцах month и day указываются порядковый номер месяца и день этого месяца. В столбце count содержатся суммарные (накопленные) количества билетов, проданных на каждый момент времени. С первого дня нового месяца подсчет числа проданных билетов начинается сначала. Таким образом, в на- шем примере в качестве раздела (partition) будет выступать множество строк, у ко- торых даты продажи билета (т. е. даты бронирования) относятся к одному и тому же месяцу. В результате в полученной выборке будет сформировано два раздела. Понятие оконного кадра (window frame) является важным, поскольку многие окон- ные функции работают не со всеми строками раздела, а только с теми, которые обра- зуют оконный кадр текущей строки. Если строки в разделе не упорядочены, то окон- ным кадром текущей строки по умолчанию считается множество всех строк раздела. Однако в том случае, когда строки в разделе упорядочены по какому-то критерию, тогда в состав оконного кадра по умолчанию включаются строки, начиная с первой строки раздела и заканчивая текущей строкой. Если же существуют строки, имеющие такое же значение критерия сортировки, что и текущая строка, и расположенные по- сле нее, то они также включаются в состав оконного кадра текущей строки. Обратите внимание на первые строки в представленной выборке. В строках с тре- тьей по пятую значения в столбце count одинаковые и равны 5. Равенство значений имеет следующее объяснение. В рамках одного бронирования с номером «854C4C» были проданы сразу три билета на этот рейс, поэтому в этих трех строках значения в столбце book_date одинаковые. Строки в выборке упорядочены по значениям столбца book_date. Таким образом, для каждой из этих трех строк, т. е. для третьей, четвер- той и пятой, значения критерия сортировки одинаковые, поэтому оконным кадром для каждой из них будут являться первые пять строк первого раздела выборки. Под- счет числа проданных билетов выполняется в пределах оконного кадра. В результате и появляется значение 5 в каждой из этих трех строк, а значений 3 и 4 нет вообще. 131 В приведенной выборке отражены также и случаи одновременного бронирования двух билетов на данный рейс. Вы можете найти соответствующие строки самосто- ятельно. Теперь посмотрим, с помощью какого запроса был получен этот результат, и на его примере объясним синтаксические конструкции, используемые для работы с окон- ными функциями. жүктеу/скачать 3,05 Mb. Достарыңызбен бөлісу:
|