第13课：PySpark SQL全语法精讲【过滤分组聚合排序多表关联查询实战】

---

一、课前导读

经过上一节课的学习，你已经掌握了DataFrame的多种创建方式和基础操作。你能够从文件、RDD、JDBC等数据源创建DataFrame，也能熟练地使用select、filter、withColumn等操作进行数据清洗。但是，当面对复杂的数据分析需求时——比如需要多层嵌套的过滤条件、多表连接、窗口函数、子查询——仅仅使用DataFrame的DSL方法可能会让代码变得冗长且难以维护。

试想一下，你需要写一个如下的查询：
“统计2024年第一季度，每个省份中消费总额排名前3的商品类别，并且过滤掉总销售额低于10万的类别。”
如果用DataFrame的链式filter、groupBy、join、rank等实现，代码会非常长。而如果用SQL，只需要一段清晰的声明式语句即可。

Spark SQL允许你在DataFrame上直接运行标准SQL，并且能将SQL和DataFrame API混合使用。这不仅让复杂逻辑的表达变得简单，还能利用Catalyst优化器生成高效的执行计划。本课程将带你全面掌握PySpark SQL的核心语法，包括过滤、分组、聚合、排序、多表关联（内连接、外连接、半连接等）、子查询以及常用SQL函数。学完这节课，你将能够像操作传统数据库一样，轻松应对各种复杂的数据分析场景。

二、学习目标

完成本节课的学习后，你将能够：

1. 使用SQL查询DataFrame：将DataFrame注册为临时视图，使用spark.sql()执行SQL语句
2. 掌握基础SQL语法：WHERE、GROUP BY、HAVING、ORDER BY、LIMIT等
3. 编写聚合查询：使用COUNT、SUM、AVG、MAX、MIN以及聚合函数与GROUP BY的组合
4. 执行多表连接：INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、SEMI JOIN、ANTI JOIN
5. 使用子查询：标量子查询、IN/EXISTS子查询、关联子查询
6. 应用窗口函数：ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、SUM OVER
7. 使用常用内置函数：字符串函数、日期函数、条件函数（CASE WHEN）
8. 优化SQL性能：理解谓词下推、分区剪枝、广播Join等概念

三、核心理论知识点

知识点	说明
临时视图	createOrReplaceTempView创建会话级临时表；createGlobalTempView创建跨会话全局表
基础查询	SELECT、FROM、WHERE、DISTINCT
分组聚合	GROUP BY + 聚合函数，以及HAVING对聚合结果过滤
排序	ORDER BY（ASC/DESC），LIMIT限制条数
连接类型	INNER、LEFT、RIGHT、FULL、LEFT SEMI、LEFT ANTI、CROSS
连接优化	广播提示/*+ BROADCAST(t) */
子查询	标量子查询、IN/NOT IN、EXISTS/NOT EXISTS、关联子查询
窗口函数	ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)，RANK，DENSE_RANK，LAG，LEAD，聚合窗口
常用函数	if/case when、datediff、date_add、year/month、concat、substring、regexp_extract
集合操作	UNION（去重）、UNION ALL、INTERSECT、EXCEPT

四、原理通俗讲解

4.1 SQL在Spark中的执行流程

当你执行spark.sql("SELECT ...")时，Spark SQL做了几件事：

1. 解析：将SQL字符串解析为“未解析的逻辑计划”（Unresolved Logical Plan），此时表名、列名尚未绑定到具体的DataFrame。
2. 分析：使用Catalog（元数据仓库）将表名解析为具体的DataFrame，检查列是否存在，生成“逻辑计划”。
3. 优化：Catalyst优化器对逻辑计划进行重写，例如：谓词下推（将WHERE条件推到数据源）、列剪枝（只读取需要的列）、常量折叠等。
4. 物理规划：将优化后的逻辑计划转换为一个或多个物理计划，并基于成本模型选择最佳方案（如选择BroadcastJoin还是SortMergeJoin）。
5. 执行：生成RDD并执行，返回新的DataFrame或结果。

这个过程与DataFrame API完全相同，因此SQL和DataFrame DSL的性能没有本质差异，可以根据可读性选择。

4.2 为什么SQL有时比DataFrame DSL更简洁？

SQL是一种声明式语言，你只需描述“想要什么”，不用写“如何做”。DataFrame DSL虽然也是声明式，但嵌套方法调用在表达复杂逻辑时容易变得复杂。例如，多层子查询用SQL写清晰易懂，而用DataFrame的join、select等组合会非常繁琐。

4.3 临时视图与全局临时视图

