开窗函数有多能打？

大家好，我是阳仔。

SQL是数据分析师的必备技能，无论大厂还是小厂，在技术面试轮经常会考到开窗函数。

在这里我就结合面试和工作经验，聊聊开窗函数都有哪些应用场景。

入门 - 排名、占比问题

涉及知识点：排序窗口函数、聚合窗口函数

现有客户消费统计表 user_agreement_table，字段 user_id 用户id、product 产品名称、gmv 交易额

问题1：找出每个用户消费金额最大的产品

思路：用row_number( ) over( )求每个用户产品购买数量排序，然后取出第一行

SELECT
  T1.user_id
  , T1.product
  FROM
  (SELECT user_id
    , product
    , row_number() over(partition by user_id order by gmv desc） as rank
    FROM user_agreement_table
    ) as T1
  WHERE T1.rank = 1

思路：用sum( ) over( )求每个用户消费总金额，然后计算每个产品消费金额占比（这个问题还可以用子查询解决）

SELECT
  user_id
  , product
  , sum(gmv) over(partition by user_id) as total_all     -- 消费总金额
  , gmv/sum(gmv) over(partition by user_id) as p    -- 占比
  FROM user_agreement_table

举个例子：

现有活跃用户明细表 user_active_table，字段 date 日期、user_id 用户id，假设日期是连续的

问题1：每天活跃用户量的环比值

思路：计算每天活跃用户量，并用lag( ) over( )求昨天活跃用户数

SELECT
  date
  , count(user_id) as a    -- 活跃用户量
  , lag(count(user_id),1) over(partition by date order by date) as b    -- 昨天活跃用户量，这里partiton by 可以省略
  , count(user_id)/lag(count(user_id),1) over(partition by date order by date) as yoy    -- 环比
  FROM user_active_table
  GROUP BY date    -- 如果开窗函数中含有聚合函数，需要接group by

思路：用偏移窗口函数，计算用户向前偏移7行的日期，如果当前日期减7天等于用户向前偏移7行的日期，说明用户连续7天活跃

SELECT
  T1.user_id
  FROM
  (SELECT
    date
    , user_id
    , lag(date,7) over(partition by user_id order by date) as date2 -- 向前偏移7行
    FROM user_active_table) as T1
  WHERE date_sub(T1.date,7) = T1.date2

涉及知识点：截取部分行 rows between ...... and ......

我们需要先了解完整的开窗函数：表达式 + over ( partition by 分区依据 order by 排序依据  rows between ......  and ...... )

比如我们在做指数的时候经常会用到3月移动平均，此时可以先求连续3期数据和再平均

sum(字段) over(partition by 分区 order by 排序依据 rows between 3 preceding and current row) / 3

窗口函数是衡量数据分析师SQL水平的重要标识，一般面试最难也就到这一层级。