ClickHouse: Funções de Janela do Zero

Tutoriais
Pandas

ClickHouse é um sistema de gerenciamento de banco de dados altamente escalável, orientado a colunas e relacional, otimizado para cargas de trabalho analíticas. É um produto de código aberto desenvolvido pela Yandex, uma empresa de mecanismo de busca. Uma das principais características do ClickHouse é seu suporte a funções analíticas avançadas, incluindo funções de janela.

As funções de janela foram introduzidas pela primeira vez no final dos anos 90 pelo SQL Server e, desde então, tornaram-se uma característica padrão em muitos bancos de dados relacionais, incluindo o ClickHouse. Hoje, as funções de janela são uma ferramenta indispensável para analistas de dados e desenvolvedores e são amplamente utilizadas em muitas indústrias.

Essas funções, também conhecidas como funções analíticas, são uma classe de funções que executam cálculos com base em uma janela deslizante de linhas. São usadas para realizar vários tipos de análise em conjuntos de dados, como calcular totais acumulados, médias móveis e classificações. As funções de janela são uma ferramenta poderosa para análise de dados e podem simplificar significativamente a escrita de consultas complexas.

O ClickHouse suporta uma ampla gama de funções de janela, incluindo funções integradas para classificação, percentil, distribuição cumulativa, numeração de linhas e totais acumulados. Além disso, também suporta funções de janela definidas pelo usuário, que permitem aos usuários criar funções personalizadas para casos de uso específicos.

Neste artigo, apresentarei o conceito de funções de janela e fornecerei uma visão abrangente das funções de janela disponíveis no ClickHouse. Também fornecerei exemplos de como usar essas funções em cenários do mundo real. Este artigo é destinado a desenvolvedores experientes que já estão familiarizados com SQL e desejam aprender mais sobre funções de janela no ClickHouse.

Exemplos do Mundo Real de Uso de Funções de Janela

As funções de janela são uma ferramenta poderosa para análise de dados e são amplamente utilizadas em várias indústrias, como finanças, comércio eletrônico e saúde.

Análise Financeira

Uma das aplicações mais antigas de funções de janela foi na análise financeira. Na análise de mercado de ações, os desenvolvedores podem usar funções de janela para calcular médias móveis, totais em execução e mudanças percentuais. Por exemplo, calcular uma média móvel de 50 dias do preço de fechamento de uma ação é um caso de uso comum para funções de janela em finanças. Outro exemplo é calcular o total acumulado dos ganhos de uma empresa ao longo de um período de tempo.

E-commerce Analytics

No comércio eletrônico, as funções de janela podem analisar o comportamento do cliente e padrões de vendas. Os desenvolvedores podem usar funções de janela para calcular o total acumulado de vendas para cada produto, o ranking de produtos com base em suas vendas e a taxa de crescimento das vendas ao longo do tempo. Além disso, as funções de janela podem ser usadas para analisar o comportamento do cliente, calculando a frequência média de compras e o valor médio de compra de um cliente ao longo de um período de tempo.

Análise em Saúde

Funções do Windows em saúde podem analisar dados de pacientes, como sinais vitais, resultados de laboratório e uso de medicamentos. Por exemplo, desenvolvedores podem usar funções de janela para calcular a média móvel da frequência cardíaca de um paciente, o total acumulado das doses de medicamentos de um paciente e a classificação de pacientes com base em seus resultados de laboratório.

Esses são apenas alguns exemplos dos muitos cenários do mundo real onde os desenvolvedores podem usar funções de janela. A principal conclusão é que as funções de janela podem ser usadas para realizar análises avançadas em uma ampla variedade de conjuntos de dados e podem simplificar significativamente a escrita de consultas complexas.

Sintaxe das Funções de Janela no ClickHouse

