0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Inclusive_Solutions_LLC

SQL / pandas / Polars 構文比較表

0
Posted at

SQL・pandas・Polarsは、どれも表形式データを絞り込み・加工・集計に使用しますが、構文の考え方には諸々の違いがあります。

基本姿勢
SQL 欲しい結果を宣言する
pandas DataFrameを順番に操作する
Polars 列に対する式を組み立てる

本記事では、同じ操作を3つの書き方で並べ、実際にセルを実行しながら違いを確認します。ベンチマークではなく、構文と考え方の比較が目的です。

すぐに使えるチートシートはこちら Google Colab版

すぐに実行して、試せるコードレシピはこちら。

0. データを準備する

orders（注文）とcustomers（顧客）の2テーブルを、SQL・pandas・Polarsそれぞれの形で用意します。
price列にはあえてNULL（欠損値）を1件混ぜてあります。NULLの扱いは3つで挙動が近いようで細部が違うので、随所で確認します。

import numpy as np
import pandas as pd
import polars as pl
import sqlite3

orders_data = {
    "order_id": [1, 2, 3, 4, 5],
    "customer_id": ["C001", "C002", "C001", "C003", "C002"],
    "category": ["Book", "Food", "Book", "Gadget", "Food"],
    "price": [1200, 800, 1500, 5000, None],
    "quantity": [2, 3, 1, 1, 2],
    "order_date": ["2026-07-01", "2026-07-01", "2026-07-02", "2026-07-03", "2026-07-04"],
    "status": ["completed", "completed", "completed", "cancelled", "completed"],
}

customers_data = {
    "customer_id": ["C001", "C002", "C003"],
    "customer_name": ["Aoki", "Sato", "Tanaka"],
    "region": ["East", "West", "East"],
}

# --- pandas ---
df = pd.DataFrame(orders_data)
df["order_date"] = pd.to_datetime(df["order_date"])
customers_df = pd.DataFrame(customers_data)

# --- Polars ---
df_pl = pl.DataFrame(orders_data).with_columns(pl.col("order_date").str.to_date())
customers_pl = pl.DataFrame(customers_data)

# --- SQL（SQLiteのインメモリDB）---
conn = sqlite3.connect(":memory:")
pd.DataFrame(orders_data).to_sql("orders", conn, index=False)
pd.DataFrame(customers_data).to_sql("customers", conn, index=False)

def run_sql(query: str) -> pd.DataFrame:
    """SQLを実行してpandas DataFrameで受け取るヘルパー"""
    return pd.read_sql_query(query, conn)

df

実行結果:

   order_id customer_id category   price  quantity order_date     status
0         1        C001     Book  1200.0         2 2026-07-01  completed
1         2        C002     Food   800.0         3 2026-07-01  completed
2         3        C001     Book  1500.0         1 2026-07-02  completed
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled
4         5        C002     Food     NaN         2 2026-07-04  completed

1. 行と列を選ぶ

やりたいこと SQL pandas Polars
複数列を選ぶ SELECT order_id, price df[["order_id", "price"]] df.select("order_id", "price")
列名を変える SELECT price AS unit_price df.rename(columns={...}) df.rename({...})
条件で絞る WHERE status = 'completed' AND price >= 1000 df.loc[条件] df.filter(条件)
いずれかに一致 WHERE category IN (...) .isin([...]) .is_in([...])
NULL判定 IS NULL / IS NOT NULL .isna() / .notna() .is_null() / .is_not_null()

priceがNULLの行（order_id=5）は price >= 1000 の比較では3つとも一致せず除外されます。NULL/NaNとの比較は「不明」扱いになるため、というのがSQLの考え方で、pandas・Polarsもこれに揃えています。

