0Pricing
Pandas & NumPy Academy · 课时

Pandas 与 SQL：选择合适的工具

比较 Pandas 中的分组聚合与合并和 SQL 中的 GROUP BY 与 JOIN，并决定每项转换应由哪一层处理

Pandas 与 SQL：选择合适的工具 是 CoddyKit 上的免费 Pandas & NumPy Academy 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Pandas & NumPy Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Pandas & NumPy Academy 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

Two Tools, Complementary Strengths

Both Pandas and SQL are tools for data manipulation, and both are used by professional data analysts. The key insight is that they are complementary, not competing: SQL excels at declarative set-based operations on large tables stored in relational databases, while Pandas excels at imperative, row-by-row and complex algorithmic transformations on data already loaded into memory. The best pipelines use each tool for what it does best.

SQL Strengths: What SQL Does Better

SQL is generally superior when: data is large (gigabytes to terabytes) and must be filtered before loading; joins span multiple large tables where database indexes provide order-of-magnitude speedups; aggregations are simple (SUM, COUNT, GROUP BY); result sets are small relative to input; or concurrent reads/writes are needed (database handles transactions and locking). SQL's declarative syntax also lets query optimisers choose the best physical plan automatically.

-- SQL excels at:
-- 1. Filtering billions of rows using an index
SELECT * FROM orders WHERE customer_id = 12345;

-- 2. Joining large tables efficiently
SELECT o.order_id, c.name, SUM(o.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY o.order_id, c.name;

-- 3. Window functions on ordered data
SELECT order_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)
FROM orders;

Pandas Strengths: What Pandas Does Better

Pandas is generally superior when: you need custom Python logic that SQL cannot express (machine learning preprocessing, custom string parsing, complex algorithms); data transformation chains are many steps long; you need visualisation immediately after analysis; the data is already in memory and further SQL round-trips would add latency; or you are doing exploratory analysis where you want interactive iteration. Pandas also handles non-tabular operations like matrix computation and time series smoothing.

import pandas as pd

# Pandas excels at:
# 1. Custom Python logic that SQL cannot express
df['clean_name'] = df['name'].str.strip().str.title().str.replace(r'[^a-zA-Z ]', '', regex=True)

# 2. Vectorised string parsing
df[['first', 'last']] = df['full_name'].str.split(' ', n=1, expand=True)

# 3. Rolling statistics and time series
df['7day_avg'] = df['daily_sales'].rolling(7).mean()

# 4. Direct visualisation
# df.groupby('category')['sales'].sum().plot(kind='bar')

Mapping SQL Operations to Pandas

Most SQL operations have direct Pandas equivalents. Knowing both syntaxes makes you more versatile and helps you translate between them when moving between tools. WHERE becomes boolean indexing or .query(); GROUP BY + SUM becomes .groupby().sum(); JOIN becomes pd.merge(); ORDER BY becomes .sort_values(); and DISTINCT becomes .drop_duplicates(). The semantic meaning is identical; only the syntax differs.

import pandas as pd

df = pd.DataFrame({'region': ['N','S','N','E'], 'amount': [100,200,150,300]})

# SQL: SELECT region, SUM(amount) FROM df WHERE amount>100 GROUP BY region ORDER BY region
# Pandas:
result = (
    df[df['amount'] > 100]
    .groupby('region')['amount']
    .sum()
    .reset_index()
    .sort_values('region')
)
print(result)

When Data Size Dictates the Choice

A practical decision framework based on data size: under 100 MB — use Pandas entirely, SQL overhead is not worth it; 100 MB – 10 GB — filter and aggregate in SQL, load a summary DataFrame into Pandas; 10 GB – 1 TB — use SQL or Dask for processing, Pandas only for final summary; over 1 TB — use distributed SQL (BigQuery, Spark SQL, Redshift). Never try to load a 100 GB table into Pandas on a 16 GB laptop — it will crash or thrash the disk.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///large.db')

# Right approach: SQL handles the heavy lifting
summary_df = pd.read_sql_query(
    '''
    SELECT region, product_category,
           SUM(revenue) AS total_revenue,
           COUNT(DISTINCT customer_id) AS unique_customers
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY region, product_category
    ''',
    con=engine
)
# summary_df is small — now do Pandas things on it
print(summary_df.sort_values('total_revenue', ascending=False))

SQL Window Functions vs Pandas Rolling

SQL's window functions (OVER (PARTITION BY ... ORDER BY ...)) are powerful but have limitations: they compute running ranks, lag/lead, and simple rolling aggregates well, but complex rolling statistics (e.g., rolling Pearson correlation) are not expressible in SQL. Pandas' rolling() and expanding() cover a much wider range of window computations, including custom functions via .apply(). For standard window functions on large data, prefer SQL; for complex window logic, prefer Pandas.

import pandas as pd

df = pd.DataFrame({
    'date': pd.date_range('2024-01-01', periods=30),
    'sales': [100 + i*10 + (i%7)*20 for i in range(30)]
})

# Pandas rolling — easy with arbitrary window functions
df['7d_mean'] = df['sales'].rolling(7).mean()
df['7d_std']  = df['sales'].rolling(7).std()
df['7d_corr'] = df['sales'].rolling(7).corr(df['sales'].shift(1))
print(df.tail())

Complex Joins: Pandas Flexibility

SQL joins are key-equality based (with some exceptions). Pandas pd.merge_asof() supports fuzzy time-based joins (matching the nearest key rather than exact equality), which is invaluable for time series alignment (e.g., joining stock prices to trade events at the nearest preceding price). Pandas also supports conditional joins using merge followed by filtering, which SQL requires a subquery or LATERAL join to express. These advanced join patterns are one area where Pandas clearly wins.

