Sql запрос в sql запросе: Вложенные запросы (SQL Server) — SQL Server

SQL Server. Оптимизация SQL запросов, план запроса в SQL Server. Советы по оптимизация запросов SQL

Здесь приведем некоторые советы по оптимизации запросов SQL Server при анализе плана выполнения запроса. 

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

2. Общая мудрость гласит, что поиск (seek) — это хорошо для производительности, поскольку он представляет собой прямой доступ SQL Server к требуемым строкам данных, в то время как сканирование (scan) — это плохо, поскольку он предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке.

3. RID Lookup (поиск идентификатора записи) легко пофиксить (в плане запроса) — если вы видите этот оператор, это означает, что у вас отсутствует кластеризованный индекс на таблице. По крайней мере, вы должны добавить кластеризованный индекс. И вы тут же получите некоторый рост производительности для большинства, если не для всех, ваших запросов.
SQL Server использует Key Lookup (поиск ключей в записи), когда он знает, что с большей эффективностью может использовать некластеризованный индекс, а затем перейти к кластерзованному индексу для поиска оставшихся значения строк, которые отсутствуют в некластеризованном индексе.

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

5. Index spools. Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb. SQL Server часто использует спул для обработки сложных запросов, преобразуя данные в рабочую таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск в tempdb.

6. Соединения в плане запросов. Merge join оптимально. Nested loops нормально для небольших данных. Hash match — присмотреться (возможно надо упорядочить сначала набор или индекс иметь на поля упорядочивания, по которым идет связь).

7. Если я вижу по плану запроса, что данные напрямую поступают из некластеризованных индексов, то доволен, что данные извлекаются эффективно, поскольку возвращается только ограниченное число столбцов, и надеюсь, что они вернутся в предпочтительном порядке (оттого, что я создаю свои индексы довольно узкими).
Если все, что я вижу, это Index scan (сканирование индекса), тогда все прекрасно, но следует убедиться, что:
a. Я не вижу table scan (сканирование таблицы) — на худой конец, это может быть сканирование кластерного индекса.
b. Я не использую без необходимости SELECT * в своем запросе — зачем считывать все эти лишние данные в память или мешать использованию более узкого индекса, если в этом нет необходимости.
c. SQL Server не сканирует весь индекс, чтобы вернуть только ограниченное подмножество строк.
Затем я ищу в плане любые другие операторы, обычно вызывающие проблемы: сортировки, спулы, хэш-соединения и т.д.
Наконец, я быстро просматриваю план в поисках желтых восклицательных знаков на любых операторах в плане. Эти символы отмечают действия, о которых SQL Server считает, что должен нас предупредить.

8. Стараемся не использовать поля allow null. Избегаем по возможности left join

9. Памятка по плану запроса — https://yadi.sk/i/spoZqlZSJ75eKQ

Источник: https://sql-ex.ru/blogs/?/Kak_Ja_ispolzuju_plany_vypolneniJa_v_SQL_Server_dlJa_resheniJa_problem.html

исключение набора строк из SQL запроса

