Notas del curso Análisis de datos - 20 - Función de ventana Hive Core Skills

Hola a todos, en esta lección aprenderemos la parte más difícil de las habilidades principales de Hive: las funciones de ventana. En esta lección aprenderemos la parte más difícil de las funciones principales de la ventana de habilidades de Hive. Cuando estábamos aprendiendo MySQL antes, ya habíamos aprendido algunas funciones de ventana, pero solo aprendimos tres funciones de clasificación de ventanas. En esta lección, aprenderemos más sobre las funciones de ventana, incluida la agregación, la clasificación de particiones, la clasificación dividida y el análisis de desplazamiento.

Antes de comenzar a aprender, debemos aclarar la diferencia entre funciones de ventana y agrupación GROUP BY. Los dos tienen similitudes funcionales, pero son fundamentalmente diferentes.

1. La agrupación cambiará la estructura de la tabla, pero las funciones de ventana no cambiarán la estructura de la tabla. Por ejemplo, si la tabla original tiene 10 filas de datos y después de dividirse en dos grupos, solo hay dos filas de datos y la función de ventana aún devuelve 10 filas de datos.

2. La agrupación solo puede consultar campos de agrupación, incluidos campos de agrupación (nombres de grupo) y campos de funciones agregadas. Las funciones de ventana no tienen restricciones en los campos de consulta, es decir, puedes consultar cualquier campo de la tabla original, más una columna de valores agregados por la función de ventana.

Bien, ahora entremos en el mundo de las funciones de ventana~

El contenido principal de esta lección:

1. Cálculo acumulativo de funciones de ventana

(1) suma(...) sobre(...)<

(2) promedio(....) sobre(...) )

(3) Resumen de sintaxis

2. Función de ventana de clasificación de particiones

(1) número_fila()

(2) rango ()

(3) densa_rank()

3. Función de ventana de clasificación de sectores

(1) ntile(n) over(...... )

4. Función de ventana de análisis de compensación

5. Ejercicios clave

Cuando elaboramos informes, a menudo nos encontramos con cálculos a partir de un determinado mes. El valor acumulado generalmente se puede lograr a través de esta función en. SOBRESALIR.

Entonces, ¿cómo implementar este cálculo de valor acumulativo en HiveSQL? ¡Eso es usar funciones de ventana!

Algunas notas sobre la función de ventana:

Análisis de requisitos: dado que queremos acumular mensualmente, primero debemos convertir cada tiempo de transacción en 2018 en meses, y luego La agrupación mensual se realiza para el cálculo resumido para obtener la tabla de pagos mensuales totales para 2018, y luego se utiliza la función de ventana para el cálculo acumulativo basado en esta tabla.

Monto total del pago mensual en la tabla de 2018:

Luego use la función de ventana para el cálculo acumulativo mensual:

Cálculo acumulativo anual.

Este requisito tiene un requisito más que el requisito 1 que es el resumen anual. Entonces solo necesitamos agregar un campo de año a la subconsulta del requisito anterior.

Descripción:

1. En over, la partición actúa como una ventana para agrupar datos. De hecho, después de agregar una partición, se puede entender que se divide en varias ventanas y se realizan cálculos acumulativos o particiones dentro de cada ventana.

Si no se agrega la partición por a.año, los resultados simplemente se agruparán por mes:

2. Ordenar por es el orden en el que se acumulará, orden ascendente ASC, descendente orden DESC, el valor predeterminado es orden ascendente.

Al observar las acciones, a menudo se ven gráficos de líneas K como este, entre los que se suelen utilizar los gráficos de media móvil de 7 y 30 días. Entonces, ¿cómo se utiliza la función de ventana para calcular el valor? ¿media móvil?

Análisis de demanda: esta demanda requiere un promedio móvil mensual del monto pagado en los últimos tres meses. Aquí necesitamos usar un nuevo punto de conocimiento después de ordenar por mes en la función de ventana promedio. Agregue una fila entre las dos filas anteriores y la fila actual para establecer el rango de cálculo del promedio móvil. Esta oración significa incluir esta fila y las dos filas anteriores. Las otras partes están escritas de manera similar a los requisitos anteriores. Primero, tome el pago total de cada mes en 2018 y luego use la función de ventana para encontrar el promedio móvil.

Nota:

suma(...A...) sobre(partición por...B.. orden por...C... filas entre... D1...y...D2...)

promedio(...A....) over(partición por...B.. orden por...C)

avg(...A...) over() partición por ...B.. ordenar por ...C.. filas entre ...D1... y ...D2 .. .)

A: El nombre del campo a procesar

B: El nombre del campo a agrupar

C: El nombre del campo a ordenar

D: rango de filas a calcular

Filas no calificadas entre la fila anterior y la fila actual

- incluyendo esta fila y todas las filas anteriores

Líneas no calificadas actuales entre la línea y la línea siguiente

- incluida esta línea

Líneas entre las 3 líneas anteriores y la línea actual