import pandas as pd

trades = pd.DataFrame({
    'time': pd.to_datetime(['2024-01-01 10:00', '2024-01-01 10:05', '2024-01-01 10:12']),
    'symbol': ['AAPL', 'AAPL', 'AAPL'],
    'shares': [100, 200, 50]
})
prices = pd.DataFrame({
    'time': pd.to_datetime(['2024-01-01 10:00', '2024-01-01 10:10']),
    'price': [185.0, 186.5]
})

# Fuzzy join: match each trade to the nearest preceding price
result = pd.merge_asof(trades.sort_values('time'),
                       prices.sort_values('time'),
                       on='time', direction='backward')
print(result)

Pandas for Data Profiling, SQL for Production

A common workflow pattern: use Pandas for EDA and data profiling on a representative sample (e.g., the first million rows), iteratively develop your transformation logic, then translate the key steps to SQL for production scale. Pandas allows rapid iteration with immediate visual feedback; SQL runs reliably at scale with minimal infrastructure. Keep the two in sync: when you add a new feature in Pandas, write the equivalent SQL stored procedure or view for production.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///data.db')

# Development: sample in Pandas for fast iteration
df_sample = pd.read_sql_query(
    'SELECT * FROM orders ORDER BY RANDOM() LIMIT 10000',
    con=engine
)
# Explore and prototype:
df_sample['revenue_tier'] = pd.cut(
    df_sample['amount'],
    bins=[0, 100, 500, float('inf')],
    labels=['low', 'mid', 'high']
)
print(df_sample['revenue_tier'].value_counts())
# Production: translate cut logic to SQL CASE WHEN

pandasql: Writing SQL Against DataFrames

The pandasql library lets you write SQL queries directly against Pandas DataFrames using SQLite under the hood. sqldf('SELECT * FROM df WHERE amount > 100', locals()) runs the query on the df DataFrame. This is useful if you think in SQL but your data is already in Pandas, or for teaching SQL concepts with in-memory data. However, it is slower than native Pandas for most operations — use it for familiarity, not performance.

# pip install pandasql
import pandas as pd
# from pandasql import sqldf

df = pd.DataFrame({
    'product': ['A', 'B', 'A', 'C', 'B'],
    'sales': [100, 200, 150, 80, 220]
})

# With pandasql (commented out as it requires install):
# result = sqldf('SELECT product, SUM(sales) AS total FROM df GROUP BY product', locals())

# Equivalent native Pandas:
result = df.groupby('product')['sales'].sum().reset_index()
print(result)

Decision Framework: A Quick Reference

Use this decision guide when choosing between SQL and Pandas:

  • Data in a database AND large? Filter and aggregate in SQL first.
  • Need custom Python logic? Use Pandas after a SQL pre-filter.
  • Exploratory analysis on a sample? Pandas gives faster iteration.
  • Time series with complex rolling stats? Pandas rolling/ewm.
  • Simple GROUP BY on millions of rows? SQL with indexes.
  • Multiple small DataFrames already in memory? pd.merge() is fine.
  • Need ACID transactions? SQL database, not Pandas.

Combining Both: The Hybrid Pipeline

The most practical approach is a hybrid pipeline that plays to each tool's strengths. SQL handles ingestion, coarse filtering, and standard aggregations on large raw tables. The output — a manageable DataFrame — is handed to Pandas for feature engineering, custom metrics, rolling statistics, and visualisation. Results are optionally written back to the database for serving. This pipeline is readable, scalable, and maintainable by any analyst who knows both SQL and Python.

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('sqlite:///pipeline.db')

# Step 1: SQL coarse aggregation
df = pd.read_sql_query('''
    SELECT DATE(order_date) AS date, region, SUM(amount) AS daily_revenue
    FROM orders WHERE status = 'completed'
    GROUP BY DATE(order_date), region
    ORDER BY date
''', con=engine, parse_dates=['date'])

# Step 2: Pandas rolling and pivoting (hard in SQL)
df['7d_avg'] = df.groupby('region')['daily_revenue'].transform(
    lambda x: x.rolling(7, min_periods=1).mean()
)
pivot = df.pivot(index='date', columns='region', values='7d_avg')
print(pivot.tail())

Quick Check

Test your understanding of Data Analysis concepts from this lesson.

Lesson Recap

In this lesson you learned: SQL excels at large-scale filtering, joining, and simple aggregations on indexed data, Pandas excels at custom Python logic, complex rolling statistics, and exploratory analysis, and the best strategy is a hybrid pipeline that uses SQL for coarse reduction and Pandas for complex transformations on the manageable result. Next up we start inferential statistics with SciPy: normality testing and descriptive stats.

常见问题解答

「Pandas 与 SQL：选择合适的工具」课时是免费的吗？

是的 — 「Pandas 与 SQL：选择合适的工具」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Pandas & NumPy Academy 课程的其余内容，请升级到 CoddyKit PRO。 Pandas & NumPy Academy 课程共包含 4 节课。

「Pandas 与 SQL：选择合适的工具」这节课中我会学到什么？

比较 Pandas 中的分组聚合与合并和 SQL 中的 GROUP BY 与 JOIN，并决定每项转换应由哪一层处理 你通过在浏览器中直接运行的动手代码来练习 Pandas & NumPy Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Pandas & NumPy Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Pandas & NumPy Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。

「Pandas 与 SQL：选择合适的工具」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Pandas & NumPy Academy 课中编写并运行代码吗？

能。每节 Pandas & NumPy Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 使用 SQLAlchemy 连接数据库
  2. 从 Pandas 运行 SQL 查询
  3. 将 DataFrames 写入数据库表
  4. Pandas 与 SQL：选择合适的工具
← 返回 Pandas & NumPy Academy