Transformación de datos con consultas T-SQL

Completados

T-SQL está disponible tanto en los almacenes de Fabric como en los almacenes de lago de datos. Un almacén proporciona compatibilidad completa con T-SQL de lectura y escritura: puede usar las instrucciones SELECT, INSERT, UPDATE, DELETE, y CREATE TABLE AS SELECT (CTAS) para dar forma a los datos directamente. Un almacén de lago de datos también expone sus tablas Delta a través de un punto de conexión de análisis SQL, que admite T-SQL para consultas y análisis, pero es de solo lectura. Este módulo se centra en transformar los datos en un almacenamiento, donde la funcionalidad de lectura y escritura le permite crear lógica de transformación que conserva los resultados.

Consulta de datos en el almacenamiento

Puede ejecutar consultas T-SQL en un almacén de Fabric mediante el editor de consultas SQL en el explorador de almacenamiento. Para abrirla, seleccione Nueva consulta SQL en la cinta de opciones. El editor admite IntelliSense, resaltado de sintaxis y varias pestañas de consulta, por lo que puede escribir y iterar sobre consultas complejas sin salir del navegador.

También puede conectarse al almacén de datos desde herramientas externas, como SQL Server Management Studio (SSMS) o Visual Studio Code con la extensión MSSQL, a través de la cadena de conexión T-SQL. Esta flexibilidad permite a su equipo usar cualquier herramienta con la que se sienta más cómodo.

Para los usuarios que prefieren una experiencia sin código, el Visual Editor de Power Query proporciona una interfaz de arrastrar y colocar. Arrastre tablas desde el panel Explorer a un lienzo y, a continuación, aplique operaciones de Power Query tales como combinaciones, filtros y agregaciones visualmente. El editor genera T-SQL en segundo plano y puede cambiar a Ver SQL en cualquier momento para ver la consulta resultante. También puede guardar la salida como una nueva vista o tabla directamente desde el editor visual.

Sugerencia

Visual Editor de Power Query es una buena opción para la exploración rápida de datos o al crear combinaciones simples. Para las transformaciones complejas que implican funciones de ventana, CTE o lógica condicional, use el editor de consultas SQL.

Filtrar y dar forma a los datos

Los patrones de transformación más comunes comienzan con el filtrado de filas y la selección de columnas. En un almacén con datos de preprocesamiento, suele ser necesario acotar un conjunto de datos antes de realizar un preprocesamiento adicional. El filtrado elimina al principio las filas irrelevantes, lo que mejora tanto la claridad como el rendimiento de las operaciones posteriores.

SELECT
    customer_id,
    order_date,
    amount
FROM staging.orders
WHERE order_date >= '2024-01-01'
    AND status = 'Completed';

Más allá del filtrado simple, a menudo necesita volver a dar forma a los datos agregando columnas calculadas. Las expresiones y la lógica condicional crean nuevas columnas que codifican reglas de negocio directamente en la consulta:

SELECT
    order_id,
    quantity,
    unit_price,
    quantity * unit_price AS line_total,
    CASE
        WHEN quantity * unit_price > 1000 THEN 'High'
        ELSE 'Standard'
    END AS order_tier
FROM staging.orders;

Normalmente, debe decidir cómo manejar los valores NULL antes de que los datos se transfieran a tablas analíticas. Use ISNULL para reemplazar un valor NULL por un valor predeterminado o COALESCE para elegir el primer valor distinto de NULL de una lista de columnas:

SELECT
    ISNULL(discount, 0) AS discount,
    COALESCE(shipping_address, billing_address) AS address
FROM staging.orders;

Las conversiones de tipos de datos son otra tarea común de transformación. Los datos de origen pueden llegar como cadenas que deben convertirse en fechas o números para ordenar y agregar correctamente:

SELECT
    CAST(order_date_text AS DATE) AS order_date,
    CONVERT(DECIMAL(10,2), amount_text) AS amount
FROM staging.raw_orders;

Estos patrones forman los bloques de construcción para transformaciones más complejas. Cada consulta da forma a los datos un paso más cerca de su forma analítica final.

Combinar datos con uniones y agregaciones

Las transformaciones reales casi siempre requieren combinar datos de varias tablas. Los sistemas de origen normalizan los datos en muchas tablas, pero los consumidores analíticos necesitan una vista combinada. Puede usar combinaciones para reunir conjuntos de datos relacionados y agregaciones para resumir los datos combinados en métricas significativas.