run_sql("SELECT
           order_id
          ,price AS unit_price
         FROM orders
         WHERE status = 'completed' AND price >= 1000")

実行結果:

   order_id  unit_price
0         1      1200.0
1         3      1500.0

df.loc[df["status"].eq("completed") & df["price"].ge(1000), ["order_id", "price"]].rename(columns={"price": "unit_price"})

実行結果:

   order_id  unit_price
0         1      1200.0
2         3      1500.0

(
    df_pl.filter((pl.col("status") == "completed") & (pl.col("price") >= 1000))
    .select("order_id", pl.col("price").alias("unit_price"))
)

実行結果:

shape: (2, 2)
┌──────────┬────────────┐
│ order_id ┆ unit_price │
│ ---      ┆ ---        │
│ i64      ┆ i64        │
╞══════════╪════════════╡
│ 1        ┆ 1200       │
│ 3        ┆ 1500       │
└──────────┴────────────┘

# NULL/NaNの行だけ抽出して、3つとも同じ結果になることを確認
print(run_sql("SELECT * FROM orders WHERE price IS NULL"))
print(df.loc[df["price"].isna()])
print(df_pl.filter(pl.col("price").is_null()))

実行結果:

   order_id customer_id category price  quantity  order_date     status
0         5        C002     Food  None         2  2026-07-04  completed
   order_id customer_id category  price  quantity order_date     status
4         5        C002     Food    NaN         2 2026-07-04  completed
shape: (1, 7)
┌──────────┬─────────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ quantity ┆ order_date ┆ status    │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆ ---      ┆ ---        ┆ ---       │
│ i64      ┆ str         ┆ str      ┆ i64   ┆ i64      ┆ date       ┆ str       │
╞══════════╪═════════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ 2        ┆ 2026-07-04 ┆ completed │
└──────────┴─────────────┴──────────┴───────┴──────────┴────────────┴───────────┘

2. 並べ替え・件数制限・重複削除

やりたいこと SQL pandas Polars
複数列で並べ替え ORDER BY category, price DESC sort_values([...], ascending=[...]) sort([...], descending=[...])
先頭N件 LIMIT n .head(n) .head(n)
列を基準に重複削除 ウィンドウ関数などが必要 drop_duplicates("customer_id") unique(subset="customer_id")

SQLにORDER BYがないと出力順は保証されません。pandas・Polarsも、最終出力の順序が重要なら明示的にsortすることを習慣にすると事故が減ります。

run_sql("SELECT * FROM orders ORDER BY category, price DESC")

実行結果:

   order_id customer_id category   price  quantity  order_date     status
0         3        C001     Book  1500.0         1  2026-07-02  completed
1         1        C001     Book  1200.0         2  2026-07-01  completed
2         2        C002     Food   800.0         3  2026-07-01  completed
3         5        C002     Food     NaN         2  2026-07-04  completed
4         4        C003   Gadget  5000.0         1  2026-07-03  cancelled

df.sort_values(["category", "price"], ascending=[True, False])

実行結果:

   order_id customer_id category   price  quantity order_date     status
2         3        C001     Book  1500.0         1 2026-07-02  completed
0         1        C001     Book  1200.0         2 2026-07-01  completed
1         2        C002     Food   800.0         3 2026-07-01  completed
4         5        C002     Food     NaN         2 2026-07-04  completed
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled

df_pl.sort(["category", "price"], descending=[False, True])

実行結果:

shape: (5, 7)
┌──────────┬─────────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ quantity ┆ order_date ┆ status    │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆ ---      ┆ ---        ┆ ---       │
│ i64      ┆ str         ┆ str      ┆ i64   ┆ i64      ┆ date       ┆ str       │
╞══════════╪═════════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ 3        ┆ C001        ┆ Book     ┆ 1500  ┆ 1        ┆ 2026-07-02 ┆ completed │
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ 2        ┆ 2026-07-01 ┆ completed │
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ 2        ┆ 2026-07-04 ┆ completed │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ 3        ┆ 2026-07-01 ┆ completed │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ 1        ┆ 2026-07-03 ┆ cancelled │
└──────────┴─────────────┴──────────┴───────┴──────────┴────────────┴───────────┘

# customer_idを基準に最初の1件だけ残す
print(df.drop_duplicates("customer_id"))
print(df_pl.unique(subset="customer_id", keep="first").sort("order_id"))

実行結果:

   order_id customer_id category   price  quantity order_date     status
0         1        C001     Book  1200.0         2 2026-07-01  completed
1         2        C002     Food   800.0         3 2026-07-01  completed
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled
shape: (3, 7)
┌──────────┬─────────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ quantity ┆ order_date ┆ status    │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆ ---      ┆ ---        ┆ ---       │
│ i64      ┆ str         ┆ str      ┆ i64   ┆ i64      ┆ date       ┆ str       │
╞══════════╪═════════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ 2        ┆ 2026-07-01 ┆ completed │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ 3        ┆ 2026-07-01 ┆ completed │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ 1        ┆ 2026-07-03 ┆ cancelled │
└──────────┴─────────────┴──────────┴───────┴──────────┴────────────┴───────────┘

3. 新しい列を作る

price × quantity で売上列を、価格帯を判定するCASE/条件分岐列をそれぞれ作ります。

やりたいこと SQL pandas Polars
四則演算の列 price * quantity AS sales df.assign(sales=...) df.with_columns((...).alias("sales"))
条件分岐の列 CASE WHEN ... THEN ... ELSE ... END np.where(条件, a, b) pl.when(条件).then(a).otherwise(b)

いずれも元のテーブル/DataFrameは変更せず、新しい結果を返す書き方にしてあります（pandasはdf["sales"] = ...のような直接代入も可能）。

run_sql('''
SELECT *,
    price * quantity AS sales,
    CASE WHEN price >= 1000 THEN 'high' ELSE 'low' END AS price_band
FROM orders
''')

実行結果:

   order_id customer_id category   price  quantity  order_date     status  \
0         1        C001     Book  1200.0         2  2026-07-01  completed   
1         2        C002     Food   800.0         3  2026-07-01  completed   
2         3        C001     Book  1500.0         1  2026-07-02  completed   
3         4        C003   Gadget  5000.0         1  2026-07-03  cancelled   
4         5        C002     Food     NaN         2  2026-07-04  completed   

    sales price_band  
0  2400.0       high  
1  2400.0        low  
2  1500.0       high  
3  5000.0       high  
4     NaN        low  

df.assign(
    sales=lambda x: x["price"] * x["quantity"],
    price_band=lambda x: np.where(x["price"].ge(1000), "high", "low"),
)

実行結果:

   order_id customer_id category   price  quantity order_date     status  \
0         1        C001     Book  1200.0         2 2026-07-01  completed   
1         2        C002     Food   800.0         3 2026-07-01  completed   
2         3        C001     Book  1500.0         1 2026-07-02  completed   
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled   
4         5        C002     Food     NaN         2 2026-07-04  completed   

    sales price_band  
0  2400.0       high  
1  2400.0        low  
2  1500.0       high  
3  5000.0       high  
4     NaN        low  

df_pl.with_columns(
    (pl.col("price") * pl.col("quantity")).alias("sales"),
    pl.when(pl.col("price") >= 1000).then(pl.lit("high")).otherwise(pl.lit("low")).alias("price_band"),
)

実行結果:

shape: (5, 9)
┌──────────┬─────────────┬──────────┬───────┬───┬────────────┬───────────┬───────┬────────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ … ┆ order_date ┆ status    ┆ sales ┆ price_band │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆   ┆ ---        ┆ ---       ┆ ---   ┆ ---        │
│ i64      ┆ str         ┆ str      ┆ i64   ┆   ┆ date       ┆ str       ┆ i64   ┆ str        │
╞══════════╪═════════════╪══════════╪═══════╪═══╪════════════╪═══════════╪═══════╪════════════╡
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ … ┆ 2026-07-01 ┆ completed ┆ 2400  ┆ high       │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ … ┆ 2026-07-01 ┆ completed ┆ 2400  ┆ low        │
│ 3        ┆ C001        ┆ Book     ┆ 1500  ┆ … ┆ 2026-07-02 ┆ completed ┆ 1500  ┆ high       │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ … ┆ 2026-07-03 ┆ cancelled ┆ 5000  ┆ high       │
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ … ┆ 2026-07-04 ┆ completed ┆ null  ┆ low        │
└──────────┴─────────────┴──────────┴───────┴───┴────────────┴───────────┴───────┴────────────┘

4. 集計する（GROUP BY / HAVING相当）

カテゴリ別に合計・平均・件数を出し、さらに「合計金額が2000以上のカテゴリだけ」に絞ります（HAVINGに相当する処理）。

処理対象 SQL pandas / Polars
集計前の行を絞る WHERE filter / 真偽値による行選択
集計後の結果を絞る HAVING 集計後にもう一度絞り込む

COUNT(*)は行数、COUNT(price)はNULLを除いた件数です。pandasのsizeとcount、Polarsのpl.len()と列のcount()もこれと同じ違いがあります。

run_sql('''
SELECT category, SUM(price) AS total_price, AVG(price) AS average_price, COUNT(*) AS order_count
FROM orders
GROUP BY category
HAVING SUM(price) >= 2000
''')

実行結果:

  category  total_price  average_price  order_count
0     Book       2700.0         1350.0            2
1   Gadget       5000.0         5000.0            1

(
    df.groupby("category", as_index=False)
    .agg(total_price=("price", "sum"), average_price=("price", "mean"), order_count=("order_id", "size"))
    .query("total_price >= 2000")
)

実行結果:

  category  total_price  average_price  order_count
0     Book       2700.0         1350.0            2
2   Gadget       5000.0         5000.0            1

(
    df_pl.group_by("category")
    .agg(
        pl.col("price").sum().alias("total_price"),
        pl.col("price").mean().alias("average_price"),
        pl.len().alias("order_count"),
    )
    .filter(pl.col("total_price") >= 2000)
)

実行結果:

shape: (2, 4)
┌──────────┬─────────────┬───────────────┬─────────────┐
│ category ┆ total_price ┆ average_price ┆ order_count │
│ ---      ┆ ---         ┆ ---           ┆ ---         │
│ str      ┆ i64         ┆ f64           ┆ u32         │
╞══════════╪═════════════╪═══════════════╪═════════════╡
│ Book     ┆ 2700        ┆ 1350.0        ┆ 2           │
│ Gadget   ┆ 5000        ┆ 5000.0        ┆ 1           │
└──────────┴─────────────┴───────────────┴─────────────┘

5. テーブルを結合する

ordersにcustomersのcustomer_name・regionを左外部結合で付け足します。

結合 SQL pandas Polars
左外部結合 LEFT JOIN ... ON ... df.merge(other, on=key, how="left") df.join(other, on=key, how="left")

pandasのmergeはSQLのJOINに近いAPIですが、キーが両方欠損値の場合の扱いなど細部にSQLとの違いがあります。

run_sql('''
SELECT
   orders.*
 , customers.customer_name
 , customers.region
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.customer_id
''')

実行結果:

   order_id customer_id category   price  quantity  order_date     status  \
0         1        C001     Book  1200.0         2  2026-07-01  completed   
1         2        C002     Food   800.0         3  2026-07-01  completed   
2         3        C001     Book  1500.0         1  2026-07-02  completed   
3         4        C003   Gadget  5000.0         1  2026-07-03  cancelled   
4         5        C002     Food     NaN         2  2026-07-04  completed   

  customer_name region  
0          Aoki   East  
1          Sato   West  
2          Aoki   East  
3        Tanaka   East  
4          Sato   West  

df.merge(customers_df, on="customer_id", how="left")

実行結果:

   order_id customer_id category   price  quantity order_date     status  \
0         1        C001     Book  1200.0         2 2026-07-01  completed   
1         2        C002     Food   800.0         3 2026-07-01  completed   
2         3        C001     Book  1500.0         1 2026-07-02  completed   
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled   
4         5        C002     Food     NaN         2 2026-07-04  completed   

  customer_name region  
0          Aoki   East  
1          Sato   West  
2          Aoki   East  
3        Tanaka   East  
4          Sato   West  

df_pl.join(customers_pl, on="customer_id", how="left")

実行結果:

shape: (5, 9)
┌──────────┬─────────────┬──────────┬───────┬───┬────────────┬───────────┬───────────────┬────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ … ┆ order_date ┆ status    ┆ customer_name ┆ region │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆   ┆ ---        ┆ ---       ┆ ---           ┆ ---    │
│ i64      ┆ str         ┆ str      ┆ i64   ┆   ┆ date       ┆ str       ┆ str           ┆ str    │
╞══════════╪═════════════╪══════════╪═══════╪═══╪════════════╪═══════════╪═══════════════╪════════╡
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ … ┆ 2026-07-01 ┆ completed ┆ Aoki          ┆ East   │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ … ┆ 2026-07-01 ┆ completed ┆ Sato          ┆ West   │
│ 3        ┆ C001        ┆ Book     ┆ 1500  ┆ … ┆ 2026-07-02 ┆ completed ┆ Aoki          ┆ East   │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ … ┆ 2026-07-03 ┆ cancelled ┆ Tanaka        ┆ East   │
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ … ┆ 2026-07-04 ┆ completed ┆ Sato          ┆ West   │
└──────────┴─────────────┴──────────┴───────┴───┴────────────┴───────────┴───────────────┴────────┘

6. データを縦に連結する

翌日分の注文データ（orders2）を、既存のordersに縦連結します。

操作 SQL pandas Polars
重複を残して連結 UNION ALL pd.concat([...], ignore_index=True) pl.concat([...])
重複を除いて連結 UNION .drop_duplicates()を追加 .unique()を追加

pandas・Polarsのconcatは、重複を残すUNION ALLに近い挙動です。

orders_data2 = {
    "order_id": [6, 7],
    "customer_id": ["C001", "C004"],
    "category": ["Book", "Gadget"],
    "price": [2000, 3000],
    "quantity": [1, 1],
    "order_date": ["2026-07-05", "2026-07-06"],
    "status": ["completed", "completed"],
}
df2 = pd.DataFrame(orders_data2)
df2["order_date"] = pd.to_datetime(df2["order_date"])
df2_pl = pl.DataFrame(orders_data2).with_columns(pl.col("order_date").str.to_date())
pd.DataFrame(orders_data2).to_sql("orders2", conn, index=False)

run_sql("SELECT * FROM orders UNION ALL SELECT * FROM orders2")

実行結果:

   order_id customer_id category   price  quantity  order_date     status
0         1        C001     Book  1200.0         2  2026-07-01  completed
1         2        C002     Food   800.0         3  2026-07-01  completed
2         3        C001     Book  1500.0         1  2026-07-02  completed
3         4        C003   Gadget  5000.0         1  2026-07-03  cancelled
4         5        C002     Food     NaN         2  2026-07-04  completed
5         6        C001     Book  2000.0         1  2026-07-05  completed
6         7        C004   Gadget  3000.0         1  2026-07-06  completed

pd.concat([df, df2], ignore_index=True)

実行結果:

   order_id customer_id category   price  quantity order_date     status
0         1        C001     Book  1200.0         2 2026-07-01  completed
1         2        C002     Food   800.0         3 2026-07-01  completed
2         3        C001     Book  1500.0         1 2026-07-02  completed
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled
4         5        C002     Food     NaN         2 2026-07-04  completed
5         6        C001     Book  2000.0         1 2026-07-05  completed
6         7        C004   Gadget  3000.0         1 2026-07-06  completed

pl.concat([df_pl, df2_pl])

実行結果:

shape: (7, 7)
┌──────────┬─────────────┬──────────┬───────┬──────────┬────────────┬───────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ quantity ┆ order_date ┆ status    │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆ ---      ┆ ---        ┆ ---       │
│ i64      ┆ str         ┆ str      ┆ i64   ┆ i64      ┆ date       ┆ str       │
╞══════════╪═════════════╪══════════╪═══════╪══════════╪════════════╪═══════════╡
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ 2        ┆ 2026-07-01 ┆ completed │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ 3        ┆ 2026-07-01 ┆ completed │
│ 3        ┆ C001        ┆ Book     ┆ 1500  ┆ 1        ┆ 2026-07-02 ┆ completed │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ 1        ┆ 2026-07-03 ┆ cancelled │
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ 2        ┆ 2026-07-04 ┆ completed │
│ 6        ┆ C001        ┆ Book     ┆ 2000  ┆ 1        ┆ 2026-07-05 ┆ completed │
│ 7        ┆ C004        ┆ Gadget   ┆ 3000  ┆ 1        ┆ 2026-07-06 ┆ completed │
└──────────┴─────────────┴──────────┴───────┴──────────┴────────────┴───────────┘

7. ウィンドウ関数に近い処理

顧客ごと（PARTITION BY相当）に、日付順（ORDER BY相当）の累計金額を求めます。

SQLの構文 pandas Polars
PARTITION BY groupby() .over()
ORDER BY 事前にsort_values() 事前にsort()
SUM(...) OVER groupby().cumsum() .cum_sum().over()

⚠️ ここはSQLとpandas/Polarsで挙動が異なる要注意ポイントです。priceがNULLの行（order_id=5）で、SQLは直前までの累計（800）をそのまま引き継ぐのに対し、pandas・Polarsは欠損を欠損のまま伝播させ、その行のcumulative_priceがNaN/nullになります。実際に下のセルを実行して見比べてみてください。

run_sql('''
SELECT
   *
 , SUM(price) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_price
FROM orders
''')

実行結果:

   order_id customer_id category   price  quantity  order_date     status  \
0         1        C001     Book  1200.0         2  2026-07-01  completed   
1         3        C001     Book  1500.0         1  2026-07-02  completed   
2         2        C002     Food   800.0         3  2026-07-01  completed   
3         5        C002     Food     NaN         2  2026-07-04  completed   
4         4        C003   Gadget  5000.0         1  2026-07-03  cancelled   

   cumulative_price  
0            1200.0  
1            2700.0  
2             800.0  
3             800.0  
4            5000.0  

(
    df.sort_values(["customer_id", "order_date"])
    .assign(cumulative_price=lambda x: x.groupby("customer_id")["price"].cumsum())
)

実行結果:

   order_id customer_id category   price  quantity order_date     status  \
0         1        C001     Book  1200.0         2 2026-07-01  completed   
2         3        C001     Book  1500.0         1 2026-07-02  completed   
1         2        C002     Food   800.0         3 2026-07-01  completed   
4         5        C002     Food     NaN         2 2026-07-04  completed   
3         4        C003   Gadget  5000.0         1 2026-07-03  cancelled   

   cumulative_price  
0            1200.0  
2            2700.0  
1             800.0  
4               NaN  
3            5000.0  

(
    df_pl.sort(["customer_id", "order_date"])
    .with_columns(pl.col("price").cum_sum().over("customer_id").alias("cumulative_price"))
)

実行結果:

shape: (5, 8)
┌──────────┬─────────────┬──────────┬───────┬──────────┬────────────┬───────────┬──────────────────┐
│ order_id ┆ customer_id ┆ category ┆ price ┆ quantity ┆ order_date ┆ status    ┆ cumulative_price │
│ ---      ┆ ---         ┆ ---      ┆ ---   ┆ ---      ┆ ---        ┆ ---       ┆ ---              │
│ i64      ┆ str         ┆ str      ┆ i64   ┆ i64      ┆ date       ┆ str       ┆ i64              │
╞══════════╪═════════════╪══════════╪═══════╪══════════╪════════════╪═══════════╪══════════════════╡
│ 1        ┆ C001        ┆ Book     ┆ 1200  ┆ 2        ┆ 2026-07-01 ┆ completed ┆ 1200             │
│ 3        ┆ C001        ┆ Book     ┆ 1500  ┆ 1        ┆ 2026-07-02 ┆ completed ┆ 2700             │
│ 2        ┆ C002        ┆ Food     ┆ 800   ┆ 3        ┆ 2026-07-01 ┆ completed ┆ 800              │
│ 5        ┆ C002        ┆ Food     ┆ null  ┆ 2        ┆ 2026-07-04 ┆ completed ┆ null             │
│ 4        ┆ C003        ┆ Gadget   ┆ 5000  ┆ 1        ┆ 2026-07-03 ┆ cancelled ┆ 5000             │
└──────────┴─────────────┴──────────┴───────┴──────────┴────────────┴───────────┴──────────────────┘

8. まとめ

似ているところ

共通する考え方 SQL pandas Polars
必要な列を選ぶ SELECT 列選択 select
条件で絞る WHERE 真偽値による行選択 filter
新しい列を作る 式とAS assign with_columns
グループ化・集計 GROUP BY / SUM等 groupby / agg group_by / agg
表を結合する JOIN merge join

考え方の違い

  • SQLは「欲しい結果」を宣言する。書く順序（SELECT→...）と実際の処理順序（FROM→WHERE→GROUP BY→...）が異なる。
  • pandasは「DataFrameを次にどう変えるか」を順番に書く。Indexによる自動整列があるので、Series同士の計算では位置ではなくラベルが基準になる場面がある。
  • Polarsは「列に対する式」を組み立て、select/filter/group_byなどのコンテキストに渡す。Lazy APIを使うと、実行前にクエリ全体を最適化できる点はSQLに近い。

つまずきやすい点

  • NULL判定は IS NULL / .isna() / .is_null() で行う。= NULLは使えない。
  • COUNT(*)とNULLを除いたCOUNT(column)は別物。pandasのsizeとcount、Polarsのpl.len()と列のcount()も同様。
  • 集計後の絞り込み（HAVING相当）は、pandas・Polarsでは集計してからもう一度filter・queryする。
  • 出力順はORDER BY / sort_values / sortを明示しないと保証されない。

使い分けの目安

状況 選択肢
データがDB内にあり、DB側で完結させたい SQL
Notebookで途中結果を確認しながら分析したい pandas
Pythonの分析ライブラリや既存コードと組み合わせたい pandas
式中心で書きたい／Lazy APIで最適化したい Polars

構文を個別に暗記するより、「列を選ぶ→行を絞る→列を加工する→グループ化する→集計する→並べ替える」という処理の単位で覚えると、3つを行き来しやすくなります。

弊社について

本記事を書いている 合同会社インクルーシブソリューションズ は、データ基盤構築・分析基盤設計・システム改善支援を中心に活動している小規模IT法人です。

主な領域は、

  • データマート設計・データパイプライン構築
  • SQL / Python を用いたデータ処理設計
  • BI導入支援・分析基盤の整備
  • 既存システムの運用改善・可視化支援

といった、「データを使える状態にする」ための活動です。

弊社の企業活動に興味がある方は、ぜひ公式サイトも覗いてみてください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?