窗口分析函数

来源：互联网发布：竖琴知乎编辑：程序博客网时间：2024/06/08 08:00

在进行数据分析时，有时候我们需要进行环比或同比分析，这时候一些窗口分析函数，就会比较方便。

这里介绍两个窗口分析函数lead和lag

lead(col, step, default_value) over(partition by col1 order by col2) ,这个函数是与往前步长的数据做对比，下面是我这边的一个例子。

SELECT TIMESTAMP,content_id,request,impression,lead(request, 1, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) rq1,lead(impression, 1, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) imp1,lead(request, 2, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) rq2,lead(impression, 2, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) imp2,lead(request, 3, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) rq3,lead(impression, 3, 0) OVER (PARTITION BY content_id ORDER BY TIMESTAMP) imp3FROM (SELECT TIMESTAMP,content_id,sum(request) request,sum(impression) impression,sum(click) clickFROM db_reformat.article_feedback_10min lateral VIEW json_tuple(json_string, 'timestamp', 'content_id', 'product_id', 'strategy', 'request', 'impression', 'click') t AS TIMESTAMP,content_id,product_id,strategy,request,impression,clickWHERE DATE = '20171128'AND product_id = 'ali'AND content_id = '140625598'AND TIMESTAMP = 201711281200GROUP BY TIMESTAMP,content_id) a

与lead类似，方向相反的函数是lag，用法与lead相似。

lag(col, step, default_value) over()

阅读全文

0 0