临时视图的作用域是当前SparkSession，会话结束即消失。全局临时视图作用于整个Spark应用（跨会话），存储在global_temp数据库中，访问需加前缀global_temp.。

df.createOrReplaceTempView("my_view")
spark.sql("SELECT * FROM my_view")  # 有效

df.createGlobalTempView("global_view")
spark.sql("SELECT * FROM global_temp.global_view")  # 有效

五、重点概念拆解

5.1 基础查询语法

SELECT [DISTINCT] 列名1 [AS 别名], 列名2, ...
FROM 表名
WHERE 条件表达式
GROUP BY 列名1, 列名2
HAVING 聚合条件
ORDER BY 列名1 [ASC|DESC], 列名2 ...
LIMIT N

• WHERE：在分组前对行进行过滤，可使用列表达式、逻辑运算（AND/OR）、IN、BETWEEN、LIKE等。
• GROUP BY：指定分组列，配合聚合函数使用。
• HAVING：在分组后对聚合结果进行过滤（WHERE不能使用聚合函数）。
• ORDER BY：全局排序（会触发Shuffle），支持多列及不同方向。
• LIMIT：限制返回行数，常用于快速查看数据。

5.2 聚合函数

常用聚合函数：COUNT(*)，COUNT(DISTINCT col)，SUM(col)，AVG(col)，MAX(col)，MIN(col)，STDDEV(col)，VARIANCE(col)。

注意事项：

• COUNT(*) 包括NULL行，COUNT(col) 不包括NULL。
• 聚合函数会忽略NULL值（除COUNT(*)外）。

5.3 连接类型详解

连接类型	SQL语法	说明
内连接	INNER JOIN	只返回匹配的行
左外连接	LEFT JOIN 或 LEFT OUTER JOIN	返回左表所有行，右表不匹配则为NULL
右外连接	RIGHT JOIN	返回右表所有行
全外连接	FULL OUTER JOIN	返回所有行，不匹配则为NULL
左半连接	LEFT SEMI JOIN	返回左表中在右表有匹配的行，类似IN
左反连接	LEFT ANTI JOIN	返回左表中在右表无匹配的行，类似NOT IN
交叉连接	CROSS JOIN	笛卡尔积，慎用

Spark SQL中JOIN默认是INNER JOIN。对于大数据量连接，优化策略：广播小表（/*+ BROADCAST(t) */），避免全量Shuffle。

5.4 子查询

• 标量子查询：返回单行单列，可以在SELECT或WHERE中使用。

  SELECT name, (SELECT AVG(salary) FROM employees) as avg_salary FROM employees
  

• IN/NOT IN子查询：常用于过滤。

  SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active=1)
  

• EXISTS/NOT EXISTS：检查子查询是否有行返回。

  SELECT * FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.id)
  

• 关联子查询：子查询中引用了外层表的列，逐行执行效率较低，尽量改写为JOIN。

5.5 窗口函数

窗口函数在保持行数不变的情况下，对每一行计算基于“窗口”的聚合或排名值。语法：

函数() OVER (PARTITION BY 列1, 列2 ORDER BY 列3 [ROWS/RANGE BETWEEN ...])

常见窗口函数：

• 排名：ROW_NUMBER()（连续唯一）、RANK()（有间隔）、DENSE_RANK()（无间隔）
• 取值：LAG(col, offset, default)（前偏移行）、LEAD(...)（后偏移行）
• 聚合窗口：SUM(col) OVER(...)、AVG(col) OVER(...)、MAX(col) OVER(...)

窗口函数通常用于计算排名、移动平均、环比同比等。

5.6 常用内置函数

• 字符串：CONCAT(str1, str2)、SUBSTRING(str, pos, len)、TRIM、UPPER/LOWER、REPLACE、REGEXP_EXTRACT、REGEXP_REPLACE
• 日期：CURRENT_DATE、DATE_ADD(date, days)、DATE_DIFF(end, start)、YEAR(date)、MONTH(date)、DATE_FORMAT(date, format)
• 条件：IF(cond, true_val, false_val)、CASE WHEN ... THEN ... ELSE ... END
• 类型转换：CAST(col AS type)
• 集合：COLLECT_LIST、COLLECT_SET、EXPLODE

5.7 集合操作

• UNION：合并两个查询结果，去重（相当于UNION DISTINCT）
• UNION ALL：合并，不去重，效率高
• INTERSECT：交集
• EXCEPT（或MINUS）：差集（左表有但右表无）

要求两个查询的列数相同且类型兼容。

六、易错点避坑

6.1 混淆WHERE和HAVING

WHERE在分组前过滤行，HAVING在分组后过滤聚合结果。错误示例：

