10 хитростей SQL, которые вы не думали, были возможны

Подобные схемы действительно работают — они не только привлекают внимание, если содержание также ценно (и в этом случае, поверьте мне), формат статьи может быть чрезвычайно интересным.

Эта статья познакомит вас с 10 трюками SQL, которые многие из вас, возможно, даже не думали, были возможны. Эта статья является кратким изложением моего нового, чрезвычайно быстро развивающегося, смехотворно ребяческого юмора, который я выступаю на конференциях (недавно в JAX и Devoxx France ). Вы можете процитировать меня по этому поводу:

Полные слайды можно увидеть на слайд-шоу:

… и я уверен, что скоро будет запись на видео. Вот 10 хитростей SQL, которые вы не могли себе представить :

Вступление

Чтобы понять значение этих 10 трюков SQL, в первую очередь важно понять контекст языка SQL. Почему я говорю о SQL на конференциях Java? (а я обычно единственный!) Вот почему:

С самого начала у разработчиков языков программирования возникло желание создавать языки, на которых вы сообщаете машине, ЧТО вы хотите получить, а не КАК ее получить. Например, в SQL вы сообщаете компьютеру, что вы хотите «соединить» (JOIN) таблицу пользователей и таблицу адресов и найти пользователей, которые живут в Швейцарии. Вам все равно, КАК база данных будет извлекать эту информацию (например, должна ли таблица пользователей быть загружена первой или таблица адресов? Должны ли эти две таблицы объединяться во вложенном цикле или с помощью хэш-карты? Должны ли все данные сначала загружаться в память и затем фильтруется для швейцарских пользователей, или мы должны загружать только швейцарские адреса в первую очередь? И т.д.)

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

• Установить формальные отношения внешнего ключа между таблицами (это говорит базе данных, что каждому адресу гарантированно соответствует соответствующий пользователь)
• Добавьте индекс в поле поиска: Страна (это говорит базе данных, что конкретные страны можно найти в O(log N) вместо O(N) )

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

1. Все это стол

Это самая тривиальная уловка, и даже не совсем уловка, но она имеет фундаментальное значение для глубокого понимания SQL: все является таблицей ! Когда вы видите SQL-выражение, подобное этому:

… Вы быстро заметите сидящего за столом person в предложении FROM . Это круто, это стол. Но вы поняли, что все утверждение — это тоже таблица? Например, вы можете написать:

И теперь вы создали так называемую «производную таблицу» — то есть вложенную SELECT в предложении FROM .

Это тривиально, но если подумать, довольно элегантно. Вы также можете создавать специальные таблицы в памяти с помощью конструктора VALUES() как таковые в некоторых базах данных (например, PostgreSQL, SQL Server):

Который просто дает:

Если это предложение не поддерживается, вы можете вернуться к производным таблицам, например, в Oracle:

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

В SQL все является таблицей. Когда вы вставляете строки в таблицу, вы на самом деле не вставляете отдельные строки. Вы действительно вставляете целые таблицы. Большинству людей просто приходится вставлять таблицы из одной строки большую часть времени, и поэтому они не понимают, что на самом деле делает INSERT .

Все это стол. В PostgreSQL четные функции являются таблицами:

Вышеуказанные выходы:

Если вы программируете на Java, вы можете использовать аналог Java 8 Stream API, чтобы сделать этот шаг вперед. Рассмотрим следующие эквивалентные понятия:

В Java 8 «все является потоком» (по крайней мере, как только вы начнете работать с потоками). Независимо от того, как вы преобразуете поток, например, с помощью map() или filter() , результирующий тип всегда снова является Stream.

Мы написали целую статью, чтобы объяснить это более глубоко и сравнить API потока с SQL: общие предложения SQL и их эквиваленты в потоках Java 8

И если вы ищете «лучшие потоки» (то есть потоки с еще большей семантикой SQL), ознакомьтесь с jOOλ , библиотекой с открытым исходным кодом, которая переносит оконные функции SQL в Java .

2. Генерация данных с помощью рекурсивного SQL

Распространенные табличные выражения (также: CTE, также называемый факторингом подзапроса, например, в Oracle) — это единственный способ объявить переменные в SQL (кроме неясного предложения WINDOW , которое знают только PostgreSQL и Sybase SQL Anywhere).

Это мощная концепция. Чрезвычайно мощный. Рассмотрим следующее утверждение:

Это дает

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

Это легко понять. Это делает CTE (Common Table Expressions) уже очень полезным, но что действительно здорово, так это то, что им разрешено быть рекурсивными! Рассмотрим следующий пример PostgreSQL:

