Есть ли эмпирическое правило для построения SQL-запроса из понятного человеку описания?

Question

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

Существует ли какой-либо систематический пошаговый или математический способ построения SQL-запроса из данного понятного человеку описания?

Например, как определить, нужно ли для SQL-запроса объединение, а не подзапрос, потребует ли он группирования, потребуется ли предложение IN и т. Д.

Например, тот, кто изучал цифровую электронику, знал бы о таких методах, как карта Карно или метод Куин Маклауски. Это некоторые систематические подходы к упрощению цифровой логики.

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

Answer 1

Существует ли какой-либо систематический пошаговый или математический способ построения SQL-запроса из данного понятного человеку описания?

Да, есть.

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

С каждой таблицей (базой или результатом запроса) связан связанный предикат - шаблон оператора заполнения на естественном языке (named-)blank, параметризованный именами столбцов.

-- person [liker] likes person [liked]

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

liker  | liked
--------------
Bob    | Dex    -- Bob likes Dex
Bob    | Alice  -- Bob likes Alice
Alice  | Carol  -- Alice likes Carol

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

/*
    Alice likes Carol
AND NOT Alice likes Alice
AND NOT Alice likes Bob
AND NOT Alice likes Dex
AND NOT Alice likes Ed
...
AND Bob likes Alice
AND Bob likes Dex
AND NOT Bob likes Bob
AND NOT Bob likes Carol
AND NOT Bob likes Ed
...
AND NOT Carol likes Alice
...
AND NOT Dex likes Alice
...
AND NOT Ed likes Alice
...
*/   

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

-- person [liker] likes person [liked]
-- Likes(liker, liked)
SELECT * FROM Likes

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

Внутри SELECT заявление:
• Базовая таблица с именем T с псевдонимом A имеет предикат / строки, где T(A.C,...),
• R CROSS JOIN S & R INNER JOIN S есть предикат / строки, где the predicate of R AND the predicate of S, (Строки, которые являются комбинацией строки из каждого аргумента с псевдонимом A после переименования его столбцов C,... в A.C,....)
• R ON condition & R WHERE condition есть предикат / строки, где the predicate of R AND condition,
• SELECT DISTINCT A.C AS D,... FROM R (может быть с неявным A. и / или неявный AS D) имеет предикат / есть строки где FOR SOME [value for] затем упал столбцы тогда the predicate of R с A.C,... заменен на D,..., (Удаленные столбцы не являются параметрами нового предиката.)
• Эквивалентно SELECT DISTINCT A.C AS D,... FROM R имеет предикат / строки, где FOR SOME A.*,..., A.C=D AND ... AND the predicate of R, (Это может быть менее компактно, но больше похоже на SQL.)
• (X,...) IN (R) средства predicate of R с колоннами C,... заменен на X,...,
• Так (...) IN (SELECT * FROM T) средства T(...),

Естественный язык и сокращение для (человек, понравившийся) строк, где [человек] - это Боб, а Бобу нравится тот, кому нравится [понравилось], но кому не нравится Эд.

/* (person, liked) rows where
for some value for x,
    [person] likes [x]
and [x] likes [liked]
and [person] = 'Bob'
and not [x] likes 'Ed'

/* (person, liked) rows where
FOR SOME [value for] x,
        Likes(person, x)
    AND Likes(x, liked)
    AND person = 'Bob'
    AND NOT Likes(x, 'Ed')
*/

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

/* (person, liked) rows where
FOR SOME [values for] l1.*, l2.*,
        person = l1.liker AND liked = l2.liked
    AND Likes(l1.liker, l1.liked)
    AND Likes(l2.liker, l2.liked)
    AND l1.liked = l2.liker
    AND person = 'Bob'
    AND NOT Likes(l1.liked, 'Ed')
*/
SELECT l1.liker AS person, l2.liked AS liked
FROM
    /* (l1.liker, l1.liked, l2.liker, l2.liked) rows where
        Likes(l1.liker, l1.liked)
    AND Likes(l2.liker, l2.liked)
    AND l1.liked = l2.liker
    AND l1.liker = 'Bob'
    AND NOT Likes(l1.liked, 'Ed')
    */
Likes l1 INNER JOIN Likes l2
ON l1.liked = l2.liker
WHERE l1.liker = 'Bob'
AND NOT (l1.liked, 'Ed') IN (SELECT * FROM Likes)

• R UNION CORRESPONDING S имеет предикат / строки, где the predicate of R OR the predicate of S,
• R EXCEPT S имеет предикат / строки, где the predicate of R AND NOT the predicate of S,
• VALUES(C,...)((X,...),...) имеет предикат / строки, где (C = X AND ...) OR ...,

/* (person) rows where
    FOR SOME liked, Likes(person, liked)
OR  person = 'Bob'
*/
    SELECT liker AS person
    FROM Likes
UNION
    VALUES (person) (('Bob'))

Таким образом, если мы выражаем наши желаемые строки в терминах заданных шаблонов операторов на естественном языке базовой таблицы, для которых строки принимают значение true или false (для возврата или нет), то мы можем преобразовать в запросы SQL, которые являются вложениями логических сокращений и операторов и / или имен таблиц. & операторы. И тогда СУБД может полностью преобразовать в таблицы, чтобы вычислить строки, делающие наш предикат истинным.

См. Как получить совпадающие данные из другой таблицы SQL для двух разных столбцов: Внутреннее соединение и / или Объединение? повторно применяем это к SQL. (Еще одно самостоятельное присоединение.)
См. Реляционная алгебра для банковского сценария для получения дополнительной информации о выражениях на естественном языке (В контексте реляционной алгебры.)

Answer 2

Вот что я делаю в не сгруппированных запросах:

Я положил в FROM предложение таблицы, для которой я ожидаю получить ноль или одну выходную строку на строку в таблице. Часто вы хотите что-то вроде "все клиенты с определенными свойствами". Затем таблица клиентов переходит в FROM пункт.

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

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

Как пользоваться WHERE и прогнозы это просто.