SELECT dept, AVG(salary) FROM emp WHERE AVG(salary) > 5000 GROUP BY dept  -- 错误

正确为使用HAVING。

6.2 使用LEFT JOIN后过滤右表列导致隐式内连接

SELECT * FROM orders LEFT JOIN users ON orders.user_id = users.id WHERE users.age > 18

WHERE条件会过滤掉右表为NULL的行，实际变成内连接。如需保留左表所有行，应将条件放到ON子句：

SELECT * FROM orders LEFT JOIN users ON orders.user_id = users.id AND users.age > 18

6.3 子查询效率低，应改写为JOIN

关联子查询往往逐行执行，性能差。例如：

SELECT * FROM t1 WHERE col1 IN (SELECT col2 FROM t2 WHERE t2.key = t1.key)

应改写为JOIN：

SELECT t1.* FROM t1 JOIN t2 ON t1.key = t2.key AND t1.col1 = t2.col2

6.4 窗口函数中忘记PARTITION BY导致全部分为一个分区

ROW_NUMBER() OVER (ORDER BY score)会对所有行排序后编号，导致数据倾斜（尤其大数据量）。通常需要先PARTITION BY分组，再在组内排序。

6.5 对大数据量使用ORDER BY全排序

ORDER BY会触发全局排序，数据会经过一个分区，可能导致性能瓶颈。如果只需要取Top N，可以结合LIMIT，Spark会优化为每个分区取TopN后再合并。

七、完整实战案例

本案例将使用一个电商订单数据集，演示从基础查询到复杂分析的完整SQL语法。我们将创建订单表、用户表、商品表，通过SQL完成以下分析：

1. 基础过滤与聚合
2. 分组统计（销售额、订单量）
3. 多表关联（订单+用户+商品）
4. 窗口函数（用户订单排名、环比增长）
5. 子查询与集合操作

# ============== spark_sql_complete_demo.py ==============
# 功能：PySpark SQL 全语法实战（过滤、分组、聚合、排序、多表关联、窗口函数、子查询）
# 数据量：模拟10万订单，1万用户，1000商品
# 环境：PySpark 3.x

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DoubleType, TimestampType
from pyspark.sql.functions import expr
import random
from datetime import datetime, timedelta

# ========== 1. 创建SparkSession ==========
spark = SparkSession.builder \
    .appName("SparkSQLCompleteDemo") \
    .master("local[4]") \
    .config("spark.sql.shuffle.partitions", "8") \
    .config("spark.sql.adaptive.enabled", "true") \
    .getOrCreate()

sc = spark.sparkContext
sc.setLogLevel("WARN")

print("=" * 80)
print("PySpark SQL 全语法实战")
print("=" * 80)

# ========== 2. 生成模拟数据 ==========
print("\n生成模拟数据集...")
# 用户表
num_users = 10000
users_data = []
for i in range(1, num_users + 1):
    age = random.randint(18, 70)
    city = random.choice(["北京", "上海", "广州", "深圳", "杭州", "成都"])
    users_data.append((i, f"user_{i}", age, city))
user_schema = StructType([
    StructField("user_id", IntegerType(), False),
    StructField("user_name", StringType(), True),
    StructField("age", IntegerType(), True),
    StructField("city", StringType(), True)
])
users_df = spark.createDataFrame(users_data, schema=user_schema)

# 商品表
num_products = 1000
products_data = []
for i in range(1, num_products + 1):
    category = random.choice(["电子", "服装", "食品", "家居", "美妆"])
    price = round(random.uniform(10, 2000), 2)
    products_data.append((i, f"product_{i}", category, price))
product_schema = StructType([
    StructField("product_id", IntegerType(), False),
    StructField("product_name", StringType(), True),
    StructField("category", StringType(), True),
    StructField("price", DoubleType(), True)
])
products_df = spark.createDataFrame(products_data, schema=product_schema)

# 订单表：10万条订单，时间跨度90天
num_orders = 100000
orders_data = []
start_date = datetime(2024, 1, 1)
for i in range(1, num_orders + 1):
    user_id = random.randint(1, num_users)
    product_id = random.randint(1, num_products)
    quantity = random.randint(1, 5)
    order_date = start_date + timedelta(days=random.randint(0, 89))
    # 金额 = 商品价格 * 数量，但价格需要从商品表关联，这里先占位，后续用SQL join计算
    # 为了简化，直接生成随机金额
    amount = round(random.uniform(20, 5000), 2)
    orders_data.append((i, user_id, product_id, quantity, amount, order_date))
