SQL 多表 JOIN 与子查询怎么选

真实业务数据分散在多张表:用户、订单、商品、支付。会写 JOIN,才能把「故事」拼完整。

四种 JOIN 直觉

  • INNER JOIN:两边都匹配才保留
  • LEFT JOIN:保留左表全部,右表没有则 NULL
  • RIGHT JOIN:少用,能写成 LEFT 就写成 LEFT
  • FULL JOIN:MySQL 不直接支持,常用 UNION 模拟

示例:用户与订单

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01';

想找「注册了但没下单」的用户:

SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

子查询 vs JOIN

-- 子查询:先找高价值用户,再查明细
SELECT *
FROM orders
WHERE user_id IN (
  SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 10000
);

-- 等价思路用 JOIN + 聚合派生表,往往更好优化
SELECT o.*
FROM orders o
JOIN (
  SELECT user_id
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 10000
) vip ON o.user_id = vip.user_id;

选型建议

  1. 关联取字段:优先 JOIN
  2. 存在性判断:可用 EXISTS
  3. 子查询结果很大时,注意重复计算,考虑临时表/派生表

MySQL 索引原理与慢查询排查入门

接口变慢,十有八九跟 SQL 和索引有关。理解 B+Tree 索引、会看执行计划,是后端和数据分析同学的共同必修课。

索引解决什么问题

没有索引时,MySQL 可能整表扫描;有合适索引时,可以快速定位到目标行。代价是:占用空间,拖慢写入。

最左前缀原则

-- 联合索引 (city, status, created_at)
SELECT * FROM orders WHERE city='北京' AND status='paid';           -- 能用
SELECT * FROM orders WHERE city='北京';                             -- 能用
SELECT * FROM orders WHERE status='paid';                           -- 通常用不上该联合索引

用 EXPLAIN 看执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status='paid';

重点看:

  • type:all 很危险;ref/range 通常可接受
  • key:实际用了哪个索引
  • rows:预估扫描行数
  • Extra:是否 Using filesort / Using temporary

慢查询怎么开

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记下来

常见优化思路

  1. 给 WHERE / JOIN / ORDER BY 高频字段建合适索引
  2. 避免在索引列上包函数:WHERE DATE(created_at)=...
  3. 只查需要的列,慎用 SELECT *
  4. 深分页改成基于上次最大 ID 的翻页
-- 深分页不友好
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 更好
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

时间序列基础:日期索引、重采样与滚动窗口

订单、流量、传感器数据都带时间。掌握日期索引、重采样、滚动统计,你就能做趋势、环比和简单异常发现。

变成时间索引

import pandas as pd

df["order_date"] = pd.to_datetime(df["order_date"])
ts = df.set_index("order_date").sort_index()
print(ts.loc["2026-01":"2026-03"])  # 按月份切片

重采样:日 → 周/月

daily = ts["amount"].resample("D").sum()
weekly = ts["amount"].resample("W").sum()
monthly = ts["amount"].resample("ME").sum()

滚动窗口:平滑噪声

# 7 日滑动均值
smooth = daily.rolling(window=7, min_periods=1).mean()

# 同时看均值和标准差,便于发现异常波动
roll = daily.rolling(7).agg(["mean", "std"])

环比 / 同比

mom = monthly.pct_change()          # 月环比
yoy = monthly.pct_change(periods=12) # 月同比(需足够长历史)

小案例:找出销量异常日

mu = daily.rolling(14).mean()
sigma = daily.rolling(14).std()
anomaly = daily[(daily - mu).abs() > 2 * sigma]
print(anomaly)

注意时区与缺失日期

# 补齐没有下单的日期,金额填 0
full = daily.asfreq("D").fillna(0)

分析趋势前先问:时间是否单调、有没有缺口、时区是否统一。这三件事比算法更影响结论。

用 groupby / pivot_table 做业务汇总分析

业务同学最常问:「按城市/品类/月份,销量多少、客单价多少?」Pandas 的 groupby 和 pivot_table 就是为这类问题准备的。

