窗口函数
| 函数名 | 功能 |
|---|---|
| CUME_DIST() | 为组内值生成累积分布序号 |
| DENSE_RANK() | 为组内值生成连续排序号，其中，相同值具有相同序号 |
| FIRST_VALUE() | 取各组内的第一个值作为返回结果 |
| LAG() | 为各组内对应值生成滞后值 |
| LAST_VALUE() | 取各组内的最后一个值作为返回结果 |
| LEAD() | 为各组内对应值生成提前值 |
| NTH_VALUE() | 返回该组内的第nth行作为结果。若该行不存在，则默认返回NULL |
| NTILE() | 根据num_buckets integer将有序的数据集合平均分配到num_buckets所指定数量的桶中，并将桶号分配给每一行。分配时应尽量做到平均分配 |
| PERCENT_RANK() | 为各组内对应值生成相对序号 |
| RANK() | 为各组内值生成跳跃排序序号，其中，相同的值具有相同序号 |
| ROW_NUMBER() | 为各组内值生成连续排序序号，其中，相同的值其序号也不相同 |
示例
-- rown_number(),rank(),dense_rank()
select val,row_number() over (order by val) as 'row_number',rank() over (order by val) as 'rank',dense_rank() over (order by val) as 'dense_rank' from numbers;
select val,row_number() over w as 'row_number',rank() over w as 'rank',dense_rank() over w as 'dense_rank' from numbers window w as (order by val);
-- row_number(),cume_dist(),percent_ank()
select val,row_number() over (order by val) as 'row_number',cume_dist() over (order by val) as 'cume_dist',percent_rank() over (order by val) as 'percent_rank' from numbers;
select val,row_number() over w as 'row_number',cume_dist() over w as 'cume_dist',percent_rank() over w as 'percent_rank' from numbers windows w as (order by val);
-- first_value(),last_value(),nth_value()
select time,subject,val,first_value(val) over (partition by subject ordder by time rows unbounded preceding) as 'first',last_value(val) over (partition by subject order by time rows unbounded perceding) as 'last',nth_value(val,2) over (partition by subject order by time rows unbounded preceding) as 'second',nth_value(val,4) over (partition by subject order by time rows unbounded preceding) as 'fourth' from observations;
select time,subject,val,first_value(val) over w as 'first',last_value() over w as 'last',nth_value(val,2) over w as 'second',nth_value(val,4) over w as 'fourth' from observations window w as (partition by subject order by time rows unbounded preceding);
- frame子句
- current row：当前行
- unbounded preceding：从当前分区的第一行开始，到当前行结束
- unbounded following：从当前行开始，到当前分区的最后一行
- expr preceding：当前行前expr行到当前行
- expr following：当前行到当前行后expr行
-- lag(),lead()
select t,val,lag(val) over (order by t) as 'lag',lead(val) over (order by t) as 'lead',val - lag(val) over (order by t) as 'lag diff',val - lead(val) over (order by t) as 'lead diff' from series;
select t,val,lag(val) over w as 'lag',lead(val) over w as 'lead',val - lag(val) over w as 'lag diff',val - lead(val) over w as 'lead diff' from series window w as (order by t);
-- ntile()
select val,row_number() over (order by val) as 'row_number',ntile(2) over (order by val) as 'ntile2',ntile(4) over (order by val) as 'ntile4' from numbers;
select val,row_number() over w as 'row_number',ntile(2) over w as 'ntiile2',ntile(4) over w as 'ntile4' from numbers window w as (order by val);
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