order_schema = StructType([
    StructField("order_id", IntegerType(), False),
    StructField("user_id", IntegerType(), True),
    StructField("product_id", IntegerType(), True),
    StructField("quantity", IntegerType(), True),
    StructField("amount", DoubleType(), True),
    StructField("order_date", TimestampType(), True)
])
orders_df = spark.createDataFrame(orders_data, schema=order_schema)

# 注册临时视图
users_df.createOrReplaceTempView("users")
products_df.createOrReplaceTempView("products")
orders_df.createOrReplaceTempView("orders")

print("数据生成完毕:")
print(f"用户数: {users_df.count()}")
print(f"商品数: {products_df.count()}")
print(f"订单数: {orders_df.count()}")

# ========== 3. 基础查询：WHERE、DISTINCT、ORDER BY、LIMIT ==========
print("\n" + "=" * 80)
print("步骤1: 基础查询语法")
print("=" * 80)

# 3.1 简单查询与过滤
sql1 = """
    SELECT user_id, city, age 
    FROM users 
    WHERE age BETWEEN 25 AND 35 AND city IN ('北京', '上海')
    LIMIT 10
"""
print("1. 查询北京上海年龄25-35岁的用户（前10条）:")
spark.sql(sql1).show()

# 3.2 去重 DISTINCT
sql2 = """
    SELECT DISTINCT city 
    FROM users
    ORDER BY city
"""
print("2. 城市列表:")
spark.sql(sql2).show(10)

# 3.3 排序与限制
sql3 = """
    SELECT order_id, user_id, amount, order_date
    FROM orders
    WHERE amount > 1000
    ORDER BY amount DESC
    LIMIT 10
"""
print("3. 金额大于1000的订单TOP10:")
spark.sql(sql3).show()

# ========== 4. 分组聚合：GROUP BY、HAVING、聚合函数 ==========
print("\n" + "=" * 80)
print("步骤2: 分组聚合")
print("=" * 80)

# 4.1 按城市统计用户数、平均年龄
sql4 = """
    SELECT city, 
           COUNT(*) as user_cnt, 
           AVG(age) as avg_age,
           MAX(age) as max_age
    FROM users
    GROUP BY city
    ORDER BY user_cnt DESC
"""
print("4. 各城市用户统计:")
spark.sql(sql4).show()

# 4.2 按商品类别统计销售额、订单数、平均单价（需要关联订单与商品）
sql5 = """
    SELECT p.category,
           COUNT(DISTINCT o.order_id) as order_cnt,
           SUM(o.amount) as total_sales,
           AVG(o.amount) as avg_order_amount
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
    ORDER BY total_sales DESC
"""
print("5. 各商品类别销售统计:")
spark.sql(sql5).show()

# 4.3 使用HAVING过滤分组后结果（销售额>50000的类别）
sql6 = """
    SELECT p.category,
           SUM(o.amount) as total_sales
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    GROUP BY p.category
    HAVING SUM(o.amount) > 50000
    ORDER BY total_sales DESC
"""
print("6. 销售额大于5万的类别:")
spark.sql(sql6).show()

# ========== 5. 多表连接 ==========
print("\n" + "=" * 80)
print("步骤3: 多表连接 (JOIN)")
print("=" * 80)

# 5.1 内连接：订单+用户+商品，获取完整信息
sql7 = """
    SELECT o.order_id, u.user_name, u.city, p.product_name, p.category, o.quantity, o.amount, o.order_date
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
    JOIN products p ON o.product_id = p.product_id
    LIMIT 10
"""
print("7. 订单详细信息（内连接）:")
spark.sql(sql7).show(10, truncate=False)

# 5.2 左外连接：找出未下过订单的用户（左连接+过滤右表NULL）
sql8 = """
    SELECT u.user_id, u.user_name, o.order_id
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    WHERE o.order_id IS NULL
    LIMIT 10
"""
print("8. 从未下过订单的用户（左反连接效果）:")
spark.sql(sql8).show()

# 更标准写法：使用LEFT ANTI JOIN
sql8_anti = """
    SELECT u.user_id, u.user_name
    FROM users u
    LEFT ANTI JOIN orders o ON u.user_id = o.user_id
    LIMIT 10
"""
print("LEFT ANTI JOIN 写法:")
spark.sql(sql8_anti).show()

# 5.3 半连接：查询有过订单的用户（类似IN子查询）
sql9 = """
    SELECT user_id, user_name
    FROM users
    WHERE user_id IN (SELECT DISTINCT user_id FROM orders)
    LIMIT 10
"""
print("9. 有过订单的用户（IN子查询）:")
spark.sql(sql9).show()

