Ir al contenido
Manual Crono SQL

Sentencia SELECT

En esta sección se documenta el funcionamiento de la sentencia SELECT del lenguaje. La sintaxis SELECT de Crono SQL aporta algunas ventajas (algunas importantes) frente al SQL ISO. Sin embargo, el mayor beneficio del lenguaje se manifiesta en el resto de instrucciones DML (INSERT, UPDATE, MERGE, …), donde Crono SQL automatiza toda la lógica de carga. Por eso la sentencia SELECT es tan importante… ¡Es prácticamente lo único que tendrá que codificar el desarrollador de un proyecto ETL/DWH!

A continuación se describen sistemáticamente todas las características soportadas en la sentencia SELECT del lenguaje Crono SQL.

Basado en el lenguaje SQL

Sección titulada «Basado en el lenguaje SQL»

Proposición: Cuualquier sentencia SELECT válida en SQL es válida también en Crono SQL

SELECT 'Hola mundo';
Ver SQL compilado 
SELECT 'Hola mundo' AS expr1

Si ninguna tabla participa en la consulta, se debe terminar la sentencia con el carácter punto y coma ”;”. En cualquier otro caso, el punto y coma es opcional.

select *
from staging.Customer
Ver SQL compilado 
SELECT *
FROM staging.Customer

Se pueden incluir las cláusulas JOIN, WHERE, GROUP BY, HAVING y/o ORDER BY

SELECT
  Customer.CustomerId AS CustomerId,
  Person.FirstName AS FirstName,
  Person.LastName AS LastName,
  sum(Sales.subtotal) AS Amount
FROM staging.SalesOrderHeader Sales
INNER JOIN staging.Customer ON (Sales.customerId=Customer.customerId)
LEFT JOIN staging.Person ON (Customer.PersonID=Person.BusinessEntityId)
WHERE Person.FirstName='Fernando'
GROUP BY
  Customer.CustomerId,
  Person.FirstName,
  Person.LastName
HAVING sum(Sales.subtotal)>3000
ORDER BY sum(Sales.subtotal) DESC
Ver SQL compilado 
SELECT
  Customer.CustomerId AS CustomerId,
  Person.FirstName AS FirstName,
  Person.LastName AS LastName,
  sum(Sales.subtotal) AS Amount
FROM staging.SalesOrderHeader Sales
INNER JOIN staging.Customer ON (Sales.customerId=Customer.customerId)
LEFT JOIN staging.Person ON (Customer.PersonID=Person.BusinessEntityId)
WHERE Person.FirstName='Fernando'
GROUP BY
  Customer.CustomerId,
  Person.FirstName,
  Person.LastName
HAVING sum(Sales.subtotal)>3000
ORDER BY sum(Sales.subtotal) DESC

Se pueden utilizar las funciones propias del motor de base de datos o funciones definidas por el usuario.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

Referencia a columnas existentes

Sección titulada «Referencia a columnas existentes»

A diferencia del SQL ISO, en Crono SQL se puede hacer referencia a otra columna de la sentencia SELECT mediante el Alias de la columna.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  upper(customer) UpperCustomer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON sales.CustomerId=customer.CustomerId
LEFT JOIN staging.Person CustomerPerson ON Customer.PersonID=CustomerPerson.BusinessEntityId
WHERE OrderYear=2012
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  upper(concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName)) AS UpperCustomer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  upper(concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName)),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

Prescindiendo del GROUP BY

Sección titulada «Prescindiendo del GROUP BY»

Se puede utilizar la cláusula GROUP BY ALL para indicar que se agrupe por todas las columnas que no sean funciones de agregación.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY ALL
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

Siempre se puede prescindir totalmente de la cláusula GROUP BY. Crono SQL incluirá las columnas necesarias en el SQL generado.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

USING

Sección titulada «USING»