Это дает

Как это работает? Это сравнительно легко, когда вы видите через множество ключевых слов. Вы определяете общее табличное выражение, которое имеет ровно два подзапроса UNION ALL .

Первый подзапрос UNION ALL — это то, что я обычно называю «начальным рядом». Это «семена» (инициализирует) рекурсию. Он может создать один или несколько рядов, по которым мы потом вернёмся. Помните: все является таблицей, поэтому наша рекурсия произойдет для всей таблицы, а не для отдельной строки / значения.

Второй подзапрос UNION ALL — это место, где происходит рекурсия. Если вы посмотрите внимательно, вы заметите, что он выбирает из t . Т.е. второму подзапросу разрешено выбирать из того самого CTE, который мы собираемся объявить. Рекурсивный. Таким образом, он также имеет доступ к столбцу v , который объявлен CTE, который уже использует его.

В нашем примере мы заполняем рекурсию строкой (1) , а затем выполняем рекурсию, добавляя v + 1 . Затем рекурсия останавливается на месте использования путем установки LIMIT 5 ( остерегайтесь потенциально бесконечных рекурсий — как в случае Java 8 Streams ).

Примечание: полнота по Тьюрингу

Рекурсивный CTE делает выполнение SQL: 1999 завершенным, что означает, что любая программа может быть написана на SQL! (если вы достаточно сумасшедший)

Один впечатляющий пример, который часто появляется в блогах: набор Мандельброта, например, как показано на http://explainextended.com/2013/12/31/happy-new-year-5/

Запустите выше на PostgreSQL, и вы получите что-то вроде

Впечатляет, а?

3. Выполнение общих расчетов

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

Промежуточный итог легко понять, концептуально.

В Microsoft Excel вы просто вычисляете сумму (или разницу) двух предыдущих (или последующих) значений, а затем используете полезный перекрестный курсор для перемещения этой формулы по всей электронной таблице. Вы «пропускаете» эту сумму через электронную таблицу. «Подводящий итог».

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

Оконные функции — это мощная концепция — поначалу их не так просто понять, но на самом деле они действительно очень просты:

Оконные функции — это агрегаты / ранжирование в подмножестве строк относительно текущей строки, преобразуемой с помощью SELECT

Вот и все.

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

Синтаксис может быть кратко изложен следующим образом, отдельные части являются необязательными

Итак, у нас есть какая-либо функция (примеры таких функций мы увидим позже), за которой следует предложение OVER() , которое определяет окно. Т.е. это предложение OVER() определяет:

• PARTITION : Для окна будут рассматриваться только те строки, которые находятся в том же разделе, что и текущая строка.
• ORDER : Окно можно заказать независимо от того, что мы выбираем
• Определение ROWS (или RANGE ): окно может быть ограничено фиксированным количеством строк «впереди» и «позади»

Это все, что нужно для оконных функций.

Теперь, как это поможет нам рассчитать промежуточную сумму? Рассмотрим следующие данные:

Давайте предположим, что BALANCE — это то, что мы хотим вычислить из AMOUNT

Интуитивно мы сразу видим, что верно следующее:

Таким образом, в простом английском языке любой баланс может быть выражен с помощью следующего псевдо SQL:

В реальном SQL это будет записано следующим образом:

Объяснение:

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

Все это будет происходить в памяти над набором данных, который уже был выбран вами в ваших предложениях FROM .. WHERE и т. Д., И, таким образом, будет чрезвычайно быстрым.

Интермеццо

Прежде чем мы перейдем ко всем другим удивительным трюкам, рассмотрим это: мы видели

• (Рекурсивные) общие табличные выражения (CTE)
• Оконные функции

Обе эти функции:

• Потрясающие
• Очень мощный
• декларативный
• Часть стандарта SQL
• Доступно в большинстве популярных РСУБД (кроме MySQL)
• Очень важные строительные блоки

Если из этой статьи можно сделать вывод, это тот факт, что вы должны абсолютно точно знать эти два строительных блока современного SQL. Почему? Потому как:

4. Нахождение самой большой серии без пробелов

У переполнения стека есть эта очень хорошая функция, чтобы побудить людей оставаться на их сайте как можно дольше. Жетоны:

Для масштаба, вы можете увидеть, сколько значков у меня есть. Тонны.

