SQLServer中Partition By及row_number 函数使用详解

1. 什么是Partition By

在SQLServer中，Partition By是用来对数据集进行分组的函数，它可以将数据集分成若干个组，每个组里面的数据有相同的值。Partition By函数可以在聚合函数中使用，能够对每个分组内的数据进行聚合计算。

下面以一个简单的例子来说明Partition By的使用方法:

SELECT name,score,AVG(score) OVER(PARTITION BY name) AS avg_score

FROM student

上述查询语句中使用了Partition By，它的功能是按照学生姓名对成绩数据集进行分组，然后计算每个分组内成绩的平均值。这样就可以获取每个学生的平均成绩，并且可以知道哪些学生的成绩比较优秀，哪些学生的成绩需要加强。

2. row_number函数基础用法

row_number函数是用来生成一个序列号的函数，它可以为每一行数据生成一个唯一的序列号，通常情况下会与Partition By一起使用。

SELECT name, score, row_number() OVER(PARTITION BY name ORDER BY score DESC) AS rownum

FROM student

上述查询语句中，row_number函数会为每个学生的成绩数据按照从高到低的顺序生成一个序列号，序列号1表示成绩最高，序列号2表示成绩次高，以此类推。

2.1 row_number函数中的ORDER BY关键字

row_number函数中的ORDER BY关键字指定了对哪个字段进行排序，如果不指定ORDER BY关键字，则无法保证生成的序列号的顺序正确。

SELECT name, score, row_number() OVER(PARTITION BY name) AS rownum

FROM student

上述查询语句中，虽然指定了Partition By，但是没有指定ORDER BY关键字，这意味着生成的序列号可能是随机的，不满足我们的需求，因此需要在row_number函数中添加ORDER BY关键字。

2.2 row_number函数中的PARTITION BY关键字

row_number函数中的PARTITION BY关键字用来对数据进行分组，和在聚合函数中使用的Partition By的作用类似。

SELECT name, score, row_number() OVER(PARTITION BY name ORDER BY score DESC) AS rownum

FROM student

上述查询语句中，指定了Partition By和ORDER BY关键字，它们的作用是先按照学生姓名分组，再按照成绩从高到低排序，最后生成序列号。

3. 在实际应用中的使用案例

Partition By和row_number函数的组合使用在实际应用中非常常见，下面以一个案例来说明它们的具体应用。

假设有一个订单表order，包含订单号、客户号、订单日期、订单金额等字段，现在需要求出每个客户的最近一笔订单。

SELECT *

FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM order
) tmp
WHERE rn = 1

上述查询语句中，首先使用Partition By将订单数据按照客户编号进行分组，然后按照订单日期从大到小进行排序生成序列号。最后，将每个客户最近一笔订单的数据筛选出来。

需要注意，这里将有序排列的数据行筛选出来用WHERE rn=1实现，是因为每个分组的第一个元素的序列号都是1。因此筛选出序列号为1的行，就能得到每个分组的第一个元素，即每个客户的最近一笔订单。

4. 总结

Partition By和row_number函数是非常有用的SQL函数，它们的组合使用可以方便地对数据进行分组、排序和计算。在实际应用中，我们常常需要用到这两个函数，比如求每个客户的最近一笔订单，求每个学生的平均成绩等等。因此，熟练掌握Partition By和row_number函数的使用方法是很有必要的。