Se puede utilizar la cláusula USING para simplificar la sintaxis de los JOIN equi-join.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING Sales(CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (Sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

La cláusula USING también puede utilizarse cuando los campos de la equi-join tienen distinto nombre.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING Sales(CustomerId)
LEFT JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (Sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

Si no se especifica el nombre de la tabla izquierda en la cláusula USING, se asume que es la tabla del FROM es la que participa en la relación.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING CustomerId
LEFT JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

Si la relación equi-join está formada por distintos campos, se pueden especificar en la cláusula USING separados por comas.

SELECT count(*)
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING (CompanyId,CustomerId)
LEFT JOIN staging.Person CustomerPerson USING customer(CompanyId,PersonID BusinessEntityId)
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
SELECT count(*) AS expr1
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.CompanyId=customer.CompanyId AND sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (customer.CompanyId=CustomerPerson.CompanyId AND customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012

CHECK SNOWFLAKE

Sección titulada «CHECK SNOWFLAKE»

La cláusula CHECK SNOWFLAKE, colocada justo después de todos los JOINs, verifica que las relaciones no pierden ni duplican ningún registro de la tabla del FROM. Se trata de una comprobación fundamental para validar que no estamos cometiendo ninguna equivocación al escribir la consulta y que los datos de origen son coherentes con lo esperado.

SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING CustomerId
INNER JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
CHECK SNOWFLAKE
WHERE year(sales.OrderDate)=2012
Ver SQL compilado 
IF EXISTS (
  SELECT count(*)
  FROM staging.SalesOrderHeader sales
  LEFT JOIN staging.customer ON (sales.CustomerId=customer.CustomerId)
  LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
  HAVING count(CASE WHEN customer.CustomerId IS NOT NULL AND CustomerPerson.BusinessEntityId IS NOT NULL THEN 1 END) <> (SELECT count(*) FROM staging.SalesOrderHeader sales)
) THROW 50001,'Las relaciones de esta consulta pierden o duplican registros de sales.',1


SELECT
  year(sales.OrderDate) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.CustomerId=customer.CustomerId)
INNER JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

La cláusula CHECK SNOWFLAKE verifica que todas las ventas correspondan a un cliente y que ese cliente exista en la tabla de personas. Si no fuera así, la consulta no se ejecutaría y devolvería un error.

Subconsultas

Sección titulada «Subconsultas»

Se pueden incluir subconsultas.

SELECT
  Person.BusinessEntityId,
  Person.LastName,
  HomeAddress.AddressLine1 HomeAddressLine1,
  HomeAddress.AddressLine2 HomeAddressLine2,
  HomeAddress.City HomeCity,
  ShippingAddress.AddressLine1 ShippingAddressLine1,
  ShippingAddress.AddressLine2 ShippingAddressLine2,
  ShippingAddress.City ShippingCity
FROM staging.Person
LEFT JOIN (
  select * from staging.BusinessEntityAddress
  where AddressTypeId=2) BEHomeAddress using BusinessEntityId
LEFT JOIN (
  select * from staging.BusinessEntityAddress
  where AddressTypeId=5) BEShippingAddress using BusinessEntityId
LEFT JOIN staging.Address HomeAddress using BEHomeAddress(AddressId)
LEFT JOIN staging.Address ShippingAddress using BEShippingAddress(AddressId)
Ver SQL compilado 
SELECT
  Person.BusinessEntityId AS BusinessEntityId,
  Person.LastName AS LastName,
  HomeAddress.AddressLine1 AS HomeAddressLine1,
  HomeAddress.AddressLine2 AS HomeAddressLine2,
  HomeAddress.City AS HomeCity,
  ShippingAddress.AddressLine1 AS ShippingAddressLine1,
  ShippingAddress.AddressLine2 AS ShippingAddressLine2,
  ShippingAddress.City AS ShippingCity
FROM staging.Person
LEFT JOIN (SELECT *
           FROM staging.BusinessEntityAddress
           WHERE AddressTypeId=2) BEHomeAddress ON Person.BusinessEntityId=BEHomeAddress.BusinessEntityId
LEFT JOIN (SELECT *
           FROM staging.BusinessEntityAddress
           WHERE AddressTypeId=5) BEShippingAddress ON Person.BusinessEntityId=BEShippingAddress.BusinessEntityId
LEFT JOIN staging.Address HomeAddress ON (BEHomeAddress.AddressId=HomeAddress.AddressId)
LEFT JOIN staging.Address ShippingAddress ON (BEShippingAddress.AddressId=ShippingAddress.AddressId)

Subconsultas con FILTER y COLUMNS

Sección titulada «Subconsultas con FILTER y COLUMNS»

Después del nombre de la tabla, se puede incluir la cláusula FILTER para seleccionar solo una parte de los registros de la tabla. El código SQL generado incluirá una subconsulta similar a la del Ejemplo anterior.

SELECT
  Person.BusinessEntityId,
  Person.PersonType,
  Person.LastName,
  HomeAddress.AddressLine1  HomeAddressLine1,
  HomeAddress.AddressLine2  HomeAddressLine2,
  HomeAddress.City    HomeCity,
  ShippingAddress.AddressLine1  ShippingAddressLine1,
  ShippingAddress.AddressLine2  ShippingAddressLine2,
  ShippingAddress.City    ShippingCity
FROM staging.Person
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=2) BEHomeAddress using BusinessEntityId
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=5) BEShippingAddress using BusinessEntityId
LEFT JOIN staging.Address HomeAddress using BEHomeAddress(AddressId)
LEFT JOIN staging.Address ShippingAddress using BEShippingAddress(AddressId)
Ver SQL compilado 
SELECT
  Person.BusinessEntityId AS BusinessEntityId,
  Person.PersonType AS PersonType,
  Person.LastName AS LastName,
  HomeAddress.AddressLine1 AS HomeAddressLine1,
  HomeAddress.AddressLine2 AS HomeAddressLine2,
  HomeAddress.City AS HomeCity,
  ShippingAddress.AddressLine1 AS ShippingAddressLine1,
  ShippingAddress.AddressLine2 AS ShippingAddressLine2,
  ShippingAddress.City AS ShippingCity
FROM staging.Person
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=2) BEHomeAddress ON (Person.BusinessEntityId=BEHomeAddress.BusinessEntityId)
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=5) BEShippingAddress ON (Person.BusinessEntityId=BEShippingAddress.BusinessEntityId)
LEFT JOIN staging.Address HomeAddress ON (BEHomeAddress.AddressId=HomeAddress.AddressId)
LEFT JOIN staging.Address ShippingAddress ON (BEShippingAddress.AddressId=ShippingAddress.AddressId)

