SQL高效使用20招:数据分析师必备技巧

liuian 2025-07-06 14:03 48 浏览

基础优化技巧

善用EXPLAIN分析执行计划

EXPLAIN
SELECT * FROM orders WHERE order_date > '2024-01-01';

避免SELECT * 的陷阱

-- 推荐写法
SELECT order_id, customer_name, order_amount
FROM orders;

给表取简洁别名

SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;

日期处理技巧

安全转换日期格式

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date)
FROM orders;
-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m')
FROM orders;

计算日期差值

-- 计算年龄
SELECT name, AGE(birth_date)
FROM employees;

获取当前时间戳

-- 跨数据库通用
SELECT CURRENT_TIMESTAMP AS now;

高级查询技巧

窗口函数实战

SELECT
  product_id,
  sale_date,
  SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

CTE递归查询

WITH RECURSIVE org_tree AS (
  SELECT id, name, parent_id
  FROM departments
  WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id
  FROM departments d
  JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree;

数据清洗技巧

缺失值处理

-- 填充默认值
SELECT
  COALESCE(address, '未填写') AS safe_address
FROM customers;

数据脱敏处理

SELECT
  name,
  CONCAT('****', RIGHT(phone, 4)) AS secure_phone
FROM users;

快速去重

SELECT DISTINCT department
FROM employees;

性能优化技巧

索引使用原则

-- 创建组合索引
CREATE INDEX idx_orders_date_status
ON orders (order_date, order_status);

分区表优化

-- 按年分区
CREATE TABLE sales (
  id INT,
  sale_date DATE
) PARTITION BY RANGE (EXTRACT(YEAR FROM sale_date));

批处理代替循环

-- 批量更新
UPDATE products
SET price = price * 1.1
WHERE category = '电子产品';

实战技巧

快速抽样

-- 随机取100条
SELECT *
FROM users
ORDER BY RANDOM()
LIMIT 100;

行列转换

-- 使用CASE实现透视
SELECT
  product_id,
  SUM(CASE WHEN quarter = 'Q1' THEN sales END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN sales END) AS Q2
FROM sales_data
GROUP BY product_id;

连续登录检测

SELECT
  user_id,
  login_date,
  LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
FROM login_records;

实用函数

字符串分割

-- PostgreSQL示例
SELECT
  SPLIT_PART(full_name, ' ', 1) AS first_name,
  SPLIT_PART(full_name, ' ', 2) AS last_name
FROM contacts;

JSON解析

-- MySQL示例
SELECT
  JSON_EXTRACT(user_data, '$.address.city') AS city
FROM user_profiles;

正则表达式匹配

SELECT *
FROM logs
WHERE message ~ 'ERROR [1-5]{3}';

总结精要

类别	重点技巧
查询优化	执行计划分析、索引优化
数据处理	日期转换、行列转换
性能提升	批量操作、分区表
安全规范	参数化查询、数据脱敏