No ClickHouse, as funções de janela são usadas na cláusula SELECT de uma consulta para realizar cálculos em um conjunto de linhas. A sintaxe básica para usar uma função de janela no ClickHouse é a seguinte:

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 analisar cada parte da sintaxe:

  1. [column_list]: Esta é a lista de colunas que você deseja retornar na consulta.
  2. [windows_function_name]([argument_list]): Este é o nome da função de janela que você deseja usar e a lista de argumentos para essa função.
  3. AS [alias_name]: Esta cláusula é opcional e é usada para dar um nome de alias ao resultado da função de janela.
  4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): Esta é a especificação do quadro de janela para a função de janela.
  • PARTITION BY [partition_column_list]: Esta cláusula é opcional e divide o conjunto de resultados em partições com base nos valores nas colunas especificadas.
  • ORDER BY [order_column_list]: Esta cláusula é obrigatória para especificar a ordem em que a função de janela processa as linhas.
  • ROWS [BETWEEN [start_offset] AND [end_offset]]: Esta cláusula é opcional e é usada para especificar o intervalo de linhas para a função de janela operar. O start_offset e end_offset podem ser inteiros positivos ou negativos ou valores especiais como UNBOUNDED PRECEDING ou CURRENT ROW.

Aqui está um exemplo de uso de uma função de janela no 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.

É importante notar que as funções de janela no ClickHouse só podem ser usadas na cláusula SELECT de uma consulta e não podem ser usadas na cláusula WHERE ou HAVING. Além disso, as funções de janela podem ser combinadas com outras funções, como funções agregadas, para realizar uma análise de dados ainda mais avançada.

Análise Financeira com Funções de Janela

Na indústria financeira, acompanhar o desempenho dos investimentos ao longo do tempo é fundamental para a tomada de decisões. As funções de janela no ClickHouse podem realizar uma análise sofisticada de dados financeiros, como cálculo de médias móveis e totais em execução.

Vamos considerar um cenário em que temos uma tabela de preços diários de ações para uma única ação. Nossa meta é calcular a média móvel de 50 dias do preço de fechamento e o total em execução do retorno diário do investimento.

Geração de Dados:

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.

Solicitação 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): Essa função window calcula a média móvel de 50 dias do preço de fechamento, considerando a média dos 50 registros anteriores ao registro atual e o próprio registro atual (ou menos, se o número de dias for inferior a 50), ordenados pela data. O frame da janela é especificado com ORDER BY date para definir a ordem em que a função window processa os registros e ROWS BETWEEN 49 PRECEDING AND CURRENT ROW para especificar o intervalo de registros sobre os quais a função window opera.
  2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: Essa função window calcula o total acumulado do retorno diário de investimento, somando os retornos diários, calculados como a diferença entre o preço de fechamento atual e o preço de fechamento anterior dividido pelo preço de fechamento anterior. A função lag é usada para recuperar o valor de um registro anterior na mesma partição, e o frame da janela é especificado com ORDER BY date para garantir que os retornos sejam calculados na ordem correta.

A saída da consulta retorna a data, o símbolo, o preço de fechamento, a média móvel de 50 dias e o total acumulado do retorno diário de investimento para o símbolo AAAA.

Com funções window no ClickHouse, analistas financeiros podem realizar análises sofisticadas de dados financeiros em tempo real e tomar decisões informadas com base nos resultados.

E-commerce Analytics With Windows Functions

Análise de dados de vendas no setor de comércio eletrônico é fundamental para entender o comportamento do cliente e tomar decisões de negócios sólidas. As funções de janela do ClickHouse podem realizar análise complexa de dados de comércio eletrônico, como calcular totais acumulados e classificar produtos por vendas.

Vamos imaginar que temos uma tabela de informações diárias de vendas para apenas um site de comércio eletrônico. Para classificar os produtos com base nas vendas totais, vamos calcular o total acumulado de vendas.

Geração de Dados:

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.

Solicitação 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): Essa função de janela calcula o total acumulado de vendas para cada produto, somando as vendas para cada linha, particionadas pelo nome do produto e ordenadas pela data. O frame de janela é especificado com PARTITION BY product_name para dividir os dados em partições com base no nome do produto, e ORDER BY date para especificar a ordem em que a função de janela processa as linhas.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): Essa função de janela calcula a classificação de cada produto dentro de sua categoria com base nas vendas totais. A função ROW_NUMBER gera um número único para cada linha dentro de uma partição, e o frame de janela é especificado com PARTITION BY product_category para dividir os dados em partições com base na categoria do produto, e ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC para ordenar os dados dentro de cada partição em ordem decrescente com base no total acumulado de vendas.

A saída da consulta retorna o nome do produto, a categoria do produto, o total acumulado de vendas e a classificação de cada produto com base em suas vendas totais dentro de sua categoria de produto.

Com as funções de janela no ClickHouse, analistas de comércio eletrônico podem realizar análise sofisticada de dados de vendas em tempo real e tomar decisões informadas com base nos resultados.

