Puntos clave: El tutorial de la Función de Lag Snowflake demuestra cómo calcular medias móviles usando la función de lag de SQL para analizar tendencias de ventas. Proporciona fragmentos de código SQL para crear tablas de ejemplo y verificar cálculos.
- Utiliza la función de retardo para los cálculos de medias móviles.
- Ofrece fragmentos de código SQL y datos de ejemplo para aplicaciones prácticas.
Este tutorial te muestra cómo utilizar la función de retardo para calcular medias móviles en Snowflake.
Se basa en el trabajo que compartimos en Snowflake SQL Aggregate Functions & Table Joins y Snowflake Window Functions: Partition By y Order By.
Uso del retardo para calcular una media móvil
Podemos utilizar la función lag() para calcular una media móvil. La utilizamos cuando queremos detectar tendencias o para Reduce la volatilidad del número diario, ya que puede variar mucho.
En otras palabras, es mejor mirar una semana de ventas en lugar de un día para Ve cómo está rindiendo un producto.
Crea 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]');
Escribe una sentencia SQL
Ahora queremos calcular la media móvil de ventas totales de los cuatro días anteriores.
Aquí tenemos una instrucción select dentro de una sentencia select porque queremos un total de pedidos por día. Luego, la sentencia lag revisa ese registro para ver el día anterior.
select shipdate, (quantity + lag(quantity,1) over (order by shipdate) +
lag(quantity,2) over (order by shipdate) +
lag(quantity,3)over (order by shipdate) + lag(quantity,4)
over (order by shipdate)) / 5 as movingaverage from
(select shipdate, sum(quantity) as quantity from orders group by shipdate);
Aquí está la media móvil. Las primeras filas son nulas porque la función de retardo mira más atrás que la ventana para filas que no existen.
Podemos demostrar que este cálculo están correcto calculándolo de otra manera.
Sumemos los pedidos por fecha de envío.
select shipdate, sum(quantity)
from orders group by shipdate
order by shipdate;
Luego copiamos los resultados en una hoja de cálculo:
- A la izquierda está la función Windows.
- A la derecha está la consulta anterior.
He añadido una columna usando la función de hoja de cálculo average() para mostrar que los números son los mismos. Así que te puedes Ve fácilmente cómo funciona la función de lag de Windows.
windows function sum and group by =AVERAGE(E3:E7)
SHIPDATE MOVINGAVERAGE SHIPDATE SUM(QUANTITY) moving average
2020-09-30 2020-09-30 427
2020-10-01 2020-10-01 230
2020-10-02 2020-10-02 657
2020-10-03 2020-10-03 604
2020-10-04 488.6 2020-10-04 525 488.6
2020-10-05 462 2020-10-05 294 462
2020-10-06 547.2 2020-10-06 656 547.2
2020-10-07 485.2 2020-10-07 347 485.2
2020-10-08 470.8 2020-10-08 532 470.8
2020-10-09 486.2 2020-10-09 602 486.2
2020-10-10 495.2 2020-10-10 339 495.2
2020-10-11 465.8 2020-10-11 509 465.8
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.