Funciones de ventana SQL en entrevistas de ciencia de datos proporcionadas por Airbnb, Netflix, Twitter y Uber

[ad_1]

Las funciones de ventana son un grupo de funciones que realizan cálculos en un rango de filas relacionadas con su fila actual. Se considera que tiene un nivel avanzado de SQL y, a menudo, se le pregunta en entrevistas de ciencia de datos. También se united states of america comúnmente en el trabajo para resolver muchos tipos diferentes de problemas. Recapitulemos los 4 tipos diferentes de funciones de ventana y expliquemos por qué y cuándo las usaría.

4 tipos de funciones de ventana
1. Funciones agregadas regulares
o Estos son agregados como AVG, MIN/MAX, Count, SUM
o Querrá usarlos para agregar sus datos y agruparlos por alguna otra columna como mes o año
2. Funciones de clasificación
o ROW_Range, RANK, RANK_DENSE
o Estas son funciones que le ayudan a organizar sus datos. Puede organizar todo su registro u organizarlo por grupos como mes o país
o Extremadamente útil para crear índices de clasificación dentro de grupos
3. Crear estadísticas
o Estos son excelentes cuando necesita generar estadísticas simples como NTILE (percentiles, cuartiles, medianas).
o Puede usar esto para todo su registro o por grupo
4. Manejo de datos de sequence temporales
o Una función de ventana muy común, especialmente cuando necesita calcular tendencias como un promedio móvil de mes a mes o una métrica de crecimiento
o LAG y Direct son las dos funciones que le permiten hacer esto.

1. Función agregada standard

Las funciones agregadas regulares son funciones como promedio, conteo, suma, mínimo/máximo aplicadas a las columnas. El objetivo es aplicar la función de agregación cuando desee aplicar agregaciones a diferentes grupos en el conjunto de datos, p. B. mes.

Esto es related al tipo de cálculo que se puede realizar con una función agregada, que encontraría en la cláusula Choose, pero a diferencia de las funciones agregadas normales, las funciones de ventana no agrupan varias filas en una sola fila de salida, se agrupan juntas o conservan su propia identidad, dependiendo de cómo los encuentres.
Ejemplo de promedio ():
Veamos un ejemplo de una función de ventana avg() implementada para responder una pregunta de análisis de datos. Puedes ver la pregunta y escribir el código en el siguiente enlace:
plataforma.stratascratch.com/codificación-pregunta?id=10302&python=

Este es un ejemplo perfecto de cómo usar una función de ventana y luego aplicar avg() a un grupo de meses. Aquí estamos tratando de calcular la distancia promedio por dólar por mes. Esto es difícil de hacer en SQL sin esta función de ventana. Aquí aplicamos la función de ventana avg() a la tercera columna, donde encontramos el valor promedio mes-año para cada mes-año en el conjunto de datos. Podemos usar esta métrica para calcular la diferencia entre el promedio mensual y el promedio de la fecha para cada fecha de consulta en la hoja de cálculo.

APRENDE MÁS SOBRE: Por qué deberías usar la función de video de Instagram

El código para implementar la función de ventana se vería así:

SELECCIONE a.ask for_date,
a.dist_to_expense,
AVG(a.dist_to_value) SOBRE(PARTICIÓN POR a.ask for_mnth) COMO avg_dist_to_charge
AFUERA
(ELEGIR *,
to_char(fecha_solicitud::fecha, ‘AAAA-MM’) AS mes_solicitud,
(distancia_al_viaje/costo_monetario) AS dist_al_costo
DESDE uber_request_logs) un
ORDEN POR fecha_solicitud

2. Funciones de clasificación
Las funciones de clasificación son una herramienta importante para un científico de datos. Siempre ordenan e indexan sus datos para comprender mejor qué filas de su conjunto de datos son las mejores. Las funciones de la ventana SQL le ofrecen 3 utilidades de clasificación: RANK(), DENSE_RANK(), ROW_Range(), según su caso de uso exacto. Estas funciones lo ayudan a organizar y enumerar sus datos en grupos según sus necesidades.
Rango () Ejemplo:
Echemos un vistazo a una función de ventana de clasificación de ejemplo para ver cómo podemos ordenar datos dentro de grupos usando funciones de ventana SQL. Siga este enlace de forma interactiva: platform.stratascratch.com/coding-dilemma?id=9898&python=

Aquí queremos encontrar los salarios más altos por departamento. Sin ventanas, no podemos encontrar fácilmente los 3 salarios principales, ya que esto solo nos da los 3 salarios principales en todos los departamentos, por lo que debemos ordenar los salarios por departamento uno por uno. Esto lo hace rank() y se divide por departamento. A partir de ahí, es muy fácil filtrar los 3 principales en todos los departamentos.

