ClickHouse: Funciones de Windows desde Cero

Tutoriales
Pandas

ClickHouse es un sistema de gestión de bases de datos altamente escalable, orientada a columnas y relacional, optimizado para cargas de trabajo analíticas. Es un producto de código abierto desarrollado por Yandex, una compañía de motor de búsqueda. Una de las características clave de ClickHouse es su soporte para funciones analíticas avanzadas, incluyendo funciones de ventana.

Las funciones de ventana se introdujeron por primera vez a fines de la década de 1990 por SQL Server, y desde entonces, se han convertido en una característica estándar en muchas bases de datos relacionales, incluido ClickHouse. Hoy en día, las funciones de ventana son una herramienta indispensable para analistas de datos y desarrolladores y se utilizan ampliamente en muchos sectores.

Estas funciones, también conocidas como funciones analíticas, son una clase de funciones que realizan cálculos basados en una ventana deslizante de filas. Se utilizan para realizar diversos tipos de análisis en conjuntos de datos, como calcular totales acumulados, promedios móviles y clasificaciones. Las funciones de ventana son una herramienta poderosa para el análisis de datos y pueden simplificar significativamente la escritura de consultas complejas.

ClickHouse soporta una amplia gama de funciones de ventana, incluyendo funciones integradas para clasificación, percentil de clasificación, distribución acumulativa, numeración de filas, y totales acumulados. Además, también admite funciones de ventana definidas por el usuario, lo que permite a los usuarios crear funciones personalizadas para casos de uso específicos.

En este artículo, presentaré el concepto de funciones de ventana y proporcionaré una visión general completa de las funciones de ventana disponibles en ClickHouse. También ofreceré ejemplos de cómo utilizar estas funciones en escenarios del mundo real. Este artículo está dirigido a desarrolladores experimentados que ya están familiarizados con SQL y desean aprender más sobre las funciones de ventana en ClickHouse.

Ejemplos del Mundo Real de Uso de Funciones de Ventana

Las funciones de ventana son una herramienta poderosa para el análisis de datos y se utilizan ampliamente en varios sectores, como finanzas, comercio electrónico y salud.

Análisis Financiero

Uno de los primeros usos de las funciones de ventana fue en el análisis financiero. En el análisis de mercados de valores, los desarrolladores pueden utilizar funciones de ventana para calcular promedios móviles, totales acumulados y cambios porcentuales. Por ejemplo, calcular un promedio móvil de 50 días del precio de cierre de una acción es un caso de uso común para las funciones de ventana en finanzas. Otro ejemplo es calcular el total acumulado de las ganancias de una empresa durante un período de tiempo.

E-commerce Analytics

En el comercio electrónico, las funciones de ventana pueden analizar el comportamiento del cliente y los patrones de ventas. Los desarrolladores pueden utilizar funciones de ventana para calcular el total acumulado de ventas de cada producto, el ranking de productos basado en sus ventas y el porcentaje de crecimiento de ventas en el tiempo. Además, las funciones de ventana pueden usarse para analizar el comportamiento del cliente calculando la frecuencia promedio de compra y el valor promedio de compra de un cliente durante un período de tiempo.

Análisis de Salud

Las funciones de Windows en el ámbito de la salud pueden analizar datos de pacientes, como signos vitales, resultados de laboratorio y uso de medicamentos. Por ejemplo, los desarrolladores pueden utilizar funciones de Windows para calcular el promedio móvil de la frecuencia cardíaca de un paciente, el total acumulado de dosis de medicamentos administradas a un paciente y el ranking de pacientes en función de sus resultados de laboratorio.

Estos son solo algunos ejemplos de los numerosos escenarios del mundo real donde los desarrolladores pueden emplear funciones de Windows. La conclusión clave es que las funciones de Windows pueden utilizarse para realizar análisis avanzados en una amplia gama de conjuntos de datos y pueden simplificar enormemente la redacción de consultas complejas.

Sintaxis de Funciones de Ventana en ClickHouse

En ClickHouse, las funciones de ventana se utilizan en la cláusula SELECT de una consulta para realizar cálculos sobre un conjunto de filas. La sintaxis básica para usar una función de ventana en ClickHouse es la siguiente:

SQL

  

SELECT
  [column_list],
  [windows_function_name]([argument_list])
    OVER ([PARTITION BY [partition_column_list]]
         [ORDER BY [order_column_list]]
         [ROWS [BETWEEN [start_offset] AND [end_offset]]])
  AS [alias_name]
FROM [table_name];

