pandasユーザー向けSQL - ClickHouse Documentation

DataStoreは、pandasスタイルの操作を最適化されたSQLに変換します。このガイドでは、pandasユーザーが各操作の背後で生成されるSQLを理解できるように説明します。

生成された SQL を表示する

Query

from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

query = (ds
    .filter(ds['amount'] > 1000)
    .groupby('region')
    .agg({'amount': ['sum', 'mean']})
    .sort('sum', ascending=False)
    .head(10)
)

# SQLを表示する
print(query.to_sql())

Response

SELECT region, SUM(amount) AS sum, AVG(amount) AS mean
FROM file('sales.csv', 'CSVWithNames')
WHERE amount > 1000
GROUP BY region
ORDER BY sum DESC
LIMIT 10

基本操作の対応

フィルタリング (WHERE)

pandas	SQL
`df[df['age'] > 25]`	`WHERE age > 25`
`df[df['city'] == 'NYC']`	`WHERE city = 'NYC'`
`df[(df['x'] > 10) & (df['y'] < 20)]`	`WHERE x > 10 AND y < 20`
`df[(df['a'] == 1) \| (df['b'] == 2)]`	`WHERE a = 1 OR b = 2`
`df[~(df['status'] == 'inactive')]`	`WHERE NOT status = 'inactive'`
`df[df['col'].isin([1, 2, 3])]`	`WHERE col IN (1, 2, 3)`
`df[df['val'].between(10, 20)]`	`WHERE val BETWEEN 10 AND 20`
`df[df['name'].str.contains('John')]`	`WHERE position('John' IN name) > 0`

選択 (SELECT)

pandas	SQL
`df['col']`	`SELECT col`
`df[['a', 'b', 'c']]`	`SELECT a, b, c`
`df.head(10)`	`LIMIT 10`
`df.tail(10)`	複雑 (ORDER BY … DESC LIMIT 10)
`df.drop_duplicates()`	`SELECT DISTINCT *`

ソート (ORDER BY)

pandas	SQL
`df.sort_values('col')`	`ORDER BY col ASC`
`df.sort_values('col', ascending=False)`	`ORDER BY col DESC`
`df.sort_values(['a', 'b'])`	`ORDER BY a ASC, b ASC`
`df.sort_values(['a', 'b'], ascending=[True, False])`	`ORDER BY a ASC, b DESC`
`df.nlargest(10, 'col')`	`ORDER BY col DESC LIMIT 10`
`df.nsmallest(5, 'col')`	`ORDER BY col ASC LIMIT 5`

GroupBy と集計

基本的な GroupBy

pandas	SQL
`df.groupby('city')['sales'].sum()`	`SELECT city, SUM(sales) FROM ... GROUP BY city`
`df.groupby('city')['sales'].mean()`	`SELECT city, AVG(sales) FROM ... GROUP BY city`
`df.groupby('city').size()`	`SELECT city, COUNT(*) FROM ... GROUP BY city`
`df.groupby(['a', 'b'])['c'].sum()`	`SELECT a, b, SUM(c) FROM ... GROUP BY a, b`

集計関数

pandas	SQL
`sum()`	`SUM()`
`mean()`	`AVG()`
`count()`	`COUNT()`
`min()`	`MIN()`
`max()`	`MAX()`
`std()`	`stddevPop()`
`var()`	`varPop()`
`median()`	`MEDIAN()`
`nunique()`	`COUNT(DISTINCT col)`
`first()`	`any()`
`last()`	`anyLast()`

複数の集計

# pandasの場合
df.groupby('city').agg({
    'sales': ['sum', 'mean'],
    'quantity': 'sum'
})

# SQLの場合
SELECT city, 
       SUM(sales) AS sales_sum, 
       AVG(sales) AS sales_mean,
       SUM(quantity) AS quantity_sum
FROM data
GROUP BY city

HAVING 句

# pandasスタイル
df.groupby('city')['sales'].sum().query('sales > 10000')

# DataStoreスタイル
ds.groupby('city').agg({'sales': 'sum'}).having(ds['sum'] > 10000)

# SQL
SELECT city, SUM(sales) AS sum
FROM data
GROUP BY city
HAVING sum > 10000

JOIN

pandas	SQL
`pd.merge(df1, df2, on='id')`	`JOIN df2 ON df1.id = df2.id`
`pd.merge(df1, df2, on='id', how='left')`	`LEFT JOIN df2 ON ...`
`pd.merge(df1, df2, on='id', how='right')`	`RIGHT JOIN df2 ON ...`
`pd.merge(df1, df2, on='id', how='outer')`	`FULL OUTER JOIN df2 ON ...`
`pd.merge(df1, df2, left_on='a', right_on='b')`	`JOIN df2 ON df1.a = df2.b`

JOIN の例

# pandas
result = pd.merge(employees, departments, on='dept_id', how='left')

# SQL equivalent（同等のSQL）
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

文字列操作

pandas	SQL
`df['col'].str.upper()`	`upper(col)`
`df['col'].str.lower()`	`lower(col)`
`df['col'].str.len()`	`length(col)`
`df['col'].str.strip()`	`trim(col)`
`df['col'].str.contains('x')`	`position('x' IN col) > 0`
`df['col'].str.startswith('x')`	`startsWith(col, 'x')`
`df['col'].str.endswith('x')`	`endsWith(col, 'x')`
`df['col'].str.replace('a', 'b')`	`replace(col, 'a', 'b')`
`df['col'].str[:5]`	`substring(col, 1, 5)`

