Notas del curso de análisis de datos: función de 20 ventanas de Hive Core Skills

Hola a todos. En esta lección, aprenderemos la parte más difícil de las habilidades principales de Hive: la función de ventana. Hemos aprendido algunas funciones de ventana antes cuando aprendimos MySQL, pero solo aprendimos tres funciones de ventana de clasificación. En esta lección, aprenderemos más sobre las funciones de ventana, incluidos los cálculos de acumulación, la clasificación de particiones, la clasificación de sectores y el análisis de desplazamiento.

Antes del aprendizaje formal, debemos aclarar la diferencia entre funciones de ventana y agrupación. Son funcionalmente similares pero 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, la tabla original tiene 10 filas de datos, pero después de dividirse en dos grupos, solo quedan dos filas y la función de ventana aún devuelve diez filas de datos.

2. La agrupación solo puede consultar campos agrupados, incluidos los campos de agrupación (nombres de grupo) y los campos de funciones agregadas. La función de ventana no tiene restricciones en los campos de consulta, es decir, puede consultar cualquier campo en la tabla original, así como una columna de nuevos valores agregados por la función de ventana.

Bien, ahora entremos al mundo de las funciones de ventana~

El contenido principal de esta lección:

1. Cálculo acumulativo de funciones de ventana

p>

(1) suma(…)sobre(…)

(2) promedio(…)sobre(…)

③Resumen gramatical

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

(1) Número de fila ()

②Clasificación ()

(3) Clasificación densa ()

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

(1) n extremos (...)

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

5 .Clave ejercicios

Al realizar informes, muchas veces te encuentras con el cálculo del valor acumulado a partir de un determinado mes, lo que normalmente se puede lograr mediante funciones en EXCEL.

Entonces, ¿cómo calcular este valor acumulativo en HiveSQL? ¡Eso es usar la función de ventana!

Algunas notas sobre la función de ventana:

Análisis de requisitos: dado que queremos acumular por mes, primero debemos convertir el tiempo de cada transacción en 2018 a meses y proceder a la agrupación y agregación Calcule para obtener la tabla de resumen del monto del pago mensual en 2018 y luego use la función de ventana para realizar cálculos acumulativos basados ​​​​en esta tabla.

Tabla de resumen del monto del pago mensual de 2018:

Luego use la función de ventana para la acumulación mensual:

Resumen anual.

Este requisito tiene un requisito más que el requisito 1 que es el resumen anual. Luego solo necesitamos agregar un campo de año a la última subconsulta requerida.

Descripción:

La partición por en 1 y superiores desempeña el papel de agrupar datos en la ventana. 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 en cada ventana.

Si no agrega particiones por año, los resultados en ejecución simplemente se agruparán por mes:

2. ¿Cuál es el orden de acumulación, ASC ascendente y desc descendente? El valor es orden ascendente.

Cuando observa las acciones, a menudo ve este tipo de gráfico de líneas K. Este tipo de gráfico de líneas K se utiliza a menudo como gráfico de tendencias para los promedios móviles de 7 y 30 días. ¿Cómo calcular la media móvil usando la función de ventana?

Análisis de requisitos: este requisito requiere el monto de pago promedio móvil de los últimos tres meses por mes. Aquí se necesita un nuevo punto de conocimiento. Después de que la función de ventana promeda el orden en un mes, agregue una oración "Filas entre las 2 filas anteriores y actuales" para establecer el rango para calcular el promedio móvil. Esta oración debe incluir esta línea y las dos líneas anteriores. El método de escritura de otras partes es similar a los requisitos anteriores. Primero, tome la suma de los montos de los pagos mensuales para 2018, luego use una función de ventana para encontrar el promedio móvil.

Nota:

suma(…A…)sobre(partición por…B…orden por…C…línea entre…D1…y…D2…)

avg(...A...)sobre (partición por...B...ordenar por...C...fila entre d1...y...D2...)

Respuesta: El nombre del campo a procesar

p>

b: Nombre del campo de agrupación

c: Nombre del campo de clasificación

d: Rango de filas calculadas

Fila anterior independiente y la línea actual

, incluida esta línea y todas las líneas anteriores.

La línea entre la línea actual y las siguientes líneas independientes

: incluye esta línea y todas las líneas siguientes.

Las líneas entre las primeras 3 líneas y la línea actual

- incluyendo la línea interior y las primeras tres líneas.

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

——Desde las primeras tres líneas hasta la siguiente línea (5 líneas)

max( ...)sobre (partición por... orden por... línea entre... y...)

min (...) sobre (partición por... orden por.. línea entre... y... )

Número de filas (), rango (), rango de densidad ()

Uso: La función de estas tres funciones es devolver. el número de clasificación de la regla correspondiente.

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

rango() sobre (partición por...A. ..ordenar por...B...)

clasificación() sobre (partición por...A...ordenar por...B...)

p>

