10 моих SQL best practice05.01.2024 10:45

Привет, Хабр)

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

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

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

Готовы? Поехали)

Если хочется больше контента не только по SQL, но и по ML, Data Science, Python — добро пожаловать в тг

А вот тут отдельно разбор заданий с DS

EXISTS/NOT EXISTS вместо IN/NOT IN

Причина банальна: EXISTS завершается, как только находит нужное значение, в то время как IN обязательно проходит всю таблицу. Операторы IN/NOT IN плохо оптимизированы в целом.

Уже на средненькой БД разница в скорости становится ощутимой.

В тему: лучше использовать = вместо LIKE, если мы ищем точное совпадение. Всё из-за того, что = использует столбцы на основе индекса, а это быстрее чем LIKE.

По той же причине там, где дубликаты не проблема, лучше использовать UNION All, а не UNION.

Аккуратнее с SELECT *

«Ну и запросы у вас», — сказала база данных и повисла.

Всё очевидно: не стоит раскидываться SELECT * там, где можно явно указать нужные столбцы.

  • лучше так: 

SELECT e.name
FROM employee e

  • чем так: 

SELECT *
FROM employee e

Объединение с помощью JOIN + ON

Не стоит использовать WHERE для неявного объединения таблиц, например так: 

SELECT * 
FROM a, b 
WHERE a.foo = b.bar

Вместо этого лучше явно объединить таблицы с помощью JOIN: 

SELECT
  , o.id
  , o.total
    p.vendor
FROM
  orders AS o
  JOIN products AS p ON o.product_id = p.id

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

Фильтрации сразу в запросе

Не стоит сперва вызывать функцию, а потом накидывать фильтр. Лучше уж передать фильтр в качестве параметра

  • не очень: 

SELECT t.id 
FROM dbo.fn_func_table () AS t 
WHERE (t.IsActive = 1)

  • лучше: 

SELECT t.ID 
FROM dbo.fn_func_table (1) AS t

SELECT DISTINCT вместо SELECT + GROUP BY

Мелочь, но использование SELECT DISTINCT яснее показывает смысл запроса (имхо):

  • лучше так: 

    SELECT DISTINCT
      customer_id
    , date_trunc('day', created_at) AS purchase_date
FROM orders

  • вместо этого: 

    SELECT
      customer_id
    , date_trunc('day', created_at) AS purchase_date
FROM orders
GROUP BY 1, 2

При этом с DISTINCT лучше не злоупотреблять

(кстати, кто тоже ставит запятые слева? Кмк, это облегчает поиск пропущенных запятых. Или уродско?)

Аккуратнее с подстановочными операторами

Хмм, какой запрос будет выполняться дольше: 

SELECT column 
FROM table 
WHERE col LIKE "man%"

  • или этот: 

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

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

Одинарные кавычки для строк

Да-да, какой-нибудь BigQuery разрешает двойные/тройные кавычки для строк, но для других SQL-диалектов «строки» — это идентификаторы, а '''строки''' интерпретируются ещё по-другому.

Поэтому лучше строки всегда писать в одинарных кавычках, ради всеобщего блага.

  • давайте так: 

SELECT *
FROM customers
WHERE email LIKE '%@domain.com'
SELECT *
FROM customers
WHERE email LIKE "%@domain.com"

Во-втором случае некоторые диалекты могут начать жаловаться наподобие column "%@domain.com" does not exist

Меньше подзапросов

Не стоит писать больше 1 подзапроса на временную таблицу.

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

Сначала пишем запрос с двумя подзапросами, который вычисляет три средние зарплаты: 

SELECT T1.COUNTRY, 
         AVG(T1.salary) AS AVG_salary_per_country
         T2.AVG_salary_per_city,
         T3.AVG_salary_company

FROM salary_table AS T1
CROSS JOIN 
      SELECT 
         T1.CITY, 
         AVG(T1.salary) AS AVG_salary_per_city,
         T3.AVG_salary_company
      FROM salary_table AS T2
      CROSS JOIN
          SELECT AVG(salary) AS AVG_salary_company
          FROM salary_table
      AS T3
      GROUP BY 1,3
   AS T2
GROUP BY 1,3,4

Временная таблица одна, а подзапросов несколько — это проблема. При помощи WITH и CTE мы можем создать более понятные и переиспользуемые запросы. Ну и мы можем выбирать все необходимые данные в итоговом запросе. Взгляните на это: 

WITH 
SALARY_COUNTRY AS 
    SELECT T1.COUNTRY, 
           AVG(T1.salary) AS AVG_salary_per_country
    FROM salary_table AS T1
GROUP BY 1,

SALARY_CITY AS 
    SELECT T1.CITY, 
           AVG(T1.salary) AS AVG_salary_per_city
    FROM salary_table AS T1
    GROUP BY 1,

SALARY_GLOBAL AS 
    SELECT AVG(salary) AS AVG_salary_company
    FROM salary_table

SELECT 
    T1.country,
    T1.AVG_salary_per_country,
    T2.city, 
    T2.AVG_salary_per_city,
    T3.AVG_salary_company
FROM SALARY_COUNTRY       AS T1
CROSS JOIN SALARY_CITY    AS T2
CROSS JOIN SALARY_GLOBAL  AS T3

Порядок OR, AND

Эти операторы умеют разные приоритеты. Это вытекает уже из того, что OR — логическое сложение, а AND — логическое умножение.

Как можно догадаться, вот этот запрос вернёт не совсем то, что нужно: 

SELECT CustomerId
FROM Customer
WHERE FirstName = 'AndreyEx' OR LastName = 'Destroyer' AND CustomerId > 0

И нужно так: 

SELECT CustomerId
FROM Customer
WHERE (FirstName = 'AndreyEx' OR LastName = 'Destroyer') AND CustomerId > 0

Давать такие советы даже странно, но мне реально попадались такие кейсы.

Только не NULL

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

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

Вместо вывода

Вот пару ссылок, где люди делятся своими предложениями по написанию лучших SQL-запросов:

Ну и немножко нетленной классики по SQL и по БД в целом (большая часть легко гуглится):

  • «PostgreSQL. Основы языка SQL» — Евгений Моргунов

  • «Рефакторинг SQL приложений» — Стефан Фаро

  • «Оптимизация запросов PostgreSQL» — Домбровская Г.Р, Новиков Б.А, Бейликова А.

  • «SQL и реляционная теория. Как грамотно писать код на SQL» — К. Дж. Дейт

  • «Реляционные базы данных в примерах» — Святослав Куликов

  • «SQL (Quick Start)» — Крис Фиайли

Как бонус — отличная напоминалка по оконным функциям отсюда, сам часто пользуюсь:

7249b0f88931ec739dca77b5f1defcc7.png

Если хочется больше контента не только по SQL, но и по ML, Data Science, Python — добро пожаловать в тг

А вот тут отдельно разбор заданий с DS

© Habrahabr.ru