- incluyendo la línea actual y la siguiente línea no calificada

La línea entre las primeras 3 líneas y la siguiente 1 línea

-- La línea entre las primeras 3 líneas y la siguiente 1 línea (5 líneas)

max(......) over(particionar por ......ordenar por filas entre ............ y . .....)

min(......) over(partición por ...... orden por ...... ... ... entre... ... fila)

número_fila(), rango(), rango_denso()

Uso: Las funciones de estas tres funciones son devolver el número de clasificación de la regla correspondiente

número_fila () sobre(partición por...A.. orden por... B..)

número_fila() sobre(partición por...A.. orden por... .. B. .): El campo ordenar por en la función ventana no puede tener el mismo nombre que el campo seleccionar, porque ambos se ejecutan al mismo tiempo.

Análisis de requisitos: primero, use la función de ventana para calcular el monto total pagado por cada usuario en 2019 y ordenarlo, y luego use esto como una subconsulta para recuperar los pagos y los nombres de usuario 10.º, 20.º y 30. Importe total y orden de clasificación. Las empresas suelen utilizar denso_rank para la clasificación, por lo que utilizamos denso_rank directamente aquí.

Ranking del importe total pagado por cada usuario en 2019:

Usuarios clasificados en los puestos 10, 20 y 30 por importe pagado en 2019:

ntile(n ) over (partición por ..A... orden por...B.. )

n: Número de sectores a dividir

A: Nombre del campo a agrupar

B: Nombre del campo a ordenar

Análisis de requisitos: este requisito es muy simple: convierta el primer paso de la función de ventana de clasificación del requisito 5 en sectores. Tenga en cuenta que la condición del filtro de tiempo ha cambiado a enero de 2019.

Análisis de demanda: clasifique los 10 primeros, es decir, divida un **** en 10 grupos y tome el primer grupo. Entonces, dividamos los grupos:

Luego tomemos el primer grupo:

Nota: Las funciones de análisis de retraso y avance pueden eliminar, respectivamente, las primeras N filas del mismo campo en la misma consulta. Los datos (Lag) y las últimas N filas de datos (Lead) se tratan como columnas separadas.

En aplicaciones prácticas, si desea extraer la diferencia entre los datos de hoy y ayer en un campo determinado, entonces la aplicación de las funciones Lag y Lead es particularmente importante. Por supuesto, esta operación se puede lograr con autouniones de tablas, pero LAG y LEAD son declaraciones SQL más eficientes y concisas que las uniones izquierdas, derechas y otras autouniones.

lag(exp_str, offset, defval) over(parte por ......ordenar por ......)

lead(exp_str, offset, defval) over (partir por ...... ordenar por ......)

ejemplo de función lag():

ejemplo de función lead():

Análisis de requisitos: primero, debemos sacar el tiempo de pago de cada usuario de la tabla user_trade, colocar a cada usuario en una ventana, ordenar por tiempo de pago y eliminar la columna de compensación: lead(dt, 1, dt) over (partición por nombre de usuario orden por dt). Luego, según esta subconsulta, filtre los usuarios cuyo intervalo de tiempo sea superior a 100 días y calcule el número.

Nota: Si la función del analizador de compensación anterior se escribe como líder (dt, 1, dt), no es necesario agregar que dt no está vacío, porque cuando el valor predeterminado es 0, el intervalo de tiempo Ciertamente no cumple con la condición.

Análisis de requisitos:

Paso 1: este requisito consiste en utilizar dos tablas, user_trade y user_info. La primera registra el tiempo y el monto del pago, y la segunda registra la ciudad y el género. Realice una combinación izquierda en las dos primeras tablas según el nombre de usuario, luego elimine los campos correspondientes y use la función de ventana para agrupar y ordenar:

Los resultados de este paso son los siguientes:

Paso 2: De acuerdo con los resultados anteriores, saque el TOP3:

Análisis de requisitos:

Paso 1: Este requisito también utiliza dos tablas, user_refund y user_info. Primero sacamos el monto del reembolso y la marca del teléfono móvil de cada usuario del reembolso, y luego usamos la función de ventana para dividir y ordenar 25 se divide en 4 sectores:

Nota: La razón por la que se agrega WHERE dt. aquí no está vacío porque user_refund es una tabla de particiones y la tabla de particiones debe tener restricciones en los campos de partición; de lo contrario, Hive informará un error.

Paso 2: Seleccione los 25 primeros, que es el primer segmento:

Finalmente, agregue un comando para exportar los datos de resultados desde Hive:

Eso es todo Todo el contenido de esta lección. Completar todo el ejercicio es en realidad la mitad de la batalla. La función Ventana tiene cierto grado de dificultad, pero dominar el método, practicar más, aprender a desmontar los requisitos y hacerlo paso a paso puede reducir en gran medida la dificultad. Espero tener la oportunidad de utilizar una habilidad tan compleja en el futuro, ¡jaja~!