引言
在第一篇中，我们深入探讨了ORM框架SQLAlchemy的高级用法以及性能优化策略。然而，要充分释放数据库的潜力，我们还需要掌握更多高级SQL特性，并将其与强大的数据分析工具生态系统有效集成。本篇将聚焦于窗口函数、CTE递归查询、JSON操作、全文搜索以及与Pandas的无缝集成， раскрывая 高级SQL在数据分析领域的强大威力。
窗口函数与分析查询：洞察数据背后的模式
窗口函数是现代SQL标准中极为强大的分析利器。它们允许在查询结果集的“窗口”（一组相关的行）上执行计算，而无需像传统聚合函数那样压缩结果集。窗口函数的核心优势在于，它能够在保留明细数据的同时，进行灵活的组内分析、排名、和趋势计算，为深入数据挖掘提供了无限可能。
在SQLAlchemy中，我们可以借助 over() 函数来定义窗口，并结合各种窗口函数实现复杂的分析逻辑。以下是几个关键的窗口函数类型及其应用场景：
排名函数 (Ranking Functions)：
RANK()：为窗口内的每一行分配排名，相同值的行排名相同，排名会跳跃。例如，在销售额排名中，如果有多笔订单销售额相同，则它们并列排名，之后的排名会跳过相应的名次。DENSE_RANK()：与RANK()类似，但排名不会跳跃。即使存在并列排名，后续排名依然是连续的。更适用于关注相对排名位置的场景。ROW_NUMBER()：为窗口内的每一行分配唯一的连续排名，即使值相同，排名也不同。适用于需要唯一标识每一行记录排名的场景。NTILE(n)：将窗口内的数据划分为n个桶（bucket），并为每个桶内的行分配桶编号。适用于数据分段分析，例如，将客户按消费能力划分为不同的等级。
值函数 (Value Functions)：
LAG(column, offset, default)：返回窗口中当前行之前offset行的column列的值。常用于计算环比增长、同比变化等时间序列分析场景。LEAD(column, offset, default)：返回窗口中当前行之后offset行的column列的值。与LAG()类似，但方向相反。FIRST_VALUE(column)：返回窗口中第一行的column列的值。适用于获取分组内初始值或基准值的场景。LAST_VALUE(column)：返回窗口中最后一行的column列的值。适用于获取分组内最后值或结束值的场景。
聚合函数 (Aggregate Functions) 作为窗口函数：
SUM() OVER (window_definition)：计算窗口内指定列的累计和。例如，计算订单金额的累积总额，观察销售额增长趋势。AVG() OVER (window_definition)：计算窗口内指定列的移动平均值。例如，平滑销售额波动，分析长期趋势。MIN()/MAX() OVER (window_definition)：计算窗口内的最小值/最大值。例如，找出每个用户历史订单中的最低/最高消费金额。COUNT() OVER (window_definition)：计算窗口内的行数。例如，统计每个类别下产品的数量。
示例：分析销售趋势，洞察用户行为
以下代码示例演示了如何结合多种窗口函数，从销售数据中挖掘有价值的业务洞察：
from sqlalchemy import select, func, over, partition_by, desc, Date
from sqlalchemy.sql import cast
def analyze_sales_trends(session):
# 定义窗口函数查询
window_query = select(
cast(Order.created_at, Date).label('order_date'), # 将时间戳转换为日期
func.sum(Order.total).label('daily_total'), # 当日总销售额
func.sum(Order.total).over(partition_by=Order.user_id).label('user_total'), # 用户历史总消费额
func.dense_rank().over(order_by=desc(Order.total)).label('order_rank'), # 订单金额排名
func.avg(Order.total).over(partition_by=func.extract('month', Order.created_at)).label('monthly_avg') # 月度平均订单金额
).filter(
Order.status == 'completed' # 筛选已完成订单
).order_by(
Order.created_at # 按订单创建时间排序
)
results = session.execute(window_query).all()
# 进一步处理结果并打印
for row in results:
print(f"日期: {
row.order_date}, 当日总额: {
row.daily_total}, "
f"用户总额: {
row.user_total}, 订单排名: {
row.order_rank}, "
f"月平均: {
row.monthly_avg:.2f}")
return results # 返回原始结果集，方便后续分析或可视化
这段代码示例中，我们使用了 PARTITION BY 子句将数据按用户ID或月份进行分组，并在每个分组内应用窗口函数进行计算。通过分析结果，我们可以清晰地了解每个用户的消费总额、订单金额排名，以及月度销售额的平均水平，从而为精细化运营和决策提供有力支持。
CTE与递归查询：化繁为简，驾驭层级数据
公共表表达式 (CTE) 是一种强大的SQL构造，它允许我们定义临时的、命名的结果集，然后在后续的查询中像普通表一样引用。CTE 的出现，极大地简化了复杂SQL查询的编写