Vamos a desglosar cada parte de la sintaxis:

  1. [column_list]: Esta es la lista de columnas que deseas devolver en la consulta.
  2. [windows_function_name]([argument_list]): Este es el nombre de la función de ventana que deseas utilizar y la lista de argumentos para esa función.
  3. AS [alias_name]: Esta cláusula es opcional y se utiliza para asignar un nombre de alias al resultado de la función de ventana.
  4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): Esta es la especificación del marco de ventana para la función de ventana.
  • PARTITION BY [lista_de_columnas_de_partición]: Esta cláusula es opcional y divide el conjunto de resultados en particiones basadas en los valores en las columnas especificadas.
  • ORDER BY [lista_de_columnas_ordenadas]: Esta cláusula es obligatoria para especificar el orden en que la función ventana procesa las filas.
  • ROWS [BETWEEN [desplazamiento_inicial] AND [desplazamiento_final]]: Esta cláusula es opcional y se utiliza para especificar el rango de filas sobre las que la función ventana debe operar. El desplazamiento_inicial y desplazamiento_final pueden ser enteros positivos o negativos o valores especiales como UNBOUNDED PRECEDING o CURRENT ROW.

A continuación se muestra un ejemplo de uso de una función ventana en ClickHouse:

SQL

  

SELECT
  date,
  product_id,
  sales,
  SUM(sales) OVER (PARTITION BY product_id ORDER BY date) AS running_total
FROM sales_data;

I use the SUM windows function to calculate the running total of sales for each product, grouped by the product_id column. The window frame is specified with PARTITION BY product_id to divide the result set into partitions based on the product_id and ORDER BY date to specify the order in which the windows function processes the rows. The output of the windows function is given an alias name running_total.

Es importante tener en cuenta que las funciones ventana en ClickHouse solo pueden ser utilizadas en la cláusula SELECT de una consulta y no pueden ser utilizadas en la cláusula WHERE o HAVING. Además, las funciones ventana pueden combinarse con otras funciones, como funciones agregadas, para realizar análisis de datos aún más avanzados.

Análisis Financiero con Funciones Ventana

En la industria financiera, rastrear el rendimiento de las inversiones a lo largo del tiempo es fundamental para la toma de decisiones. Las funciones ventana en ClickHouse pueden realizar un análisis sofisticado de datos financieros, como el cálculo de promedios móviles y totales acumulados.

Consideremos un escenario en el que tenemos una tabla de precios diarios de una sola acción. Nuestro objetivo es calcular el promedio móvil de 50 días del precio de cierre y el total acumulado del rendimiento diario de la inversión.

Generación de Datos:

SQL

  

CREATE TABLE stock_prices (
  date Date,
  symbol String,
  open Float32,
  close Float32,
  high Float32,
  low Float32,
  volume UInt64
) ENGINE = MergeTree(date, (symbol, date), 8192);

INSERT INTO stock_prices
SELECT
  toDate('yyyy-MM-dd', d),
  'AAAA',
  rand(),
  rand(),
  rand(),
  rand(),
  rand() * 100000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table stock_prices to store daily stock prices for the symbol AAAA. I then insert randomly generated data into the table for the years 2022–2023.

Solicitud SQL:

SQL

  

SELECT
  date,
  symbol,
  close,
  AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS moving_average,
  SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100 AS running_return
FROM stock_prices
WHERE symbol = 'AAAA';

I use windows functions to perform financial analysis on the stock price data.

  1. AVG(close) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW): Esta función de ventana calcula el promedio móvil de 50 días del precio de cierre tomando el promedio de las 50 filas anteriores a la fila actual y la fila actual misma (o menos si el número de días es menor a 50), ordenadas por la fecha. El marco de ventana se especifica con ORDER BY date para indicar el orden en que la función de ventana procesa las filas y ROWS BETWEEN 49 PRECEDING AND CURRENT ROW para especificar el rango de filas sobre las que la función de ventana operará.
  2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Esta función de ventana calcula el total acumulado del rendimiento diario de la inversión sumando los rendimientos diarios, que se calculan como la diferencia entre el precio de cierre actual y el precio de cierre anterior dividido por el precio de cierre anterior. La función lag se utiliza para recuperar el valor de una fila anterior en la misma partición, y el marco de ventana se especifica con ORDER BY date para asegurar que los rendimientos se calculen en el orden correcto.

La salida de la consulta devuelve la fecha, el símbolo, el precio de cierre, el promedio móvil de 50 días y el total acumulado del rendimiento diario de la inversión para el símbolo AAAA.

Con las funciones de ventana en ClickHouse, los analistas financieros pueden realizar análisis sofisticados de datos financieros en tiempo real y tomar decisiones informadas basadas en los resultados.