groupby 基础

import pandas as pd

# 假设 df 有 city, category, amount, qty
summary = (
    df.groupby("city", as_index=False)
      .agg(订单数=("order_id", "count"),
           销售额=("amount", "sum"),
           客单价=("amount", "mean"))
      .sort_values("销售额", ascending=False)
)

多字段分组

by_city_cat = df.groupby(["city", "category"]).agg(
    销量=("qty", "sum"),
    销售额=("amount", "sum"),
).reset_index()

pivot_table:更像 Excel 透视表

pivot = pd.pivot_table(
    df,
    index="city",
    columns="category",
    values="amount",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="合计",
)

时间维度汇总

df["month"] = df["order_date"].dt.to_period("M").astype(str)
monthly = df.groupby("month")["amount"].sum()

转化率这类「先聚合再计算」

funnel = df.groupby("channel").agg(
    访问=("user_id", "nunique"),
    下单=("order_id", "nunique"),
)
funnel["下单率"] = funnel["下单"] / funnel["访问"]

选型建议

  • 只要长表汇总 → groupby
  • 要行列交叉的宽表 → pivot_table
  • 结果要给业务看 → 最后 round、改中文列名、导出 Excel

Pandas 读写 CSV/Excel 与缺失值处理实战

数据分析第一关不是建模,而是把表读进来并洗干净。CSV/Excel 读写和缺失值处理,是 Pandas 每天都在用的基本功。

读入数据

import pandas as pd

df = pd.read_csv("orders.csv", encoding="utf-8-sig")
# 大文件可先看前几行、指定列
df_preview = pd.read_csv("orders.csv", nrows=5, usecols=["order_id", "amount", "status"])

# Excel
xlsx = pd.read_excel("sales.xlsx", sheet_name="2026Q1")

写出结果

df.to_csv("clean_orders.csv", index=False, encoding="utf-8-sig")
df.to_excel("clean_orders.xlsx", index=False)

缺失值长什么样

print(df.isna().sum())          # 每列缺失数量
print(df.isna().mean().sort_values(ascending=False))  # 缺失比例

常见处理策略

# 1) 删除:缺失很少且随机
df2 = df.dropna(subset=["order_id", "user_id"])

# 2) 填充:数值用中位数,类别用众数/未知
df["amount"] = df["amount"].fillna(df["amount"].median())
df["city"] = df["city"].fillna("未知")

# 3) 标记:保留信息,另加一列
df["amount_missing"] = df["amount"].isna().astype(int)

类型也要一起修

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df["status"] = df["status"].astype("category")

一条实用清洗流水线

def clean_orders(path: str) -> pd.DataFrame:
    df = pd.read_csv(path)
    df.columns = [c.strip().lower() for c in df.columns]
    df = df.drop_duplicates(subset=["order_id"])
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
    df = df[df["amount"].notna() & (df["amount"] >= 0)]
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    return df.reset_index(drop=True)

先标准化列名 → 去重 → 转类型 → 再处理缺失,顺序很重要。

反爬常见手段与合规应对策略(限流、UA、代理)

写爬虫绕不开反爬。正确心态不是「怎么硬刚」,而是识别机制、降低频率、合法合规。本文梳理常见手段与应对思路。

常见反爬手段

  • User-Agent / Referer / Cookie 校验
  • IP 频率限制、验证码、封禁
  • JS 动态参数、接口签名
  • 蜜罐链接、隐藏字段
  • 登录态、设备指纹

基础:让请求更「像正常人」

import random
import time
import requests

SESSION = requests.Session()
SESSION.headers.update({
    "User-Agent": "Mozilla/5.0 ...",
    "Accept-Language": "zh-CN,zh;q=0.9",
})

def polite_get(url: str):
    time.sleep(random.uniform(0.8, 1.8))
    resp = SESSION.get(url, timeout=20)
    if resp.status_code == 429:
        time.sleep(5)
        resp = SESSION.get(url, timeout=20)
    resp.raise_for_status()
    return resp

