码到成功
sparksql的常见用法
# Spark SQL 常用语法实战：把常见查询写法一次讲清楚
如果你已经接触过 MySQL、PostgreSQL、SQL Server 这类数据库，Spark SQL 的上手门槛并不高。
它的核心价值在于：
- 保留了 SQL 的表达力
- 可以直接运行在 Spark 的分布式执行引擎上
- 很适合做清洗、聚合、宽表拼接、指标计算、明细分析
这篇文章围绕 Spark SQL 的常用写法展开，重点覆盖：
- 临时视图怎么用
- 常见查询怎么写
- 聚合、连接、窗口函数怎么写
- 数组、JSON、空值处理怎么写
- 写 Spark SQL 时有哪些实用建议
---
## 1. Spark SQL 是什么
Spark SQL 可以理解成 Spark 提供的一套结构化数据处理接口。
你可以用它来查询：
- Hive 表
- Parquet 文件
- ORC 文件
- JSON 文件
- CSV 文件
- DataFrame 注册出来的视图
对于开发者来说，最常见的工作方式有两种：
1. 直接写 SQL 查询
2. 用 DataFrame 读数据，再注册成视图，然后写 SQL
很多项目里，第二种方式更常见，因为读取数据和执行 SQL 可以拆开处理。
---
## 2. 准备一组示例数据
为了把常见语法讲清楚，下面准备三张逻辑表：
- `users`：用户表
- `products`：商品表
- `orders`：订单明细表
字段如下：
### 2.1 `users`
| 字段 | 含义 |
| --- | --- |
| `user_id` | 用户编号 |
| `user_name` | 用户名 |
| `city` | 城市 |
| `user_level` | 用户等级 |
### 2.2 `products`
| 字段 | 含义 |
| --- | --- |
| `product_id` | 商品编号 |
| `product_name` | 商品名 |
| `category` | 类目 |
| `price` | 单价 |
### 2.3 `orders`
| 字段 | 含义 |
| --- | --- |
| `order_id` | 订单编号 |
| `user_id` | 用户编号 |
| `product_id` | 商品编号 |
| `quantity` | 购买数量 |
| `amount` | 订单金额 |
| `status` | 订单状态 |
---
## 3. 把 DataFrame 注册成临时视图
Spark SQL 最常见的入口就是临时视图。
例如在 PySpark 里：
```python
users_df.createOrReplaceTempView("users")
products_df.createOrReplaceTempView("products")
orders_df.createOrReplaceTempView("orders")
```
注册完成后，就可以直接写 SQL：
```python
spark.sql("""
select *
from users
""").show()
```
如果你用的是 Spark Shell 或 Scala，写法也类似：
```scala
usersDf.createOrReplaceTempView("users")
productsDf.createOrReplaceTempView("products")
ordersDf.createOrReplaceTempView("orders")
```
这里有两个容易混淆的概念：
- `createOrReplaceTempView`：会话级临时视图
- `createOrReplaceGlobalTempView`：全局临时视图
日常开发中，大多数场景用临时视图就够了。
---
## 4. 最基础的查询写法
### 4.1 查询全部字段
```sql
select *
from orders;
```
这是最简单的写法，但在正式任务里通常不建议长期依赖 `select *`，原因是：
- 字段不够明确
- 上游表结构变动会带来不必要影响
- 读取字段过多时会增加开销
更常见的写法是显式列出字段：
```sql
select
order_id,
user_id,
product_id,
amount,
status
from orders;
```
### 4.2 条件过滤
```sql
select
order_id,
user_id,
amount,
status
from orders
where amount >= 100
and status = 'paid';
```
常见条件包括：
- 比较运算：`=`, `<>`, `>`, `<`, `>=`, `<=`
- 区间判断：`between ... and ...`
- 集合判断：`in (...)`
- 空值判断：`is null`, `is not null`
- 多条件组合：`and`, `or`, `not`
例如：
```sql
select *
from users
where city in ('Shanghai', 'Beijing')
and user_level is not null;
```
### 4.3 排序与取前几行
```sql
select
order_id,
user_id,
amount
from orders
order by amount desc
limit 10;
```
这个写法常用于：
- 查看金额最高的订单
- 观察样本数据
- 检查计算结果是否符合预期
---
## 5. 去重、别名与表达式
### 5.1 去重
```sql
select distinct city
from users;
```
如果需要按多个字段去重：
```sql
select distinct user_id, city
from users;
```
### 5.2 字段别名
```sql
select
user_id as uid,
user_name as uname,
user_level as level_name
from users;
```
### 5.3 直接写计算表达式
```sql
select
order_id,
quantity,
amount,
amount / quantity as unit_amount
from orders
where quantity > 0;
```
Spark SQL 支持在 `select` 中直接写大量表达式，这也是它非常适合做指标计算的原因之一。
---
## 6. `CASE WHEN`：做条件判断最常用
`CASE WHEN` 在 Spark SQL 里出现频率很高，经常用于：
- 分类打标
- 业务映射
- 规则判断
- 指标分层
示例：
```sql
select
user_id,
amount,
case
when amount >= 1000 then 'high'
when amount >= 300 then 'middle'
else 'low'
end as amount_tag
from orders;
```
再比如对订单状态做中文映射：
```sql
select
order_id,
status,
case
when status = 'paid' then '已支付'
when status = 'cancelled' then '已取消'
when status = 'refunded' then '已退款'
else '其他状态'
end as status_name
from orders;
```
---
## 7. 聚合查询：`GROUP BY`、`HAVING`
聚合是 Spark SQL 里最常见的一类操作。
### 7.1 按用户统计订单金额
```sql
select
user_id,
count(*) as order_cnt,
sum(amount) as total_amount,
avg(amount) as avg_amount,
max(amount) as max_amount,
min(amount) as min_amount
from orders
group by user_id;
```
### 7.2 按城市统计用户数
```sql
select
city,
count(*) as user_cnt
from users
group by city;
```
### 7.3 使用 `HAVING` 过滤聚合结果
```sql
select
user_id,
sum(amount) as total_amount
from orders
group by user_id
having sum(amount) >= 1000;
```
`WHERE` 和 `HAVING` 的区别非常关键：
- `WHERE`：过滤明细行
- `HAVING`：过滤聚合结果
---
## 8. 多表连接：`JOIN` 是高频语法
Spark SQL 的很多核心任务，本质上都是多表拼接。
### 8.1 内连接
```sql
select
o.order_id,
u.user_name,
p.product_name,
o.quantity,
o.amount
from orders o
join users u
on o.user_id = u.user_id
join products p
on o.product_id = p.product_id;
```
内连接只保留关联成功的数据。
### 8.2 左连接
```sql
select
u.user_id,
u.user_name,
o.order_id,
o.amount
from users u
left join orders o
on u.user_id = o.user_id;
```
左连接适合：
- 保留主表全部数据
- 查看哪些记录没有匹配上
- 做标签补充
### 8.3 连接时的常见注意点
连接查询里最容易出问题的是这几类情况：
- 连接键类型不一致
- 一对多关系导致结果放大
- 字段重名没有加表别名
- 维表重复导致明细被意外膨胀
所以在写连接 SQL 时，建议明确：
- 连接键是什么
- 主表是谁
- 连接后预期行数会不会变化
---
## 9. 子查询与 CTE
复杂 SQL 往往离不开子查询和 CTE。
### 9.1 子查询
```sql
select *
from (
select
user_id,
sum(amount) as total_amount
from orders
group by user_id
) t
where t.total_amount >= 1000;
```
### 9.2 CTE 写法
CTE 就是 `WITH` 子句，适合把复杂逻辑拆开写。
```sql
with user_amount as (
select
user_id,
sum(amount) as total_amount
from orders
group by user_id
),
user_profile as (
select
u.user_id,
u.user_name,
u.city,
a.total_amount
from users u
left join user_amount a
on u.user_id = a.user_id
)
select *
from user_profile
where total_amount >= 1000;
```
这种写法的优点很明显：
- 可读性更高
- 分层更清晰
- 更适合维护复杂业务逻辑
---
## 10. 窗口函数：做排名、组内统计特别方便
窗口函数是 Spark SQL 里非常实用的一块内容。
### 10.1 组内排名
```sql
select
user_id,
order_id,
amount,
row_number() over (
partition by user_id
order by amount desc
) as rn
from orders;
```
这个写法常用于：
- 每个用户金额最高的一笔订单
- 每个类目金额最高的商品
- 每个分组取前 N 条记录
### 10.2 每组取金额最高的一条
```sql
with ranked_orders as (
select
user_id,
order_id,
amount,
row_number() over (
partition by user_id
order by amount desc, order_id asc
) as rn
from orders
)
select
user_id,
order_id,
amount
from ranked_orders
where rn = 1;
```
### 10.3 累计求和
```sql
select
user_id,
order_id,
amount,
sum(amount) over (
partition by user_id
order by amount desc
rows between unbounded preceding and current row
) as running_amount
from orders;
```
除了 `row_number()`，常见窗口函数还包括：
- `rank()`
- `dense_rank()`
- `sum() over (...)`
- `avg() over (...)`
- `count() over (...)`
---
## 11. 空值处理：`null` 相关写法要熟
空值处理在数据开发里非常常见。
### 11.1 判断空值
```sql
select *
from users
where city is null;
```
```sql
select *
from users
where city is not null;
```
### 11.2 用默认值填充
```sql
select
user_id,
coalesce(city, 'unknown') as city_name
from users;
```
`coalesce` 的作用是返回第一个非空值。
例如：
```sql
select
user_id,
coalesce(city, 'unknown') as city_name,
coalesce(user_level, 'normal') as level_name
from users;
```
### 11.3 避免空值参与计算带来歧义
```sql
select
order_id,
coalesce(amount, 0) as safe_amount
from orders;
```
---
## 12. 字符串处理函数
字符串处理也是 Spark SQL 的常见需求。
### 12.1 拼接字符串
```sql
select
user_id,
concat(user_name, '_', city) as user_tag
from users;
```
### 12.2 截取与替换
```sql
select
product_name,
substr(product_name, 1, 4) as short_name,
regexp_replace(product_name, ' ', '') as clean_name
from products;
```
### 12.3 大小写转换与去空格
```sql
select
trim(user_name) as user_name_clean,
upper(city) as city_upper,
lower(city) as city_lower
from users;
```
常见字符串函数包括：
- `concat`
- `substr`
- `length`
- `trim`
- `upper`
- `lower`
- `regexp_replace`
- `split`
---
## 13. 数组与拆分：`explode` 很实用
实际业务里，经常遇到一行里带一个数组字段，或者某个字段里存着分隔字符串。
这时 `explode` 很好用。
### 13.1 拆分字符串再展开
假设有一个字段 `tags`，内容类似：
```text
phone,electronics,hot
```
可以这样写：
```sql
select
product_id,
explode(split(tags, ',')) as tag
from product_tags;
```
执行后，一行会被拆成多行。
例如原始数据：
```text
product_id = 101, tags = phone,electronics,hot
```
可能会变成：
```text
101 phone
101 electronics
101 hot
```
### 13.2 `explode_outer`
如果担心空数组或空字段导致数据直接丢掉，可以考虑：
```sql
select
product_id,
explode_outer(split(tags, ',')) as tag
from product_tags;
```
---
## 14. JSON 处理：Spark SQL 也很常见
很多原始数据都带 JSON 字段，Spark SQL 对这类场景支持不错。
### 14.1 提取 JSON 字段
假设表 `event_log` 中有个字段 `payload`：
```json
{"page":"home","channel":"app","score":98}
```
可以直接取值：
```sql
select
get_json_object(payload, '$.page') as page_name,
get_json_object(payload, '$.channel') as channel_name
from event_log;
```
### 14.2 把 JSON 解析成结构化字段
```sql
select
from_json(
payload,
'page string, channel string, score int'
) as payload_obj
from event_log;
```
再进一步取子字段：
```sql
with parsed_log as (
select
from_json(
payload,
'page string, channel string, score int'
) as payload_obj
from event_log
)
select
payload_obj.page,
payload_obj.channel,
payload_obj.score
from parsed_log;
```
---
## 15. `UNION` 与 `UNION ALL`
如果要合并多份结构一致的数据，通常会用到这两个语法。
### 15.1 `UNION ALL`
```sql
select user_id, amount
from orders_a
union all
select user_id, amount
from orders_b;
```
`UNION ALL` 会直接拼接结果，不去重。
### 15.2 `UNION`
```sql
select user_id, amount
from orders_a
union
select user_id, amount
from orders_b;
```
`UNION` 会去重，代价通常更高。
如果业务不要求去重，优先考虑 `UNION ALL`。
---
## 16. 去重保留一条：这是高频需求
很多明细表会出现重复数据，常见处理方式是配合窗口函数去重。
```sql
with dedup_orders as (
select
*,
row_number() over (
partition by order_id
order by amount desc
) as rn
from orders
)
select *
from dedup_orders
where rn = 1;
```
这个模式很常见，关键点在于：
- 用什么字段定义“重复”
- 用什么排序规则决定“保留哪一条”
---
## 17. 创建结果表与写入结果
Spark SQL 不只是查数据，也可以落结果。
### 17.1 建表查询
```sql
create table user_order_summary as
select
user_id,
count(*) as order_cnt,
sum(amount) as total_amount
from orders
group by user_id;
```
### 17.2 插入已有表
```sql
insert overwrite table user_order_summary
select
user_id,
count(*) as order_cnt,
sum(amount) as total_amount
from orders
group by user_id;
```
不同环境里，表管理方式可能不同：
- 有的基于 Hive Metastore
- 有的基于 Lakehouse 体系
- 有的直接查外部文件
SQL 写法会有一些差别，但整体思路基本一致。
---
## 18. 写 Spark SQL 时的几个实用建议
### 18.1 少用 `select *`
显式列字段通常更稳：
- 可读性更好
- 更方便排查问题
- 有助于减少无用列读取
### 18.2 大表连接时看清主键关系
如果维表有重复，连接结果可能被放大很多倍。
这是 Spark SQL 任务里非常常见的问题来源。
### 18.3 复杂逻辑尽量拆成 CTE
与其写一条极长 SQL，不如按语义拆成多段 `WITH`。
### 18.4 关注执行计划
可以用：
```sql
explain
select
u.city,
sum(o.amount) as total_amount
from users u
join orders o
on u.user_id = o.user_id
group by u.city;
```
执行计划能帮助你判断：
- 有没有多余扫描
- 有没有不必要的 shuffle
- 连接策略是否合理
### 18.5 小表可以考虑广播
在部分场景里，可以显式提示广播小表：
```sql
select /*+ broadcast(u) */
o.order_id,
o.amount,
u.user_name
from orders o
join users u
on o.user_id = u.user_id;
```
这类写法常用于：
- 明细大表关联小维表
- 减少 shuffle 成本
是否生效还要结合执行计划来看。
---
## 19. 一组很常用的 Spark SQL 片段
如果只保留最常用的几类写法，基本可以记住下面这些：
### 19.1 过滤
```sql
select *
from orders
where status = 'paid'
and amount >= 100;
```
### 19.2 聚合
```sql
select
user_id,
sum(amount) as total_amount
from orders
group by user_id;
```
### 19.3 连接
```sql
select
o.order_id,
u.user_name
from orders o
left join users u
on o.user_id = u.user_id;
```
### 19.4 排名
```sql
select
user_id,
order_id,
amount,
row_number() over (
partition by user_id
order by amount desc
) as rn
from orders;
```
### 19.5 空值处理
```sql
select
user_id,
coalesce(city, 'unknown') as city_name
from users;
```
---
## 20. 总结
Spark SQL 的优势不在“语法有多特别”，而在于它把熟悉的 SQL 能力放进了 Spark 的分布式执行环境里。
对于大多数数据开发任务，真正高频的内容就是这几类：
- 过滤
- 聚合
- 连接
- 窗口函数
- 空值处理
- 数组与 JSON 解析
如果你已经能把这几类写顺手，很多 Spark 数据处理任务就能比较自然地展开。
可以用一句话概括这篇文章的重点：
> Spark SQL 的核心，不是记住所有函数，而是熟练掌握常见数据处理模式。
---
## 21. 附：一段可直接运行的示例结构
下面是一段很常见的 PySpark + Spark SQL 组合写法：
```python
users_df.createOrReplaceTempView("users")
orders_df.createOrReplaceTempView("orders")
result_df = spark.sql("""
with user_amount as (
select
user_id,
count(*) as order_cnt,
sum(amount) as total_amount
from orders
where status = 'paid'
group by user_id
)
select
u.user_id,
u.user_name,
u.city,
a.order_cnt,
a.total_amount
from users u
left join user_amount a
on u.user_id = a.user_id
""")
result_df.show()
```
这段代码里已经包含了 Spark SQL 最核心的几种能力：
- 临时视图
- 条件过滤
- 聚合
- CTE
- 左连接
把这些内容掌握扎实，Spark SQL 的日常开发就已经具备不错的基础了。