Как вы рассчитываете эти значки? Давайте посмотрим на «Энтузиаст» и «Фанатик». Эти значки вручаются всем, кто проводит определенное количество дней подряд на своей платформе. Независимо от даты свадьбы или дня рождения жены, вы ДОЛЖНЫ ВОЙТИ, или счетчик снова начинает работать с нуля.

Теперь, когда мы занимаемся декларативным программированием, нам не нужно поддерживать счетчики состояния и памяти. Мы хотим выразить это в форме онлайн-аналитического SQL. Т.е. рассмотрим эти данные:

Это не очень помогает. Давайте удалим часы из отметки времени. Это легко:

Который дает:

Теперь, когда мы узнали о оконных функциях, давайте просто добавим простой номер строки к каждой из этих дат:

Который производит:

Все еще легко. Теперь, что произойдет, если вместо того, чтобы выбирать эти значения отдельно, мы вычитаем их?

Мы получаем что-то вроде этого:

Ух ты. Интересный. Итак, 14 - 1 = 13 , 15 - 2 = 13 , 16 - 3 = 13 , но 18 - 4 = 14 . Никто не может сказать это лучше, чем Дож:

Вот простой пример такого поведения:

1. ROW_NUMBER () никогда не имеет пробелов. Вот как это определяется
2. Наши данные, однако,

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

Да.

Это означает, что теперь мы можем просто GROUP BY этому произвольному значению даты:

И мы сделали. Самая большая серия последовательных дат без пропусков была найдена:

С полным запросом:

Не так сложно в конце концов, верно? Конечно, сама идея имеет значение, но сам запрос действительно очень прост и элегантен. Ни в коем случае нельзя было бы реализовать какой-либо алгоритм императивного стиля более простым способом, чем этот.

Уф.

5. Нахождение длины серии

Ранее мы видели серию последовательных значений. С этим легко справиться, поскольку мы можем злоупотреблять последовательностью целых чисел. Что, если определение «ряда» менее интуитивно понятно, и, кроме того, несколько рядов содержат одинаковые значения? Рассмотрим следующие данные, где LENGTH — длина каждой серии, которую мы хотим вычислить:

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

Как мы делаем это? «Легкий» & # 55357; & # 56841; Во-первых, давайте избавимся от шума и добавим еще один номер строки:

Это даст нам:

Теперь следующая цель — создать следующую таблицу:

В этой таблице мы хотим скопировать значение номера строки в «LO» в «нижнем» конце серии и в «HI» в «верхнем» конце серии. Для этого мы будем использовать магические LEAD () и LAG (). LEAD () может получить доступ к n-й следующей строке из текущей строки, тогда как LAG () может получить доступ к n-й предыдущей строке из текущей строки. Например:

Приведенный выше запрос производит:

Это потрясающе! Помните, что с помощью оконных функций вы можете выполнять ранжирование или агрегирование подмножеств строк относительно текущей строки. В случае LEAD () и LAG () мы просто обращаемся к одной строке относительно текущей строки, учитывая ее смещение. Это полезно во многих случаях.

Продолжая наш пример «LO» и «HI», мы можем просто написать:

… В котором мы сравниваем «предыдущий» знак ( lag(sign) ) с «текущим» знаком ( sign ). Если они разные, мы помещаем номер строки в «LO», потому что это нижняя граница нашей серии.

Затем мы сравниваем «следующий» знак ( lead(sign) ) с «текущим» знаком ( sign ). Если они разные, мы помещаем номер строки в «HI», потому что это верхняя граница нашей серии.

Наконец, немного скучная обработка NULL, чтобы все было правильно, и мы сделали:

Следующий шаг. Мы хотим, чтобы «LO» и «HI» появлялись во ВСЕХ строках, а не только в «нижней» и «верхней» границах ряда. Например, вот так:

Мы используем функцию, которая доступна по крайней мере в Redshift, Sybase SQL Anywhere, DB2, Oracle. Мы используем предложение «IGNORE NULLS», которое можно передать некоторым оконным функциям:

Много ключевых слов! Но суть всегда одна и та же. Из любой данной «текущей» строки мы смотрим на все «предыдущие значения» ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), но игнорируем все нули. Из этих предыдущих значений мы берем последнее значение, и это наше новое значение «LO». Другими словами, мы берем «ближайшее» значение «LO».

То же самое с «Привет». Из любой данной «текущей» строки мы смотрим на все «последующие значения» ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ), но игнорируем все нули. Из последующих значений мы берем первое значение, и это наше новое значение «HI». Другими словами, мы берем «самое близкое» значение «HI».

Объяснил в Powerpoint:

На 100% правильно, с немного скучным NULL теребением

Наконец, мы просто делаем последний тривиальный шаг, имея в виду ошибки off-by-1:

И мы сделали. Вот наш результат:

И полный запрос здесь:

Да. Эта вещь SQL начинает становиться интересной!

Готовы к большему?

6. Проблема суммы подмножеств с SQL

Это мой любимый!

Что такое проблема с суммой подмножеств? Найдите интересное объяснение здесь:
https://xkcd.com/287

И скучный здесь:
https://en.wikipedia.org/wiki/Subset_sum_problem

По существу, для каждого из этих итогов …

… Мы хотим найти «наилучшую» (т. Е. Ближайшую) возможную сумму, состоящую из любой комбинации этих элементов:

Поскольку вы все быстро осваиваете свою умственную математическую обработку, вы сразу же подсчитали, что это лучшие суммы:

Как это сделать с SQL? Легко. Просто создайте CTE, который содержит все 2 ⁿ * возможные * суммы, а затем найдите ближайший для каждого TOTAL :

Когда вы читаете это, вы можете быть как мой друг здесь:

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

В этой статье я не буду объяснять детали этого решения, потому что пример взят из предыдущей статьи, которую вы можете найти здесь:

Как найти ближайшую сумму подмножества с помощью SQL

Наслаждайтесь чтением деталей, но не забудьте вернуться сюда для оставшихся 4 трюков:

7. Покрытие промежуточного итога

До сих пор мы видели, как вычислить «обычную» промежуточную сумму с помощью SQL с помощью оконных функций . Это было просто. А как насчет того, чтобы ограничить текущую сумму так, чтобы она никогда не опускалась ниже нуля? По сути, мы хотим рассчитать это:

Таким образом, когда -15550 тот большой отрицательный AMOUNT -20000 , вместо того, чтобы отображать реальный TOTAL -15550 , мы просто отображали 0 . Другими словами (или наборы данных):

Как мы это сделаем?

Точно. С неясным, специфичным для поставщика SQL. В этом случае мы используем Oracle SQL

Как это работает? Удивительно легко!

Просто добавьте MODEL после любой таблицы, и вы откроете банку удивительных SQL-червей!

MODEL туда MODEL , мы можем реализовать логику электронных таблиц непосредственно в наших инструкциях SQL, как и в Microsoft Excel.

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

Смысл каждого из этих трех дополнительных пунктов лучше всего объяснить слайдами снова.

Предложение DIMENSION BY определяет размеры вашей электронной таблицы. В отличие от MS Excel, в Oracle вы можете иметь любое количество измерений:

Предложение MEASURES определяет значения, доступные в каждой ячейке вашей электронной таблицы. В отличие от MS Excel, в каждой ячейке Oracle может быть целый кортеж, а не одно значение.

Предложение RULES определяет формулы, которые применяются к каждой ячейке в вашей электронной таблице. В отличие от MS Excel, эти правила / формулы централизованы в одном месте, а не помещаются внутри каждой ячейки:

Этот дизайн делает модель немного сложнее в использовании, чем MS Excel, но гораздо более мощной, если вы решитесь. Тогда весь запрос будет «тривиально»:

Все это настолько мощно, что поставляется с собственным техническим документом Oracle, поэтому вместо того, чтобы объяснять подробности здесь, в этой статье, прочитайте превосходный технический документ:

• http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

8. Распознавание паттернов временных рядов

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

Если мы рассмотрим набор данных «длина ряда», нам может потребоваться сгенерировать триггеры для сложных событий в нашем временном ряду как таковой:

Правило вышеупомянутого триггера:

Активизировать 3- ^е повторение события, если событие происходит более 3 раз.

Как и в предыдущем предложении MODEL , мы можем сделать это с помощью специального предложения Oracle, которое было добавлено в Oracle 12c:

Простейшее возможное применение MATCH_RECOGNIZE включает в себя следующие подпункты:

Это звучит безумно Давайте посмотрим на некоторые примеры реализации предложений

Что мы здесь делаем?