限流与重试

from tenacity import retry, stop_after_attempt, wait_exponential

@retry(stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=1, max=8))
def get_with_retry(url: str):
    r = SESSION.get(url, timeout=20)
    r.raise_for_status()
    return r

代理怎么用(概念)

proxies = {"http": "http://user:pass@host:port", "https": "http://user:pass@host:port"}
requests.get(url, proxies=proxies, timeout=20)

代理用于分散请求来源,但仍要控制频率;劣质代理会更不稳定。

合规红线

  1. 不抓取明确禁止、涉及个人隐私或付费墙后的内容。
  2. 优先使用官方 API / 开放数据。
  3. 学习项目控制规模,加延迟,保留日志便于自查。

记住:稳定、可维护、不违法,比一时抓得多更重要。

用 Selenium / Playwright 处理动态渲染页面

页面上「看得见、源码里没有」的内容,多半是 JavaScript 渲染出来的。这时 HTML 请求不够用,需要浏览器自动化。

什么时候该上浏览器

  • 列表靠滚动/点击「加载更多」
  • 数据在 Ajax 接口里,但接口难逆向或有复杂签名
  • 需要真实点击、填表、等元素出现

Playwright 示例(推荐)

from playwright.sync_api import sync_playwright

with sync_playwright() as p:
    browser = p.chromium.launch(headless=True)
    page = browser.new_page()
    page.goto("https://example.com/list", wait_until="networkidle")
    page.wait_for_selector(".item-title")
    titles = page.eval_on_selector_all(
        ".item-title",
        "els => els.map(e => e.textContent.trim())"
    )
    print(titles)
    browser.close()

更稳的等待方式

# 等待某个接口返回
with page.expect_response(lambda r: "/api/items" in r.url and r.ok) as resp_info:
    page.click("text=加载更多")
data = resp_info.value.json()

能拦到接口 JSON,往往比解析 DOM 更干净。

Selenium 对照思路

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC

driver = webdriver.Chrome()
driver.get("https://example.com/list")
els = WebDriverWait(driver, 10).until(
    EC.presence_of_all_elements_located((By.CSS_SELECTOR, ".item-title"))
)
titles = [e.text for e in els]
driver.quit()

性能与稳定性建议

  1. 能抓接口就抓接口,浏览器当备选。
  2. 固定显式等待,少用盲目 sleep。
  3. 生产环境用无头模式 + 资源拦截(图片/字体)加速。

Requests + BeautifulSoup 抓取静态网页完整流程

静态网页爬虫是入门必经之路:发请求、拿 HTML、解析字段、落盘。本文用 requests + BeautifulSoup 走完一条可复用流水线。

环境准备

pip install requests beautifulsoup4 lxml

最小可运行示例

import csv
import time
import requests
from bs4 import BeautifulSoup

HEADERS = {
    "User-Agent": "Mozilla/5.0 (compatible; StudyBot/1.0; +https://antpython.net)"
}

def fetch(url: str) -> str:
    resp = requests.get(url, headers=HEADERS, timeout=15)
    resp.raise_for_status()
    resp.encoding = resp.apparent_encoding
    return resp.text

def parse_list(html: str):
    soup = BeautifulSoup(html, "lxml")
    items = []
    for a in soup.select("h2 a"):  # 按真实页面改选择器
        items.append({
            "title": a.get_text(strip=True),
            "url": a.get("href"),
        })
    return items

def save_csv(rows, path="result.csv"):
    with open(path, "w", newline="", encoding="utf-8-sig") as f:
        writer = csv.DictWriter(f, fieldnames=["title", "url"])
        writer.writeheader()
        writer.writerows(rows)

if __name__ == "__main__":
    html = fetch("https://example.com")
    rows = parse_list(html)
    save_csv(rows)
    print(f"共抓取 {len(rows)} 条")

