Sql примеры запросов: SELECT SQL — Примеры запросов SELECT в СУБД MySQL, Oracle, Access

Содержание

Пример выполнения SQL запросов

Online Справка создание SQL запросов

Пример 1 Отчет по участкам: количество и протяженность трубопроводов с разбивкой по диаметрам

В конструкции этого запроса используется оператор GROUP BY, который позволяет разделить записи базы данных по участку на группы. Группировка записей выполняться по значению внутреннего диаметра трубопровода.
Выборка данных производится с помощью ключевого слова SELECT, после которого задаются параметры выборки. Осуществляется выбор следующих параметров:

  • Внутренний диаметр трубы, м;
  • Количество участков данного диаметра, для определения количества участковиспользуются функция COUNT;
  • Протяженность трубопроводов заданного диаметра, функция SUM определяет суммарную длину участков.

Ключевое слова AS используется для указания названий столбцов.
После ключевого слова FROM указывает слой, в котором будет производиться выбор.
Выражение WHERE используется для указания критериев выборки, в данном случае выбор осуществляется только по участкам. Участки водопроводной сети в структуре слоя имеют тип-8, условие type=8, в версии сборки 7.0.0.5425 и выше задается typeid=8.

SELECT [Внутренний диаметр трубы, м] AS «Диаметр»,
COUNT(*) AS «Количество участков»,
SUM([Длина участка, м]) AS «Протяженность»
FROM [Пример водопроводной сети]
WHERE typeid=8
GROUP BY [Внутренний диаметр трубы, м]

Смотреть видеоурок


Пример 2 Заполнение адресов по потребителям значениями из базы по домам

В запросе осуществляется работа с пространственными данными.
В конструкции этого запроса используется оператор сравнения пространственных объектов между собой STWithin. Он проверяет объект для которого вызвана функция на вхождение в указанный пространственный объект сравнения.
Для внесения изменения в заданные столбцы таблицы(базы данных слоя) используется оператор UPDATE, он употребляется в связке с ключевым словом SET после которого следует наименование обновляемых полей таблицы.
После выражения WHERE, задаются критерии, по которым будут происходить обновления. В данном случае происходит обновление поля Адрес базы данных по потребителям (потребители водопроводной сети в структуре слоя имеют тип-5), при условии что, символ потребителя находится внутри площадного объекта здания. Условие выбора потребителей type=8, в версии сборки 7.0.0.5425 и выше задается typeid=8.

UPDATE [Пример водопроводной сети] SET [Пример водопроводной сети].[Адрес] =
[Здания].[Улица]+» «+[Здания].[Номер дома]
WHERE [Пример водопроводной сети].typeid=5 AND [Пример водопроводной сети].Geometry.STWithin([Здания].Geometry)

Конструкцию запроса можно изменить, применив псевдонимы для названий слоев. Для создания псевдонима для слоя, Используется оператор FROM, после названия слоя следует указать псевдоним для этого слоя (перед псевдонимом можно также добавить ключевое слово AS).

UPDATE L1 SET L1. [Адрес] = L2.[Улица]+» «+L2.[Номер дома]
FROM [Пример водопроводной сети] AS L1, [Здания] AS L2
WHERE L1.typeid=5 AND L1.Geometry.STWithin(L2.Geometry)

Смотреть видеоурок

Создание SQL-запроса вручную

Создание SQL-запроса вручную




Пожалуйста, включите JavaScript в браузере!

Создание SQL-запроса вручную

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

Чтобы сформировать SQL-запрос вручную:

  1. Перейдите в раздел События веб-интерфейса KUMA.

    Откроется форма с полем ввода.

  2. Введите SQL-запрос в поле ввода.
  3. Нажмите на кнопку .

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