E-commerce Analytics With Windows Functions

Analizar datos de ventas en la industria del comercio electrónico es fundamental para comprender el comportamiento del cliente y tomar decisiones comerciales sólidas. Las funciones de ventana de ClickHouse pueden realizar análisis complejos de datos de comercio electrónico, como calcular totales acumulados y clasificar productos por ventas.

Imaginemos que tenemos una tabla de información diaria de ventas para un solo sitio de comercio electrónico. Para clasificar los productos en función de las ventas totales, calcularemos el total acumulado de ventas.

Generación de Datos:

SQL

  

CREATE TABLE sales_data (
  date Date,
  product_name String,
  product_category String,
  sales UInt64
) ENGINE = MergeTree(date, (product_name, date), 8192);

INSERT INTO sales_data
SELECT
  toDate('yyyy-MM-dd', d),
  'Product ' || toString(intDiv(rand() * 100, 1)),
  'Category ' || toString(intDiv(rand() * 5, 1)),
  rand() * 1000
FROM generateDates('2022-01-01', '2023-02-10') d;

I create a table sales_data to store daily sales data for a single e-commerce store. I then insert randomly generated data into the table for the years 2022–2023.

Solicitud SQL:

SQL

  

SELECT
  product_name,
  product_category,
  SUM(sales) OVER (PARTITION BY product_name ORDER BY date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC) AS rank
FROM sales_data;

I use windows functions to perform e-commerce analytics on sales data.

  1. SUM(sales) OVER (PARTITION BY product_name ORDER BY date): Esta función de ventana calcula el total acumulado de ventas para cada producto sumando las ventas de cada fila, particionadas por el nombre del producto y ordenadas por la fecha. El marco de ventana se especifica con PARTITION BY product_name para dividir los datos en particiones basadas en el nombre del producto, y ORDER BY date para especificar el orden en que la función de ventana procesa las filas.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): Esta función de ventana calcula el rango de cada producto dentro de su categoría en función de sus ventas totales. La función ROW_NUMBER genera un número único para cada fila dentro de una partición, y el marco de ventana se especifica con PARTITION BY product_category para dividir los datos en particiones basadas en la categoría del producto, y ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC para ordenar los datos dentro de cada partición en orden descendente en función del total acumulado de ventas.

La salida de la consulta devuelve el nombre del producto, la categoría del producto, el total acumulado de ventas y el rango de cada producto en función de sus ventas totales dentro de su categoría de producto.

Con las funciones de ventana en ClickHouse, los analistas de comercio electrónico pueden realizar análisis sofisticados de datos de ventas en tiempo real y tomar decisiones informadas basadas en los resultados.

Análisis de Cuidados de Salud con Funciones de Ventana

Para que el sector de la salud mejore los resultados de los pacientes y tome decisiones sabias sobre el cuidado de los pacientes, es esencial el análisis de los datos de los pacientes. Las características de ventana de ClickHouse ofrecen capacidades avanzadas de análisis de datos de salud, incluyendo la capacidad de clasificar a los pacientes según diversos criterios y calcular totales acumulados.

Considere el siguiente escenario: tenemos una tabla de datos de pacientes para un hospital que incluye la demografía del paciente, su historial médico y el tratamiento actual. Intentamos determinar el total acumulado de días de hospitalización de cada paciente y clasificarlos según sus días totales de hospitalización.

Generación de Datos:

SQL

  

CREATE TABLE patient_data (
  admission_date Date,
  discharge_date Date,
  patient_id String,
  age UInt16,
  gender String,
  condition String
) ENGINE = MergeTree(admission_date, (patient_id, admission_date), 8192);

INSERT INTO patient_data
SELECT
  toDate('yyyy-MM-dd', d1),
  toDate('yyyy-MM-dd', d2),
  'Patient ' || toString(intDiv(rand() * 10000, 1)),
  rand() % 90 + 10,
  if(rand() % 2 = 0, 'Male', 'Female'),
  'Condition ' || toString(intDiv(rand() * 100, 1))
FROM generateDates('2022-01-01', '2023-02-10') d1
JOIN generateDates('2022-01-01', '2023-02-10') d2 ON d1 <= d2;

I create a table patient_data to store patient data for a hospital. I then inserted randomly generated data into the table for the years 2022–2023. Each row represents a patient’s hospitalization, including the admission date, discharge date, patient ID, age, gender, and medical condition.

Solicitud SQL #1:

SQL

  

SELECT
  patient_id,
  age,
  gender,
  condition,
  SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
FROM patient_data;

