1.熟练使用Excel、SQL、Python（2）

最新推荐文章于 2025-12-10 08:40:21 发布

原创最新推荐文章于 2025-12-10 08:40:21 发布 · 1.7k 阅读

CC 4.0 BY-SA版权

文章标签：

SQL

SQL 的基础操作，用于获取和筛选数据：

SELECT 语句：
- 基本语法：SELECT 列1, 列2 FROM 表名（如SELECT name, age FROM users）。
- 通配符：SELECT * FROM 表名（查询所有列，调试用，生产环境建议明确列名）。
- 别名：SELECT name AS 姓名, age AS 年龄 FROM users（提高可读性）。
过滤条件：
- WHERE子句：SELECT * FROM users WHERE age > 18 AND gender = '女'（支持AND/OR/NOT逻辑）。
- 比较运算符：=, <>, >, <, >=, <=, BETWEEN, LIKE（如LIKE '%北京%'匹配包含 “北京” 的字符串）。
- IN和NOT IN：SELECT * FROM users WHERE city IN ('北京', '上海', '广州')。
排序与分页：
- ORDER BY：SELECT * FROM users ORDER BY age DESC（降序排列，ASC为升序，默认）。
- LIMIT/OFFSET（MySQL）或TOP（SQL Server）：SELECT * FROM users LIMIT 10 OFFSET 20（取第 21-30 条记录）。

统计和汇总数据的核心工具：

聚合函数：
- COUNT()：统计行数（如COUNT(*)统计总记录数，COUNT(DISTINCT column)统计唯一值数量）。
- SUM()：求和（如SUM(sales_amount)计算总销售额）。
- AVG()：求平均（如AVG(age)计算平均年龄）。
- MAX()/MIN()：求最大值 / 最小值（如MAX(price)）。
GROUP BY：
- 按字段分组统计：SELECT city, COUNT(*) FROM users GROUP BY city（统计各城市用户数）。
- 多字段分组：SELECT gender, city, AVG(age) FROM users GROUP BY gender, city（按性别和城市分组求平均年龄）。
HAVING：
- 过滤分组结果：SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 100（筛选用户数超过 100 的城市）。

关联多个表的数据，是 SQL 的核心难点：

内连接（INNER JOIN）：
- 返回两表匹配的记录：SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id（获取订单及其用户信息）。
外连接：
- 左连接（LEFT JOIN）：返回左表全部记录 + 右表匹配记录（如SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id，含无订单的用户）。
- 右连接（RIGHT JOIN）、全外连接（FULL OUTER JOIN）：按需使用。
自连接（SELF JOIN）：
- 同一表关联自身：SELECT e1.name AS 员工, e2.name AS 经理 FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id（员工表中关联上级信息）。
复合条件连接：
- 多条件连接：SELECT * FROM orders JOIN products ON orders.product_id = products.id AND orders.order_date = products.launch_date。

在查询中嵌套其他查询，处理复杂逻辑：

WHERE 子查询：
- 作为过滤条件：SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users)（查询年龄大于平均的用户）。
FROM 子查询：
- 临时表：SELECT * FROM (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS temp WHERE order_count > 5（查询订单数超过 5 的用户）。
相关子查询：
- 依赖外部查询：SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000)（查询有大额订单的用户）。

高级分析必备，用于分组内计算或排序：

基本语法：
- 函数 OVER (PARTITION BY 分组字段 ORDER BY 排序字段)（如SUM(sales) OVER (PARTITION BY city ORDER BY date)计算各城市累计销售额）。
常用窗口函数：
- 排名函数：RANK(), DENSE_RANK(), ROW_NUMBER()（如RANK() OVER (PARTITION BY department ORDER BY salary DESC)计算部门内工资排名）。
- 聚合函数：SUM(), AVG(), MAX(), MIN()（如AVG(score) OVER (PARTITION BY class)计算班级平均分）。
- 偏移函数：LAG(), LEAD()（如LAG(sales, 1) OVER (ORDER BY date)获取前一天销售额，计算环比）。

处理复杂数据类型和格式：

字符串函数：
- CONCAT()：拼接字符串（如CONCAT(first_name, ' ', last_name)）。
- SUBSTRING()：截取子串（如SUBSTRING(email, 1, INSTR(email, '@')-1)提取邮箱前缀）。
- REPLACE()：替换字符（如REPLACE(phone, '-', '')删除电话号码中的横线）。
日期函数：
- DATE()/TIME()：提取日期 / 时间部分（如DATE(order_time)）。
- DATEDIFF()：计算日期间隔（如DATEDIFF('day', start_date, end_date)）。
- DATE_ADD()/DATE_SUB()：日期加减（如DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)）。
条件表达式：
- CASE WHEN：SELECT CASE WHEN score >= 90 THEN '优秀' WHEN score >= 80 THEN '良好' ELSE '一般' END AS 等级 FROM students。

处理复杂分析需求：

CTE（公共表表达式）：
- 临时命名查询块：WITH monthly_sales AS (SELECT MONTH(order_date) AS month, SUM(amount) AS total FROM orders GROUP BY MONTH(order_date)) SELECT * FROM monthly_sales WHERE total > 10000。
UNION/UNION ALL：
- 合并查询结果：SELECT name, '用户' AS type FROM users UNION ALL SELECT product_name, '产品' FROM products（需列数和类型一致）。
EXISTS/NOT EXISTS：
- 判断记录是否存在：SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)（替代IN，性能更优）。

提升查询效率的关键：

索引原理：
- 理解 B 树 / B + 树索引结构，加速WHERE/JOIN/ORDER BY操作（如CREATE INDEX idx_age ON users (age)）。
执行计划分析：
- 使用EXPLAIN查看查询执行路径，优化低效查询（如避免全表扫描、减少临时表）。
优化原则：
- 避免在索引字段使用函数（如WHERE YEAR(order_date) = 2023会失效）。
- 复合索引遵循 “最左前缀” 原则（如索引(a, b, c)支持WHERE a=1和WHERE a=1 AND b=2，但不支持WHERE b=2）。

理解数据存储结构，辅助分析：

三大范式：
- 第一范式（1NF）：字段原子性（如避免 “姓名，年龄” 复合字段）。
- 第二范式（2NF）：消除部分依赖（如订单表拆分出独立的产品表）。
- 第三范式（3NF）：消除传递依赖（如用户表中地址信息单独建表）。
ER 图（实体关系图）：
- 识别实体（如用户、订单）、属性（如用户 ID、姓名）和关系（如一对多、多对多）。

结合业务需求应用 SQL：

业务分析场景：
- 用户留存分析：WITH first_visit AS (SELECT user_id, MIN(visit_date) AS first_date FROM visits GROUP BY user_id) SELECT fv.first_date, COUNT(DISTINCT v.user_id) AS 留存用户数 FROM first_visit fv JOIN visits v ON fv.user_id = v.user_id AND DATEDIFF(v.visit_date, fv.first_date) = 7 GROUP BY fv.first_date。
- 漏斗分析：多表关联统计各步骤转化率。
工具与生态：
- SQL 客户端：Navicat、DBeaver、SQL Server Management Studio（SSMS）。
- 大数据 SQL：Hive SQL（Hadoop）、Spark SQL（分布式计算）。
- 数据仓库：Snowflake、Redshift、BigQuery（支持复杂查询和 PB 级数据）。