# 更高效：SEMI JOIN
sql9_semi = """
    SELECT u.user_id, u.user_name
    FROM users u
    LEFT SEMI JOIN orders o ON u.user_id = o.user_id
    LIMIT 10
"""
print("LEFT SEMI JOIN 写法:")
spark.sql(sql9_semi).show()

# 5.4 广播提示：对大表与小表连接时，显式广播小表
sql10 = """
    SELECT /*+ BROADCAST(p) */ o.order_id, p.product_name
    FROM orders o
    JOIN products p ON o.product_id = p.product_id
    LIMIT 10
"""
print("10. 广播提示示例（会使用BroadcastHashJoin）:")
spark.sql(sql10).show()

# ========== 6. 子查询 ==========
print("\n" + "=" * 80)
print("步骤4: 子查询")
print("=" * 80)

# 6.1 标量子查询：查询每个订单金额相对于整体平均金额的差值
sql11 = """
    SELECT order_id, amount,
           (SELECT AVG(amount) FROM orders) as global_avg,
           amount - (SELECT AVG(amount) FROM orders) as diff
    FROM orders
    LIMIT 10
"""
print("11. 标量子查询（平均订单金额）:")
spark.sql(sql11).show()

# 6.2 IN子查询：购买了特定类别商品的订单
sql12 = """
    SELECT order_id, user_id, amount
    FROM orders
    WHERE product_id IN (SELECT product_id FROM products WHERE category = '电子')
    LIMIT 10
"""
print("12. 购买了电子类商品的订单:")
spark.sql(sql12).show()

# 6.3 EXISTS子查询：订单金额大于该用户平均订单金额的订单
sql13 = """
    SELECT o1.order_id, o1.user_id, o1.amount
    FROM orders o1
    WHERE EXISTS (
        SELECT 1 
        FROM orders o2 
        WHERE o2.user_id = o1.user_id 
        GROUP BY o2.user_id 
        HAVING AVG(o2.amount) < o1.amount
    )
    LIMIT 10
"""
print("13. 金额高于自己平均订单金额的订单（EXISTS关联子查询）:")
spark.sql(sql13).show()

# 注意：关联子查询效率可能较低，建议改写为JOIN，这里仅做语法演示

# ========== 7. 窗口函数 ==========
print("\n" + "=" * 80)
print("步骤5: 窗口函数")
print("=" * 80)

# 7.1 ROW_NUMBER：每个用户订单金额排名，取每个用户金额最高的3个订单
sql14 = """
    SELECT user_id, order_id, amount,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rn
    FROM orders
    QUALIFY rn <= 3
    LIMIT 20
"""
print("14. 每个用户金额最高的3个订单（ROW_NUMBER）:")
# 注意：QUALIFY 是Spark 3.0+支持的语法，等价于子查询过滤
# 如果版本低，改用子查询方式
try:
    spark.sql(sql14).show()
except:
    sql14_alt = """
        SELECT user_id, order_id, amount, rn
        FROM (
            SELECT user_id, order_id, amount,
                   ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) as rn
            FROM orders
        ) t
        WHERE rn <= 3
        LIMIT 20
    """
    spark.sql(sql14_alt).show()

# 7.2 RANK 和 DENSE_RANK：处理金额相同的情况
sql15 = """
    SELECT user_id, amount,
           RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as rank,
           DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) as dense_rank
    FROM orders
    WHERE user_id = 1
    LIMIT 10
"""
print("15. 用户1的订单金额排名（RANK vs DENSE_RANK）:")
spark.sql(sql15).show()

# 7.3 LAG/LEAD：计算每个用户相邻订单的金额变化
sql16 = """
    SELECT user_id, order_date, amount,
           LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_date) as prev_amount,
           amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_date) as diff
    FROM orders
    WHERE user_id = 1
    ORDER BY order_date
    LIMIT 10
"""
print("16. 用户1订单金额环比（LAG）:")
spark.sql(sql16).show()

# 7.4 聚合窗口：计算每个用户截止到当前订单的累计金额
sql17 = """
    SELECT user_id, order_date, amount,
           SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total
    FROM orders
    WHERE user_id = 1
    ORDER BY order_date
"""
print("17. 用户1累计消费金额:")
spark.sql(sql17).show(10)

# ========== 8. 集合操作 ==========
print("\n" + "=" * 80)
print("步骤6: 集合操作 (UNION, INTERSECT, EXCEPT)")
print("=" * 80)

# 8.1 UNION ALL vs UNION
sql18 = """
    SELECT user_id, city FROM users WHERE city = '北京'
    UNION ALL
    SELECT user_id, city FROM users WHERE city = '上海'
    LIMIT 10
"""
print("18. UNION ALL 北京和上海用户:")
spark.sql(sql18).show(10)

