数据 | 核心生产要素[2]数据分析

SQL基本功

Catalogue
  1. SQL重点
    1. 1. 执行顺序
    2. 2. 表连接
    3. 3. 聚合函数
    4. 4. 时间函数
    5. 5. 窗口函数
  2. 一、多表JOIN
  3. 二、窗口函数
    1. 2.1 LAG函数
    2. 2.2 SUM+CASE函数
    3. 2.3 DATE_SUB函数
    4. 2.4 DATE_DIFF函数
  4. 三、分区分桶
  5. 四、SQL核心知识点
    1. 4.1 基础操作与聚合
    2. 4.2 查询结构对比：CTE/子查询/视图/临时表
    3. 4.3 分布式环境中的SQL逻辑
    4. 4.4 窗口函数：面试高频考点
  6. 资料

SQL重点

1. 执行顺序

2. 表连接

3. 聚合函数

4. 时间函数

5. 窗口函数

一、多表JOIN

二、窗口函数

2.1 LAG函数

用于 在查询结果集中 访问 前一行数据。
该函数 可用于 计算当前行和前一行 之间的差异，或者 将前一行的数据与 当前行数据 进行比较。

例如：

1
select user_id,time,LAG(time,1, NULL) OVER(PARTITION BY user_id ORDER BY time) as last_time from user_behavior_v2

语法如下：
LAG(expression [,offset] [,default]) OVER([partition_by_clause] order_by_clause)

  • expression：要访问前一行数据的列 或表达式。
  • offset：要访问前一行的偏移量，默认为1
  • default：前一行不存在，的默认

「使用场景」

计算当前行和前行之间的差异。 将前一行的数据与当前行数据进行比较。
识别数据中的趋势和模式。在分析数据时使用滑动窗口函数。

2.2 SUM+CASE函数

统计多维销售数据用
计算不同状态或渠道的呃呃订单总额

1
2
3
4
SELECT
 SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) AS total_amount,
 SUM(CASE WHEN channel = 'online' THEN  amount ELSE 0 END) as onlin_channel_total_amount
FROM orders;

2.3 DATE_SUB函数

从指定日期减去一个时间间隔。结合NOW()函数，可以动态获取当前时间并进行时间筛选。

2.4 DATE_DIFF函数

计算两个日期之间的差异。

三、分区分桶

四、SQL核心知识点

4.1 基础操作与聚合

JOIN类型：重点掌握INNER JOIN、LEFT JOIN、FULL OUTER JOIN、几乎不用RIGHT JOIN

GROUP BY聚合：明确COUNT 与 COUNT(DISTINCT)的区别「分布式环境中COUNT(DISTINCT)速度慢」

1
2
3
4
5
# 基数缩减：将高基数维度 转为低基数，例如按年龄分桶：
SELECT CASE WHEN age > 30 THEN 'old' ELSE 'young' END as 'age_bucket',
 count(1) 
FROM USERS
GROUP BY 1
1
2
# CASE WHEN + 聚合：例如按统计过期订单数：
COUNT(CASE WHEN status = 'expired' THEN order_id END)

4.2 查询结构对比：CTE/子查询/视图/临时表

类型 使用场景 注意事项
子查询
Subquery		 几乎不用
比如：WHERE筛选时查辅助条件、SELECT里算派生字段、关联临时结果。		 别套太多层！3层以上可读性爆炸，性能差；尽量用JOIN替代IN子查询（大数据量必卡）！
临时表
Temporary Table		 需重复使用某段逻辑
比如：大数据量分步计算、多报表共用中间数据、复杂JOIN拆分。		 用完记得清！会话结束自动删（部分库需手动），占存储，别建索引过度（小表没必要）！
视图
View		 (固定模板)把常用SQL保存成“虚拟表”，多人复用统一口径。
比如：核心指标口径、固定维度的报表底表、权限管控的脱敏数据。		 不存储数据，仅保存查询逻辑。多层视图嵌套会慢到崩溃，无存储、改源表结构可能崩！
CTE
公共表表达式		 (临时草稿纸)写复杂SQL时拆逻辑，一次性用、可读性优先。
比如：多层嵌套统计、一次性数据校验、递归查询（如组织架构）。		 最提升代码可读性，优先用。适合当前SQL会话生效，重复用会重复计算，大数据量递归易卡壳！

4.3 分布式环境中的SQL逻辑

触发“数据混洗（Shuffle）”的关键字：JOIN、GROUP BY、ORDER BY（需注意性能）
高可扩展性关键字（Map端直接执行）：SELECT 、FROM、WHERE、LIMIT（效率高）

4.4 窗口函数：面试高频考点

基础语法：

1
2
3
4
5
RAND() OVER (
	PARTTION BY <分区字段> -- 按什么分组（如部门、国家）
	ORDER BY <排序字段>  -- 组内排序规则
	rows BETWEEN <前n行> PRECEDING AND <后n行> FOLLWING -- 窗口范围
)

资料

推荐学习资源

  • Leetcode：侧重sql题目的练习
  • DataLemur：侧重面试级SQL题，贴近实战
  • StrataScratch：含真实业务场景的SQL练习