ClickHouse: 처음부터 배우는 Windows 함수

튜토리얼
Pandas

ClickHouse는 분석 작업을 위해 최적화된 고도로 확장 가능한 컬럼 지향적 관계형 데이터베이스 관리 시스템입니다. 검색 엔진 회사인 Yandex에서 개발한 오픈소스 제품입니다. ClickHouse의 핵심 기능 중 하나는 고급 분석 기능을 지원한다는 것으로, 이 중에서도 윈도우 함수를 포함합니다.

윈도우 함수는 1990년대 후반 SQL Server에서 처음 소개되었으며, 그 이후 많은 관계형 데이터베이스에서 표준 기능으로 자리 잡았습니다. 이 중에는 ClickHouse도 포함됩니다. 현재 윈도우 함수는 데이터 분석가와 개발자에게 필수적인 도구로 사용되며 다양한 산업에서 널리 활용되고 있습니다.

이러한 함수는 분석 함수라고도 하며, 슬라이딩 윈도우의 행 집합을 기반으로 계산을 수행하는 함수의 한 부류입니다. 런닝 토탈, 이동 평균, 순위 등 데이터 세트에 대한 다양한 유형의 분석을 수행하는 데 사용됩니다. 윈도우 함수는 데이터 분석을 위한 강력한 도구로서 복잡한 쿼리를 작성하는 데 큰 도움이 됩니다.

ClickHouse는 순위, 백분위 순위, 누적 분포, 행 번호, 런닝 토탈을 포함한 다양한 윈도우 함수를 지원합니다. 또한, 사용자가 특정 사용 사례를 위한 사용자 정의 함수를 생성할 수 있는 사용자 정의 윈도우 함수도 지원합니다.

이 글에서는 윈도우 함수의 개념을 소개하고 ClickHouse에서 사용 가능한 윈도우 함수에 대한 포괄적인 개요를 제공합니다. 또한 이러한 함수를 실제 상황에서 어떻게 사용하는지 예시를 제공합니다. 이 글은 SQL에 익숙하고 ClickHouse의 윈도우 함수에 대해 더 알고 싶어하는 경험 많은 개발자를 대상으로 합니다.

윈도우 함수 사용 실제 사례

윈도우 함수는 데이터 분석을 위한 강력한 도구로서 금융, 전자상거래, 의료 등 다양한 산업에서 널리 사용되고 있습니다.

금융 분석

윈도우 함수의 가장 초기의 응용 중 하나는 금융 분석이었습니다. 주식 시장 분석에서 개발자들은 윈도우 함수를 사용하여 이동 평균, 누적 합계, 백분율 변화를 계산할 수 있습니다. 예를 들어, 주식의 종가에 대한 50일 이동 평균을 계산하는 것은 금융에서 윈도우 함수의 일반적인 사용 사례입니다. 또 다른 예로는 회사의 수익을 일정 기간 동안 누적 합계로 계산하는 것입니다.

E-commerce Analytics

전자상거래에서 윈도우 함수는 고객 행동과 판매 패턴을 분석할 수 있습니다. 개발자들은 윈도우 함수를 사용하여 각 제품의 누적 판매 합계, 판매를 기준으로 한 제품 순위, 시간에 따른 판매 성장률을 계산할 수 있습니다. 또한, 윈도우 함수는 고객의 평균 구매 빈도와 일정 기간 동안의 평균 구매 가치를 계산하여 고객 행동을 분석하는 데 사용될 수 있습니다.

의료 분석

Windows 기능은 의료 분야에서 환자 데이터를 분석하는 데 사용될 수 있습니다. 예를 들어, 생명 양상, 실험실 결과, 약물 사용 등이 있습니다. 개발자는 Windows 함수를 사용하여 환자의 심박수 이동 평균, 환자의 약물 투여량 누적 합계, 실험실 결과에 따른 환자 순위를 계산할 수 있습니다.

이는 개발자가 Windows 함수를 사용할 수 있는 많은 실제 시나리오 중 몇 가지에 불과합니다. 핵심 요점은 Windows 함수가 다양한 유형의 데이터 세트에 대한 고급 분석을 수행하는 데 사용될 수 있으며 복잡한 쿼리 작성을 크게 간소화할 수 있다는 것입니다.

ClickHouse에서의 Windows 함수 구문

ClickHouse에서 Windows 함수는 쿼리의 SELECT 절에서 일련의 행에 대한 계산을 수행하는 데 사용됩니다. ClickHouse에서 Windows 함수를 사용하는 기본 구문은 다음과 같습니다:

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];