Поддерживаемые функции и операторы

  • SELECT – поля событий, которые следует возвращать.

    Для SELECT в программе поддержаны следующие функции и операторы:

    • Функции агрегации: count, avg, max, min, sum.
    • Арифметические операторы: +, -, *, /, <, >, =, !=, >=, <=.

      Вы можете комбинировать эти функции и операторы.

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

  • FROM – источник данных.

    При создании запроса в качестве источника данных вам нужно указать значение events.

  • WHERE – условия фильтрации событий.
    • AND, OR, NOT, =, !=, >, >=, <, <=
    • IN
    • BETWEEN
    • LIKE
    • ILIKE
    • inSubnet
    • match (в запросах используется синтаксис регулярных выражений re2, специальные символы требуется дополнительно экранировать с помощью обратной косой черты (\))
  • GROUP BY – поля событий или псевдонимы, по которым следует группировать возвращаемые данные.

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

  • ORDER BY – столбцы, по которым следует сортировать возвращаемые данные.

    Возможные значения:

    • DESC – по убыванию.
    • ASC – по возрастанию.
  • OFFSET – пропуск указанного количества строк перед выводом результатов запроса.
  • LIMIT – количество отображаемых в таблице строк.

    Значение по умолчанию – 250.

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


    Примеры запросов:

    • SELECT * FROM `events` WHERE Type IN ('Base', 'Audit') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events с типом Base и Audit, отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE BytesIn BETWEEN 1000 AND 2000 ORDER BY Timestamp ASC LIMIT 250

      Все события таблицы events, для которых в поле BytesIn значение полученного трафика находится в диапазоне от 1000 до 2000 байт, отсортированные по столбцу Timestamp в порядке возрастания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE Message LIKE '%ssh:%' ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events, которые в поле Message содержат данные, соответствующие заданному шаблону %ssh:% в нижнем регистре, и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE inSubnet(DeviceAddress, '00.0.0.0/00') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events для хостов, которые входят в подсеть 00.0.0.0/00, отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT * FROM `events` WHERE match(Message, 'ssh.*') ORDER BY Timestamp DESC LIMIT 250

      Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh.*, и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

    • SELECT max(BytesOut) / 1024 FROM `events`

      Максимальный размер исходящего трафика (КБ) за выбранный период времени.

    • SELECT count(ID) AS "Count", SourcePort AS "Port" FROM `events` GROUP BY SourcePort ORDER BY Port ASC LIMIT 250

      Количество событий и номер порта. События сгруппированы по номеру порта и отсортированы по столбцу Port в порядке возрастания. Количество отображаемых в таблице строк – 250.

      Столбцу ID в таблице событий присвоено имя Count, столбцу SourcePort присвоено имя Port.

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


Пример:

SELECT * FROM `events` WHERE match(Message, 'ssh:\'connection.*') ORDER BY Timestamp DESC LIMIT 250

Все события таблицы events, которые в поле Message содержат текст, соответствующий шаблону ssh: 'connection', и отсортированы по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

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


Пример:

SELECT * FROM `events` WHERE DeviceAddress = '00.00.00.000' AND Extra LIKE '%"app":"example"%' ORDER BY Timestamp DESC LIMIT 250

Все события таблицы events для хостов с IP-адресом 00. 00.00.000, на которых запущен процесс example, отсортированные по столбцу Timestamp в порядке убывания. Количество отображаемых в таблице строк – 250.

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

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

Подробнее об SQL см. в справке ClickHouse. Также см. поддерживаемые функции ClickHouse.

В начало

Синтаксис SQL

Резюме : в этом руководстве мы познакомим вас с синтаксисом SQL, который поможет вам понять детали операторов SQL.

SQL является декларативным языком, поэтому его синтаксис читается как естественный язык. Оператор SQL начинается с глагола, описывающего действие, например, SELECT, INSERT, UPDATE или DELETE. После глагола идут подлежащее и сказуемое.

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

См. следующую инструкцию SQL:

 

SELECT имя ОТ сотрудники КУДА ГОД(дата_найма) = 2000;