• Мы упорядочиваем таблицу по ID , который является порядком, в котором мы хотим сопоставить события. Легко.
• Затем мы указываем значения, которые мы хотим в результате. Нам нужен trg «MEASURE», который определяется как классификатор, то есть литерал, который мы будем использовать в PATTERN позже. Кроме того, мы хотим, чтобы все строки соответствовали друг другу.
• Затем мы указываем шаблон, подобный регулярному выражению. Шаблон представляет собой событие «S» для запуска, за которым, возможно, следует «R» для повтора, «X» для нашего специального события X, за которым следует один или несколько «R» для повторения снова. Если весь шаблон соответствует, мы получаем SRXR или SRXRR или SRXRRR, то есть X будет в третьей позиции серии длины> = 4
• Наконец, мы определяем R и X как одно и то же: событие, когда SIGN(AMOUNT) текущей строки совпадает с SIGN(AMOUNT) предыдущей строки. Нам не нужно определять «S». «S» — это просто любой другой ряд.

Этот запрос волшебным образом выдаст следующий результат:

Мы можем видеть один «X» в нашем потоке событий. Именно там, где мы этого ожидали. При 3-м повторении события (того же знака) в серии длиной> 3.

Boom!

Поскольку нас не интересуют события «S» и «R», давайте просто удалим их как таковые:

производить:

Спасибо Оракул!

Опять же, не ожидайте, что я объясню это лучше, чем превосходный технический документ Oracle, который я настоятельно рекомендую прочитать, если вы все равно используете Oracle 12c:

• http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf

9. Поворот и разворот

Если вы прочитали это далеко, следующее будет почти слишком смущающе простым:

Это наши данные, то есть актеры, названия фильмов и рейтинги фильмов:

Это то, что мы называем поворотом:

Обратите внимание на то, как мы сгруппированы по актерам, а затем «определили» количество фильмов по рейтингу, в котором играл каждый актер. Вместо того, чтобы показывать это «реляционным» способом (то есть каждая группа представляет собой ряд), мы повели все это, чтобы получить столбец на группу. Мы можем сделать это, потому что мы знаем все возможные группы заранее.

Отключение происходит наоборот, когда из вышесказанного мы хотим вернуться к представлению «строка на группу»:

Это на самом деле очень просто. Вот как мы это сделаем в PostgreSQL:

Мы можем добавить простое предложение FILTER к статистической функции, чтобы подсчитать только некоторые данные.

Во всех других базах данных мы бы сделали это так:

Здесь хорошо то, что агрегатные функции обычно учитывают только NULL значения, поэтому, если мы сделаем все значения NULL , которые не интересны для агрегации, мы получим тот же результат.

Теперь, если вы используете либо SQL Server, либо Oracle, вы можете вместо этого использовать встроенные предложения PIVOT или UNPIVOT . Опять же, как в случае с MODEL или MATCH_RECOGNIZE , просто добавьте это новое ключевое слово после таблицы и получите тот же результат:

Легко. Следующий.

10. Злоупотребление XML и JSON

Прежде всего

JSON — это просто XML с меньшими возможностями и меньшим синтаксисом

Теперь все знают, что XML потрясающий. Следствие таково:

JSON менее классный

Не используйте JSON.

Теперь, когда мы решили эту проблему, мы можем спокойно проигнорировать постоянную рекламу JSON-in-the-the-database-обман (о чем большинство из вас пожалеет через пять лет) и перейти к последнему примеру. Как сделать XML в базе данных.

Вот что мы хотим сделать:

Учитывая исходный документ XML, мы хотим проанализировать этот документ, развернуть список фильмов, разделенных запятыми, на каждого актера и создать денормализованное представление актеров / фильмов в одном отношении.

Готовы. Поставил. Идти.Это идея. У нас есть три CTE:

В первом мы просто анализируем XML. Вот с PostgreSQL:

Легко.

Затем мы делаем магию XPath, чтобы извлечь отдельные значения из структуры XML и поместить их в столбцы:

Все еще легко.

Наконец, немного магии рекурсивного сопоставления с шаблоном регулярных выражений, и все готово!

Давайте подведем итоги:

Вывод

Все, что показала эта статья, было декларативным. И относительно легко. Конечно, для забавного эффекта, которого я пытаюсь достичь в этом выступлении, был взят некоторый преувеличенный SQL, и я прямо назвал все «легким». Это совсем не просто, вы должны практиковать SQL. Как и многие другие языки, но немного сложнее, потому что:

1. Синтаксис время от времени немного неудобен
2. Декларативное мышление не легко. По крайней мере, это очень разные

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

Разве это не круто?

And if that was a bit over the top, do note that I’m happy to visit your JUG / conference to give this talk ( just contact us ), or if you want to get really down into the details of these things, we also offer this talk as a public or in-house workshop . Do get in touch! We’re looking forward.

See again the full set of slides here:

Ссылка:

10 SQL Tricks That You Didn’t Think Were Possible from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog.