窗口函数至排序——SQLServer2012可高用

常用到的窗口函数

---

工作中要常对数据进行分析，分析前要对原始数据中找到想要的格式，数据原本存储的格式不一定时我们想要的，要在基础上进行一定的处理，下面介绍的几种方式是常用的数据排序的集中方式，包含 排名函数（row_number()）、排序函数（rank(),dense_rank()）、聚合函数（常用统计函数）、偏移函数（lag(),lead(),first_value(),last_value()）等内容

数据源为上篇文章的最后添加样本数据，上篇文章的最后用到的几个窗口函数会在这篇文章中详细介绍

排名函数

Row_Number() :将数据行根据一定的规则进行排名，排列出1,2,3,4···的形式，此函数后必须跟over(),且over()必须指定排名列，以order by [列名]形式，此排列顺序一定是连续的，也可以添加分区，在不指定分区 partition by [列名]时默认在查询条件内排序，指定分区后在分区内排名

1. 查找出所有用户最近一次的账单记录

--显然可以看出用一般的T-SQL语句 group by 是可以做到的
select actid,max(trandate) as trandate from transactions group by actid 

--如果在增加其余的几列显然想过不是我们想要的结果了,因为在账单中每个账单号对于用户来是唯一的，日期是账单号的唯一，执行下面的语句会显示出查出全部的内容
select actid,tranid,val,max(trandate) as trandate from transactions group by actid,tranid,val

--当然还有其他办法，编写比较复杂，这里就不介绍了 下面我们看一下 窗口排名函数 row_number()的做法

with c as(
    select actid,tranid,val,trandate,
        ROW_NUMBER() OVER(partition by actid order by trandate desc) 
    as rownum from transactions
)
select actid,tranid,val,trandate from c where rownum=1

1. 每个账号最近五次的消费记录

显然根据上面的查询方法只需要修改 最后查询后的where rownum<=5

3. 每个账号消费最多的五条记录

首先要根据 actid 进行分区，然后根据 val 排序, 最后根据排序值 取出 rownum<=5
修改如下

with c as(
    select actid,tranid,val,trandate,
        ROW_NUMBER() OVER(partition by actid order by val desc) 
    as rownum from transactions
)
select actid,tranid,val,trandate from c where val=1

一般情况下相比于其他的窗口函数 row_number() 的使用率是最高的，使用场景页多种多样
比如:在SQL Server 2012之前没引入 offset / fetch时我们经常用它来进行分页工作,进行修改序列操作生成操作
例如上文中 虚拟表函数编写，和修改订单号让数据化

4. 分页: 一般界面展示减少数据库访问压力，会每次返回一定量的数据

declare 
@pagesize int =150, --模拟每页的显示数量
@currpage int = 500; --第几页

--把所有数据当作数据源
with c as(
	select actid,tranid,val,trandate,
	    row_number() over(order by (select null)) as rownum
	from transactions
)
-- top查询 和限制 rownum 值完成分页效果
select top (@pagesize) actid,tranid,val,trandate 
from c where rownum>(@currpage-1)*@pagesize and rownum<@currpage*@pagesize+1

排序函数

排序函数 和排名函数用法类似，生成结果上有所差异
rank() 非连续 如果排序列值不唯一时出现相同值，且下值会出现跳跃现象；排序列值唯一是效果与row_number()函数一致
dense_rank() 连续排列，当列值不唯一时出现相同值，下值和上值会城现连续现象

rank()

select actid,tranid,val,trandate,
    ROW_NUMBER() over(order by val) as rownum,
    RANK() over(order by val) as rank
from transactions 
order by val
offset 0 rows fetch first 1000 rows only;

dense_rank()

--dense_rank
select actid,tranid,val,trandate,
    ROW_NUMBER() over(order by val) as rownum,
    RANK() over(order by val) as rnk,
    DENSE_RANK() over(order by val) as dense_rnk
from transactions 
order by val
offset 0 rows fetch first 1000 rows only;

聚合、偏移函数

聚合函数
分区内逐条查找，遇见之后更新，

select * ,
    max(val) over(partition by actid order by tranid) as max_val,
    min(val) over(partition by actid order by tranid) as min_val,
    sum(val) over(partition by actid order by tranid) as sum_val
    from transactions

偏移函数
Lag() 前一条，未找到为null
Lead() 后一条，未找到默认null，可指定偏移量，和默认值
一个参数效果

select *,
    LAG(val) over(partition by actid order by tranid,trandate) as pre_value,
    LEAD (val) over(partition by actid order by tranid,trandate) as next_value
from transactions

可以看出偏移量，默认为1行，且未找到值为 null

两个参数偏移函数，第一个参数偏移列，二个参数偏移行

select *,
    LAG(val,3) over(partition by actid order by tranid,trandate) as pre_value,
    LEAD (val,3) over(partition by actid order by tranid,trandate) as next_value
from transactions

指定默认值,将null列默认值设置为0.00

select *,
    LAG(val,3,0.00) over(partition by actid order by tranid,trandate) as pre_value,
    LEAD (val,3,0.00) over(partition by actid order by tranid,trandate) as next_value
from transactions

first_value() 分区内第一个值
last_value() 分区内左后一个值

select *,
    first_value(val) over(partition by actid order by tranid,trandate) as first_value,
    last_value(val) over(partition by actid order by tranid,trandate
        rows between current row and unbounded following 
        ) as last_value
from transactions

数据透视

行变列方便操作

下面语句为查找出用户流水最大的五条记录编号，并变为列的形式

with c as(
    select actid,tranid,
    row_number() over (partition by actid order by val desc) as rownum
    from transactions
)
select * from c
    pivot(max(tranid)
    for rownum in([1],[2],[3],[4],[5])
    )as p
order by actid 

字符串拼接

将上表中消费编号拼接为一列形式输出

with c as(
    select actid,tranid,
    row_number() over (partition by actid order by val desc) as rownum
    from transactions
)
select actid,concat([1],',',[2],',',[3],',',[4],',',[5]) as tranids
from c
    pivot(max(tranid)
        for rownum in([1],[2],[3],[4],[5])
    )as p
order by actid