Análise de Saúde com Funções de Janela

Para o setor de saúde melhorar os resultados dos pacientes e tomar decisões sábias sobre o cuidado do paciente, a análise de dados do paciente é essencial. As funcionalidades de janela do ClickHouse oferecem capacidades avançadas de análise de dados de saúde, incluindo a capacidade de classificar pacientes de acordo com vários critérios e calcular totais acumulados.

Considere o seguinte cenário: temos uma tabela de dados de pacientes para um hospital que inclui demografia do paciente, histórico médico e tratamento atual. Queremos determinar o total acumulado de dias de hospitalização de cada paciente e classificá-los com base em seus dias totais de hospitalização.

Geração de Dados:

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.

Solicitação 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, calculei o total acumulado de dias gastos no hospital usando uma função de janela.

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): Essa função window calcula o total acumulado de dias de hospitalização para cada paciente, somando o número de dias entre a data de admissão e a data de alta para cada linha, particionado pelo ID do paciente e ordenado pela data de admissão. O frame da janela é especificado com PARTITION BY patient_id para dividir os dados em partições baseadas no ID do paciente e ORDER BY admission_date para especificar a ordem em que a função window processa as linhas.

Os resultados da consulta fornecem o ID do paciente, idade, gênero, condição e total acumulado de dias gastos no hospital para cada paciente.

Na segunda, e mais complexa, solicitação SQL, utilizarei funções window para classificar os pacientes pelo total de dias de hospitalização.

Solicitação 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: Essa função window atribui uma classificação única a cada paciente com base em seu total acumulado de dias de hospitalização. A função atribui uma classificação a cada linha com base na ordem especificada com ORDER BY running_total_days DESC, o que significa que os pacientes com o maior total acumulado de dias de hospitalização receberão a menor classificação. A função ROW_NUMBER() é uma função interna no ClickHouse que atribui um número único a cada linha dentro do frame de janela especificado.
  2. (SELECT ...): A consulta interna calcula o total acumulado de dias de hospitalização para cada paciente usando a função de janela SUM. O resultado da consulta interna é então usado como entrada para a consulta externa, onde a função de janela ROW_NUMBER é aplicada para classificar os pacientes por seus totais de dias de hospitalização.

Os resultados da consulta fornecem a classificação, idade, gênero, condição e total acumulado de dias gastos no hospital de cada paciente.

Aqui está um exemplo dos dados que esta consulta poderia gerar:


Para visualizar os resultados da consulta, você pode usar várias ferramentas de visualização de dados, como Matplotlib, Seaborn, Plotly, etc. em Python. Aqui está um exemplo de como você pode visualizar os resultados da consulta usando Matplotlib:

Python

  

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

# Estabelecer uma conexão com o banco de dados ClickHouse
engine = create_engine("clickhouse://:/")

# Executar a consulta SQL e armazenar os resultados em um DataFrame do Pandas
df = pd.read_sql_query("", engine)

# Plotar os resultados usando um 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()

Neste código, primeiro uso a função create_engine da biblioteca SQLAlchemy para estabelecer uma conexão com o banco de dados ClickHouse. A função read_sql_query é então usada para executar a consulta SQL e armazenar os resultados em um DataFrame do Pandas. Finalmente, o gráfico de barras é criado usando a função bar da biblioteca Matplotlib, onde o eixo x representa o ID do paciente, o eixo y representa o total acumulado de dias de hospitalização e a cor de cada barra representa a classificação do 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.

Conclusão

Finalmente, com o ClickHouse, as funções de janela são uma excelente ferramenta para análise de dados extensiva e procedimentos de agregação. Elas permitem que os desenvolvedores realizem cálculos complexos dentro de uma consulta, como totais acumulados, classificações e percentis, que normalmente exigiriam várias consultas ou até pré-processamento de dados. As funções de janela podem facilitar significativamente a análise e a agregação de dados, fornecendo um mecanismo conciso e rápido para realizar esses cálculos.

No entanto, tenha em mente que as funções de janela podem ser computacionalmente caras, especialmente com conjuntos de dados grandes. Isso pode ser minimizado utilizando índices adequados e construindo consultas de forma inteligente. No entanto, ainda é fundamental entender as consequências de desempenho das funções de janela e usá-las com moderação.

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