面向 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
SELECT *
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

String 操作

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)`

日期时间操作

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` (对每一列)
`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 示例

​String 操作

​日期时间操作

​算术运算

​NULL 值处理

​完整示例

​pandas 代码

​对应的 SQL

​DataStore 代码

​SQL 关键字速览

​pandas 用户提示

​1. 用 SQL 操作的方式来思考

​2. 通过 to_sql() 学习

​3. 利用 SQL 特性

查看生成的 SQL 语句

基本操作映射

筛选 (WHERE)

选择 (SELECT)

排序 (ORDER BY)

GroupBy 与聚合

基础 GroupBy

聚合函数

多个聚合

HAVING 子句

JOIN

Join 示例

String 操作

日期时间操作

算术运算

NULL 值处理

完整示例

pandas 代码

对应的 SQL

DataStore 代码

SQL 关键字速览

pandas 用户提示

1. 用 SQL 操作的方式来思考

2. 通过 to_sql() 学习

3. 利用 SQL 特性