Aquí está el código para generar esta tabla. Puede copiar y pegar el editor de SQL en el enlace de arriba y ver el mismo resultado.

SELECCIONE departamento,
Salario,
RANK() SOBRE (PARTICIÓN POR un departamento
ORDEN POR a.salary DESC) COMO rank_id
AFUERA
(ELIGE Departamento, Salario
DESDE twitter_employee
GRUPO POR departamento, salario
ORDEN POR departamento, salario) a
ORDEN POR Departamento,
REF salario

APRENDE MÁS SOBRE: El rojo y el negro - Le Rouge et Le Black - Julien Sorel

3. NTIL
NTILE es una característica muy útil para las personas en los campos de análisis de datos, análisis de negocios y ciencia de datos. Muchas veces en su trabajo diario necesita generar estadísticas sólidas como cuartil, quintil, mediana, decil cuando necesita terminar con datos estadísticos y NTILE facilita la generación de estos resultados.

NTILE toma un argumento para la cantidad de contenedores (o, básicamente, en cuántos contenedores desea dividir sus datos) y luego crea esa cantidad de contenedores al dividir sus datos en esa cantidad de contenedores. Usted determina cómo se ordenan y dividen los datos si desea agrupaciones adicionales.

ejemplo NTILE(100)
En este ejemplo, aprenderemos a usar NTILE para categorizar nuestros datos en percentiles. Puede seguir el enlace de forma interactiva aquí: system.stratascratch.com/coding-issue?id=10303&python=

Lo que está tratando de hacer aquí es identificar el 5 por ciento outstanding de las reclamaciones en función de una puntuación que devuelve un algoritmo. Pero no puede simplemente encontrar el 5% outstanding y realizar un pedido porque desea encontrar el 5% top-quality por estado. Entonces, una forma de hacer esto es usar una función de clasificación NTILE() y luego PARTICIONAR por estado. Luego puede aplicar un filtro en la cláusula The place para obtener el 5% outstanding.

Aquí está el código para mostrar toda la tabla anterior. Puedes copiarlo y pegarlo en el enlace de arriba.

SELECCIONE número_política,
Condición,
costo_reclamo,
puntuación de fraude,
percentil
AFUERA
(ELEGIR *,
NTILE(100) SOBRE(PARTICIÓN POR estado
ORDEN POR Fraud_Score DESC) COMO Percentil
DESDE Fraud_Score) a
DONDE percentil <=5

4. Manejo de datos de series temporales

LAG y LEAD son dos funciones de ventana útiles para manejar datos de series de tiempo. La única diferencia entre LAG y LEAD es si desea tomar de filas anteriores o siguientes, casi como tomar muestras de datos pasados o futuros.

Puede usar LAG y LEAD para calcular el crecimiento mensual o los promedios móviles. Como científico de datos y analista de negocios, siempre está tratando con datos de series temporales y creando estas métricas de tiempo.

LAG() ejemplo:

En este ejemplo, queremos encontrar el porcentaje de crecimiento año tras año, que es una pregunta muy común que los científicos de datos y los analistas comerciales responden a diario. La descripción del problema, los datos y el editor de SQL se pueden encontrar en el siguiente enlace si desea intentar codificar la solución usted mismo: plataforma.stratascratch.com/codificación-pregunta?id=9637&python=

APRENDE MÁS SOBRE: ¿Por qué los especialistas en marketing social utilizan sitios de redes sociales?

Lo difícil de este problema es que los datos están configurados: debe usar el valor de la fila anterior en su métrica. Pero SQL no está diseñado para eso. SQL está diseñado para calcular lo que quieras, siempre que los valores estén en la misma fila. Entonces podemos usar la función de ventana lag() o lead() que toma las filas anteriores o siguientes y las inserta en su fila actual, lo que hace esta pregunta.

Aquí está el código para mostrar toda la tabla anterior. Puede copiar y pegar el código en el editor SQL en el enlace de arriba:

seleccionar año

anfitrión_del_año_actual,

host_año_anterior,

round(((host_año_actual – host_año_anterior)/(cast(anfitrión_año_anterior AS numérico))))*100) crecimiento_estimado

AFUERA

(seleccione el año,

anfitrión_del_año_actual,

LAG(anfitrión_año_actual, 1) SOBRE (ORDENAR POR año) COMO host_año_anterior

AFUERA

(ELIJA EXTRACTO (Año

DESDE host_since::fecha) AS año,

recuento (id) host_año_actual

DESDE airbnb_search_detalles

DONDE host_since NO ES NULO

GRUPO POR extracto (año

DESDE host_desde::fecha)

ORDEN POR año) t1) t2