DateTime 操作

pandas	SQL
`df['date'].dt.year`	`toYear(date)`
`df['date'].dt.month`	`toMonth(date)`
`df['date'].dt.day`	`toDayOfMonth(date)`
`df['date'].dt.hour`	`toHour(date)`
`df['date'].dt.dayofweek`	`toDayOfWeek(date)`
`df['date'].dt.quarter`	`toQuarter(date)`

算術演算

pandas	SQL
`df['a'] + df['b']`	`a + b`
`df['a'] - df['b']`	`a - b`
`df['a'] * df['b']`	`a * b`
`df['a'] / df['b']`	`a / b`
`df['a'] // df['b']`	`intDiv(a, b)`
`df['a'] % df['b']`	`a % b`
`df['a'] ** 2`	`pow(a, 2)`
`df['a'].abs()`	`abs(a)`
`df['a'].round(2)`	`round(a, 2)`

NULL処理

pandas	SQL
`df['col'].isna()`	`isNull(col)`
`df['col'].notna()`	`isNotNull(col)`
`df.dropna()`	`WHERE col IS NOT NULL` (各 col について)
`df.fillna(0)`	`ifNull(col, 0)`
`df.fillna({'a': 0, 'b': 'x'})`	`ifNull(a, 0), ifNull(b, 'x')`

完全な例

pandas のコード

import pandas as pd

df = pd.read_csv("sales.csv")

result = (df
    [df['date'] >= '2024-01-01']              # フィルター
    [df['amount'] > 100]                      # フィルター
    [['region', 'category', 'amount']]        # カラムを選択
    .groupby(['region', 'category'])          # グループ化
    .agg({
        'amount': ['sum', 'mean', 'count']
    })
    .reset_index()                            # フラット化
    .query('amount_sum > 10000')              # Having
    .sort_values('amount_sum', ascending=False)  # ソート
    .head(20)                                 # 件数制限
)

対応する SQL

SELECT 
    region,
    category,
    SUM(amount) AS amount_sum,
    AVG(amount) AS amount_mean,
    COUNT(amount) AS amount_count
FROM file('sales.csv', 'CSVWithNames')
WHERE date >= '2024-01-01'
  AND amount > 100
GROUP BY region, category
HAVING amount_sum > 10000
ORDER BY amount_sum DESC
LIMIT 20

DataStore のコード

from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

result = (ds
    .filter(ds['date'] >= '2024-01-01')
    .filter(ds['amount'] > 100)
    .select('region', 'category', 'amount')
    .groupby('region', 'category')
    .agg({'amount': ['sum', 'mean', 'count']})
    .having(ds['sum'] > 10000)
    .sort('sum', ascending=False)
    .head(20)
)

# 生成された SQL を確認
print(result.to_sql())

SQLキーワードの概要

pandasの操作	SQL句
`df[condition]`	`WHERE`
`df[['a', 'b']]`	`SELECT a, b`
`df.groupby('x')`	`GROUP BY x`
`.agg({'col': 'sum'})`	`SUM(col)`
`.sort_values('x')`	`ORDER BY x`
`.head(n)`	`LIMIT n`
`pd.merge()`	`JOIN`
`.drop_duplicates()`	`DISTINCT`
`.having()`	`HAVING`

pandasユーザーのためのヒント

1. SQLの操作単位で考える

DataStore のコードを書くときは、まず実現したい SQL を考えます:

# 実行したい場合: SELECT ... WHERE ... GROUP BY ... ORDER BY ... LIMIT
# 記述方法:
ds.filter(...).groupby(...).agg(...).sort(...).head(...)

2. 学習には to_sql() を活用する

# pandasのコードがどのようにSQLに変換されるかを確認する
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())

3. SQL機能を活用する

DataStoreでは、pandasの構文でSQLの機能を利用できます。

# ウィンドウ関数
ds['rank'] = F.row_number().over(partition_by='category', order_by='score')

# 条件付き集計
ds.groupby('region').agg({
    'high_value': ('amount', F.sum_if(Field('amount') > 1000))
})

​生成された SQL を表示する

​基本操作の対応

​フィルタリング (WHERE)

​選択 (SELECT)

​ソート (ORDER BY)

​GroupBy と集計

​基本的な GroupBy

​集計関数

​複数の集計

​HAVING 句

​JOIN

​JOIN の例

​文字列操作

​DateTime 操作

​算術演算

​NULL処理

​完全な例

​pandas のコード

​対応する SQL

​DataStore のコード

​SQLキーワードの概要

​pandasユーザーのためのヒント

​1. SQLの操作単位で考える

​2. 学習には to_sql() を活用する

​3. SQL機能を活用する

生成された SQL を表示する

基本操作の対応

フィルタリング (WHERE)

選択 (SELECT)

ソート (ORDER BY)

GroupBy と集計

基本的な GroupBy

集計関数

複数の集計

HAVING 句

JOIN

JOIN の例

文字列操作

DateTime 操作

算術演算

NULL処理

完全な例

pandas のコード

対応する SQL

DataStore のコード

SQLキーワードの概要

pandasユーザーのためのヒント

1. SQLの操作単位で考える

2. 学習には to_sql() を活用する

3. SQL機能を活用する