La cláusula FILTER es muy útil en combinación con la cláusula CHECK SNOWFLAKE. En el siguiente ejemplo, se verifica que cada persona tenga una única HomeAddress (o ninguna) y una única ShippingAddress (o ninguna). Si no fuera así, la consulta no duplicaría los registros porque devolvería previamente un error.

SELECT
  Person.BusinessEntityId,
  Person.PersonType,
  Person.LastName,
  HomeAddress.AddressLine1  HomeAddressLine1,
  HomeAddress.AddressLine2  HomeAddressLine2,
  HomeAddress.City    HomeCity,
  ShippingAddress.AddressLine1  ShippingAddressLine1,
  ShippingAddress.AddressLine2  ShippingAddressLine2,
  ShippingAddress.City    ShippingCity
FROM staging.Person
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=2) BEHomeAddress using BusinessEntityId
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=5) BEShippingAddress using BusinessEntityId
LEFT JOIN staging.Address HomeAddress using BEHomeAddress(AddressId)
LEFT JOIN staging.Address ShippingAddress using BEShippingAddress(AddressId)
CHECK SNOWFLAKE
Ver SQL compilado 
IF EXISTS (
  SELECT count(*)
  FROM staging.Person
  LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=2) BEHomeAddress ON (Person.BusinessEntityId=BEHomeAddress.BusinessEntityId)
  LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=5) BEShippingAddress ON (Person.BusinessEntityId=BEShippingAddress.BusinessEntityId)
  LEFT JOIN staging.Address HomeAddress ON (BEHomeAddress.AddressId=HomeAddress.AddressId)
  LEFT JOIN staging.Address ShippingAddress ON (BEShippingAddress.AddressId=ShippingAddress.AddressId)
  HAVING count(*) <> (SELECT count(*) FROM staging.Person)
) THROW 50001,'Las relaciones de esta consulta pierden o duplican registros de Person.',1


SELECT
  Person.BusinessEntityId AS BusinessEntityId,
  Person.PersonType AS PersonType,
  Person.LastName AS LastName,
  HomeAddress.AddressLine1 AS HomeAddressLine1,
  HomeAddress.AddressLine2 AS HomeAddressLine2,
  HomeAddress.City AS HomeCity,
  ShippingAddress.AddressLine1 AS ShippingAddressLine1,
  ShippingAddress.AddressLine2 AS ShippingAddressLine2,
  ShippingAddress.City AS ShippingCity
FROM staging.Person
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=2) BEHomeAddress ON (Person.BusinessEntityId=BEHomeAddress.BusinessEntityId)
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=5) BEShippingAddress ON (Person.BusinessEntityId=BEShippingAddress.BusinessEntityId)
LEFT JOIN staging.Address HomeAddress ON (BEHomeAddress.AddressId=HomeAddress.AddressId)
LEFT JOIN staging.Address ShippingAddress ON (BEShippingAddress.AddressId=ShippingAddress.AddressId)

Se puede utilizar la cláusula COLUMNS para seleccionar, renombrar, u operar sobre las columnas físicas de la tabla. El código SQL generado incluirá una subconsulta con esas columnas.

SELECT
  Person.BusinessEntityId,
  Person.PersonType,
  Person.PersonName,
  HomeAddress.AddressLine1  HomeAddressLine1,
  HomeAddress.AddressLine2  HomeAddressLine2,
  HomeAddress.City      HomeCity,
  ShippingAddress.AddressLine1  ShippingAddressLine1,
  ShippingAddress.AddressLine2  ShippingAddressLine2,
  ShippingAddress.City      ShippingCity
FROM staging.Person COLUMNS (BusinessEntityId,PersonType,LastName PersonName) FILTER (PersonType='IN')
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=2) BEHomeAddress using BusinessEntityId
LEFT JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=5) BEShippingAddress using BusinessEntityId
LEFT JOIN staging.Address HomeAddress using BEHomeAddress(AddressId)
LEFT JOIN staging.Address ShippingAddress using BEShippingAddress(AddressId)
Ver SQL compilado 
SELECT
  Person.BusinessEntityId AS BusinessEntityId,
  Person.PersonType AS PersonType,
  Person.PersonName AS PersonName,
  HomeAddress.AddressLine1 AS HomeAddressLine1,
  HomeAddress.AddressLine2 AS HomeAddressLine2,
  HomeAddress.City AS HomeCity,
  ShippingAddress.AddressLine1 AS ShippingAddressLine1,
  ShippingAddress.AddressLine2 AS ShippingAddressLine2,
  ShippingAddress.City AS ShippingCity
FROM (SELECT BusinessEntityId, PersonType, LastName AS PersonName FROM staging.Person WHERE PersonType='IN') Person
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=2) BEHomeAddress ON (Person.BusinessEntityId=BEHomeAddress.BusinessEntityId)
LEFT JOIN (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=5) BEShippingAddress ON (Person.BusinessEntityId=BEShippingAddress.BusinessEntityId)
LEFT JOIN staging.Address HomeAddress ON (BEHomeAddress.AddressId=HomeAddress.AddressId)
LEFT JOIN staging.Address ShippingAddress ON (BEShippingAddress.AddressId=ShippingAddress.AddressId)

ANTI JOIN

Sección titulada «ANTI JOIN»

El lenguaje Crono SQL soporta todos los joins habituales:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • CROSS JOIN (también CROSS APPLY)

Además, implementa el ANTI JOIN. Un ANTI JOIN devuelve todos los registros de la izquierda que no aparecen en la parte derecha de la relación. Para ello, el SQL generado incluye un predicado NOT EXISTS IN (…)

La siguiente consulta devuelve todos los clientes que no tienen ninguna venta. Puede ampliar la información sobre los ANTI JOIN en el blog de SQL Server de Dale Burnett.

select *
FROM staging.customer
ANTI JOIN staging.SalesOrderHeader sales using customerId
Ver SQL compilado 
SELECT *
FROM staging.customer
WHERE NOT EXISTS (SELECT 1 FROM staging.SalesOrderHeader sales WHERE customer.customerId=sales.customerId)

El ANTI JOIN se puede combinar con el resto de características del lenguaje.

Esta consulta devuelve todos las personas que no tienen Home Address.

select *
FROM staging.Person
ANTI JOIN staging.BusinessEntityAddress FILTER (AddressTypeId=2) BEHomeAddress using BusinessEntityId
Ver SQL compilado 
SELECT *
FROM staging.Person
WHERE NOT EXISTS (SELECT 1 FROM (SELECT * FROM staging.BusinessEntityAddress WHERE AddressTypeId=2) BEHomeAddress WHERE Person.BusinessEntityId=BEHomeAddress.BusinessEntityId)

SEMI JOIN

Sección titulada «SEMI JOIN»

El lenguaje Crono SQL implementa también la relación SEMI JOIN. Un SEMI JOIN devuelve todos los registros de la izquierda que aparecen en la parte derecha de la relación. Para ello, el SQL generado incluye un predicado EXISTS IN (…), por lo que a diferencia del INNER JOIN no duplica los registros en el resultado.

Esta consulta devuelve todos los clientes que tienen alguna venta (sin duplicados). Puede ampliar la información sobre los SEMI JOIN en el blog de SQL Server de Dale Burnett.

select *
FROM staging.customer
SEMI JOIN staging.SalesOrderHeader sales using customerId
Ver SQL compilado 
SELECT *
FROM staging.customer
WHERE EXISTS (SELECT 1 FROM staging.SalesOrderHeader sales WHERE customer.customerId=sales.customerId)

UNPIVOT

Sección titulada «UNPIVOT»

Se puede utilizar el operador UNPIVOT (según la sintaxis de T-SQL) para despivotar las columnas de una tabla.

En este ejemplo, las columna “AddressLine1” y “AddressLine2” se han convertido en filas diferenciadas, duplicándose los registros.

SELECT
  AddressId,
  AddressItem,
  content
FROM staging.Address
UNPIVOT (content FOR AddressItem in (AddressLine1,AddressLine2)) as unpvt
Ver SQL compilado 
SELECT
  AddressId,
  AddressItem,
  content
FROM staging.Address
UNPIVOT (content FOR AddressItem IN (AddressLine1,AddressLine2)) unpvt

ORDER BY

Sección titulada «ORDER BY»

Se puede utilizar la cláusula ORDER BY para forzar la ordenación del resultado.

SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) as OrderMonth,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
ORDER BY OrderYear, OrderMonth
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) AS OrderMonth,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
GROUP BY
  year(sales.OrderDate),
  month(sales.OrderDate)
ORDER BY
  year(sales.OrderDate),
  month(sales.OrderDate)

El ORDER BY se puede escribir haciendo referencia a la posición de las columnas.

SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) as OrderMonth,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
ORDER BY 1,2
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) AS OrderMonth,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
GROUP BY
  year(sales.OrderDate),
  month(sales.OrderDate)
ORDER BY
  1,
  2

SELECT DISTINCT

Sección titulada «SELECT DISTINCT»

Se puede utilizar la palabra clave DISTINCT para obtener los valores distintos

SELECT DISTINCT FirstName
FROM staging.Person
Ver SQL compilado 
SELECT DISTINCT FirstName
FROM staging.Person

SELECT TOP

Sección titulada «SELECT TOP»

Se puede utilizar la palabra clave TOP para limitar el número de registros del resultado.

Esta consulta devuelve los 5 clientes con mayores ventas.

SELECT TOP 5
  SalesTerritory.Name Territory,
  Customer.CustomerId,
  CustomerPerson.FirstName,
  CustomerPerson.LastName,
  sum(sales.subtotal) Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING sales(customerId)
INNER JOIN staging.SalesTerritory USING TerritoryId
LEFT JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
ORDER BY Amount DESC
Ver SQL compilado 
SELECT TOP 5
  SalesTerritory.Name AS Territory,
  Customer.CustomerId AS CustomerId,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
INNER JOIN staging.SalesTerritory ON (sales.TerritoryId=SalesTerritory.TerritoryId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
GROUP BY
  SalesTerritory.Name,
  Customer.CustomerId,
  CustomerPerson.FirstName,
  CustomerPerson.LastName
ORDER BY sum(sales.subtotal) DESC

OVER ()

Sección titulada «OVER ()»

Las funciones de ventana OVER (…) también están soportadas.

Esta consulta devuelve las ventas acumuladas desde el principio de cada año. La funciones de ventana, también llamadas funciones analíticas, tienen mucha utilidad en entornos ETL/DWH y permiten simplificar el desarrollo de muchos escenarios ETL comunes. Puede ampliar la información sobre las funciones de ventana en la documentación de la cláusula OVER en T-SQL.

SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) as OrderMonth,
  sum(sales.subtotal) AS Amount,
  sum(Amount) over (partition by OrderYear order by OrderMonth)  AmountYTD
FROM staging.SalesOrderHeader sales
ORDER BY OrderYear, OrderMonth
Ver SQL compilado 
SELECT
  year(sales.OrderDate) AS OrderYear,
  month(sales.OrderDate) AS OrderMonth,
  sum(sales.subtotal) AS Amount,
  sum(sum(sales.subtotal)) OVER (PARTITION BY year(sales.OrderDate) ORDER BY month(sales.OrderDate)) AS AmountYTD
FROM staging.SalesOrderHeader sales
GROUP BY
  year(sales.OrderDate),
  month(sales.OrderDate)
ORDER BY
  year(sales.OrderDate),
  month(sales.OrderDate)

TOP OVER ()

Sección titulada «TOP OVER ()»

Se puede incluir la cláusula OVER junto a la palabra clave TOP para limitar el número de registros por grupos de registros.

Esta consulta devuelve los tres clientes con más ventas en cada territorio.

SELECT TOP 3 OVER (PARTITION BY Territory ORDER BY Amount DESC)
  SalesTerritory.Name Territory,
  Customer.CustomerId,
  CustomerPerson.FirstName,
  CustomerPerson.LastName,
  sum(sales.subtotal) Amount
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING sales(customerId)
INNER JOIN staging.SalesTerritory USING TerritoryId
INNER JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
Ver SQL compilado 
SELECT
  Territory,
  CustomerId,
  FirstName,
  LastName,
  Amount
FROM (
    SELECT
      Territory,
      CustomerId,
      FirstName,
      LastName,
      Amount,
      ROW_NUMBER() OVER (PARTITION BY Territory ORDER BY Amount DESC) rownumber
    FROM (
        SELECT
          SalesTerritory.Name AS Territory,
          Customer.CustomerId AS CustomerId,
          CustomerPerson.FirstName AS FirstName,
          CustomerPerson.LastName AS LastName,
          sum(sales.subtotal) AS Amount
        FROM staging.SalesOrderHeader sales
        INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
        INNER JOIN staging.SalesTerritory ON (sales.TerritoryId=SalesTerritory.TerritoryId)
        INNER JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
        GROUP BY
          SalesTerritory.Name,
          Customer.CustomerId,
          CustomerPerson.FirstName,
          CustomerPerson.LastName
      ) allRows
  ) allRowsNumbered
WHERE rownumber<=3

La combinación TOP n OVER () tiene muchos usos en procesos ETL/DWH. La sentencia SQL generada es un consulta sobre una subconsulta de una subconsulta. La siguiente consulta devuelve la última venta de cada cliente.

SELECT TOP 1 OVER (PARTITION BY customerId ORDER BY orderDate desc)
  Customer.CustomerId,
  CustomerPerson.FirstName,
  CustomerPerson.LastName,
  SalesOrderHeader.OrderDate,
  SalesOrderHeader.subtotal Amount
FROM staging.SalesOrderHeader
INNER JOIN staging.Customer USING customerId
INNER JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
Ver SQL compilado 
SELECT
  CustomerId,
  FirstName,
  LastName,
  OrderDate,
  Amount
FROM (
    SELECT
      CustomerId,
      FirstName,
      LastName,
      OrderDate,
      Amount,
      ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) rownumber
    FROM (
        SELECT
          Customer.CustomerId AS CustomerId,
          CustomerPerson.FirstName AS FirstName,
          CustomerPerson.LastName AS LastName,
          SalesOrderHeader.OrderDate AS OrderDate,
          SalesOrderHeader.subtotal AS Amount
        FROM staging.SalesOrderHeader
        INNER JOIN staging.Customer ON (SalesOrderHeader.customerId=Customer.customerId)
        INNER JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
      ) allRows
  ) allRowsNumbered
WHERE rownumber=1

WITH

Sección titulada «WITH»

Las sentencias CTE con cláusula WITH están soportadas.

WITH addresses AS (
  SELECT bia.BusinessEntityID,bia.AddressTypeId,CountryRegion.Name Region,Address.AddressLine1,Address.City
  FROM staging.BusinessEntityAddress bia
  INNER JOIN staging.Address using AddressId
  INNER JOIN staging.StateProvince USING Address(StateProvinceId)
  INNER JOIN staging.CountryRegion USING StateProvince(CountryRegionCode)
)
SELECT
  Person.FirstName,
  Person.LastName,
  HomeAddress.AddressLine1 HomeAddressLine1,
  HomeAddress.City HomeCity,
  HomeAddress.Region HomeRegion,
  ShippingAddress.AddressLine1 ShippingAddressLine1,
  ShippingAddress.City ShippingCity ,
  ShippingAddress.Region ShippingRegion ,