Язык кода: SQL (язык структурированных запросов) (sql)

Как видите, это читается как обычное предложение.

Получите имена сотрудников, нанятых в 2000 году.

SELECT first_name , FROM employee и WHERE  являются предложениями в операторе SQL. Некоторые пункты являются обязательными, например, SELECT  и FROM , тогда как другие, например, WHERE , являются необязательными.

Поскольку SQL был разработан специально для людей, не обладающих техническими знаниями, он очень прост и понятен. Чтобы написать оператор SQL, вам просто нужно сказать, что вы хотите, а не как вы хотите, как в других императивных языках, таких как PHP, Java и C++.

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

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

Команды SQL

SQL состоит из множества команд. Каждая команда SQL обычно заканчивается точкой с запятой (;). Например, ниже приведены две разные команды SQL, разделенные точкой с запятой (;).

 

ВЫБЕРИТЕ имя Фамилия ОТ сотрудники; УДАЛИТЬ ОТ сотрудников КУДА наем_дата < '1990-01-01';

Язык кода: SQL (язык структурированных запросов) (sql)

SQL использует точку с запятой (;) для обозначения конца команды.

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

Литералы

Литералы — это явные значения, которые также известны как константы. SQL предоставляет три типа литералов: строковые, числовые и двоичные.

Строковый литерал состоит из одного или нескольких буквенно-цифровых символов, заключенных в одинарные кавычки, например:

 

«Джон» '1990-01-01' '50'

Язык кода: SQL (язык структурированных запросов) (sql)

50 — число. Однако, если вы заключите его в одинарные кавычки, например, '50' , SQL будет рассматривать его как строковый литерал.

Как правило, SQL чувствителен к регистру по отношению к строковым литералам, поэтому значение 'John' не совпадает с 'JOHN' .

Числовые литералы представляют собой целое, десятичное или экспоненциальное представление, например:

 

200 -5 6.0221415E23

Язык кода: SQL (язык структурированных запросов) (sql)

SQL представляет двоичное значение с использованием нотации x'0000' , где каждая цифра является шестнадцатеричным значением, например:

x'

4 01'
х'0f0ff'

Язык кода: SQL (язык структурированных запросов) (sql)

Ключевые слова

В SQL есть много ключевых слов, которые имеют специальные значения, такие как SELECT, INSERT, UPDATE, DELETE и DROP. Эти ключевые слова являются зарезервированными словами, поэтому их нельзя использовать в качестве имен таблиц, столбцов, индексов, представлений, хранимых процедур, триггеров или других объектов базы данных.

Идентификаторы

Идентификаторы относятся к определенным объектам в базе данных, таким как таблицы, столбцы, индексы и т. д. SQL нечувствителен к регистру ключевых слов и идентификаторов.

Следующие операторы эквивалентны.

 

Выберите  * От сотрудников; ВЫБЕРИТЕ * ИЗ СОТРУДНИКОВ; выбрать * из сотрудников; ВЫБЕРИТЕ * ОТ сотрудников;

Язык кода: SQL (язык структурированных запросов) (sql)

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

Комментарии

Для документирования операторов SQL используются комментарии SQL. При анализе операторов SQL с комментариями ядро ​​базы данных игнорирует символы в комментариях.

Комментарий обозначается двумя последовательными дефисами ( -- ), которые позволяют прокомментировать оставшуюся строку. См. следующий пример.

 

ВЫБЕРИТЕ идентификатор_сотрудника, зарплата ОТ сотрудники КУДА зарплата < 3000;-- сотрудники с низкой зарплатой

Язык кода: SQL (язык структурированных запросов) (sql)

Это комментарий SQL.

 

-- сотрудники с низкой зарплатой

Язык кода: SQL (язык структурированных запросов) (sql)

Для документирования кода, который может занимать несколько строк, используется многострочная нотация в стиле C ( /* */ ), как показано в следующем заявлении:

 

