clickhouse开窗函数-全解详解开窗函数用法

171 阅读 0 评论 113 点赞

我是靠谱客的博主激动红酒，最近开发中收集的这篇文章主要介绍clickhouse开窗函数-全解详解开窗函数用法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

详解开窗函数用法

-- 当前版本21.4开窗函数功能还处于试验阶段，所有需要打开如下设置
set allow_experimental_window_functions = 1;

aggregation

-- 先看一个简单的例子 通过count开窗进行逐层计数，其中intDiv(number, 3)表示除3向下取正
select number, count() over (partition by intDiv(number, 3) order by number rows unbounded preceding) from numbers(10)

-- 聚合函数开窗，取每一个分组中的最大值，类似的还有min、avg
select number, max(number) over (partition by intDiv(number, 3) order by number desc rows unbounded preceding) from numbers(10)

-- 窗口中不使用partition by关键字的话，窗口将是全部数据
select number, avg(number) over (order by number rows unbounded preceding) from numbers(10)

-- 提取窗口中间数
select number, quantileExact(number) over (partition by intDiv(number, 3) rows unbounded preceding) from numbers(10)

-- 可以通过设置别名对窗口结果进行二次处理
select q * 10, quantileExact(number) over (partition by intDiv(number, 3) rows unbounded preceding) q from numbers(10)

-- 多窗口支持
select number,intDiv(number, 3)p3, intDiv( number, 5) p5, max(number) over (partition by p3 order by number desc rows unbounded preceding),median(number) over (partition by p5 order by number rows unbounded preceding) as m from numbers(11) order by number

在这里插入图片描述

groupArray

-- 通过groupArray聚合分组内某字段数
select number, groupArray(number) over (partition by intDiv( number,3)) from numbers(10)

在这里插入图片描述

-- 窗口结果支持去重
select distinct sum(0) over (rows unbounded preceding) from numbers(2)

-- 支持 WINDOW 关键字 
select sum(number) over w1, sum(number) over w2
from numbers(10)
window
    w1 as (rows unbounded preceding),
    w2 as (partition by intDiv(number, 3) rows unbounded preceding)

row_number

-- 支持排名函数rank、dense_rank、row_number 
select number, p, o,
    count(*) over w,
    rank() over w,
    dense_rank() over w,
    row_number() over w
from (select number, intDiv(number, 5) p, mod(number, 3) o
    from numbers(31) order by o, number) t
window w as (partition by p order by o)
order by p, o, number

在这里插入图片描述

lead

-- 支持lag、lead,获取创建内向上或向下指定位置的数据；参数lagInFrame(数据列,自上向下数第几个位置,默认值)
select number, p, pp,
    lagInFrame(number) over w as lag1,
    lagInFrame(number, 2) over w as lag2,
    lagInFrame(number,  number - pp, number * 11) over w as lag,
    leadInFrame(number, 1,  number) over w as lead
from (select number, intDiv(number, 5) p, p * 5 pp from numbers(16))
window w as (partition by p order by number
    rows between unbounded preceding and unbounded following)
order by number

在这里插入图片描述

first_value

-- first_value：取窗口内第一个值；last_value：取窗口内最后一个值
select
    number,
    fIrSt_VaLue(number) over w,
    lAsT_vAlUe(number) over w
from numbers(10)
window w as (order by number range between 1 preceding and 1 following)
order by number