首页 > 开窗函数有多能打?
头像
leviholmes
发布于 2021-08-10 22:21
+ 关注

开窗函数有多能打?

大家好,我是阳仔。

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

问题2:每个用户在各个产品消费金额的占比分布

思路:用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

问题2:连续活跃7天的用户有哪些(这个问题还可以用子查询解决)

思路:用偏移窗口函数,计算用户向前偏移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水平的重要标识,一般面试最难也就到这一层级。

如果你是刚入门或者想转行做数据分析师,一定多刷题库,不要在这个环节掉链子。



这次就分享到这里啦,想参加腾讯秋招、社招的小伙伴可以加我v15201344924。

全部评论

(1) 回帖
加载中...
话题 回帖

推荐话题

相关热帖

近期热帖

历年真题 真题热练榜 24小时
技术(软件)/信息技术类
查看全部

近期精华帖

热门推荐