¿Existe alguna regla práctica para construir una consulta SQL a partir de una descripción legible para el ser humano?

Cada vez que hay una descripción de la consulta en frente de nosotros, tratamos de aplicar la heurística y la lluvia de ideas para construir la consulta.

¿Existe alguna manera sistemática paso a paso o matemática para construir una consulta SQL a partir de una descripción dada por humanos?

Por ejemplo, cómo determinar eso, si una consulta SQL necesitaría una unión en lugar de una subconsulta, si requeriría un grupo, si requeriría una cláusula IN, etc.

Por ejemplo, quien haya estudiado Electrónica Digital conocerá métodos como el Mapa Karnaugh o el Método Quin McClausky. Estos son algunos enfoques sistemáticos para simplificar la lógica digital.

¿Hay algún método como este para analizar las consultas sql de forma manual para evitar una lluvia de ideas cada vez?

¿Existe alguna manera sistemática paso a paso o matemática para construir una consulta SQL a partir de una descripción dada por humanos?

Sí hay.

Resulta que las expresiones de lenguaje natural y las expresiones lógicas y las expresiones de álgebra relacional y las expresiones de SQL (un híbrido de las dos últimas) se corresponden de una manera bastante directa.

Cada tabla (base o resultado de consulta) tiene un predicado asociado – una plantilla de instrucción de espacios en blanco de llenado en el lenguaje natural (con nombre) parametrizada por nombres de columna.

 -- person [liker] likes person [liked] 

Una tabla contiene todas las filas que, usando los valores de las columnas de la fila para completar los espacios en blanco (nombrados), hacen una afirmación verdadera también conocida como proposición .

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

Cada proposición de llenar un predicado con los valores de una fila en una tabla es verdadera. Y cada proposición de llenar un predicado con los valores de una fila que no está en una tabla es falsa.

 /* 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 ... */ 

El DBA da el predicado para cada tabla base. La syntax de SQL para una statement de tabla se parece mucho a la tradicional abreviatura de lógica para la versión en lenguaje natural de un predicado dado.

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

Una expresión (sub) de consulta SQL transforma los valores de la tabla de argumentos en un nuevo valor de tabla que contiene las filas que crean una statement verdadera a partir de un nuevo predicado. El nuevo predicado de la tabla se puede express en términos de predicado (s) de tabla de argumento de acuerdo con los operadores relacionales / de tabla de la (sub) expresión. Escribimos una expresión SQL cuyo predicado es el predicado para la tabla que queremos.

Dentro de una instrucción SELECT , cuando una tabla base T con columnas C,... tiene (tal vez implícito) alias A podemos tratar a A como una tabla con el valor de T pero con columnas renombradas a AC,... (Podemos usar C para AC cuando no es ambiguo.) Entonces el predicado de una tabla base llamada T con alias A es T(AC,...) . El predicado de R CROSS JOIN S o R INNER JOIN S es el predicado de R AND ed con el predicado de S El predicado de la condition R ON/WHERE es el predicado de R AND ed con condition . El predicado de (...) IN R es R(...) . Una cláusula SELECT DISTINCT que enumera las columns to keep de una tabla coloca FOR SOME columns to drop o THERE EXISTS columns to drop antes de su predicado. (Esas columnas no son parámetros del predicado resultante.) Una cláusula SELECT que cambia el nombre de una columna de una tabla (tal vez con A. implícita) a través de AS cambia el nombre de un nombre en su predicado. (Esto elimina A. s.)

 /* rows that make a true statement from FOR SOME l1.liked, l2.liker: Likes(person, l1.liked) AND Likes(l2.liker, liked) AND l1.liked = l2.liker AND person = 'Bob' AND NOT Likes(person, 'Ed') */ SELECT l1.liker AS person, l2.liked AS liked FROM /* rows that make a true statement from Likes(l1.liker, l1.liked) AND Likes(l2.liker, l2.liker) AND l1.liked = l2.liker AND l1.liker = 'Bob' AND NOT Likes(l1.liker, 'Ed') */ Likes l1 INNER JOIN Likes l2 ON l1.liked = l2.liker WHERE l1.liker = 'Bob' AND NOT (l1.liker, 'Ed') IN (SELECT * FROM Liker) 

El predicado de R UNION CORRESPONDING S es el predicado de R OR ed con el predicado de S Para R EXCEPT S , usamos AND NOT . VALUES(C,...)((V,...),...) tiene predicado (C = V AND ...) OR ...

 /* rows that make a true statement from FOR SOME liked, Likes(person, liked) OR person = 'Bob' */ SELECT liker AS person FROM Likes UNION VALUES (person) (('Bob')) 

Entonces, si expresamos nuestras filas deseadas en términos de plantillas de statement de lenguaje natural de tabla base dadas que las filas son verdaderas o falsas (que se devolverán o no), entonces podemos traducir a consultas SQL que anidan líneas cortas lógicas y operadores y / o nombres de tabla y operadores. Y luego el DBMS puede convertir totalmente a tablas para calcular las filas haciendo que nuestro predicado sea verdadero.

Consulte esto para aplicar esto a SQL y esto para obtener más información sobre frases de lenguaje natural.

Esto es lo que hago en consultas no agrupadas:

Puse en la cláusula FROM la tabla de la cual espero recibir cero o una fila de salida por fila en la tabla. A menudo, desea algo así como “todos los clientes con ciertas propiedades”. Luego, la tabla de clientes va a la cláusula FROM .

Use combinaciones para agregar columnas y filtrar filas. Las uniones no deben duplicar filas. Una unión debería encontrar cero o una fila, nunca más. Eso lo mantiene muy intuitivo porque puede decir que “una combinación agrega columnas y filtra algunas filas”.

Las subconsultas deben evitarse si una unión puede reemplazarlas. Las uniones se ven mejor, son más generales y a menudo son más eficientes (debido a las debilidades comunes del optimizador de consultas).

Cómo usar WHERE y las proyecciones es fácil.