EXCEPT — исключение набора строк из SQL запроса

  • 1. Введение

    • 1. 1 Синтаксис SQL запроса

    • 1.2 Получение данных из таблицы

    • 1.3 Вызов функции

    • 1.4 Конкатенация строк

    • 1.5 Арифметические операции

    • 1.6 Исключение дубликатов

  • 2. Отсечение строк и сортировка

    • 2.1 Выражение WHERE

    • 2.2 Логические операторы

    • 2.3 Порядок условий

    • 2.4 Операции сравнения

    • 2.5 BETWEEN

    • 2.6 IN

    • 2.7 Поиск по шаблону

    • 2.8 Обработка NULL значений

    • 2.9 Сортировка

    • 2.10 Ограничение количества строк LIMIT

    • 2.11 Пропуск первых строк результата

  • 3. Соединения

    • 3.1 Соединение двух таблиц

    • 3.2 Псевдонимы таблиц

    • 3.3 Добавляем WHERE

    • 3.4 Несколько условий соединения

    • 3.5 Использование таблицы несколько раз

    • 3.6 Типы соединения

    • 3. 7 RIGHT JOIN

    • 3.8 FULL JOIN

    • 3.9 Декартово произведение

    • 3.10 Синтаксис через WHERE

  • 4. Агрегатные функции

    • 4.1 Агрегатные функции

    • 4.2 NULL значения в агрегатных функциях

    • 4.3 Количество уникальных значений

    • 4.4 Отсутствие строк

    • 4.5 GROUP BY

    • 4.6 Дополнительные столбцы в списке выборки с GROUP BY

    • 4.7 GROUP BY и WHERE

    • 4.8 GROUP BY по нескольким выражениям

    • 4.9 NULL значения в GROUP BY

    • 4.10 HAVING

    • 4.11 ROLLUP

    • 4.12 CUBE

    • 4.13 GROUPING SETS

  • 5. Операции над множествами

    • 5.1 Доступные операции над множествами

    • 5.2 Из какого запроса строка?

    • 5.3 Пересечение строк

    • 5.4 Исключение строк

    • 5.5 Дубликаты строк

    • 5.6 Совпадение типов данных столбцов

    • 5. 7 Сортировка

    • 5.8 Несколько операций

  • 6. Подзапросы

    • 6.1 Подзапрос одиночной строки

    • 6.2 Коррелированный подзапрос

    • 6.3 Подзапрос вернул более одной строки

    • 6.4 Подзапрос не вернул строк

    • 6.5 Попадание в список значений

    • 6.6 Отсутствие в списке значений

    • 6.7 NULL значения в NOT IN

    • 6.8 Проверка существования строки

    • 6.9 Проверка отсутствия строки

  • 7. Строковые функции

    • 7.1 CONCAT — конкатенация строк

    • 7.2 Преобразование регистра букв

    • 7.3 LENGTH — определение длины строки

    • 7.4 Извлечение подстроки

    • 7.5 POSITION — поиск подстроки

    • 7.6 Дополнение до определенной длины

    • 7.7 TRIM — удаление символов с начала и конца строки

    • 7.8 REPLACE — замена подстроки

    • 7.9 TRANSLATE — замена набора символов

  • 8. !)

  • 8.5 Получение числа из строки

  • 8.6 ROUND — округление числа

  • 8.7 TRUNC — усечение числа

  • 8.8 CEIL — следующее целое число

  • 8.9 FLOOR — предыдущее целое число

  • 8.10 GREATEST — определение большего числа

  • 8.11 LEAST — определение меньшего числа

  • 8.12 ABS — модуль числа

  • 8.13 TO_CHAR — форматирование числа

  • 9. Рекурсивные подзапросы

    • 9.1 Подзапрос во фразе FROM

    • 9.2 Введение в WITH

    • 9.3 Несколько подзапросов в WITH

    • 9.4 Простейший рекурсивный запрос

    • 9.5 Рекурсивный запрос посложнее

    • 9.6 Строим иерархию объектов

    • 9.7 Путь до элемента

    • 9.8 Сортировка (плохая)

    • 9.9 Сортировка (надежная)

    • 9.10 Форматирование иерархии

    • 9.11 Нумерация вложенных списков

    • 9.12 Листовые строки CONNECT_BY_ISLEAF

  • 10. Оконные функции

    • 10.1 Получение номера строки

    • 10.2 Номер строки в рамках группы

    • 10.3 Составляем рейтинг — RANK

    • 10.4 Несколько человек на место — DENSE_RANK

    • 10.5 Разделение на группы — NTILE

    • 10.6 Агрегатные оконные функции

    • 10.7 Обработка NULL значений

    • 10.8 Нарастающий итог SUM + ORDER BY

    • 10.9 Неуникальные значения в нарастающем итоге SUM + ORDER BY

    • 10.10 Собираем строки через разделитель — STRING_AGG

    • 10.11 WITHIN GROUP

    • Оглавление
    • Операции над множествами

    Задача исключения набора строк из выборки является типовой для разработчика.

    Обычно применяют:

    • NOT IN;
    • NOT EXISTS;
    • внешнее соединение с проверкой на NULL значения столбца, имеющим ограничение NOT NULL;
    • EXCEPT (MINUS в СУБД Oracle и MySQL).

    Рассмотрим оператор исключения строк EXCEPT. После применения EXCEPT в результате останутся строки из первого запроса, которых нет во втором запросе.

    Пример:

    SELECT *
      FROM table1
    
    value
    1
    3
    5
    SELECT *
      FROM table2
    
    value
    2
    3
    4
    SELECT value
      FROM table1
    EXCEPT
    SELECT value
      FROM table2
    
    # value
    1 1
    2 5
    • Практическое задание

      Исключение строк

    5.3 Пересечение строк

    5.5 Дубликаты строк

    Сделано ребятами из Сибири

    © 2023 LearnDB

    SQLBolt — Learn SQL — SQL Урок 12: Порядок выполнения запроса

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

    Полный запрос SELECT

    SELECT DISTINCT column, AGG_FUNC( column_or_expression ), …
    ОТ моей таблицы
    ПРИСОЕДИНЯЙТЕСЬ к другой_таблице
    ON mytable.column = другая_таблица.column
    ГДЕ ограничение_выражение
    СГРУППИРОВАТЬ ПО столбцу
    НАЛИЧИЕ выражение_ограничения
    ORDER BY столбец ASC/DESC
    ПРЕДЕЛ счет СМЕЩЕНИЕ счет ;

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

    1.

    FROM и JOIN s

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

    2.

    ГДЕ

    Как только мы получим полный рабочий набор данных, первый проход ГДЕ ограничения применяются к
    отдельные строки и строки, не удовлетворяющие этому ограничению, отбрасываются. Каждое из ограничений
    может обращаться только к столбцам непосредственно из таблиц, запрошенных в предложении FROM . Псевдонимы в
    SELECT часть запроса недоступна в большинстве баз данных, поскольку они могут включать
    выражения, зависящие от частей запроса, которые еще не выполнены.

    3.

    ГРУППИРОВАТЬ ПО

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

    4.

    HAVING

    Если в запросе есть предложение GROUP BY , то ограничения в HAVING 9Затем применяется пункт 0020.
    к сгруппированным строкам, отбросить сгруппированные строки, которые не удовлетворяют ограничению. Как ГДЕ
    пункт, псевдонимы также недоступны с этого шага в большинстве баз данных.

    5.

    SELECT

    Все выражения в части SELECT запроса вычисляются окончательно.

    6.

    DISTINCT

    Из оставшихся строк строки с повторяющимися значениями в столбце, отмеченном как DISTINCT , будут отброшены.

    7.

    ORDER BY

    Если порядок указан в предложении ORDER BY , строки затем сортируются по указанным данным в
    либо по возрастанию, либо по убыванию. Поскольку все выражения в части SELECT запроса
    были вычислены, вы можете ссылаться на псевдонимы в этом предложении.

    8.

    LIMIT / OFFSET

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

    Заключение

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

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

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

    Категории

    SQLGuides



    и удобочитаемость.

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

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

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

    Вопрос интервью SQL

    Премиум против Freemium

    Ссылка на вопрос: https://platform.stratascratch.com/coding/10300-premium-vs-freemium

    Мы будем использовать этот вопрос на собеседовании по SQL в качестве примера, взятого из технических собеседований в Microsoft и озаглавленного «Premium vs Freemium». Задача состоит в том, чтобы найти общее количество загрузок для платных и неплатящих пользователей по дате и включить только те записи, где у неплатящих клиентов больше загрузок, чем у платных. Более того, этот вопрос связан с набором данных, разделенным на три таблицы, которые необходимо объединить.

    Исходное решение

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

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

     ВЫБЕРИТЕ дату, "Неоплачиваемый",
                 оплата
    ИЗ
      (ВЫБЕРИТЕ стр.дату,
              p.sum AS платит,
              n.nonpaying AS "Неплатежеспособный"
       ИЗ
         (ВЫБЕРИТЕ дату, сумму(загрузки)
          ОТ ms_user_dimension a
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension b ON a.acc_id = b.acc_id
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_download_facts c ON a.user_id=c.user_id
          ГДЕ pay_customer = 'да'
          СГРУППИРОВАТЬ ПО дате
          ЗАКАЗАТЬ ПО дате) стр
       ПРИСОЕДИНИТЬСЯ
         (ВЫБЕРИТЕ дату, сумму(загрузки) КАК БЕСПЛАТНО
          ОТ ms_user_dimension a
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension b ON a.acc_id = b.acc_id
          ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_download_facts c ON a.user_id=c.user_id
          ГДЕ pay_customer = 'нет'
          СГРУППИРОВАТЬ ПО дате
          ORDER BY date) n ON p.date = n.date
       ЗАКАЗАТЬ ПО п.дата) с
    СГРУППИРОВАТЬ ПО дате, "Неоплачиваемый",
                   оплата
    ИМЕЕТ ("NonPaying" - платит) >0
    ЗАКАЗАТЬ ПО дате ASC
     

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

    Передовой опыт написания SQL-запросов: как структурировать код

    1. Удалите несколько вложенных запросов

    Даже не понимая, что именно делает код, мы видим, что он имеет несколько вложенных запросов. Есть основной запрос, в котором выбираются три столбца, затем в его предложении FROM есть еще один длинный запрос, называемый внутренним запросом. У него есть псевдоним «s». Но тогда сам этот внутренний запрос «s» также имеет два дополнительных и почти идентичных внутренних запроса, «p» и «n», которые объединяются вместе с помощью оператора JOIN. Хотя иметь один внешний запрос и один внутренний запрос абсолютно нормально, более двух запросов, вложенных друг в друга, считаются не очень читабельными, и их следует избегать.

    Один из подходов к избавлению от такого большого количества вложенных запросов состоит в том, чтобы определить некоторые или все из них в форме общих табличных выражений или CTE — конструкций, которые используют ключевое слово WITH и позволяют повторно использовать один запрос несколько раз. Итак, пусть это будет наш первый шаг — мы можем превратить все три вложенных запроса «s», «p» и «n» в CTE.

     С р АС
      (ВЫБЕРИТЕ дату, сумму(загрузки)
       ОТ ms_user_dimension a
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension b ON a.acc_id = b.acc_id
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_download_facts c ON a.user_id=c.user_id
       ГДЕ pay_customer = 'да'
       СГРУППИРОВАТЬ ПО дате
       ЗАКАЗАТЬ ПО дате),
         н КАК
      (ВЫБЕРИТЕ дату, сумму(загрузки) КАК БЕСПЛАТНО
       ОТ ms_user_dimension a
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension b ON a. acc_id = b.acc_id
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_download_facts c ON a.user_id=c.user_id
       ГДЕ pay_customer = 'нет'
       СГРУППИРОВАТЬ ПО дате
       ЗАКАЗАТЬ ПО дате),
         с АС
      (ВЫБЕРИТЕ стр.дату,
              p.sum AS платит,
              n.nonpaying AS "Неплатежеспособный"
       С р
       ПРИСОЕДИНЯЙТЕСЬ n ON p.date = n.date
       ЗАКАЗАТЬ ПО п.дате)
    ВЫБЕРИТЕ дату, "Неоплачиваемый",
                 оплата
    ОТ с
    СГРУППИРОВАТЬ ПО дате, "Неоплачиваемый",
                   оплата
    ИМЕЕТ ("NonPaying" - платит) >0
    ЗАКАЗАТЬ ПО дате ASC
     

    Теперь этот запрос уже стал немного легче читать. Даже не понимая точно, что происходит, мы можем видеть, что есть два очень похожих шага, 'p' и 'n', которые должны быть выполнены сначала, чтобы активировать 's', а затем результат 's' можно использовать в основной запрос. Но что же это за «р», «н», «с» и другие псевдонимы? Это подводит нас ко второму пункту.

    2. Обеспечить согласованность псевдонимов

    Псевдонимы в SQL могут быть назначены столбцам, запросам и таблицам для изменения их первоначальных имен. Их необходимо использовать при объединении таблиц с одинаковыми именами столбцов, чтобы избежать двусмысленности в именах столбцов. Псевдонимы также полезны для облегчения понимания кода другими и для замены имен столбцов по умолчанию при использовании аналитических функций (например, СУММ() или СЧЁТ()).

    Существует также несколько неписаных правил относительно псевдонимов, которым следует следовать, поскольку неправильно используемый псевдоним может скорее запутать, чем помочь. Начнем с псевдонимов таблицы и запроса. Хорошо, когда это немного больше, чем просто одна буква, и они позволяют нам понять, что находится в таблице или что выдает запрос. В нашем случае первый CTE, который в настоящее время называется «p», используется для подсчета количества загрузок, сделанных платными клиентами, поэтому более информативным названием будет, например, «платный». Стоит отметить, что псевдонимы, хотя и информативные, не должны быть слишком длинными, например, «paying_customers» может быть немного длинным. Затем второй CTE, «n», такой же, но для неплатящих клиентов, поэтому, следуя схеме, его можно назвать «бесплатным».

    Наконец, CTE ‘s’ объединяет два значения: количество загрузок платных и неплатящих клиентов, но пока не фильтрует их, потому что это происходит в основном запросе. Таким образом, его имя может быть, например, «all_downloads». Теперь обратите внимание, что это еще не все таблицы, которым присвоены псевдонимы. Это связано с тем, что в первых двух CTE мы объединяем три таблицы друг с другом, и, поскольку они имеют общие имена столбцов, им нужно дать псевдонимы. В настоящее время это просто «a», «b» и «c», но более информативными названиями будут «users», «accounts» и «downlds» — аббревиатура здесь, потому что в этой таблице уже есть столбец «downloads».

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

     С оплатой AS
      (ВЫБРАТЬ загрузки.дата, сумма(загрузки.загрузки)
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'да'
       СГРУППИРОВАТЬ ПО downlds.date
       ЗАКАЗАТЬ ПО downlds.date),
         неплатежный AS
      (ВЫБЕРИТЕ загрузки.дата, сумма(загрузки.загрузки) КАК неоплачиваемые
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'нет'
       СГРУППИРОВАТЬ ПО downlds.date
       ЗАКАЗАТЬ ПО downlds.date),
         all_downloads КАК
      (ВЫБЕРИТЕ дату оплаты,
              pay. sum КАК платит,
              nonpaying.nonpaying AS "Неплатежеспособный"
       ОТ оплаты
       ПРИСОЕДИНЯЙТЕСЬ к nonpaying ON pay.date = nonpaying.date
       ЗАКАЗАТЬ ПО ОПЛАТЕ.ДАТА)
    ВЫБЕРИТЕ дату, "Неоплачиваемый",
                 оплата
    ИЗ all_downloads
    СГРУППИРОВАТЬ ПО дате, "Неоплачиваемый",
                   оплата
    ИМЕЕТ ("NonPaying" - платит) >0
    ЗАКАЗАТЬ ПО дате ASC
     

    Это были псевдонимы таблицы и запроса, теперь давайте посмотрим на псевдонимы, присвоенные столбцам. Во-первых, дать псевдонимы результатам аналитических функций. Взгляните на первый запрос. Есть функция SUM(), но к ней не добавляется псевдоним, хотя позже этот столбец используется повторно. Вот почему в CTE all_downloads нам нужно написать pay.sum, чтобы выбрать его. Добавим псевдоним, например n_paying.

    Еще одна вещь — поддерживать согласованность псевдонимов столбцов в именах, а также избегать конфликтов с другими псевдонимами. Как и в CTE nonpaying, есть функция SUM(), которая правильно назначается как псевдоним, но этот псевдоним совпадает с псевдонимом CTE, что может сбивать с толку. Давайте придерживаться того же соглашения об именах, что и раньше, и изменим этот псевдоним на n_nonpaying.

    Теперь в CTE all_downloads происходит много всего. Во-первых, псевдоним «paying», назначенный второму столбцу, совпадает с псевдонимом одного из CTE. И сразу после этого псевдоним третьего столбца — «NonPaying» в кавычках. Хотя SQL позволяет нам назначать такие псевдонимы, использовать такие псевдонимы опасно, потому что каждый раз, когда мы хотим использовать его повторно, нам нужно снова использовать кавычки и сопоставлять все заглавные и строчные буквы в псевдониме. Мы могли бы изменить эти два псевдонима на что-то другое и без кавычек. Но на самом деле даже не обязательно использовать эти алиасы, ведь аналитических функций здесь нет, поэтому имена столбцов из предыдущих, ‘n_paying’ и n_nonpaying’ остаются прежними и на них можно ссылаться в основном запросе, не вызывая проблем.

     С оплатой AS
      (ВЫБРАТЬ downlds.date, sum(downlds.downloads) AS n_paying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users. acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'да'
       СГРУППИРОВАТЬ ПО downlds.date
       ЗАКАЗАТЬ ПО downlds.date),
         неплатежный AS
      (SELECTdownlds.date, sum(downlds.downloads) AS n_nonpaying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'нет'
       СГРУППИРОВАТЬ ПО downlds.date
       ЗАКАЗАТЬ ПО downlds.date),
         all_downloads КАК
      (ВЫБЕРИТЕ дату оплаты,
              оплата.n_paying,
              неоплачиваемый.n_неоплачиваемый
       ОТ оплаты
       ПРИСОЕДИНЯЙТЕСЬ к nonpaying ON pay.date = nonpaying.date
       ЗАКАЗАТЬ ПО ОПЛАТЕ.ДАТА)
    ВЫБЕРИТЕ дату, n_nonpaying,
                 n_paying
    ИЗ all_downloads
    СГРУППИРОВАТЬ ПО дате, n_nonpaying,
                   n_paying
    ИМЕЕТ (n_nonpaying - n_paying) >0
    ЗАКАЗАТЬ ПО дате ASC
     

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

    3. Удалите ненужные предложения ORDER BY

    Теперь, когда мы позаботились о псевдонимах, давайте начнем сокращать объем кода в нашем решении. Первая вещь довольно второстепенна, но все же способствует удобочитаемости запроса. Речь идет о предложениях ORDER BY. Они, очевидно, используются для сортировки данных в таблице и часто полезны или даже необходимы. В конце концов, иногда необходимо использовать предложение ORDER BY в сочетании с оконной функцией или при выборе верхних строк таблицы с помощью ключевого слова LIMIT. Мы также можем захотеть расположить окончательные результаты в определенном порядке на собеседованиях, иногда это даже может быть требованием.

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

     С оплатой AS
      (ВЫБРАТЬ downlds.date, sum(downlds.downloads) AS n_paying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'да'
       СГРУППИРОВАТЬ ПО downlds.date),
         неплатежный AS
      (SELECTdownlds.date, sum(downlds.downloads) AS n_nonpaying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       ГДЕ account.paying_customer = 'нет'
       СГРУППИРОВАТЬ ПО downlds.date),
         all_downloads КАК
      (ВЫБЕРИТЕ дату оплаты,
              оплата.n_paying,
              неоплачиваемый.n_неоплачиваемый
       ОТ оплаты
       ПРИСОЕДИНЯЙТЕСЬ к nonpaying ON pay. date = nonpaying.date)
    ВЫБЕРИТЕ дату, n_nonpaying,
                 n_paying
    ИЗ all_downloads
    СГРУППИРОВАТЬ ПО дате, n_nonpaying,
                   n_paying
    ИМЕЕТ (n_nonpaying - n_paying) >0
    ЗАКАЗАТЬ ПО дате ASC
     

    4. Удаление ненужных подзапросов и CTE

    Далее, есть гораздо более серьезная тема, чем удаление ненужных предложений ORDER BY — удаление ненужных подзапросов и CTE. Мы говорили о них раньше, когда разбивали вложенные запросы на CTE, но тогда мы просто оставили их как есть, не анализируя, действительно ли они нам нужны.

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

    Чтобы уменьшить количество запросов в нашем случае, есть два направления. Одной из возможностей было бы объединить запрос all_downloads с основным запросом. В конце концов, эти два запроса почти идентичны, и если бы мы только добавили фильтр, поэтому выражение, говорящее, что разница между n_nonpaying и n_paying должна быть больше 0, к запросу в all_downloads, дало бы те же результаты. Мы могли бы безопасно избавиться от CTE all_downloads и вместо этого объединить платные и неоплачиваемые CTE в основном запросе. Таким образом, мы можем сократить количество запросов до 3. Но можем ли мы добиться большего?

    Можем, потому что оказывается, что первые два CTE, а именно «платный» и «неплатный», могут быть выполнены внутри CTE «all_downloads». Это потому, что эти первые два CTE почти идентичны, а главное отличие заключается в предложении WHERE. Мы выбираем одни и те же типы данных из одних и тех же таблиц, но в разных условиях. Но в SQL есть другой способ выбора и даже выполнения арифметических операций над данными с использованием разных условий только в одном запросе: нам нужно использовать CASES.

    Мы можем использовать их для преобразования строк «да» и «нет» из столбца «paying_customer» в количество загрузок, а затем суммировать их, чтобы получить общее количество. Это означает, что весь первый CTE можно заменить следующим фрагментом кода:

     sum(CASE
        КОГДА pay_customer = 'да', ТОГДА загрузки
        КОНЕЦ)
     

    Это будет очень похоже на неплатежеспособных клиентов. Обе эти инструкции могут быть выполнены прямо в CTE all_downloads, если мы объединим три таблицы, пользователей, учетные записи и загрузки и включим оператор данных GROUP BY также в это CTE.

     С all_downloads КАК
      (ВЫБЕРИТЕ дату загрузки,
              сумма(СЛУЧАЙ
                    КОГДА account.paying_customer = 'yes', ТО downlds.downloads
                КОНЕЦ) КАК n_paying,
              сумма(СЛУЧАЙ
                    КОГДА account.paying_customer = 'no', ТО downlds.downloads
                КОНЕЦ) AS n_nonpaying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users. acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       СГРУППИРОВАТЬ ПО downlds.date)
    ВЫБЕРИТЕ дату, n_nonpaying,
                 n_paying
    ИЗ all_downloads
    СГРУППИРОВАТЬ ПО дате, n_nonpaying,
                   n_paying
    ИМЕЕТ (n_nonpaying - n_paying) >0
    ЗАКАЗАТЬ ПО дате ASC
     

    С этими изменениями у нас осталось только два запроса, один набор из нескольких JOIN и два случая, когда данные нужно выбирать из таблицы вместо четырех. В то же время мы не можем решить этот вопрос, используя только один запрос, потому что столбцы «n_paying» и «n_nonpaying» должны быть определены в одном запросе, прежде чем использоваться в фильтре в другом запросе.

    5. HAVING vs WHERE

    Говоря о производительности и эффективности, в нашем запросе есть еще одна деталь, которая излишне замедляет его. Эта деталь — предложение HAVING в основном запросе. Но почему именно ИМЕЕТ, а не ГДЕ? Два пункта очень похожи друг на друга и позволяют фильтровать данные на основе некоторых условий. Как и в этом случае, когда разница между значениями n_nonpaying и n_paying должна быть больше 0. Это условие можно определить как с ключевыми словами WHERE, так и с ключевыми словами HAVING, но одно из них гораздо более подходит в этой ситуации. Ключевое отличие состоит в том, что предложение HAVING может включать агрегатные функции, например. СУММ() или СЧЕТ(). Он позволяет создавать условия на основе суммы, среднего, минимального или максимального значения или количества элементов в наборе данных или в разделах, определенных с помощью предложения GROUP BY. Именно по этой причине предложение HAVING всегда должно сопровождаться оператором GROUP BY.

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

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

     С all_downloads КАК
      (ВЫБЕРИТЕ дату загрузки,
              сумма(СЛУЧАЙ
                    КОГДА account.paying_customer = 'yes', ТО downlds.downloads
                КОНЕЦ) КАК n_paying,
              сумма(СЛУЧАЙ
                    КОГДА account.paying_customer = 'no', ТО downlds.downloads
                КОНЕЦ) AS n_nonpaying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       СГРУППИРОВАТЬ ПО downlds. date)
    ВЫБЕРИТЕ дату, n_nonpaying,
                 n_paying
    ИЗ all_downloads
    ГДЕ (n_nonpaying - n_paying) > 0
    ЗАКАЗАТЬ ПО дате ASC
     

    6. Форматирование текста

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

    В SQL нет официальных правил форматирования текста в запросах, но есть некоторые неофициальные рекомендации, которым следуют многие пользователи SQL. Вероятно, наиболее распространенным и известным является то, что все ключевые слова, такие как SELECT, FROM, WHERE, GROUP BY, ORDER BY и т. д., должны быть написаны заглавными буквами. Это также относится к другим встроенным ключевым словам, которые появляются внутри предложений, таких как JOIN, AS, IN, ON или ASC и DESC. Когда дело доходит до названий функций, таких как SUM() или COUNT(), нет единого мнения о том, должны ли они быть написаны полностью заглавными или только строчными буквами, но, вероятно, лучше использовать их также с заглавной буквы, чтобы лучше отличать их от столбцов. имена, которые должны быть написаны с маленькой буквы.

    Еще одно важное правило заключается в том, что, хотя для работы кода не обязательно, каждое предложение, такое как SELECT, FROM, WHERE, GROUP BY и т. д., должно находиться в новой строке. Для дальнейшего повышения удобочитаемости также рекомендуется иметь новую строку для каждого имени столбца в предложении SELECT. Более того, если мы используем подзапросы или CTE, хорошим подходом является использование таблиц, чтобы визуально отличить внутреннюю часть скобки от остальной части запроса.

    Код из нашего примера уже в основном хорошо отформатирован. Но мы по-прежнему можем добавлять новые строки в предложение SELECT основного запроса и использовать заглавные буквы в именах функций SUM().

     С all_downloads КАК
      (ВЫБЕРИТЕ дату загрузки,
              СУММА(СЛУЧАЙ
                    КОГДА account.paying_customer = 'yes', ТО downlds.downloads
                КОНЕЦ) КАК n_paying,
              СУММА(СЛУЧАЙ
                    КОГДА account.paying_customer = 'no', ТО downlds.downloads
                КОНЕЦ) AS n_nonpaying
       ОТ пользователей ms_user_dimension
       ВНУТРЕННЕЕ СОЕДИНЕНИЕ ms_acc_dimension account ON users.acc_id = account.acc_id
       INNER JOIN ms_download_facts downlds ON users.user_id=downlds.user_id
       СГРУППИРОВАТЬ ПО downlds.date)
    ВЫБЕРИТЕ дату,
           n_бесплатно,
           n_paying
    ИЗ all_downloads
    ГДЕ (n_nonpaying - n_paying) > 0
    ЗАКАЗАТЬ ПО дате ASC
     

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

    Заключение

    В заключение еще раз приведем список рекомендаций по написанию SQL-запросов:

    1. Удалить несколько вложенных запросов
    2. Обеспечить согласованность псевдонимов
    3. Удалить ненужные предложения ORDER BY
    4. Удалить ненужные подзапросы
    5. Если возможно, используйте WHERE, а не HAVING несколько вложенных запросов, вы можете превратить их в CTE - хорошее эмпирическое правило состоит в том, что один подзапрос в порядке, но несколько подзапросов или подзапрос, повторяющийся несколько раз, должны стать CTE.

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

    Imacros | Все права защищены © 2021