구문의 각 부분을 살펴보겠습니다:

  1. [column_list]: 쿼리에서 반환하려는 열 목록입니다.
  2. [windows_function_name]([argument_list]): 사용하려는 Windows 함수의 이름과 해당 함수에 대한 인수 목록입니다.
  3. AS [alias_name]: 이 절은 선택 사항이며 Windows 함수의 출력에 별칭 이름을 지정하는 데 사용됩니다.
  4. OVER ([PARTITION BY [partition_column_list]] [ORDER BY [order_column_list]] [ROWS [BETWEEN [start_offset] AND [end_offset]]]): 이는 Windows 함수에 대한 윈도우 프레임을 지정합니다.
  • PARTITION BY [partition_column_list]: 이 절은 선택적이며, 지정된 컬럼의 값을 기준으로 결과 집합을 파티션으로 나눕니다.
  • ORDER BY [order_column_list]: 이 절은 필수적이며, 윈도우 함수가 행을 처리하는 순서를 지정합니다.
  • ROWS [BETWEEN [start_offset] AND [end_offset]]: 이 절은 선택적이며, 윈도우 함수가 작동할 행 범위를 지정하는 데 사용됩니다. start_offset 및 end_offset은 양수 또는 음수 정수이거나 UNBOUNDED PRECEDING 또는 CURRENT ROW와 같은 특수 값일 수 있습니다.

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.

ClickHouse의 윈도우 함수는 쿼리의 SELECT 절에서만 사용할 수 있으며, WHERE 또는 HAVING 절에서는 사용할 수 없다는 점에 유의해야 합니다. 또한, 윈도우 함수는 집계 함수와 같은 다른 함수와 결합하여 보다 고급 데이터 분석을 수행할 수 있습니다.

윈도우 함수를 활용한 금융 분석

금융 업계에서는 투자의 성과를 시간에 따라 추적하는 것이 의사 결정을 위해 중요합니다. ClickHouse의 윈도우 함수를 사용하면 이동 평균과 누적 합계를 계산하는 등 금융 데이터의 정교한 분석을 수행할 수 있습니다.

단일 주식의 일일 주가 테이블이 있다고 가정해 보겠습니다. 우리의 목표는 종가의 50일 이동 평균과 투자 수익률의 누적 합계를 계산하는 것입니다.

데이터 생성:

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.

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): 이 창 함수는 날짜를 기준으로 현재 행과 이전 행 중 50행의 평균을 계산하여 50일 이동 평균을 계산합니다(50일이 아닌 경우 일 수가 50보다 적은 경우 더 적은 행 수). 창 함수가 행을 처리하는 순서를 지정하기 위해 ORDER BY date가 지정되고, ROWS BETWEEN 49 PRECEDING AND CURRENT ROW는 창 함수가 작동할 행 범위를 지정합니다.
  2. SUM((close - lag(close) OVER (ORDER BY date)) / lag(close) OVER (ORDER BY date)) * 100: 이 창 함수는 일일 수익률의 누적 합계를 계산합니다. 이는 현재 종가와 이전 종가의 차이를 이전 종가로 나눈 값을 합산하여 계산됩니다. lag 함수는 동일한 파티션 내의 이전 행 값을 검색하는 데 사용되며, ORDER BY date는 수익률이 올바른 순서로 계산되도록 창 프레임을 지정합니다.

쿼리의 출력은 날짜, 심볼, 종가, 50일 이동 평균 및 일일 수익률의 누적 합계를 심볼 AAAA에 대해 반환합니다.

ClickHouse의 창 함수를 사용하면 재무 분석가는 실시간으로 재무 데이터에 대한 정교한 분석을 수행하고 결과를 기반으로 판단을 내릴 수 있습니다.

E-commerce Analytics With Windows Functions

e상점 산업에서 판매 데이터를 분석하는 것은 고객 행동을 이해하고 합리적인 비즈니스 결정을 내리는 데 중요합니다. ClickHouse의 Windows 함수를 사용하면 러닝 토탈 계산 및 판매량에 따른 제품 순위 매기기와 같은 복잡한 전자상거래 데이터 분석을 수행할 수 있습니다.

단일 전자상거래 사이트의 일일 판매 정보 테이블이 있다고 가정해 보겠습니다. 총 판매량을 기준으로 제품을 순위 매기려면 판매 러닝 토탈을 계산해야 합니다.

데이터 생성:

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.

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): 이 Windows 함수는 제품별로 판매량을 합산하여 각 제품의 러닝 토탈 판매를 계산합니다. 각 행의 판매량을 합산하고, 제품 이름별로 데이터를 분할하고, 날짜별로 정렬합니다. 윈도우 프레임은 PARTITION BY product_name으로 제품 이름별로 데이터를 분할하고, ORDER BY date로 윈도우 함수가 행을 처리하는 순서를 지정합니다.
  2. ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC): 이 Windows 함수는 카테고리 내에서 총 판매량을 기준으로 각 제품의 순위를 계산합니다. ROW_NUMBER 함수는 각 파티션 내의 각 행에 고유한 번호를 생성하며, 윈도우 프레임은 PARTITION BY product_category로 제품 카테고리별로 데이터를 분할하고, ORDER BY SUM(sales) OVER (PARTITION BY product_name ORDER BY date) DESC로 각 파티션 내의 데이터를 러닝 토탈 판매량을 기준으로 내림차순으로 정렬합니다.

쿼리 출력은 제품 이름, 제품 카테고리, 판매 누적 합계 및 제품 카테고리 내에서 총 판매량을 기준으로 각 제품의 순위를 반환합니다.

ClickHouse의 창 함수를 사용하면 전자상거래 분석가가 실시간으로 정교한 판매 데이터 분석을 수행하고 결과에 따라 판단하여 의사 결정을 내릴 수 있습니다.

