Introducción
CTE es la forma abreviada de Common Table Expressions. CTE es una de las herramientas más poderosas de SQL (lenguaje de consulta estructurado), y también ayuda a limpiar los datos. Es el concepto de SQL (Lenguaje de consulta estructurado) utilizado para simplificar la codificación y ayudar a obtener el resultado lo más rápido posible. CTE es la tabla temporal utilizada para hacer referencia a la tabla original. Si la tabla original contiene demasiadas columnas y solo requerimos algunas de ellas, podemos hacer CTE (una tabla temporal) que contenga solo las columnas requeridas.
CTE se utiliza para resolver problemas complejos fácilmente con una buena legibilidad del código. Un código siempre debe ser más fácil de leer, de modo que si lo remite después de un tiempo, obtendrá ese código de inmediato. Incluso si la tercera persona se refiere a su código, puede entenderlo muy bien si la legibilidad de su código es fácil y buena. CTE es uno de los conceptos importantes de SQL. Si tiene conocimientos de CTE, se está adentrando en el mundo del SQL avanzado. Common Table Expressions (comúnmente conocido como CTE) ayuda a los analistas de datos, científicos de datos o cualquier otro analista que desee extraer sus datos de manera eficiente y efectiva de una tabla que contiene big data.
Este artículo fue publicado como parte del Blogatón de ciencia de datos.
Índice del contenido
Sintaxis de CTE
La siguiente es la sintaxis de CTE:
WITH CTE_NAME AS
( SELECT column_name1, column_name2,..., column_nameN FROM table_name WHERE condition
)
SELECT column_name1, column_name2,..., column_nameN FROM CTE_NAME;Ejemplo de CTE
Ejemplo 1:
El siguiente código se ejecuta en MySQL:
-- Creating databse
CREATE DATABASE employee_details; -- Use database
USE employee_details; -- Creating table
CREATE TABLE employee
(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(20),
email_id VARCHAR(30),
city VARCHAR(25),
Verification_status CHAR(3)
); -- Inserting values into the table
INSERT INTO employee VALUES (1,'Suhas','[email protected]','Mumbai','Yes');
INSERT INTO employee VALUES (2,'Yohani','[email protected]','Mumbai','No');
INSERT INTO employee VALUES (3,'Reshama','[email protected]','Pune','Yes');
INSERT INTO employee VALUES (4,'Raj','[email protected]','Bangalore','No');
INSERT INTO employee VALUES (5,'Shivani','[email protected]','Bangalore','Yes');
SELECT * FROM employee;
La salida se da a continuación:
En el ejemplo anterior, he creado la tabla llamada 'empleado' que contiene los detalles del empleado.
La descripción de las columnas de la tabla 'empleado' se muestra a continuación:
- emp_id: se refiere a la identificación única de cada empleado
- emp_name: se refiere al nombre de cada empleado
- email_id: se refiere a una identificación de correo electrónico de cada empleado
- ciudad: se refiere a la ciudad donde se encuentra el empleado
- Verification_status: si se realiza la verificación de los antecedentes de un empleado, este valor se almacena como 'Sí', mientras que si no se realiza la verificación de los antecedentes de un empleado, este valor se almacena como 'No'.
Supongamos que queremos extraer información específica de los empleados, entonces podemos lograrlo creando CTE.
Si queremos comprobar el estado de verificación de los empleados si está verificado o no y queremos ver los registros de los empleados cuya verificación aún no se ha realizado. Por lo tanto, requerimos solo dos columnas en nuestra CTE (tabla temporal) y las columnas son emp_name y Verification_status que contienen el valor 'No' de la tabla 'employee'.
El siguiente código muestra el ejemplo de creación de CTE usando la tabla 'empleado':
-- Creating CTE
WITH mycte AS ( SELECT emp_name , Verification_status FROM employee WHERE Verification_status = 'No'
)
SELECT * FROM mycte;La salida se da a continuación:
Aquí, “mycte” es el nombre del CTE (tabla temporal). “mycte” contiene las columnas emp_name y Verification_status. Contiene los detalles de los empleados cuya verificación aún no se ha realizado. Esto es lo que queríamos lograr.
Podemos hacer uno o más de un CTE de una misma tabla separados por coma.
¿Cómo evitar errores en expresiones de tabla comunes?
SELECT *
FROM mycte;Esta consulta SQL no es válida. Porque aquí en esta consulta SQL, no puedo usar CTE "mycte".
"mycte" CTE no existe para esta consulta SQL.
“mycte” CTE existe para la única consulta SQL donde se ha creado. Es por eso que CTE (Expresión de tabla común) se conoce como tabla temporal en SQL.
Ejemplo 2:
Ahora, comprendamos cómo usar la consulta JOIN dentro de CTE. Considere el siguiente código, que está implementado en MySQL.
-- Creating table product
CREATE TABLE product
(
p_id INT PRIMARY KEY,
p_name VARCHAR(20),
category VARCHAR(30)
); -- Creating table sales
CREATE TABLE sales
(
p_id INT PRIMARY KEY,
p_name VARCHAR(20),
gross_sales DECIMAL
); -- Inserting values into the table 'product'
INSERT INTO product VALUES (1, 'Mobile', 'Electronics');
INSERT INTO product VALUES (2, 'TV', 'Electronics');
INSERT INTO product VALUES (3, 'Car', 'Toy');
INSERT INTO product VALUES (4, 'Video game', 'Toy');
INSERT INTO product VALUES (5, 'Earphones', 'Electronics'); -- Inserting values into the table 'sales'
INSERT INTO sales VALUES (1, 'Mobile', 50000);
INSERT INTO sales VALUES (2, 'TV', 40000);
INSERT INTO sales VALUES (3, 'Car', 50000);
INSERT INTO sales VALUES (5, 'Earphones', 500000); -- Show all columns from the table 'product'
SELECT * FROM product;
-- Show all columns from the table 'product'
SELECT * FROM sales;
salida:
tabla “producto”:
tabla "ventas":
En el ejemplo anterior, he creado dos tablas "producto" y "ventas".
La descripción de las columnas de la tabla "producto":
- p_id: se refiere a la identificación única del producto
- p_name: se refiere al nombre del producto
- categoría: se refiere a la categoría a la que pertenece cada producto
La descripción de las columnas de la tabla "ventas":
- p_id: se refiere a la identificación única del producto
- p_name: se refiere al nombre del producto
- gross_sales: se refiere a las ventas brutas de cada producto
Luego, inserté valores en las tablas "producto" y "ventas".
-- Creating CTE
WITH TEMP_CTE AS
(
SELECT p.category AS category, COUNT(*) AS No_of_products, SUM(s.gross_sales) AS Total_gross_sales
FROM product p JOIN sales s ON p.p_id=s.p_id
GROUP BY category
ORDER BY Total_gross_sales DESC
)
SELECT * FROM TEMP_CTE;Ahora, se nos solicita encontrar las ventas brutas en cada categoría del producto junto con la cantidad de productos en cada categoría.
Para lograr esto, creé CTE llamado "TEMP_CTE". En esto, he usado JOIN para unir las dos tablas "producto" y "ventas". Quería unir las tablas sobre los registros que tienen coincidencias en ambas tablas. Por lo tanto, se utiliza INNER JOIN. INNER JOIN también se conoce como JOIN. Entonces, si escribe JOIN en su consulta en lugar de INNER JOIN, solo será válido. p_id es la columna común en ambas tablas.
Para encontrar las ventas brutas totales y no. de productos, necesitamos usar las funciones agregadas sum() y count() respectivamente. Para usar las funciones agregadas, la cláusula GROUP BY debe estar allí. Así que hemos agrupado nuestro resultado por categoría y encontramos la suma de las ventas brutas en cada categoría junto con el recuento de productos.
Por último, las Ventas brutas totales están ordenadas de forma descendente, de modo que podemos ver la categoría con las ventas brutas más altas.
Ventajas de CTE
Las siguientes son las ventajas de CTE:
- Common Table Expression (CTE) hace que el código sea más fácil de leer.
- CTE puede resolver consultas complejas de manera eficiente. Como analista que trabaja con consultas SQL, resolver problemas complejos es una gran tarea y CTE ayuda a manejarlo muy bien.
- El mantenimiento de consultas y subconsultas se vuelve más fácil gracias a CTE.
- Hace que la depuración de las consultas sea más fácil que las consultas normales.
- La consulta compleja puede parecer simple al agregar CTE dentro de ella.
- CTE organiza la consulta ordenadamente.
Conclusión
Para resumir, me gustaría decir; Common Table Expressions es un concepto muy fácil de implementar. Algunas de las conclusiones clave se enumeran a continuación:
- El uso de CTE hace que el código SQL sea legible, aumentando la eficiencia del código.
- CTE facilita el análisis para los analistas.
- La depuración, que es una de las partes importantes de las consultas, se vuelve más fácil gracias a CTE.
- Para extraer información específica (para ser más específicos, columnas) de una gran cantidad de datos, CTE es la forma más efectiva de manejar esta situación.
Si está dando una entrevista para cualquier puesto de analista o científico de datos, el entrevistador verifica el conocimiento de SQL. Ese tiempo CTE juega un papel vital en mostrar su conocimiento y permitirle al entrevistador sus habilidades y capacidades para resolver problemas con mayor complejidad.
Espero que este artículo sobre Expresiones de tabla comunes lo encuentre útil y lo ayude en su viaje de datos informados con SQL. ¡Con suerte, su conocimiento de la cantidad de sal en la comida ha aumentado!
Los medios que se muestran en este artículo no son propiedad de Analytics Vidhya y se utilizan a discreción del autor.
Relacionado:
- Distribución de relaciones públicas y contenido potenciado por SEO. Consiga amplificado hoy.
- Platoblockchain. Inteligencia del Metaverso Web3. Conocimiento amplificado. Accede Aquí.
- Fuente: https://www.analyticsvidhya.com/blog/2023/03/common-table-expressions-cte-in-sql/
