LAG() 函数基础概念LAG() 是 StarRocks 提供的窗口函数之一用于访问当前行之前的指定物理偏移量的行数据。该函数在时间序列分析、同比环比计算等场景中非常实用能够避免自连接查询带来的性能问题。语法结构LAG(expr, offset, default) OVER([partition_by_clause] [order_by_clause])expr需要获取前值的列或表达式offset向前偏移的行数默认为1default当偏移量超出分区范围时返回的默认值默认为NULL典型应用场景股票价格波动分析SELECT trade_date, stock_code, closing_price, LAG(closing_price, 1) OVER(PARTITION BY stock_code ORDER BY trade_date) AS prev_close FROM stock_daily用户行为路径分析SELECT user_id, event_time, page_url, LAG(page_url, 1) OVER(PARTITION BY user_id ORDER BY event_time) AS previous_page FROM user_clickstream高级使用技巧计算环比增长率SELECT month, revenue, LAG(revenue, 1) OVER(ORDER BY month) AS prev_revenue, (revenue - LAG(revenue, 1) OVER(ORDER BY month)) / LAG(revenue, 1) OVER(ORDER BY month) AS growth_rate FROM monthly_sales处理分区边界情况SELECT department, employee_id, salary, LAG(salary, 1, 0) OVER(PARTITION BY department ORDER BY salary DESC) AS higher_salary FROM employees性能优化建议合理设计PARTITION BY子句的分区键确保数据均匀分布。对于时间序列数据按时间范围分区配合ORDER BY能显著提升性能。考虑使用物化视图预计算常用窗口函数结果CREATE MATERIALIZED VIEW mv_sales_growth REFRESH ASYNC AS SELECT product_id, month, LAG(revenue, 1) OVER(PARTITION BY product_id ORDER BY month) AS prev_revenue FROM sales_data与其他函数配合使用结合 LEAD() 实现滑动窗口计算SELECT date, temperature, (LAG(temperature,1) OVER(ORDER BY date) temperature LEAD(temperature,1) OVER(ORDER BY date))/3 AS moving_avg FROM weather与 FIRST_VALUE() 组合分析SELECT user_id, login_date, login_ip, LAG(login_ip,1) OVER(PARTITION BY user_id ORDER BY login_date) AS last_login_ip, FIRST_VALUE(login_ip) OVER(PARTITION BY user_id ORDER BY login_date) AS first_login_ip FROM user_logins实际案例解析电商场景异常订单检测SELECT order_id, user_id, order_amount, order_time, LAG(order_amount,1) OVER(PARTITION BY user_id ORDER BY order_time) AS prev_order_amount, CASE WHEN order_amount 5 * LAG(order_amount,1) OVER(PARTITION BY user_id ORDER BY order_time) THEN Abnormal ELSE Normal END AS status FROM orders金融领域交易监控SELECT transaction_id, account_id, transaction_amount, transaction_time, LAG(transaction_time,1) OVER(PARTITION BY account_id ORDER BY transaction_time) AS prev_transaction_time, TIMESTAMPDIFF(SECOND, LAG(transaction_time,1) OVER(PARTITION BY account_id ORDER BY transaction_time), transaction_time) AS time_diff_seconds FROM financial_transactions注意事项窗口函数的计算结果受OVER子句中排序的影响确保ORDER BY使用确定性排序条件。在分布式环境下数据分布可能影响窗口函数计算效率合理设置分区数很重要。对于大规模数据考虑通过WHERE条件先过滤数据再应用窗口函数减少计算量。StarRocks 的向量化执行引擎能高效处理窗口函数但复杂窗口定义仍可能成为查询瓶颈。share.cnbgluf.cn/Article/details/413180.HKMshare.lfrhsdk.cn/Article/details/644402.HKMshare.jvmsdkk.cn/Article/details/382328.HKMshare.jamwhkv.cn/Article/details/829593.HKMshare.wlncrof.cn/Article/details/194273.HKM