/* увеличение на 5% для сотрудников, чья зарплата меньше 3000 */ ОБНОВИТЬ сотрудников УСТАНОВЛЕН оклад = оклад * 1,05 КУДА зарплата < 3000;

Язык кода: SQL (язык структурированных запросов) (sql)

В этом руководстве мы познакомили вас с синтаксисом SQL, который поможет вам понять каждый компонент оператора SQL.

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

Было ли это руководство полезным?

Рекомендации по написанию SQL-запросов

  • Правильность, удобочитаемость, затем оптимизация: в таком порядке
  • Сделайте свои стога как можно меньше, прежде чем искать иголки
  • Сначала узнайте свои данные
  • Разработка запроса
  • Общий порядок выполнения запроса
  • Некоторые рекомендации по запросам (не правила)
    • Прокомментируйте свой код, особенно почему
    • рекомендации SQL для FROM
      • Соединение таблиц с использованием ключевого слова ON
      • Псевдоним нескольких таблиц
    • рекомендации SQL для WHERE
      • Фильтр с WHERE до HAVING
      • Избегайте функций для столбцов в предложениях WHERE
      • Предпочтение = от до НРАВИТСЯ
      • Избегайте использования подстановочных знаков в операторах WHERE
      • Предпочтение СУЩЕСТВУЕТ В
    • рекомендации SQL для GROUP BY
      • Упорядочить несколько групп по убыванию мощности
    • рекомендации SQL для HAVING
      • Используйте только HAVING для агрегатов фильтрации
    • рекомендации SQL для SELECT
      • ВЫБЕРИТЕ столбцы, а не звезды
    • рекомендации SQL для UNION
      • Предпочесть UNION All вместо UNION
    • рекомендации SQL для ORDER BY
      • По возможности избегайте сортировки, особенно в подзапросах
  • Рекомендации по SQL для INDEX
    • Добавление индексов
    • Использовать частичные индексы
    • Использовать составные индексы
  • ОБЪЯСНИТЬ
    • Поиск узких мест
  • С
    • Организуйте свои запросы с помощью Common Table Expressions (CTE)
  • С метабазой вам даже не нужно использовать SQL
  • Вопиющие ошибки или упущения?

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

Правильность, удобочитаемость, затем оптимизация: в таком порядке

Здесь применяется стандартное предупреждение о преждевременной оптимизации. Избегайте настройки SQL-запроса, пока не будете уверены, что ваш запрос возвращает нужные вам данные. И даже в этом случае оптимизируйте запрос в первую очередь только в том случае, если он выполняется часто (например, при включении популярной информационной панели) или если запрос проходит большое количество строк. В общем, прежде чем беспокоиться о производительности, отдайте предпочтение точности (выдает ли запрос ожидаемые результаты) и удобочитаемости (могут ли другие легко понять и изменить код).

Сделайте свои стога как можно меньше, прежде чем искать иголки

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

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

Мы пересмотрим общий порядок выполнения запросов и добавим советы по сокращению пространства поиска. Затем мы поговорим о трех основных инструментах, которые можно добавить в пояс: INDEX, EXPLAIN и WITH.

Сначала узнайте свои данные

Прежде чем писать хоть одну строку кода, ознакомьтесь со своими данными, изучив метаданные, чтобы убедиться, что столбец действительно содержит ожидаемые данные. Редактор SQL в Metabase имеет удобную справочную вкладку данных (доступную через значок книги ), где вы можете просматривать таблицы в своей базе данных и просматривать их столбцы и соединения (рисунок 1):

Рис. 1 . Используйте боковую панель Data Reference для просмотра полей таблицы.

Вы также можете просмотреть примеры значений для определенных столбцов (рис. 2).

Рис. 2 . Используйте боковую панель Справочник по данным , чтобы просмотреть образцы данных. Метабаза

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