Para cada paciente, calculé el total acumulado de días en el hospital utilizando una función de ventana.

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): Esta función ventana calcula el total acumulado de días de hospitalización para cada paciente sumando el número de días entre la fecha de admisión y la fecha de alta para cada fila, particionado por el ID del paciente y ordenado por la fecha de admisión. El marco de la ventana se especifica con PARTITION BY patient_id para dividir los datos en particiones basadas en el ID del paciente y ORDER BY admission_date para especificar el orden en que la función ventana procesa las filas.

Los resultados de la consulta proporcionan el ID del paciente, la edad, el género, la condición y el total acumulado de días pasados en el hospital para cada paciente.

En la segunda solicitud SQL más compleja, utilizaré funciones ventana para clasificar a los pacientes por sus días totales de hospitalización.

Solicitud SQL #2:

SQL

  

SELECT
  patient_id,
  age,
  gender,
  condition,
  running_total_days,
  ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank
FROM (
  SELECT
    patient_id,
    age,
    gender,
    condition,
    SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date) AS running_total_days
  FROM patient_data
)

  1. ROW_NUMBER() OVER (ORDER BY running_total_days DESC) AS rank: Esta función ventana asigna un rango único a cada paciente basado en su total acumulado de días de hospitalización. La función asigna un rango a cada fila según el orden especificado con ORDER BY running_total_days DESC, lo que significa que los pacientes con el total acumulado más alto de días de hospitalización recibirán el rango más bajo. La función ROW_NUMBER() es una función incorporada en ClickHouse que asigna un número único a cada fila dentro del marco de ventana especificado.
  2. (SELECT ...): La consulta interna calcula el total acumulado de días de hospitalización para cada paciente utilizando la función de ventana SUM. El resultado de la consulta interna se utiliza luego como entrada para la consulta externa, donde se aplica la función de ventana ROW_NUMBER para clasificar a los pacientes por sus días totales de hospitalización.

Los resultados de la consulta proporcionan el rango, la edad, el género, la condición y el total acumulado de días que cada paciente ha pasado en el hospital.

Aquí hay un ejemplo de los datos que podría generar esta consulta:


Para visualizar los resultados de la consulta, puedes utilizar varias herramientas de visualización de datos como Matplotlib, Seaborn, Plotly, etc. en Python. Aquí hay un ejemplo de cómo puedes visualizar los resultados de la consulta utilizando Matplotlib:

Python

  

import matplotlib.pyplot as plt
import pandas as pd
from sqlalchemy import create_engine

# Establecer una conexión con la base de datos ClickHouse
engine = create_engine("clickhouse://:/")

# Ejecutar la consulta SQL y almacenar los resultados en un DataFrame de Pandas
df = pd.read_sql_query("", engine)

# Graficar los resultados utilizando un gráfico de barras
plt.bar(df['patient_id'], df['running_total_days'], color=df['rank'])
plt.xlabel("Patient ID")
plt.ylabel("Running Total of Hospitalization Days")
plt.title("Healthcare Analytics with Windows Functions in ClickHouse")
plt.show()

En este código, primero utilizo la función create_engine de la biblioteca SQLAlchemy para establecer una conexión con la base de datos ClickHouse. La función read_sql_query se utiliza luego para ejecutar la consulta SQL y almacenar los resultados en un DataFrame de Pandas. Finalmente, se crea el gráfico de barras utilizando la función bar de la biblioteca Matplotlib, donde el eje x representa el ID del paciente, el eje y representa el total acumulado de días de hospitalización, y el color de cada barra representa el rango del paciente.

I successfully used ClickHouse’s windows functions in those examples to evaluate healthcare data and rank patients based on their total hospitalization days. This analysis can uncover patterns and trends in patient data, which can help to inform clinical decision-making and improve patient outcomes.

Conclusión

Finalmente, con ClickHouse, las funciones de ventana son una excelente herramienta para el análisis de datos extenso y procedimientos de agregación. Permiten a los desarrolladores realizar cálculos complejos dentro de una consulta, como totales acumulados, clasificación y percentiles, que normalmente requerirían numerosas consultas o incluso preprocesamiento de datos. Las funciones de ventana pueden facilitar considerablemente el análisis y la agregación de datos al proporcionar un mecanismo conciso y rápido para realizar estos cálculos.

Sin embargo, tenga en cuenta que las funciones de ventana pueden ser computacionalmente costosas, especialmente con conjuntos de datos grandes. Esto puede minimizarse utilizando índices adecuados y construyendo consultas de manera inteligente. Sin embargo, sigue siendo fundamental comprender las consecuencias de rendimiento de las funciones de ventana y usarlas con moderación.

Source:
https://dzone.com/articles/clickhouse-windows-functions-from-scratch