Cálculo de la ventana de habilidades básicas de HiveSQL

Objetivos:

1. Dominar la suma(), avg() y otras funciones agregadas utilizadas para cálculos acumulativos, y aprender a limitar el número de filas (cálculo móvil); p>

2. Domine las funciones fila_número(), rango() y denso_rank() para ordenar

3. Domine la función ntile() para consultas grupales; > 4. Domine las funciones de análisis de compensación lag() y lead()

Función de ventana:

Similar a la función agregada, pero la función de ventana genera una para cada fila de datos. Como resultado, la función de agregación puede agregar varias filas de datos en una fila como se especifica. En términos generales, el número de filas después de la agregación es menor que el número de filas antes de la agregación, pero a veces queremos mostrar tanto los datos antes de la agregación como los datos antes de la agregación. datos después de la agregación Datos, luego se introduce la función de ventana La función de ventana se ejecuta al seleccionar y se ubica antes del pedido.

En el trabajo diario, a menudo nos encontramos con el cálculo del valor acumulado a partir de un determinado mes o un determinado día. En Excel, se puede realizar mediante funciones.

En HiveSQL. se puede realizar utilizando funciones de ventana.

1) El pago mensual total en 2018 y el pago total acumulado en el año

2) Analizar el pago total de la empresa en 2017 y 2018 mensualmente y resumirlo por año

Nota: 1. La partición por over desempeña el papel de agrupación;

2. ¿En qué orden se acumula, orden ascendente ASC, orden descendente DESC, orden ascendente predeterminado? /p >

3. La agrupación correcta es muy importante. El campo después de la partición por es el área que debe calcularse de forma acumulativa y debe comprenderse cuidadosamente

(calcule la retención de tres días, siete días). retención de días y retención de treinta días. Puede utilizar esta función de otras formas)

3) Calcule el monto de pago promedio móvil de los últimos tres meses de cada mes en 2018

. Uso: La función de estas tres funciones Todas devuelven el número de clasificación de la regla correspondiente. Dado que la función de clasificación no es un cálculo de agregación secundaria, no es necesario utilizar una subconsulta

4) En enero de 2019. , la clasificación del número de categorías de productos compradas por los usuarios

5) Seleccione los usuarios que ocuparon el puesto 10, 20 y 30 por monto de pago en 2019

6) Divida los usuarios de pago en enero de 2019 en 5 grupos según el monto del pago

7) Seleccione el 10% superior de usuarios con montos de reembolso en 2019

Nota: Las funciones de análisis de retraso y avance pueden recuperar el primer N filas de datos (Lag) y Las últimas N filas de datos (Lead) se tratan como columnas independientes.

En aplicaciones prácticas, la aplicación de las funciones Lag y Lead es particularmente importante cuando es necesario obtener la diferencia entre un determinado campo entre hoy y ayer.

Por supuesto, esta operación se puede implementar utilizando autouniones de tablas, pero en comparación con autouniones como la unión izquierda y la unión derecha, Lag y Lead son más eficientes y las declaraciones SQL son más concisas.

8) El número de usuarios cuyo intervalo de pago supera los 100 días (¿cuánto tiempo pasa entre esta compra y la siguiente?, tenga en cuenta que la función dateiff tiene la fecha más grande al frente)

9) Todas las ciudades, diferentes géneros, usuarios TOP3 con mayor monto de pago en 2018

Resumen de pasos:

1. Primero, filtre cada usuario y el consumo total cantidad de cada usuario;

p>

2. Conecte las dos tablas para extraer los campos requeridos

3. Realice un cálculo de agregación secundaria en las tablas conectadas para calcular el clasificación de montos en diferentes ciudades y géneros;

p>

4. Realizar filtrado y extracción condicional en la tabla para el cálculo de agregación secundaria

10) El 25% superior de usuarios. con el monto del reembolso de cada marca de teléfono móvil

Resumen de pasos:

1. Primero filtre cada usuario y el monto total del reembolso de cada usuario

; 2. Conecte las dos tablas para extraer los campos requeridos;

3. Agrupe las tablas conectadas según las marcas de teléfonos móviles

4. Realice filtrado y extracción condicional en las tablas agrupadas