의료 분석과 창 기능

의료 분야에서 환자 결과를 개선하고 환자 건강 관리에 대한 현명한 결정을 내리기 위해서는 환자 데이터 분석이 필수적입니다. ClickHouse의 창 기능은 다양한 기준에 따라 환자를 평가하고 누적 합계를 계산할 수 있는 고급 의료 데이터 분석 기능을 제공합니다.

다음과 같은 시나리오를 고려해 보십시오. 병원의 환자 데이터 테이블이 있으며, 이 테이블에는 환자 인구 통계, 의료 이력 및 현재 치료 정보가 포함되어 있습니다. 우리는 각 환자의 입원 일수 누적 합계를 파악하고 전반적인 입원 일수를 기준으로 그들을 순위 지정하려고 합니다.

데이터 생성:

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.

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;

각 환자에 대해 창 함수를 사용하여 병원에서 보낸 날짜의 누적 합계를 계산했습니다.

SUM(datediff(discharge_date, admission_date)) OVER (PARTITION BY patient_id ORDER BY admission_date): 이 윈도우 함수는 각 환자의 입원일과 퇴원일 사이의 일수를 합산하여 각 환자별로 병원 입원 기간의 누적 합계를 계산합니다. 환자 ID로 데이터를 분할하고 입원일로 정렬하여 윈도우 함수가 행을 처리하는 순서를 지정합니다. PARTITION BY patient_id로 환자 ID를 기준으로 데이터를 분할하고 ORDER BY admission_date로 정렬합니다.

쿼리 결과는 각 환자의 환자 ID, 나이, 성별, 상태 및 병원에서 보낸 누적 일 수를 제공합니다.

두 번째, 복잡한 SQL 요청에서는 환자들을 총 입원 일 수로 순위를 매길 것입니다.

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: 이 윈도우 함수는 환자의 누적 입원 일 수를 기준으로 각 환자에게 고유한 순위를 할당합니다. ORDER BY running_total_days DESC로 지정된 순서에 따라 각 행에 순위를 할당하므로, 가장 많은 누적 입원 일 수를 가진 환자가 가장 낮은 순위를 받습니다. ROW_NUMBER() 함수는 ClickHouse에서 내장된 함수로, 지정된 윈도우 프레임 내에서 각 행에 고유한 번호를 할당합니다.
  2. (SELECT ...): 내부 쿼리는 SUM 창 함수를 사용하여 각 환자의 입원 일수 누적합을 계산합니다. 내부 쿼리의 결과는 외부 쿼리에서 환자들을 총 입원 일수로 순위를 매기는 ROW_NUMBER 창 함수에 입력으로 사용됩니다.

쿼리 결과는 각 환자의 순위, 나이, 성별, 상태 및 병원에서 보낸 일수의 누적합을 제공합니다.

이 쿼리가 생성할 수 있는 데이터의 샘플은 다음과 같습니다.


쿼리 결과를 시각화하기 위해 Python의 Matplotlib, Seaborn, Plotly 등과 같은 다양한 데이터 시각화 도구를 사용할 수 있습니다. 다음은 Matplotlib을 사용하여 쿼리 결과를 시각화하는 방법의 예입니다.

Python

  

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

# ClickHouse 데이터베이스에 연결 설정
engine = create_engine("clickhouse://:/")

# SQL 쿼리 실행 및 결과를 Pandas DataFrame에 저장
df = pd.read_sql_query("", engine)

# 막대 그래프로 결과 플로팅
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()

이 코드에서는 SQLAlchemy 라이브러리의 create_engine 함수를 사용하여 ClickHouse 데이터베이스에 연결을 설정합니다. read_sql_query 함수를 사용하여 SQL 쿼리를 실행하고 결과를 Pandas DataFrame에 저장합니다. 마지막으로 Matplotlib 라이브러리의 bar 함수를 사용하여 막대 그래프를 생성합니다. 여기서 x축은 환자 ID를, y축은 입원 일수의 누적합을, 각 막대의 색상은 환자의 순위를 나타냅니다.

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.

결론

마지막으로, ClickHouse를 사용하면 창 함수는 광범위한 데이터 분석 및 집계 절차를 위한 훌륭한 도구입니다. 개발자가 쿼리 내에서 복잡한 계산을 수행할 수 있도록 해주므로 일반적으로 여러 쿼리 또는 데이터 사전 처리가 필요한 누적 합계, 순위 지정 및 백분위수 등이 포함됩니다. 창 함수는 이러한 계산을 수행하는 간결하고 빠른 메커니즘을 제공하여 데이터 분석 및 집계를 훨씬 쉽게 만들 수 있습니다.

그러나 창 함수가 계산 비용이 많이 들 수 있으며 특히 많은 양의 데이터셋에서 더 그럴 수 있다는 점에 유의해야 합니다. 적절한 인덱스를 사용하고 쿼리를 현명하게 구성함으로써 이를 최소화할 수 있습니다. 그러나 여전히 창 함수의 성능 영향을 이해하고 절제하게 사용하는 것이 중요합니다.

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