入门案例

原始数据

入门程序

写法一：select province,city,gdp,people, rank() over (partition by province order by gdp) as ranking from windowfunc;
写法二：select province,city,gdp,people,rank() over w1 as ranking from windowfunc window w1 as (partition by province order by gdp);
大致解读：根据province字段进行分区，也就是将province字段相同的值划分到同一个窗口之中，在每一个窗口之中根据gdp字段进行排行，得到每个省份各自的城市的gdp排行。

结果

入门总结

窗口函数有两种：

1. 直接在over关键字之后添加窗口函数的定义；

2. 预先定义一个窗口函数，然后在over关键字之后直接使用定义的窗口函数即可。

窗口函数基本格式：

[window_name] [partition_clause] [order_clause][frame_clause]

各部分解读

1. window_name：预先定义的窗口函数

2. partition_clause：书写格式为partition by properties_name,即将全部数据划分按照字段名进行划分窗口，多个字段名之间用逗号进行分隔。如果没有指定字段，则所有数据属于一个窗口。

3. order_clause：书写格式为order by properties_name.即在每一个窗口之内根据指定的字段名进行排序，多个字段之间用逗号进行分隔。同时可以desc和asc关键字来指定排序的顺序。

4. frame_clause：用来指定窗口中的操作范围。

5. 如以上四部分都不存在，则窗口为所有查询行

非聚合窗口函数

演示：

数据

执行语句

select sex,height,cume_dist() over win as 'cume_dist',first_value(height) over win as 'first',last_value(height) over win as 'last',nth_value(height,2) over win as 'second',rank() over win as 'rank',dense_rank() over win as 'dense_rank',percent_rank() over win as 'percent_rank',row_number() over win as 'row_number',ntile(4) over win as 'ntile',lag(height) over win as 'lag',lead(height) over win as 'lead' from man  window win as (partition by sex order by height);

结果

1. CUME_DIST()

   返回窗口内小于等于当前值的比例。即小于等于当前值的数据个数/窗口内数据量。
   

2. first_value(properties_name)

   返回窗口内指定字段名的第一个值

3. last_value(properties_name)

   返回窗口内指定字段名的最后一个值

4. nth_value(properties_name,N)

   返回窗口内指定字段名的第N个值

5. rank()

   对窗口内的数据进行排序，返回其排名，相同的排名一致，同时占用后面的排名

6. dense_rank()

   对窗口内的数据进行排序，返回其排名，相同的排名一致，但是不占用后面的排名

7. percent_rank()

   在dense_rank()的前提，将排名转化为百分比。即当前值的排名/最后一名的排名

8. row_number()

   返回窗口内当前行的编号，编号从1开始

9. ntile(N)

   将窗口内的数据分成N个桶，同时返回每个数据所在的桶编号。桶可以更小的分区

10. lag(properties_name,N,default_value)

    返回窗口内，当前行之前的第N个值，若不存在，则使用default_value。在不指定的情况下，N = 1，default_value=null。N必须大于等于0

11. lead(properties_name,N,default_value)

    返回窗口内，当前行之后的第N个值，若不存在，则使用default_value。在不指定的情况下，N = 1，default_value=null。N必须大于等于0

frame_clause

用来在窗口之中划分一块更小的子集，然后进行运算。

格式

frame_units frame_extent

frame_units:rows | rangeframe_extent:{frame_start | frame_between} frame_between:between frame_start and frame_end frame_start/frame_end:{ current row unbounded preceding unbounded following express preceding express following } 在frame_between中，frame_start必须早于frame_end

案例

select sex, height, min(height) over (partition by sex order by height) as 'min',min(height) over (partition by sex order by height rows between 1 preceding and 1 following) as 'min_row_1_preceing_1_following',max(height) over (partition by sex order by height range unbounded preceding) as 'range_max_unbounder_preceding' from man;

结果

默认情况

在没有frame子句的情况下，默认框架取决于是否存在order by子句

1. 使用order by：默认frame_clause是从窗口的开始到当前的所有行

2. 不使用order by：默认frame_clause是包含窗口的所有数据

案例

select sex,height,max(height) over (partition by sex order by height) as 'max_with_order',max(height) over (partition by sex) as 'max_without_order' from man;

结果：