Разработка запроса

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

  • Как и выше, изучите метаданные столбца и таблицы. Если вы используете собственный редактор запросов Metabase, вы также можете искать фрагменты SQL, содержащие код SQL для таблицы и столбцов, с которыми вы работаете. Фрагменты позволяют увидеть, как другие аналитики запрашивали данные. Или вы можете начать запрос из существующего вопроса SQL.
  • Чтобы получить представление о значениях таблицы, ВЫБЕРИТЕ * из таблиц, с которыми вы работаете, и ОГРАНИЧЬТЕ результаты. Применяйте LIMIT по мере уточнения столбцов (или добавляйте дополнительные столбцы с помощью объединений).
  • Сократите столбцы до минимального набора, необходимого для ответа на ваш вопрос.
  • Примените любые фильтры к этим столбцам.
  • Если вам нужно агрегировать данные, агрегируйте небольшое количество строк и убедитесь, что агрегирование соответствует вашим ожиданиям.
  • Когда у вас есть запрос, возвращающий нужные вам результаты, найдите разделы запроса, чтобы сохранить их как Common Table Expression (CTE) для инкапсуляции этой логики.
  • С помощью Metabase вы также можете сохранять код в виде фрагмента SQL для совместного использования и повторного использования в других запросах.

Общий порядок выполнения запроса

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

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

  1. FROM (и JOIN) получает(ют) таблицы, на которые есть ссылки в запросе. Эти таблицы представляют максимальное пространство поиска, указанное вашим запросом. По возможности ограничьте это пространство поиска, прежде чем двигаться дальше.
  2. ГДЕ фильтрует данные.
  3. GROUP BY объединяет данные.
  4. HAVING отфильтровывает агрегированные данные, которые не соответствуют критериям.
  5. SELECT захватывает столбцы (затем дедуплицирует строки, если вызывается DISTINCT).
  6. UNION объединяет выбранные данные в набор результатов.
  7. ORDER BY сортирует результаты.

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

Некоторые рекомендации по запросам (не правила)

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

Помогите людям (включая себя через три месяца), добавив комментарии, поясняющие различные части кода. Самое важное, что здесь нужно уловить, — это «почему». Например, очевидно, что приведенный ниже код отфильтровывает заказы с ID больше 10, но это происходит потому, что первые 10 заказов используются для тестирования.

 ВЫБЕРИТЕ
  я бы,
  товар
ОТ
  заказы
-- отфильтровать тестовые заказы
КУДА
  идентификатор заказа> 10
 

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

Лучшие практики SQL

для FROM

Соединение таблиц с помощью ключевого слова ON

Хотя можно «объединить» две таблицы с помощью предложения WHERE (то есть выполнить неявное соединение, например, SELECT * FROM a,b WHERE a.foo = b.bar ), вместо этого следует предпочесть явное ПРИСОЕДИНЕНИЕ:

 ВЫБЕРИТЕ
  о.ид,
  о.общий,
  стр. поставщик
ОТ
  заказы КАК О
  ПРИСОЕДИНЯЙТЕСЬ к продуктам КАК p ON o. product_id = p.id
 

В основном для удобства чтения, так как JOIN + 9Синтаксис 0014 ON отличает объединения от предложений WHERE , предназначенных для фильтрации результатов.

Псевдоним нескольких таблиц

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

Избегать

 ВЫБЕРИТЕ
  заглавие,
  фамилия,
  имя
ИЗ художественных_книг
  ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ
  ПО fiction_books.author_id = fiction_authors.id
 

Предпочтительно

 ВЫБЕРИТЕ
  книги.название,
  авторы.фамилия,
  авторы.first_name
ИЗ художественных_книг КАК книги
  ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к авторам фикции КАК авторам
  ПО books. author_id = author.id
 

