Puntos clave: Las Funciones de Ventana Copo de Nieve permiten cálculos complejos a través de filas sin agregación. Este tutorial explica características clave como OVER(), PARTITION BY y ORDER BY, demostrando su utiliza en cálculos como totales acumulados y clasificaciones.
- Las funciones de ventana difieren del SQL GROUP TRADICIONAL POR.
- Utiliza OVER() para Define la ventana de cálculos.
Snowflake soporta funciones de Windows. Piensa en las funciones de Windows como si se ejecutaran sobre un subconjunto de filas, excepto que los resultados devuelven cada fila. Eso es diferente del grupo SQL tradicional porque está un resultado para cada grupo.
Una función de Windows podría ser útil en ejemplos como:
- Una suma corriente
- Los valores medios sobre un número determinado de filas anteriores
- Un percentil de clasificación de cada fila entre todas las filas.
El tema de las funciones de las ventanas en Snowflake están amplio y complejo. Este tutorial sirve como una breve visión general y seguiremos desarrollando tutoriales adicionales.
Definiciones de copos de nieve
Snowflake define ventanas como un grupo de filas relacionadas. están define mediante la sentencia over(). La sentencia over() indica a Snowflake que te desea utiliza una función de Windows en lugar de la función SQL tradicional, ya que algunas funciones funcionan en ambos contextos.
Un marco de ventanas está un subgrupo de ventanas. Los marcos de ventanas requieren un orden por sentencia, ya que las filas deben estar en orden conocido.
Los marcos de ventanas pueden ser acumulativos o deslizantes, que son extensiones del orden por sentencia. Acumulativo significa a lo largo de todo el marco de ventanas. Deslizante significa añadir un desfasamiento, como +- n filas.
Una ventana también puede tener una instrucción de partición. Una partición están un grupo de filas, como la tradicional instrucción grupo por instrucciones.
Windows vs SQL normal
Por ejemplo, si te agrupa ventas por producto y te tiene 4 filas en una tabla te puede haber dos filas en el resultado:
Función de Windows
Orden por
Grupo SQL regular por
select count(*) from sales group by product:
10 product A
20 product B
Con la función Windows, te seguir teniendo el recuento en dos grupos, pero cada una de las 4 filas de la base de datos están listada y la suma están para todo el grupo, cuando te utiliza la instrucción de partición.
count 10 product A
count 10 product A
count 20 product B
count 20 product B
Crea algunos datos de muestra
Para estudiar esto, primero Crea estas dos tablas.
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 SQL. La parte superior de los datos se ve así:
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('440','tiqthogsjwsedifisiir','3077854','vdew','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('440','402','swgstdhmju','2020-09-27','sale','2020-10-01','0.7005950240358919','61','BB111');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('802','hrdngzutwelfhgwcyznt','1606845','rnmk','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('802','829','jybwzvoyzb','2020-09-27','sale','2020-10-06','0.3702248922841853','75','FF4444');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('199','ogvaevvhhqtjcqggafnv','8452159','hyxm','','2020-09-27','[email protected]');
Partición por
Una partición crea subconjuntos dentro de una ventana. Aquí, tenemos la suma de cantidad por producto.
select customernumber, ordernumber, productnumber,quantity,
sum(quantity) over (partition by productnumber) as prodqty
from orders
order by ordernumber
Esto produce los mismos resultados que esta sentencia SQL en la que la tabla de órdenes está unida consigo misma:
select customernumber,
ordernumber,
productnumber,quantity,
(select sum(quantity) from orders as o2 where o1.productnumber = o2.productnumber) as prodqty
from orders as o1
order by ordernumber
La función suma() no tiene Haz sentido para una función windows porque es están para un grupo, no para un conjunto ordenado. Sin embargo, Snowflake permite te utiliza suma con un marco de ventanas—es decir, una sentencia con una sentencia order()—dando así resultados que son difíciles de interpretar.
Veamos la función de rango, una que está relevante para los pedidos. Aquí utilizamos una función de Windows para clasificar a nuestros clientes más valiosos. Estos son los que han realizado las compras más grandes.
La función rank() no acepta argumentos. La ventana está ordenada por cantidad en orden descendente. Limitamos la salida a 10 para que encaje en la página siguiente.
select customernumber, quantity, rank() over (order by quantity desc) from orders limit 10
Aquí está la salida. El cliente que ha comprado más está listado primero.
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.