Лучший способ понять сложные операторы SQL?

Ответ 1

Когда я смотрю на сложную часть кода SQL, это то, что я делаю.

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

Понимание объединений имеет решающее значение для понимания сложного SQL. Для каждого присоединения я спрашиваю себя, почему это здесь? Есть четыре основных причины. Вам нужен столбец для выбора, вам нужно поле для предложения where, вам нужно объединение в качестве моста к третьей таблице, или вам нужно присоединиться к таблице для фильтрации записей (например, для получения сведений о клиенте, у которого есть заказы). но не нуждаясь в деталях заказа, это часто может быть сделано лучше с условием IF EXISTS where). Если это левое или правое соединение (я склонен переписывать, чтобы все было левым соединением, которое упрощает жизнь.), Я думаю, сработает ли внутреннее соединение. Зачем мне нужно левое соединение? Если я не знаю ответа, я буду запускать его в обоих направлениях и увижу разницу в данных. Если есть производные таблицы, я сначала посмотрю на них (запустив только ту часть select, чтобы увидеть, каков результат), чтобы понять, почему она есть. Если есть подзапросы, я постараюсь понять их, и если они будут медленными, вместо этого я попытаюсь преобразовать их в производную таблицу, поскольку они часто выполняются намного быстрее.

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

Я также рассматриваю любые сложные биты из списка выбора далее. Что делает это выражение случая? Почему есть подзапрос? Что делают эти функции? (Я всегда ищу код функции для любой функции, с которой я еще не знаком.) Почему есть отличное? Можно ли избавиться от него с помощью производной таблицы или агрегатной функции и группирования по операторам?

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

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

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

Ответ 2

Это могут быть полезные подсказки.

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

Например:

select ID, Description, Status from ABC where Status = 1 OR Status = 3

лучше писать как:

select 
  ID,
  Description,
  Status
from ABC
where
  Status = 1 OR
  Status = 3

с более сложным запросом вы увидите гораздо большее преимущество.

Ответ 3

Здесь описывается процедура отслеживания запроса.

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

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

Ответ 4

В основном это просто опыт и правильный отступ.

Ответ 5

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

Вот пример

WITH 
ssnInfo AS
(
    SELECT SSN, 
           UPPER(LAST_NAME), 
           UPPER(FIRST_NAME), 
           TAXABLE_INCOME,          
           CHARITABLE_DONATIONS
    FROM IRS_MASTER_FILE
    WHERE STATE = 'MN'                 AND -- limit to in-state
          TAXABLE_INCOME > 250000      AND -- is rich 
          CHARITABLE_DONATIONS > 5000      -- might donate too

),
doltishApplicants AS
(
    SELECT SSN, 
           SAT_SCORE,
           SUBMISSION_DATE
    FROM COLLEGE_ADMISSIONS
    WHERE SAT_SCORE < 100          -- About as smart as a Moose.
),
todaysAdmissions AS
(
    SELECT doltishApplicants.SSN, 
           TRUNC(SUBMISSION_DATE)  SUBMIT_DATE, 
           LAST_NAME, FIRST_NAME, 
           TAXABLE_INCOME
    FROM ssnInfo,
         doltishApplicants
    WHERE ssnInfo.SSN = doltishApplicants.SSN

)
SELECT 'Dear ' || FIRST_NAME || 
       ' your admission to WhatsaMattaU has been accepted.'
FROM todaysAdmissions
WHERE SUBMIT_DATE = TRUNC(SYSDATE)    -- For stuff received today only
;

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

Эта форма также позволяет помещать предложения фильтра с отдельным подзапросом и сохранять предложения объединения для окончательного выбора.

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

Ответ 6

форматирование помогает, но понимание теории множеств и, более того, реляционной теории помогает еще больше.

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

несколько операций (имеющих, существует, с) могут быть сначала неприятными

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

Ответ 7

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

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

Ответ 8

Еще один импорт - это стандартный синтаксис соединения:

SELECT A 
  FROM B
  JOIN C ON B.ID = C.ID
 WHERE C.X = 1

Вместо

SELECT A 
  FROM B
     , C 
 WHERE B.ID = C.ID 
   AND C.X = 1

Ответ 9

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

Белое пространство очень важно. Запрос, который выглядит невероятно сложным, может выглядеть почти упрощенным, когда присутствует правильное свободное пространство.

Что касается объединений... Ну, извините, но я не могу быть здесь очень полезен, потому что мой ответ заключается в том, что лучший способ понять конкретное соединение - понять, как объединены работы в целом. Каждый тип соединения служит очень конкретной цели, и если вы знаете, как они работают, не должно быть существенной разницы от присоединения x к y, x к y к z или x и y к a и b.

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

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

Ответ 10

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

Ответ 11

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

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

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

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

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

Ответ 12

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

(Следующее заимствовано из Inside Microsoft SQL Server 2005: T-SQL Querying, Ицик Бен-Ган.)

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>

1. ОТ: Декартово произведение (перекрестное соединение) выполняется между первыми двумя таблицами в предложении FROM, и в результате создается виртуальная таблица VT1.
2. ON: фильтр ON применяется к VT1. Только строки, для которых TRUE, вставляются в VT2.
3. OUTER (соединение): если указано OUTER JOIN (в отличие от CROSS JOIN или INNER JOIN), строки из сохраненной таблицы или таблиц, для которых не найдено совпадение, добавляются в строки из VT2 в качестве внешних строк, генерируя VT3. Если в предложении FROM появляется более двух таблиц, шаги с 1 по 3 применяются повторно между результатом последнего соединения и следующей таблицей в предложении FROM, пока не будут обработаны все таблицы.
4. ГДЕ: ГДЕ фильтр применяется к VT3. Только строки, для которых TRUE, вставляются в VT4.
5. GROUP BY: строки из VT4 упорядочены в группы на основе списка столбцов, указанного в предложении GROUP BY. VT5 генерируется.
6. КУБ | ROLLUP: супергруппы (группы групп) добавляются в строки из VT5, генерируя VT6.
7. HAVING: фильтр HAVING применяется к VT6. Только группы, для которых TRUE, вставляются в VT7.
8. SELECT: список SELECT обрабатывается, генерируя VT8.
9. DISTINCT: повторяющиеся строки удаляются из VT8. VT9 генерируется.
10. ORDER BY: строки из VT9 сортируются в соответствии со списком столбцов, указанным в предложении ORDER BY. Курсор генерируется (VC10).
11. TOP: указанное количество или процент строк выбирается с начала VC10. Таблица VT11 генерируется и возвращается вызывающей стороне.

Ответ 13

Использование CTE или производных таблиц (по крайней мере, в MS SQL) может быть полезно для форматирования инструкции SQL без разделения ее на отдельные запросы, используя временные таблицы, чтобы "присоединиться" к ним.

Я согласен с другими, что упомянутые запросы довольно просты.

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

Ответ 14

Если вы используете PostgreSQL, просмотр инкапсуляции замечателен для этого.

Ответ 15

Я разбиваю его на более мелкие запросы (поэтому мне нравятся подзапросы больше, чем JOIN)

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

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

SELECT T1.ID FROM TABLE1 T1 WHERE T1.DATE = (SELECT MAX (T11.DATE)  FROM TABLE1 T11  ГДЕ (T1.AREA = T11.AREA))

Приветствия

Ответ 16

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

Ясность в большинстве случаев важнее производительности, а подзапросы легче отлаживать.

Кстати: почему вы используете запутанные псевдонимы таблиц?