dense_rank()over(partición por...A...ordenar por...B...)

a: Nombre del campo de agrupación

b: Ordenar nombre de campo

p>

Nota: ¡No hay nombres de campo entre paréntesis para estas tres funciones!

Número_fila: Generará un número de serie para cada fila de registros encontrados por la consulta, y estos registros se ordenarán secuencialmente sin duplicación.

Rankings y Rankings. Dense_rank: Dentro de cada grupo, ranking() es un ranking de salto, cuando hay dos primeros, el tercero sigue, mientras que densa_rank() es un ranking continuo, cuando hay dos primeros, el segundo sigue.

Ejercicio de ejemplo:

Familiarícese con la estructura de la tabla de user_trade nuevamente:

Análisis de requisitos: primero limite el rango de tiempo, luego agrupe por nombre_usuario, seleccione nombre_usuario después de agrupar, Calcule la cantidad de categorías_productos para cada usuario (recuerde distinguir los duplicados) y luego use la función de ventana para ordenar la cantidad de categorías_productos. Por supuesto, el método de clasificación que elija dependerá de sus necesidades específicas. Aquí, podemos probar los resultados de tres maneras:

Nota: El campo ordenar por en la función de ventana no puede cambiarse por el campo en selección, porque ambos se ejecutan al mismo tiempo.

Análisis de requisitos: primero use la función de ventana para calcular y ordenar el monto total de pago de cada usuario en 2019, y luego use esto como una subconsulta para extraer los nombres de usuario, los montos totales de pago y las clasificaciones de 10. 20 y 30. Orden de clasificación. Las empresas generalmente usan densa_rank para ordenar, por lo que usamos densa_rank directamente aquí.

Ranking del monto total de pago por usuario en 2019:

Usuarios cuyo monto de pago ocupó el puesto 10, 20 y 30 en 2019:

Partición basada en... A... Ordenar por...B...)

n: Número de porciones divididas.

a: Nombre del campo de agrupación

b: Nombre del campo de clasificación

Análisis de requisitos: este requisito es muy simple, simplemente cambie el primer paso en el requisito 5 La clasificación La función de la ventana se puede cambiar a cortar. Tenga en cuenta que el filtro de tiempo cambia a enero de 2019.

Análisis de demanda: el 10% superior, es decir, un * * * se divide en 10 grupos y se toma 1 grupo.

Por lo tanto, agrupemos primero:

Luego mire el primer grupo:

Explicación: Las funciones de análisis de Lag y Lead pueden extraer las primeras n filas de datos (Lag) y las últimas n de el mismo campo Datos de fila (cliente principal) como una columna separada en la misma consulta.

En aplicaciones prácticas, si desea calcular 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 la tabla, pero LAG y LEAD son más eficientes que las autouniones como la unión izquierda y la unión derecha, y las declaraciones SQL son más concisas.

retraso(exp_str,offset,defval)sobre(partición por...ordenar por...)

avance(exp_str,offset,defval)sobre(partición por... ..., ordenar por...)

Ejemplo de función lag():

Ejemplo de función lead():

Análisis de requisitos: primero, sacar cada elemento de la tabla user_trade Para el tiempo de pago de cada usuario, coloque a cada usuario en una ventana, ordene por tiempo de pago y elimine la columna de compensación: liderar (dt, 1, dt) sobre (partición por usuario _ nombre ordenar por dt). Luego, según la subconsulta, filtre los usuarios cuyo intervalo de tiempo sea superior a 100 días y calcule el número.

Nota: Si la función de análisis de migración anterior se escribe como líder (dt, 1, dt), no es necesario agregar el siguiente dt no es nulo, porque si hay un valor predeterminado, el intervalo es 0, que definitivamente no cumple con las condiciones.

Análisis de requisitos:

Paso 1: Se utilizan dos tablas user_trade y user_info para este requisito. El primero toma el tiempo y el monto del pago, y el segundo toma la ciudad y el género. Primero, las dos tablas se dejan unidas según el nombre de usuario, y los campos correspondientes se extraen y ordenan mediante la función de ventana:

Los resultados de ejecución de este paso son los siguientes:

Paso 2: Saque 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, usamos la función de ventana para dividir y ordenar, y dividimos el 25% en cuatro partes:

Nota: La razón por la cual WHERE dt no es nulo Se agrega aquí Debido a que user_refund es una tabla de particiones, la tabla de particiones debe limitar los campos de partición; de lo contrario, Hive informará un error.

Paso 2: Seleccione el 25% superior, que es la primera película:

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

Esto Eso es todo por la lección. Después de toda la práctica, realmente perdí la mitad de mi vida. La función de ventana es realmente difícil, pero dominar el método, practicar más, aprender a desmontar los requisitos y hacerlo paso a paso puede reducir significativamente la dificultad. Espero poder utilizar habilidades tan complejas en el futuro, ¡jaja~!