SQL для пользователей pandas

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

Операции со строками

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. Изучайте SQL с помощью to_sql()

# Посмотрите, как ваш код pandas преобразуется в SQL
query = ds.filter(ds['x'] > 10).groupby('y').sum()
print(query.to_sql())

3. Используйте возможности SQL

DataStore сочетает возможности SQL с синтаксисом pandas:

# Оконные функции
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

​Операции со строками

​Операции с датой и временем

​Арифметические операции

​Обработка NULL

​Полный пример

​Код на pandas

​Эквивалентный SQL-запрос

​Код DataStore

​Краткая сводка по ключевым словам SQL

​Советы пользователям pandas

​1. Мыслите в терминах SQL-операций

​2. Изучайте SQL с помощью to_sql()

​3. Используйте возможности SQL