Puntos clave: Las funciones agregadas de Snowflake SQL realizan cálculos entre filas, incluyendo el recuento y el análisis estadístico, mientras que las uniones de tablas combinan datos de múltiples tablas. Funciones clave como stddev_pop() y listagg() mejoran la resumen y organización de datos.
- Las funciones agregadas calculan valores sobre filas específicas.
- Las tablas de unión permiten la recuperación combinada de datos.
- Las funciones estadísticas calculan promedios y desviaciones.
En este artículo, explicamos cómo utilizar funciones agregadas con Snowflake.
¿Qué son las funciones agregadas?
Las funciones agregadas son aquellas que realizan algún cálculo sobre todas las filas o subconjuntos de filas de una tabla.
Por ejemplo, la función agregada más sencilla están count(). te podría contar a todos los clientes en una tabla usando count(*) sin la cláusula de grupo o where. El * le indica a Snowflake que mire todas las columnas, pero te podría haber puesto solo una columna porque significa lo mismo.
select count(*) from orders
Pero si te quieres contar pedidos sobre algún subconjunto te podrías, por ejemplo, contar los clientes por tipo de pedido:
select ordertype, count(*) from orders
group by ordertype;
Crea algunos datos de muestra
Vamos a Crear algunos datos de ejemplo para explorar algunas de estas funciones. Inicia sesión en Snowflake y haz clic en el botón Crea base de datos para Crea una base de datos llamada inventario. A continuación, abre el editor de hojas de cálculo e inserta estos dos comandos SQL:
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) PRIMARY KEY,
ordernumber varchar(100),
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount number,
quantity int,
productnumber varchar(50)
)
Luego pega estos datos. Los datos se ven así:
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','zopvxqhwocrtsonemrcf','3119110','vqlx','','2020-09-22','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','ee56d97b-fcaa-11ea-ab7a-0ec120e133fc','shsyuaraxxftdzooafbg','2020-09-22','sale','2020-10-01','0.7751890540939359','40','ee56d97c-fcaa-11ea-ab7a-0ec120e133fc');
Tablas de unión
Las tablas de clientes y pedidos son relacionadas por número de pedido. Obviamente, te tendría que reunirlas en un solo conjunto cuando te necesite datos de clientes y pedidos juntos. te hacer esto con una unión, que crea ese conjunto temporalmente
te unir las dos tablas en el elemento columna número cliente. Tenga en cuenta que:
- Nosotros utilizamos para Crea un alias para abreviar los nombres de las tablas y así Haz más fácil de escribir.
- Nosotros utilizamos join en lugar de inner join. (Otros tutoriales suelen añadir inner join, pero solo confunde las cosas cuando son lo mismo. A menudo escriben esto también para contrastar con una unión izquierda, derecha o externa, que son como productos cartesianos, es decir, añaden cada uno de n pedidos a cada uno de m clientes creando así un conjunto de n*m filas.)
select c.customernumber, c.customername, o.ordernumber, c.datecreated, o.orderdate, o.shipdate from customers as c
join orders as o on c.customernumber = o.customernumber;
Desviación estándar
Calculemos la desviación estándar en los tiempos de envío. Lo hacemos en tres pasos:
- Únete a las tablas de clientes y pedidos
- Utiliza la función datediff() para calcular el tiempo de envío, es decir, cuánto tiempo debe esperar el cliente.
- Cada consulta externa se refiere a una consulta interna envolviéndola entre paréntesis [()]. Así, la consulta se está construida en etapas.
Aquí está la consulta completa. Ve abajo para Ve cómo se están desglosados.
select
avg(shiptime),
stddev_pop(shiptime)
from
(
select
customernumber,
customername,
orderdate,
shipdate,
datediff(days, orderdate, shipdate) as shiptime
from
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
order by
shiptime desc
)
Construimos la consulta en etapas. Comienza desde abajo (es decir, la consulta más interna) y avanza hacia arriba:
- Unir la tabla de clientes y pedidos para tener los detalles del cliente y del pedido en un solo conjunto y así listar ambos. (Podríamos habernos saltado este paso ya que solo sons usamos la tabla de pedidos.)
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
- Calcula el tiempo de envío usando la función datediff()
select
count(*),
datediff(days, orderdate, shipdate) as shiptime
from
orders
group by
shiptime
order by
shiptime
- Calcula la desviación estándar sobre los tiempos de envío:
select avg(shiptime), stddev_pop(shiptime) from (step b)
Aquí son los resultados:
Aquí son los resultados:
Aquí son los resultados:
AVG(SHIPTIME) STDDEV_POP(SHIPTIME)
8.539063 3.512038155
Percentil discreto
El percentil 95 significa que representa al 95% de la población. Es una estadística común, ya que los datos fuera de ese rango generalmente se están considerados casos atípicos.
Aquí mostramos cómo calcular el percentil 25:
Resultados en:
select customernumber , PERCENTILE_disc( 0.25 ) within group (order by quantity)
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
group by customernumber
order by customernumber
Resultados en:
CUSTOMERNUMBER PERCENTILE_DISC( 0.25 ) WITHIN GROUP (ORDER BY QUANTITY)
5d2b742e-fcaa-11ea-ab7a-0ec120e133fc 9
Haz una comprobación y te puedes Ve que las cantidades son pedido 92, 55 y 9. Así que el único en el percentil 25% inferior están 9.
select quantity from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
order by quantity desc;
Aquí son los resultados:
QUANTITY
92
55
9
Listagg
La función listagg lista pedidos por cliente en un array, poniéndolos en otro formato que te podría utiliza en una cláusula where que solicita una lista de elementos.
select listagg(ordernumber, '|')
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
Aquí son los resultados:
LISTAGG(ORDERNUMBER, '|')
5d2b742f-fcaa-11ea-ab7a-0ec120e133fc|5d2b7431-fcaa-11ea-ab7a-0ec120e133fc|5d2b7433-fcaa-11ea-ab7a-0ec120e133fc
Cuando te hacer consultas, te deberías cruzarlas con otras consultas para comprobar tu trabajo. Aquí listamos los números de clientes de arriba abajo en filas.
select orderumber from orders
where where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
Modo
La función mode() muestra los valores más frecuentes:
Resultados en:
select mode(quantity)
from orders
Resultados en:
MODE(QUANTITY)
13
Recursos adicionales
Para más tutoriales como este, explora estos recursos:
Guía de copos de nieve
- Snowflake 101: Introducción a la Nube de Datos Snowflake
- Cómo importar datos de Amazon S3 a Snowflake
- Cómo consultar datos JSON en Snowflake
- Cargando archivos CSV de S3 a Snowflake
- Snowflake: Uso de Analítica y Funciones Estadísticas
- Funciones de la ventana copo de nieve: Partición por y orden por
- Función de retraso de copo de nieve y medias móviles
- Funciones agregadas SQL de Snowflake y uniones de tablas
- Funciones definidas por el usuario (UDFs) en Snowflake
- Creación y uso de arroyos de copos de nieve
- Uso de procedimientos almacenados en Snowflake
- Copo de nieve vs Redshift: ¿Cuál es la diferencia y cómo elegir?
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.