选择器怎么找

  1. 浏览器 F12 → Elements 定位目标节点
  2. 优先用稳定的 class / 语义标签,少用超长绝对路径
  3. 先 print(soup.select(...)) 验证,再批量跑

礼貌爬取

  • 加 time.sleep(1),别把对方服务器打挂
  • 遵守 robots.txt 与网站服务条款
  • 只抓学习/公开数据,注意版权与隐私

常见报错速查

  • 403:UA/Headers 被拒,或需要登录 Cookie
  • 乱码:设置 resp.encoding
  • 空结果:选择器写错,或内容是 JS 动态加载(该换 Selenium/Playwright)

从零掌握 Python 异常处理与自定义异常

程序总会遇到「文件不存在」「接口超时」「用户输入非法」。把异常处理好,你的代码才像能上线的工具,而不是一碰就崩的脚本。

基础结构

try:
    with open("data.csv", encoding="utf-8") as f:
        text = f.read()
except FileNotFoundError:
    print("文件不存在,请先准备 data.csv")
except PermissionError:
    print("没有读权限")
else:
    print("读取成功,长度:", len(text))
finally:
    print("无论成败都会执行(适合收尾)")

捕获要具体,别裸 except

# 不推荐
try:
    do_something()
except:
    pass

# 推荐:只抓你能处理的异常
try:
    do_something()
except (ValueError, TypeError) as e:
    print("输入有问题:", e)

自定义业务异常

class OrderError(Exception):
    """订单相关错误基类"""

class InsufficientStockError(OrderError):
    def __init__(self, sku: str, left: int):
        self.sku = sku
        self.left = left
        super().__init__(f"SKU {sku} 库存不足,剩余 {left}")

def place_order(sku: str, qty: int, stock: dict):
    left = stock.get(sku, 0)
    if qty > left:
        raise InsufficientStockError(sku, left)
    stock[sku] = left - qty

上层可以统一捕获 OrderError,也可以单独处理库存不足。

异常链:保留根因

try:
    raw = int(user_input)
except ValueError as e:
    raise ValueError("年龄必须是整数") from e

实践建议

  1. 边界输入、IO、网络调用必须有异常策略。
  2. 库代码抛异常,业务层决定怎么展示给用户。
  3. 日志里记录异常类型和关键上下文,别只 print「出错了」。

彻底搞懂 Python 列表推导式与生成器表达式

列表推导式写起来短、读起来快,是 Python 里最受欢迎的语法糖之一。但它也容易被滥用。本文把推导式、生成器表达式、何时该用 for一次讲清楚。

基础写法

nums = [1, 2, 3, 4, 5]
squares = [n * n for n in nums]
evens = [n for n in nums if n % 2 == 0]
labels = ["偶" if n % 2 == 0 else "奇" for n in nums]

嵌套推导式:可读性优先

matrix = [[1, 2], [3, 4], [5, 6]]
flat = [x for row in matrix for x in row]
# 等价于:
flat2 = []
for row in matrix:
    for x in row:
        flat2.append(x)

超过两层嵌套,建议改回普通 for,团队协作时更友好。

生成器表达式:省内存

# 列表:立刻占满内存
total1 = sum([i * i for i in range(10_000_000)])

# 生成器:边算边丢,内存友好
total2 = sum(i * i for i in range(10_000_000))

差别只在括号:[] 是列表,() 是生成器。传给 sum、any、max 这类「吃一遍就结束」的函数时,优先生成器。

字典 / 集合推导

words = ["python", "pandas", "agent"]
length_map = {w: len(w) for w in words}
unique_lens = {len(w) for w in words}

实战:清洗一行脏数据

raw = "  Alice,  bob ,CHARLIE,, "
names = [x.strip().title() for x in raw.split(",") if x.strip()]
print(names)  # ['Alice', 'Bob', 'Charlie']

什么时候别用推导式

  • 逻辑里有多步副作用(写文件、打日志、调接口)
  • 嵌套过深,同事读不懂
  • 需要调试中间变量时

口诀:变换和过滤用推导式;业务流程用 for。