【Python数据分析300个实用技巧】121.数据存储与ETL之SQL技巧：用窗口函数计算滚动平均值_sql能实现滚动15分钟数据求平均,再从中取最大值吗-CSDN博客

本文链接：https://blog.csdn.net/jrckkyy/article/details/147384670

在这里插入图片描述

121.数据存储与ETL必杀技：3行SQL搞定海量数据滚动平均，老鸟私藏的窗口函数实战手册

窗口函数基础认知
- OVER子句解剖
- 帧范围设定
滚动平均核心语法
- 时间序列处理
- 空值填充技巧
性能优化秘籍
- 索引优化
- 分区策略
新手必踩的5个坑
- 帧范围错位
- 排序字段混淆
真实业务场景演练
- 股票K线分析
- 用户行为轨迹

嗨，你好呀，我是你的老朋友精通代码大仙。接下来我们一起学习Python数据分析中的300个实用技巧，震撼你的学习轨迹！

“SQL写得好，下班回家早！” 今天要说的这个场景，我赌你绝对遇到过：老板要最近7天的用户活跃趋势图，你吭哧吭哧写循环处理，结果数据库卡成PPT。别慌，看完这篇，你会拍着大腿说：“原来3行SQL就能搞定！”

一、窗口函数基础认知：从青铜到王者的必经之路

1.1 OVER子句解剖：你的第一个窗口

-- 错误示范（80%新手都这样写过）
SELECT user_id, 
       AVG(login_count) OVER() as avg_login
FROM user_logs

你以为这是窗口函数？错！这其实是全表平均的反复填充。真正的窗口应该是这样的：

-- 正确姿势（时间范围+滑动窗口）
SELECT login_date,
       AVG(login_count) OVER(
           ORDER BY login_date 
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) as rolling_avg
FROM daily_stats

敲黑板：没有ORDER BY的窗口函数就像没放盐的菜，看着像样实际没法用！

1.2 帧范围设定：时间旅者的操作手册

新手常犯的帧错误：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 累计平均（不是我们要的！）
VS
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW          -- 正确7日滚动

记住这个公式：N日滚动 = N-1 PRECEDING，比如7日就是6天前到当天。

二、滚动平均核心语法：一行代码封神的秘密

2.1 时间序列处理：当日期不连续时

原始数据：

日期        | 销售额
2023-01-01 | 100
2023-01-03 | 200  -- 缺失1月2日数据

普通写法会翻车：

AVG(sales) OVER(ORDER BY date ROWS 6 PRECEDING)

正确解决方案：

WITH date_seq AS (
    SELECT generate_series('2023-01-01'::date, 
                          '2023-01-07'::date, 
                          '1 day') as date
)
SELECT d.date,
       COALESCE(AVG(s.sales) OVER(
           ORDER BY d.date 
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ), 0) as filled_avg
FROM date_seq d
LEFT JOIN sales s ON d.date = s.date

2.2 空值处理三连击

-- 方法1：暴力填充
COALESCE(AVG(value),0) 

-- 方法2：前值继承 
AVG(value) IGNORE NULLS

-- 方法3：线性插值
AVG(CASE WHEN value IS NULL THEN 
    (LAG(value,1) OVER() + LEAD(value,1) OVER())/2 
    ELSE value END)

三、性能优化秘籍：让查询速度飞起来的黑科技

3.1 索引优化：速度提升10倍的秘诀

错误索引：

CREATE INDEX idx_date ON sales(date);  -- 单字段不够！

正确组合索引：

CREATE INDEX idx_window ON sales (user_id, date); 
-- 窗口函数最吃排序和分区键的索引

3.2 分区策略：十亿级数据应对方案

-- 普通写法（内存爆炸警告）
AVG() OVER(PARTITION BY user_id ORDER BY date)

-- 优化方案
CREATE MATERIALIZED VIEW user_rolling AS
SELECT user_id, date,
       AVG(value) OVER(
           PARTITION BY user_id 
           ORDER BY date 
           ROWS 6 PRECEDING
       ) 
FROM big_table
WHERE date >= now() - interval '30 days'

四、新手必踩的5个坑：血泪经验总结

帧范围错位：把ROWS和RANGE搞混

ROWS BETWEEN ...  -- 物理行数
RANGE BETWEEN ... -- 逻辑值范围（比如日期）

排序字段混淆：窗口排序和结果排序打架

SELECT date, 
       AVG() OVER(ORDER BY date) 
FROM table
ORDER BY value  -- 这个排序会破坏窗口计算！

五、真实业务场景演练：从入门到实战

5.1 股票K线场景

SELECT trade_date,
       close_price,
       AVG(close_price) OVER(
           ORDER BY trade_date 
           ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
       ) as ma5,
       AVG(close_price) OVER(
           ORDER BY trade_date 
           ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
       ) as ma20
FROM stock_data
WHERE stock_code = '600519'

5.2 用户行为轨迹分析

WITH user_journey AS (
    SELECT user_id,
           event_time,
           SUM(duration) OVER(
               PARTITION BY user_id 
               ORDER BY event_time 
               RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
           ) as hourly_usage
    FROM user_events
)
SELECT * 
FROM user_journey 
WHERE hourly_usage > 3600  -- 找到沉迷用户