# 8.2 INTERSECT：既在北京又在上海的用户（不可能，演示语法）
sql19 = """
    SELECT user_id FROM users WHERE city = '北京'
    INTERSECT
    SELECT user_id FROM users WHERE city = '上海'
"""
print("19. 同时在北京和上海的用户（空结果）:")
spark.sql(sql19).show()

# 8.3 EXCEPT：北京用户中排除有订单的用户
sql20 = """
    SELECT user_id FROM users WHERE city = '北京'
    EXCEPT
    SELECT DISTINCT user_id FROM orders
    LIMIT 10
"""
print("20. 北京用户中未下过订单的用户:")
spark.sql(sql20).show()

# ========== 9. 常用内置函数示例 ==========
print("\n" + "=" * 80)
print("步骤7: 内置函数（字符串、日期、条件）")
print("=" * 80)

# 9.1 字符串处理：隐藏用户名部分字符
sql21 = """
    SELECT user_id, 
           CONCAT(SUBSTRING(user_name, 1, 3), '***') as masked_name,
           UPPER(city) as city_upper
    FROM users
    LIMIT 10
"""
print("21. 字符串函数示例:")
spark.sql(sql21).show()

# 9.2 日期函数：提取年月，计算距今天数
sql22 = """
    SELECT order_id, order_date,
           YEAR(order_date) as order_year,
           MONTH(order_date) as order_month,
           DATE_DIFF(CURRENT_DATE(), DATE(order_date)) as days_ago
    FROM orders
    LIMIT 10
"""
print("22. 日期函数示例:")
spark.sql(sql22).show()

# 9.3 CASE WHEN：订单金额分级
sql23 = """
    SELECT order_id, amount,
           CASE 
               WHEN amount < 100 THEN '小额'
               WHEN amount BETWEEN 100 AND 500 THEN '中额'
               ELSE '大额'
           END as amount_level
    FROM orders
    LIMIT 10
"""
print("23. CASE WHEN 条件分类:")
spark.sql(sql23).show()

# ========== 10. 综合实战：月度销售分析报表 ==========
print("\n" + "=" * 80)
print("步骤8: 综合实战 - 月度销售报表")
print("=" * 80)

sql24 = """
    WITH monthly_sales AS (
        SELECT 
            DATE_FORMAT(o.order_date, 'yyyy-MM') as month,
            p.category,
            COUNT(DISTINCT o.order_id) as order_cnt,
            SUM(o.amount) as total_sales,
            AVG(o.amount) as avg_sale
        FROM orders o
        JOIN products p ON o.product_id = p.product_id
        GROUP BY DATE_FORMAT(o.order_date, 'yyyy-MM'), p.category
    ),
    ranked AS (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_sales DESC) as rank_in_month
        FROM monthly_sales
    )
    SELECT month, category, total_sales, order_cnt, avg_sale
    FROM ranked
    WHERE rank_in_month <= 3
    ORDER BY month, total_sales DESC
"""
print("24. 每月销售额前三的商品类别:")
spark.sql(sql24).show(20)

# ========== 11. 性能分析：查看执行计划 ==========
print("\n" + "=" * 80)
print("步骤9: 查看SQL执行计划")
print("=" * 80)

sql_explain = """
    SELECT u.city, SUM(o.amount) as total
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
    WHERE o.amount > 100
    GROUP BY u.city
"""
print("查询语句:")
print(sql_explain)
print("\n优化后的物理计划:")
spark.sql(sql_explain).explain(mode="simple")

# ========== 12. 清理 ==========
spark.stop()
print("\n✅ Spark SQL 全语法演示完成")

八、代码逐行解析

8.1 数据生成部分

使用循环生成用户、商品、订单数据，并定义了明确的StructType。订单表中的amount字段是随机生成的，没有严格关联商品价格，仅供演示SQL语法。实际生产环境中可以通过JOIN计算。

8.2 基础查询

WHERE age BETWEEN 25 AND 35 是语法糖，等价于 age >=25 AND age <=35。LIMIT在SQL中用于限制行数，Spark会将LIMIT下推到每个分区，提高效率。

8.3 分组聚合

GROUP BY后的列可以在SELECT中使用聚合函数。HAVING用于过滤分组后的聚合结果，注意WHERE和HAVING的顺序。

8.4 多表连接

• 内连接是最常用的关联方式。
• LEFT ANTI JOIN是Spark SQL特有的，比NOT IN子查询效率更高。
• /*+ BROADCAST(p) */是SQL hint，强制广播小表，适用于小表（几MB）与大表连接。