Это тривиальный пример, но когда количество таблиц и столбцов в вашем запросе увеличивается, вашим читателям не нужно будет отслеживать, какой столбец находится в какой таблице. Это и ваши запросы могут сломаться, если вы присоединитесь к таблице с неоднозначным именем столбца (например, обе таблицы включают поле с именем Создан_В .

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

Лучшие практики SQL для WHERE

Фильтр с WHERE перед HAVING

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

Избегайте функций для столбцов в предложениях WHERE

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

И помните, оператор конкатенации || — это тоже функция, так что не пытайтесь объединить строки для фильтрации нескольких столбцов. Вместо этого предпочесть несколько условий:

Избегать

 ВЫБЕРИТЕ героя, помощника
ОТ супергероев
ГДЕ герой || помощник = 'БэтменРобин'
 

Предпочтительно

 ВЫБЕРИТЕ героя, помощника
ОТ супергероев
КУДА
  герой = «Бэтмен»
  И
  помощник = 'Робин'
 
Предпочтение

= от до НРАВИТСЯ

Это не всегда так. Приятно знать, что LIKE сравнивает символы и может сочетаться с операторами подстановки, такими как % , тогда как оператор = сравнивает строки и числа для точного совпадения. = могут использовать индексированные столбцы. Это не относится ко всем базам данных, поскольку LIKE может использовать индексы (если они существуют для поля), если вы избегаете префикса поискового запроса с оператором подстановки, % . Что подводит нас к следующему пункту:

.

Избегайте использования подстановочных знаков в операторах WHERE

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

Избегать

 SELECT column FROM table WHERE col LIKE "%wizar%"
 

Предпочтительно

 SELECT столбец FROM table WHERE col LIKE "wizar%"
 
Предпочитает СУЩЕСТВУЕТ вместо IN

Если вам просто нужно проверить существование значения в таблице, используйте EXISTS 9От 0015 до IN , поскольку процесс EXISTS завершается, как только он находит искомое значение, тогда как IN просматривает всю таблицу. IN следует использовать для поиска значений в списках.

Аналогично, НЕ СУЩЕСТВУЕТ лучше предпочесть НЕ СУЩЕСТВУЕТ .

Рекомендации по SQL для GROUP BY

Упорядочить несколько групп по убыванию кардинальности

Где возможно, СГРУППИРОВАТЬ НА столбцов в порядке убывания кардинальности. То есть сначала группируйте по столбцам с более уникальными значениями (например, идентификаторы или номера телефонов), а затем группируйте по столбцам с меньшим количеством различных значений (например, штат или пол).

Лучшие практики SQL для HAVING

Используйте HAVING только для агрегатов фильтрации

А до HAVING отфильтруйте значения с помощью предложения WHERE перед агрегированием и группировкой этих значений.

Рекомендации по SQL для SELECT

ВЫБЕРИТЕ столбцы, а не звезды

Укажите столбцы, которые вы хотите включить в результаты (хотя можно использовать * при первом просмотре таблиц — только не забудьте LIMIT ваши результаты).

Рекомендации по SQL для UNION

Предпочесть UNION All вместо UNION

Если дубликаты не являются проблемой, UNION ALL не будет отбрасывать их, а поскольку UNION ALL не занимается удалением дубликатов, запрос будет более эффективным.

Рекомендации по SQL для ORDER BY

По возможности избегайте сортировки, особенно в подзапросах

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

Рекомендации по SQL для INDEX

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

Какие столбцы вы должны индексировать, обычно зависит от столбцов, по которым вы фильтруете (т. е. какие столбцы обычно попадают в ваши предложения WHERE ). Если вы обнаружите, что всегда фильтруете по общему набору столбцов, вам следует подумать об индексации этих столбцов.

Добавление индексов

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

 CREATE INDEX product_title_index В продуктах (название)
 

Доступны различные типы индексов, наиболее распространенный тип индекса использует B-дерево для ускорения поиска. Ознакомьтесь с нашей статьей о том, как сделать информационные панели быстрее, и ознакомьтесь с документацией по вашей базе данных, чтобы узнать, как создать индекс.

Использовать частичные индексы

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

Использовать составные индексы

Для столбцов, которые обычно используются вместе в запросах (например, last_name, first_name), рассмотрите возможность создания составного индекса. Синтаксис аналогичен созданию одного индекса. Например:

 CREATE INDEX full_name_index ON клиентов (last_name, first_name)
 

ОБЪЯСНЕНИЕ

Поиск узких мест

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

Вот пример запроса с использованием образца базы данных dvdrental , доступного для PostgreSQL.

 EXPLAIN ANALYZE SELECT название, выпуск_год
ИЗ фильма
ГДЕ выпуск_год > 2000;
 

И вывод:

 Seq Scan на пленке (стоимость=0,00..66,50 рядов=1000 ширина=19) (фактическое время=0,008..0,311 рядов=1000 петель=1)
   Фильтр: ((release_year)::integer > 2000)
 Время планирования: 0,062 мс
 Время выполнения: 0,416 мс
 

Вы увидите миллисекунды, необходимые для планирования времени, времени выполнения, а также стоимости, строк, ширины, времени, циклов, использования памяти и т. д. Чтение этих анализов — своего рода искусство, но вы можете использовать их для выявления проблемных областей в ваших запросах (таких как вложенные циклы или столбцы, индексация которых может быть полезна) по мере их уточнения.

Вот документация PostreSQL по использованию EXPLAIN.

С

Организуйте свои запросы с помощью Common Table Expressions (CTE)

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

 С product_orders КАК (
  ВЫБЕРИТЕ o.created_at AS order_date,
          p.title КАК product_title,
          (o.subtotal / o.quantity) КАК доход_на_единицу
   ОТ заказов КАК o
   LEFT JOIN products AS p ON o.product_id = p.id
   -- Отфильтровывать заказы, размещенные службой поддержки клиентов, для взимания платы с клиентов.
   ГДЕ о.количество > 0
)
ВЫБЕРИТЕ product_title КАК продукт,
       AVG(доход_на_единицу) AS avg_revenue_per_unit,
       MAX(доход_на_единицу) AS max_revenue_per_unit,
       MIN(доход_на_единицу) AS min_revenue_per_unit
ОТ product_orders
ГДЕ order_date МЕЖДУ 2019 г.-01-01" И "31-12-2019"
СГРУППИРОВАТЬ ПО продукту
ЗАКАЗАТЬ ПО avg_revenue_per_unit DESC
 

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

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

 С исходными_данными КАК (
  ВЫБЕРИТЕ события->'данные'->>'имя' КАК событие_имя,
    CAST(события->'данные'->>'ts' AS timestamp) AS event_timestamp
    CAST(события->'данные'->>'cust_id' AS int) AS customer_id
  ОТ пользовательской_активности
)
ВЫБЕРИТЕ имя_события,
       event_timestamp,
       Пользовательский ИД
ИЗ source_data
 

Кроме того, вы можете сохранить подзапрос как фрагмент SQL (рис. 3 — обратите внимание на круглые скобки вокруг фрагмента), чтобы легко повторно использовать этот код в других запросах.

Рис. 3 . Сохранение подзапроса во фрагменте и его использование в предложении FROM.

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

С метабазой вам даже не нужно использовать SQL

SQL потрясающий. Но то же самое можно сказать и о построителе запросов Metabase и редакторе блокнотов. Вы можете составлять запросы с помощью графического интерфейса Metabase для объединения таблиц, фильтрации и суммирования данных, создания настраиваемых столбцов и многого другого. А с помощью пользовательских выражений вы можете справиться с подавляющим большинством аналитических вариантов использования, даже не прибегая к SQL. Вопросы, составленные с использованием Notebook Editor также имеет преимущества автоматической детализации, которая позволяет зрителям ваших диаграмм щелкать и исследовать данные, функция, недоступная для вопросов, написанных на SQL.

Вопиющие ошибки или упущения?

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