FROM staging.Person
LEFT JOIN addresses FILTER (AddressTypeId=2) HomeAddress USING BusinessEntityID
LEFT JOIN addresses FILTER (AddressTypeId=5) ShippingAddress USING BusinessEntityID
Ver SQL compilado 
;WITH
addresses AS (
  SELECT
    bia.BusinessEntityID AS BusinessEntityID,
    bia.AddressTypeId AS AddressTypeId,
    CountryRegion.Name AS Region,
    Address.AddressLine1 AS AddressLine1,
    Address.City AS City
  FROM staging.BusinessEntityAddress bia
  INNER JOIN staging.Address ON (bia.AddressId=Address.AddressId)
  INNER JOIN staging.StateProvince ON (Address.StateProvinceId=StateProvince.StateProvinceId)
  INNER JOIN staging.CountryRegion ON (StateProvince.CountryRegionCode=CountryRegion.CountryRegionCode)
)
SELECT
  Person.FirstName AS FirstName,
  Person.LastName AS LastName,
  HomeAddress.AddressLine1 AS HomeAddressLine1,
  HomeAddress.City AS HomeCity,
  HomeAddress.Region AS HomeRegion,
  ShippingAddress.AddressLine1 AS ShippingAddressLine1,
  ShippingAddress.City AS ShippingCity,
  ShippingAddress.Region AS ShippingRegion
FROM staging.Person
LEFT JOIN (SELECT * FROM addresses WHERE AddressTypeId=2) HomeAddress ON (Person.BusinessEntityID=HomeAddress.BusinessEntityID)
LEFT JOIN (SELECT * FROM addresses WHERE AddressTypeId=5) ShippingAddress ON (Person.BusinessEntityID=ShippingAddress.BusinessEntityID)

UNION y UNION ALL

Sección titulada «UNION y UNION ALL»

Se pueden utilizar los operadores UNION y UNION ALL

Este ejemplo tiene únicamente fines didácticos. Para combinar de este modo dos o más consultas es preferible el operador COMBINE que se muestra continuación.

SELECT
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount2012,
  null Amount2013
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
UNION
  SELECT
    CustomerPerson.LastName AS LastName,
    null Amount2012,
    sum(sales.subtotal) AS Amount2013
  FROM staging.SalesOrderHeader sales
  INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
  LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
  WHERE year(sales.OrderDate)=2013
Ver SQL compilado 
SELECT
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount2012,
  NULL AS Amount2013
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2012
GROUP BY CustomerPerson.LastName
UNION
SELECT
  CustomerPerson.LastName AS LastName,
  NULL AS Amount2012,
  sum(sales.subtotal) AS Amount2013
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE year(sales.OrderDate)=2013
GROUP BY CustomerPerson.LastName

COMBINE

Sección titulada «COMBINE»

El operador COMBINE BY permite combinar dos o más consultas en un único resultado.

COMBINE BY firstname,LastName
  sales2012 AS (
    SELECT
      Person.FirstName AS FirstName,
      Person.LastName AS LastName,
      sum(sales.subtotal) AS Amount2012
    FROM staging.SalesOrderHeader sales
    INNER JOIN staging.customer USING customerId
    LEFT JOIN staging.Person USING Customer(PersonID BusinessEntityId)
    WHERE year(sales.OrderDate)=2012),
  sales2013 AS (
    SELECT
      Person.FirstName AS FirstName,
      Person.LastName AS LastName,
      sum(sales.subtotal) AS Amount2013
    FROM staging.SalesOrderHeader sales
    INNER JOIN staging.customer USING customerId
    LEFT JOIN staging.Person USING Customer(PersonID BusinessEntityId)
    WHERE year(sales.OrderDate)=2013)
Ver SQL compilado 
SELECT
  coalesce(sales2012.FirstName,sales2013.FirstName) AS FirstName,
  coalesce(sales2012.LastName,sales2013.LastName) AS LastName,
  sales2012.Amount2012 AS Amount2012,
  sales2013.Amount2013 AS Amount2013
FROM
    (SELECT
      Person.FirstName AS FirstName,
      Person.LastName AS LastName,
      sum(sales.subtotal) AS Amount2012
    FROM staging.SalesOrderHeader sales
    INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
    LEFT JOIN staging.Person ON (Customer.PersonID=Person.BusinessEntityId)
    WHERE year(sales.OrderDate)=2012
    GROUP BY
      Person.FirstName,
      Person.LastName) sales2012
FULL JOIN
    (SELECT
      Person.FirstName AS FirstName,
      Person.LastName AS LastName,
      sum(sales.subtotal) AS Amount2013
    FROM staging.SalesOrderHeader sales
    INNER JOIN staging.customer ON (sales.customerId=customer.customerId)
    LEFT JOIN staging.Person ON (Customer.PersonID=Person.BusinessEntityId)
    WHERE year(sales.OrderDate)=2013
    GROUP BY
      Person.FirstName,
      Person.LastName) AS sales2013 ON (sales2012.FirstName=sales2013.FirstName AND sales2012.LastName=sales2013.LastName)

Se pueden utilizar tablas distintas en cada consulta del COMBINE. En este ejemplo, se comparan las ventas y las compras por producto. El SQL generado combinará los resultados utilizando un FULL JOIN.