8.5 子查询

标量子查询必须返回一行一列，否则运行时错误。EXISTS子查询关联了外层表，效率较低，大数据量下应改写为JOIN。

8.6 窗口函数

ROW_NUMBER()在PARTITION BY分组内排序并编号，常用于TopN。LAG用于获取前一行值，计算环比。SUM OVER实现累计和。

8.7 集合操作

UNION ALL比UNION高效，因为不需要去重。EXCEPT对应差集。

8.8 内置函数

DATE_FORMAT、YEAR、MONTH等日期函数非常实用。CASE WHEN实现条件分支，性能优于UDF。

8.9 执行计划

explain()显示物理执行计划，可以观察是否使用了谓词下推、列剪枝、BroadcastJoin等优化。

九、业务场景落地应用

9.1 场景一：电商平台用户价值分层（RFM模型）

RFM模型通过最近一次消费（Recency）、频率（Frequency）、金额（Monetary）对用户分层。使用Spark SQL可以轻松计算。

WITH rfm_base AS (
    SELECT user_id,
           MAX(order_date) as last_order_date,
           COUNT(*) as freq,
           SUM(amount) as monetary
    FROM orders
    GROUP BY user_id
),
rfm_scores AS (
    SELECT user_id,
           NTILE(5) OVER (ORDER BY last_order_date DESC) as recency_score,
           NTILE(5) OVER (ORDER BY freq) as frequency_score,
           NTILE(5) OVER (ORDER BY monetary) as monetary_score
    FROM rfm_base
)
SELECT user_id,
       recency_score, frequency_score, monetary_score,
       (recency_score + frequency_score + monetary_score) as total_score,
       CASE 
           WHEN recency_score >= 4 AND frequency_score >= 4 AND monetary_score >= 4 THEN '高价值'
           WHEN recency_score >= 4 THEN '活跃'
           ELSE '一般'
       END as segment
FROM rfm_scores

9.2 场景二：用户留存分析（周留存率）

需要先构建用户首次购买日期，然后按周统计留存。

WITH first_purchase AS (
    SELECT user_id, MIN(DATE(order_date)) as first_date
    FROM orders
    GROUP BY user_id
),
cohorts AS (
    SELECT f.user_id, f.first_date,
           EXTRACT(WEEK FROM o.order_date) as week,
           EXTRACT(WEEK FROM f.first_date) as cohort_week
    FROM first_purchase f
    JOIN orders o ON f.user_id = o.user_id
)
SELECT cohort_week,
       COUNT(DISTINCT user_id) as new_users,
       SUM(CASE WHEN week = cohort_week THEN 1 ELSE 0 END) as week0,
       SUM(CASE WHEN week = cohort_week + 1 THEN 1 ELSE 0 END) as week1,
       SUM(CASE WHEN week = cohort_week + 2 THEN 1 ELSE 0 END) as week2
FROM cohorts
GROUP BY cohort_week
ORDER BY cohort_week

9.3 场景三：实时数仓中的DWD层加工

使用Spark SQL的INSERT OVERWRITE TABLE将ODS层数据清洗后写入DWD层分区表。

INSERT OVERWRITE TABLE dwd.order_detail PARTITION(dt='2024-01-01')
SELECT 
    order_id,
    user_id,
    product_id,
    quantity,
    amount,
    order_date
FROM ods.orders
WHERE dt='2024-01-01' AND amount > 0 AND user_id IS NOT NULL

十、常见报错排查

10.1 AnalysisException: Table or view not found

原因：临时视图未创建，或使用了全局视图但未加global_temp前缀。

解决：检查视图名称，使用spark.catalog.listTables()查看已注册的表。

10.2 AnalysisException: cannot resolve 'xxx' given input columns

原因：SQL中引用了不存在的列名，或大小写不匹配。

解决：检查列名，可使用DESCRIBE table查看Schema。

10.3 org.apache.spark.sql.AnalysisException: Hive support is required to CREATE Hive TABLE

原因：尝试创建Hive表，但SparkSession未启用Hive支持。

解决：创建时调用.enableHiveSupport()。

10.4 窗口函数报错 Window function without frame

原因：某些窗口函数（如ROW_NUMBER）不需要frame，但若写了ROWS BETWEEN可能导致错误。

解决：只写ORDER BY，不要添加ROWS子句。

10.5 LEFT ANTI JOIN 结果为空但预期非空

原因：连接条件错误，或右表NULL值导致。

解决：检查ON条件是否合理，考虑使用IS NULL过滤。

10.6 子查询返回多行导致运行时错误

原因：标量子查询返回了多行。

