Cuándo utilizar la expresión de tabla común (CTE)
Comencé a leer sobre Common Table Expression y no puedo pensar en un caso de uso en el que necesite usarlos. Parecerían redundantes ya que se puede hacer lo mismo con las tablas derivadas. ¿Hay algo que me falta o no entiendo bien? ¿Alguien puede darme un ejemplo simple de limitaciones con consultas regulares de selección, derivadas o de tablas temporales para defender el caso de CTE? Cualquier ejemplo simple sería muy apreciado.
Por ejemplo, si necesita hacer referencia/unirse al mismo conjunto de datos varias veces, puede hacerlo definiendo un CTE. Por tanto, puede ser una forma de reutilización de código.
Un ejemplo de autorreferencia es la recursividad: consultas recursivas utilizando CTE
Para definiciones interesantes de Microsoft tomadas de libros en línea:
Un CTE se puede utilizar para:
Cree una consulta recursiva. Para obtener más información, consulte Consultas recursivas mediante expresiones de tabla comunes.
Sustituir una vista cuando no se requiere el uso general de una vista; es decir, no es necesario almacenar la definición en metadatos.
Habilite la agrupación por una columna derivada de una subselección escalar o una función que no sea determinista o tenga acceso externo.
Haga referencia a la tabla resultante varias veces en la misma declaración.
Los uso para dividir consultas complejas, especialmente uniones y subconsultas complejas. Creo que las uso cada vez más como 'pseudovistas' para ayudarme a entender la intención de la consulta.
Mi única queja sobre ellos es que no se pueden reutilizar. Por ejemplo, puedo tener un proceso almacenado con dos declaraciones de actualización que podrían usar el mismo CTE. Pero el "alcance" del CTE es sólo la primera cuestión.
El problema es que los 'ejemplos simples' probablemente no necesiten CTE.
Aún así, muy útil.
Hay dos razones por las que veo para usar cte.
Para utilizar un valor calculado en la cláusula donde. Esto me parece un poco más limpio que una tabla derivada.
Supongamos que hay dos tablas: Preguntas y Respuestas unidas por Question.ID = Answers.Question_Id (e ID del cuestionario).
WITH CTE AS
(
Select Question_Text,
(SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0
Aquí hay otro ejemplo en el que quiero obtener una lista de preguntas y respuestas. Quiero que las Respuestas se agrupen con las preguntas en los resultados.
WITH cte AS
(
SELECT [Quiz_ID]
,[ID] AS Question_Id
,null AS Answer_Id
,[Question_Text]
,null AS Answer
,1 AS Is_Question
FROM [Questions]
UNION ALL
SELECT Q.[Quiz_ID]
,[Question_ID]
,A.[ID] AS Answer_Id
,Q.Question_Text
,[Answer]
,0 AS Is_Question
FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT
Quiz_Id,
Question_Id,
Is_Question,
(CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question
order by Quiz_Id, Question_Id, Is_Question Desc, Name
Uno de los escenarios que encontré útil para usar CTE es cuando desea obtener filas DISTINTAS de datos basadas en una o más columnas pero devolver todas las columnas de la tabla. Con una consulta estándar, es posible que primero tenga que volcar los valores distintos en una tabla temporal y luego intentar unirlos nuevamente a la tabla original para recuperar el resto de las columnas o podría escribir una consulta de partición extremadamente compleja que pueda devolver los resultados en una ejecución, pero lo más probable es que sea ilegible y cause problemas de rendimiento.
Pero usando CTE (como respondió Tim Schmelter en Seleccione la primera instancia de un registro )
WITH CTE AS(
SELECT myTable.*
, RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
FROM myTable
)
SELECT * FROM CTE
WHERE RN = 1
Como puede ver, esto es mucho más fácil de leer y mantener. Y en comparación con otras consultas, su rendimiento es mucho mejor.