COMBINE BY Product,ProductNumber
  sales (
  select
    Product.Name Product,
    Product.ProductNumber ProductNumber,
    sum(PurchaseOrderDetail.LineTotal) Purchases
  from staging.PurchaseOrderDetail
  inner join staging.Product  using ProductId
  ),
  purchases (
  select
    Product.Name #Product,
    Product.ProductNumber #ProductNumber,
    sum(SalesOrderDetail.LineTotal) Sales
  from staging.SalesOrderDetail
  inner join staging.Product  using ProductId
  )
Ver SQL compilado 
SELECT
  coalesce(sales.Product,purchases.Product) AS Product,
  coalesce(sales.ProductNumber,purchases.ProductNumber) AS ProductNumber,
  sales.Purchases AS Purchases,
  purchases.Sales AS Sales
FROM
    (SELECT
      Product.Name AS Product,
      Product.ProductNumber AS ProductNumber,
      sum(PurchaseOrderDetail.LineTotal) AS Purchases
    FROM staging.PurchaseOrderDetail
    INNER JOIN staging.Product ON (PurchaseOrderDetail.ProductId=Product.ProductId)
    GROUP BY
      Product.Name,
      Product.ProductNumber) sales
FULL JOIN
    (SELECT
      Product.Name AS Product,
      Product.ProductNumber AS ProductNumber,
      sum(SalesOrderDetail.LineTotal) AS Sales
    FROM staging.SalesOrderDetail
    INNER JOIN staging.Product ON (SalesOrderDetail.ProductId=Product.ProductId)
    GROUP BY
      Product.Name,
      Product.ProductNumber) AS purchases ON (sales.Product=purchases.Product AND sales.ProductNumber=purchases.ProductNumber)

MATERIALIZE

Sección titulada «MATERIALIZE»

La cláusula MATERIALIZE permite crear una tabla temporal con el contenido de una subconsulta. Es decir, antes de la ejecución de la consulta, se crean las tablas temporales necesarias y finalmente se ejecuta la consulta utilizando dichas tablas. Esta estrategia de carga simplifica el plan de ejecución del motor de base de datos y se pueden obtener mejoras de rendimiento muy significativas, sin penalizar o dificultar la escritura de la consulta.

SELECT
  SalesOrderHeader.OrderDate,
  Product.Name Product,
  Product.ProductNumber,
  sum(Sales.LineTotal) Sales
FROM staging.SalesOrderDetail FILTER (year(ModifiedDate)=2014) MATERIALIZE Sales
INNER JOIN staging.SalesOrderHeader USING SalesOrderId
INNER JOIN staging.Product USING ProductId
Ver SQL compilado 
-- Materialized query: Sales
SELECT *
INTO #Sales__39872
FROM (SELECT * FROM staging.SalesOrderDetail WHERE year(ModifiedDate)=2014) SalesOrderDetail


SELECT
  SalesOrderHeader.OrderDate AS OrderDate,
  Product.Name AS Product,
  Product.ProductNumber AS ProductNumber,
  sum(Sales.LineTotal) AS Sales
FROM #Sales__39872 Sales
INNER JOIN staging.SalesOrderHeader ON (Sales.SalesOrderId=SalesOrderHeader.SalesOrderId)
INNER JOIN staging.Product ON (Sales.ProductId=Product.ProductId)
GROUP BY
  SalesOrderHeader.OrderDate,
  Product.Name,
  Product.ProductNumber

Con la cláusula MATERIALIZE, también se pueden materializar las consultas de una sentencia COMBINE. En este ejemplo, primero se ejecutará la consulta con las ventas, luego se ejecutará una consulta con las compras, y finalmente se combinarán en un único resultado.

COMBINE bY Product,productNumber
  MATERIALIZE sales (
  select
    Product.Name Product,
    Product.ProductNumber ProductNumber,
    sum(PurchaseOrderDetail.LineTotal) Purchases
  from staging.PurchaseOrderDetail
  inner join staging.Product  using ProductId
  ),
  MATERIALIZE purchases (
  select
    Product.Name Product,
    Product.ProductNumber ProductNumber,
    sum(SalesOrderDetail.LineTotal) Sales
  from staging.SalesOrderDetail
  inner join staging.Product  using ProductId
  )
Ver SQL compilado 
-- Materialized query: sales
SELECT
  Product.Name AS Product,
  Product.ProductNumber AS ProductNumber,
  sum(PurchaseOrderDetail.LineTotal) AS Purchases
INTO #sales__D83E4
FROM staging.PurchaseOrderDetail
INNER JOIN staging.Product ON (PurchaseOrderDetail.ProductId=Product.ProductId)
GROUP BY
  Product.Name,
  Product.ProductNumber


-- Materialized query: purchases
SELECT
  Product.Name AS Product,
  Product.ProductNumber AS ProductNumber,
  sum(SalesOrderDetail.LineTotal) AS Sales
INTO #purchases__1CD26
FROM staging.SalesOrderDetail
INNER JOIN staging.Product ON (SalesOrderDetail.ProductId=Product.ProductId)
GROUP BY
  Product.Name,
  Product.ProductNumber


SELECT
  coalesce(sales.Product,purchases.Product) AS Product,
  coalesce(sales.ProductNumber,purchases.ProductNumber) AS ProductNumber,
  sales.Purchases AS Purchases,
  purchases.Sales AS Sales
