Cálculo en ventana de las habilidades básicas de HiveSQL

1. Dominar funciones agregadas como sum() y avg() para cálculos acumulativos y aprender a limitar el número de filas (cálculo móvil);

2. row_number(), rank () y densa_rank();

3. Domina la función de ntile() para consultas grupales

4. y liderar() .

Función de ventana:

Es similar a una función agregada, pero la función de ventana genera un resultado para cada fila de datos. Las funciones de agregación pueden agregar varias filas de datos en una fila de acuerdo con las regulaciones. En términos generales, el número de filas después de la agregación es menor que antes de la agregación, pero a veces queremos mostrar tanto los datos antes de la agregación como los datos después de la agregación, por lo que se introduce la función de ventana. La función de ventana se ejecuta al seleccionar, antes de ordenar por.

En el trabajo diario, a menudo nos encontramos con el cálculo del valor acumulado hasta un mes o un día, que se puede lograr mediante funciones en Excel.

En HiveSQL, esto se puede lograr utilizando funciones de ventana.

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

2) Analizar mensualmente los pagos totales de la empresa en 2017 y 2018 y resumirlos por año.

Explicación: la partición por en 1 y superiores juega un papel de agrupación;

2.ordenar por en qué orden se acumula, orden ascendente ASC, orden descendente DESC, el valor predeterminado es orden ascendente .

3. La agrupación correcta es muy importante. Los campos después de la división son áreas que requieren cálculos acumulativos y deben comprenderse detenidamente.

(Esta función se puede utilizar para calcular la retención de tres días, la retención de siete días y la retención de treinta días).

3) Encuentre el monto de pago promedio de los últimos tres meses de cada mes en 2018.

Uso: La función de estas tres funciones es devolver el número de secuencia de clasificación de la regla correspondiente. Debido a que la función de clasificación no es un cálculo de agregación auxiliar, no es necesario utilizar una subconsulta.

4) 2065 438+09+0 Ranking del número de categorías compradas por los usuarios en junio.

5) Seleccione los usuarios cuyos montos de pago se ubican en los puestos 10, 20 y 30 en 2019.

6) Divida a los usuarios que pagaron en junio 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 Lag y Lead pueden extraer las primeras N filas de datos (Lag) y las últimas N filas de datos (Lead) del mismo campo como columnas independientes en la misma consulta.

En aplicaciones prácticas, si desea obtener la diferencia entre hoy y ayer, la aplicación de funciones de retraso y adelanto es particularmente importante.

Por supuesto, esta operación se puede lograr mediante la autounión de tablas, pero Lag y Lead son más eficientes que las uniones izquierdas, derechas y otras autouniones, y las declaraciones SQL son más concisas.

8) El número de usuarios cuyo intervalo de pago supera los 100 días (¿cuándo es el tiempo entre esta compra y la siguiente? Tenga en cuenta que la función dateiff es la fecha anterior)

9 ) 2065 438+ Los usuarios TOP3 con mayor monto de pago en 2008, independientemente del género, en cada ciudad.

Resumen de pasos:

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

2.

3. Realizar cálculos resumidos secundarios en las tablas conectadas para calcular la clasificación de montos en diferentes ciudades y géneros.

4. En la tabla de cálculos de agregación secundaria Realizar filtrado condicional y. extracción.

10) El 25% superior de usuarios de cada marca de teléfono móvil reembolsa el importe.

Resumen de pasos:

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

2. Una dos tablas para extraer los campos obligatorios;

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

4. Filtrado condicional y extracción de tablas agrupadas.