Una combinación básica combina columnas de dos tablas basadas en una clave compartida:

SELECT
    o.order_id,
    o.order_date,
    c.customer_name,
    c.segment
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id;

Para resumir los datos por grupos, combine GROUP BY con funciones de agregado. Este patrón es la base de la mayoría de las consultas analíticas, que convierte las transacciones detalladas en métricas de resumen:

SELECT
    c.region,
    YEAR(o.order_date) AS order_year,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_sales
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id
GROUP BY c.region, YEAR(o.order_date);

Use la HAVING cláusula para filtrar los resultados agregados. A diferencia WHERE de (que filtra las filas individuales antes de la agrupación), HAVING filtra los grupos después de la agregación. Esta distinción es importante si desea encontrar grupos que cumplan un umbral:

SELECT
    region,
    YEAR(order_date) AS order_year,
    SUM(amount) AS total_sales
FROM staging.orders
GROUP BY region, YEAR(order_date)
HAVING SUM(amount) > 50000;

Aplicar funciones de ventana

Las funciones de ventana realizan cálculos en un conjunto de filas relacionadas con la fila actual sin contraer el conjunto de resultados. A diferencia GROUP BYde , que genera una fila de salida por grupo, las funciones de ventana mantienen cada fila en el resultado y agregan valores calculados junto con ellos. Esta característica hace que sean ideales para ejecutar totales, clasificaciones y comparaciones con valores anteriores.

SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS order_sequence,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS running_total,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount
FROM staging.orders;

En esta consulta, ROW_NUMBER asigna un número secuencial dentro de los pedidos de cada cliente, lo que resulta útil para identificar el primer o el último pedido. SUM ... OVER calcula un total acumulado continuo del importe de ventas por cliente. LAG recupera el valor de la fila anterior dentro de la partición, lo que permite realizar cálculos de cambios de un periodo a otro sin autouniones.

Otras funciones de ventana útiles incluyen RANK (que controla los vínculos de forma diferente de ROW_NUMBER), DENSE_RANKy LEAD (que se ve hacia delante en lugar de hacia atrás).

Estructura consultas complejas con CTE

Las expresiones de tabla comunes (CTE) dividen una consulta compleja en pasos legibles y con nombre. Cada CTE define un resultado intermedio al que puede hacer referencia el paso siguiente.

WITH monthly_totals AS (
    SELECT
        YEAR(order_date) AS yr,
        MONTH(order_date) AS mo,
        SUM(amount) AS monthly_total
    FROM staging.orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT
    yr,
    mo,
    monthly_total,
    SUM(monthly_total) OVER (ORDER BY yr, mo) AS ytd_total
FROM monthly_totals;

En primer lugar, el CTE agrega los pedidos en totales mensuales y, a continuación, calcula un total acumulado anual hasta la fecha. Los CTE facilitan la depuración y el mantenimiento de las consultas, especialmente cuando una transformación implica varios pasos lógicos. Puede encadenar varias CTE separándolas con comas, construyendo la transformación capa por capa.

Persista los resultados con CREATE TABLE AS SELECT

Cuando tenga una consulta de transformación que genere resultados que desee almacenar, use CREATE TABLE AS SELECT (CTAS) para crear una nueva tabla y rellenarla en una sola instrucción:

CREATE TABLE gold.regional_sales_summary
AS
SELECT
    c.region,
    YEAR(o.order_date) AS order_year,
    MONTH(o.order_date) AS order_month,
    SUM(o.amount) AS total_sales,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM staging.orders AS o
INNER JOIN staging.customers AS c
    ON o.customer_id = c.customer_id
GROUP BY c.region, YEAR(o.order_date), MONTH(o.order_date);

CTAS es útil para materializaciones únicas. Por ejemplo, crear una tabla de resumen durante una carga de datos inicial. Para las transformaciones periódicas, los procedimientos almacenados (cubiertos en una unidad posterior) proporcionan un mejor mecanismo porque se pueden parametrizar y programar.

Ahora tiene las técnicas de consulta principales para transformar los datos en un almacén de Fabric. A continuación, verá cómo hacer que estas consultas sean reutilizables guardándolas como vistas.