FROM #sales__D83E4 sales
FULL JOIN #purchases__1CD26 purchases ON (sales.Product=purchases.Product AND sales.ProductNumber=purchases.ProductNumber)

CAST automático

Sección titulada «CAST automático»

Se puede forzar el tipo de datos resultante de una columna especificándolo justo después del alias de la columna. El SQL generado incluirá una llamada a la función CAST.

SELECT
   year(sales.OrderDate)  OrderYear varchar(4),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  Upper(customer) UpperCustomer,
  CustomerPerson.FirstName,
  CustomerPerson.LastName,
  sum(sales.subtotal) AS Amount,
  count(*)    TicketsCount,
  Amount/TicketsCount AvgTicket numeric(12,2)
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer USING CustomerId
LEFT JOIN staging.Person CustomerPerson USING Customer(PersonID BusinessEntityId)
WHERE OrderYear=2012
Ver SQL compilado 
SELECT
  cast(year(sales.OrderDate) AS varchar(4)) AS OrderYear,
  Customer.CustomerId AS CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName) AS Customer,
  Upper(concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName)) AS UpperCustomer,
  CustomerPerson.FirstName AS FirstName,
  CustomerPerson.LastName AS LastName,
  sum(sales.subtotal) AS Amount,
  count(*) AS TicketsCount,
  cast(sum(sales.subtotal)/count(*) AS numeric(12,2)) AS AvgTicket
FROM staging.SalesOrderHeader sales
INNER JOIN staging.customer ON (sales.CustomerId=customer.CustomerId)
LEFT JOIN staging.Person CustomerPerson ON (Customer.PersonID=CustomerPerson.BusinessEntityId)
WHERE cast(year(sales.OrderDate) AS varchar(4))=2012
GROUP BY
  year(sales.OrderDate),
  Customer.CustomerId,
  concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName),
  Upper(concat(CustomerPerson.FirstName,' ',CustomerPerson.LastName)),
  CustomerPerson.FirstName,
  CustomerPerson.LastName

SELECTs anidados

Sección titulada «SELECTs anidados»

Es posible incluir varios SELECT en una misma consulta. Esta sintaxis permite escribir rápidamente una consulta sobre el resultado de otra consulta. Son consultas encadenadas.

Este consulta devuelve la media de las ventas anuales de cada producto.

select
  Product,
  ProductNumber,
  avg(Sales) AvgYearSales
select
  Product.Name Product,
  Product.ProductNumber,
  year(OrderDate) OrderYear,
  sum(SalesOrderDetail.LineTotal) Sales
from staging.SalesOrderDetail
inner join staging.SalesOrderHeader using SalesOrderId
inner join staging.Product  using ProductId
Ver SQL compilado 
SELECT
  Product,
  ProductNumber,
  avg(Sales) AS AvgYearSales
FROM (
    SELECT
      Product.Name AS Product,
      Product.ProductNumber AS ProductNumber,
      year(OrderDate) AS OrderYear,
      sum(SalesOrderDetail.LineTotal) AS Sales
    FROM staging.SalesOrderDetail
    INNER JOIN staging.SalesOrderHeader ON (SalesOrderDetail.SalesOrderId=SalesOrderHeader.SalesOrderId)
    INNER JOIN staging.Product ON (SalesOrderDetail.ProductId=Product.ProductId)
    GROUP BY
      Product.Name,
      Product.ProductNumber,
      year(OrderDate)
  ) a
GROUP BY
  Product,
  ProductNumber

La cláusulas SELECT encadenadas permiten, por ejemplo, contar el número de registros que devuelve una consulta previa. La siguiente consulta ejecuta un count(*) sobre el resultado de la consulta inferior.

SELECT count(*)
SELECT
  Product.Name Product,
  Product.ProductNumber,
  sum(SalesOrderDetail.LineTotal) Sales
FROM staging.SalesOrderDetail
INNER JOIN staging.Product USING ProductId
Ver SQL compilado 
SELECT count(*) AS expr1
FROM (
    SELECT
      Product.Name AS Product,
      Product.ProductNumber AS ProductNumber,
      sum(SalesOrderDetail.LineTotal) AS Sales
    FROM staging.SalesOrderDetail
    INNER JOIN staging.Product ON (SalesOrderDetail.ProductId=Product.ProductId)
    GROUP BY
      Product.Name,
      Product.ProductNumber
  ) a

Resumen

Sección titulada «Resumen»

En resumen, si se conoce SQL, ya se conoce la parte más importante de Crono SQL. Crono SQL, simplemente, facilita la escritura de SQL y aporta algunas extensiones para necesidades comunes en ETL/DWH. Destacamos:

  • Posibilidad de referenciar a Alias de columnas de la consulta
  • No es necesario el GROUP BY
  • Sintaxis simplificada de los JOIN
  • Sentencia COMBINE
  • Cláusula MATERIALIZE
  • Cláusula CHECK SNOWFLAKE
  • Cláusulas COLUMNS y FILTER para reducir el número de subconsultas
  • Cláusula TOP OVER
  • Relaciones ANTI JOIN y SEMI JOIN
  • SELECTs anidados