解决：确保子查询只返回一行，可以使用MAX()或LIMIT 1聚合。

十一、本节课知识点总结

SQL语法速查表

功能	示例
条件过滤	WHERE age > 18 AND city = '北京'
分组	GROUP BY category
分组后过滤	HAVING SUM(amount) > 1000
排序	ORDER BY amount DESC, order_date ASC
限制行数	LIMIT 10
内连接	JOIN ... ON ...
左连接	LEFT JOIN ... ON ...
左半连接	LEFT SEMI JOIN
左反连接	LEFT ANTI JOIN
标量子查询	SELECT (SELECT AVG(...))
EXISTS	WHERE EXISTS (SELECT 1 ...)
窗口排名	ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)
窗口累计	SUM(amount) OVER(PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
条件分支	CASE WHEN ... THEN ... ELSE ... END
日期处理	DATE_ADD, DATE_DIFF, YEAR, MONTH
字符串	CONCAT, SUBSTRING, REGEXP_EXTRACT

性能优化建议

• 谓词下推：在WHERE中尽早过滤数据，Spark会自动推送到数据源。
• 列剪枝：只SELECT需要的列。
• 广播小表：对小于100MB的表使用/*+ BROADCAST */。
• 避免关联子查询：尽量改写为JOIN。
• 使用集合操作时优先UNION ALL：避免去重开销。
• 启用自适应查询执行（AQE）：spark.sql.adaptive.enabled=true。

十二、课后思考作业

作业一：理论理解题

1. 请比较Spark SQL中的LEFT JOIN + WHERE right_col IS NULL与LEFT ANTI JOIN的实现差异，哪个更高效？

2. 窗口函数ROW_NUMBER()和RANK()在排名结果上有什么区别？请举例说明。

3. 为什么在子查询中使用EXISTS通常比IN更高效？在Spark中有什么特殊情况？

作业二：代码实践题

1. 使用本课生成的订单数据，写出SQL实现：

   • 统计每个年龄段（18-25, 26-35, 36-50, 50+）的消费总额和订单数
   • 找出每个商品类别中销售额最高的前3个商品（使用窗口函数）
   • 计算每个用户的累计消费金额，并标记消费超过1000的用户为“VIP”

2. 将以下DataFrame DSL代码改写为SQL：

   df.filter(col("age") > 30).groupBy("city").agg(avg("salary").alias("avg_salary")).orderBy("avg_salary", ascending=False)
   

3. 编写一个SQL，查询出“订单金额大于该用户平均订单金额”的订单，要求使用关联子查询和JOIN两种方式，并通过EXPLAIN对比执行计划。

作业三：场景应用题

某物联网平台有设备事件表（events）：event_id、device_id、event_type、event_time、value。每天产生数亿条记录。

需要分析：

• 每个设备每天的事件数量
• 每个设备的事件发生间隔（相邻事件时间差的中位数）
• 找出事件类型为alert且value大于阈值的设备，并统计其alert频率

请设计Spark SQL查询方案，并说明如何利用分区和分桶优化查询性能。

作业四：拓展研究

1. 阅读Spark官方文档中关于“ANSI Mode”的介绍，了解spark.sql.ansi.enabled的作用，以及如何启用后改变SQL行为。

2. 研究Spark SQL的INSERT OVERWRITE与INSERT INTO的区别，以及动态分区插入的使用方法。

3. 对比Spark SQL与Presto/Trino在SQL语法支持上的差异，写出1000字左右的调研报告。

---

提交方式：本次作业要求提交SQL脚本和执行结果截图，以及理论题的答案。鼓励将SQL封装在Python函数中，并编写单元测试。

扩展阅读：

• Spark SQL官方指南：SQL Reference
• 《Spark SQL内核剖析》第3-5章
• 在线练习平台：Spark SQL练习（LeetCode等）

通过本节课的学习，你已经掌握了Spark SQL的完整语法，能够像使用传统数据库一样进行复杂的数据分析。在实际工作中，SQL和DataFrame API可以混合使用，选择最合适的方式。下一节课我们将学习PySpark内置函数（字符串、日期、聚合、窗口函数）的全面解析，让你的数据处理代码更加简洁高效。我们下节课见！

---

🔗《20节课 PySpark 从入门到精通》系列课程导航

去订阅

🌟 感谢您耐心阅读到这里！
💡 如果本文对您有所启发欢迎：
👍 点赞📌 收藏 📤 分享给更多需要的伙伴。
🗣️ 期待在评论区看到您的想法, 共同进步。
🔔 关注我，持续获取更多干货内容～
🤗 我们下篇文章见～