percentsql_使用SQLServer中的PERCENT_RANK函数计算SQL百分位数_百度文...
percentsql_使⽤SQLServer中的PERCENT_RANK函数计算
SQL百分位数
percent sql
This article explores the SQL Server PERCENT_RANK analytical function to calculate SQL Percentile and its usage with various examples.
本⽂通过各种⽰例探讨了SQL Server PERCENT_RANK分析函数，以计算SQL Percentile及其⽤法。
PERCENT_RANK（）函数概述 (Overview of PERCENT_RANK() function)
In a relational database, we store the data in SQL tables. Many times, we fetch the data without any data manipulations. Let’s say you want to generate the SSRS report that contains the top 5% sales for a product in a region. In this case, you need to use SQL Server Analytical functions to perform the data manipulations such as SQL Percentile.
在关系数据库中，我们将数据存储在SQL表中。 很多时候，我们⽆需任何数据操作即可获取数据。 假设您要⽣成SSRS报告，其中包含该地区某产品的销售额最⾼的5％。 在这种情况下，您需要使⽤SQL Server Analytical函数来执⾏数据操作，例如SQL Percentile。
We have the following analytical functions in SQL Server.
在SQL Server中，我们具有以下分析功能。
CUME_DIST()
CUME_DIST（）
FIRST_VALUE()
FIRST_VALUE（）
LAST_VALUE()
LAST_VALUE（）
LEAD()
铅（）
LAG()
落后（）
PERCENTILE_COUNT()
PERCENTILE_COUNT（）
PERCENTILE_DISC()
PERCENTILE_DISC（）
PERCENT_RANK()
PERCENT_RANK（）
You can also direct to to understand how you can specify rank to each row of your result set over a partition using these window functions.
您还可以定向到以了解如何使⽤这些窗⼝函数在分区上的结果集的每⼀⾏指定排名。
PERCENT_RANK（） (PERCENT_RANK())
rank函数的用法
The PERCENT_RANK function in SQL Server calculates the relative rank SQL Percentile of each row. It always returns values greater than 0, and the highest value is 1. It does not count any NULL values. This function is nondeterministic.
SQL Server中的PERCENT_RANK函数计算每⾏的相对排名SQL百分位数。 它始终返回⼤于0的值，并且最⼤值为1。它不计算任何NULL值。 此功能是不确定的。
The syntax of the PERCENT_RANK() function is as below:
PERCENT_RANK（）函数的语法如下：
PERCENT_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
In this syntax, we use the following parameters.
在此语法中，我们使⽤以下参数。
PARTITION BY: By default, SQL Server treats the whole data set as a single set. We can specify the PARTITION By clause to divide data into multiple sets. On each set, the Percent_Rank function performs the analytical calculations. It is an optional parameter
PARTITION BY：默认情况下，SQL Server将整个数据集视为单个集合。 我们可以指定PARTITION By⼦句将数据分为多个集合。
在每个集合上，Percent_Rank函数都会执⾏分析计算。 它是⼀个可选参数
Order By: We use Order by clause to sort the data in either ascending or descending order. It is a compulsory
parameter
Order By：我们使⽤Order by⼦句对数据进⾏升序或降序排序。 这是⼀个强制性参数
Let’s look at the examples to understand it.
让我们看⼀些例⼦来理解它。
⽰例1：PERCENT_RANK函数，⽤于计算学⽣成绩SQL百分位数 (Example 1: PERCENT_RANK function for calculating the SQL Percentile of student marks)
In this example, let’s use a CTP ( common table expression) to hold the student marks, and we use PERCENT_RANK function from this data for marks in ascending order.
在此⽰例中，让我们使⽤CTP（公⽤表表达式）来保存学⽣成绩，然后使⽤该数据中的PERCENT_RANK函数以升序排列。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
In the output, we can see following outcomes
在输出中，我们可以看到以下结果
A lowest mark 49 gets Percent_Rank value zero
最低分数49的Percent_Rank值为零
The highest marks 93 gets Percent_Rank value one
最⾼分93获得Percent_Rank值1
⽰例2：⽤于计算具有NULL值SQL Percentile的PERCENT_RANK函数 (Example 2: PERCENT_RANK function with to calculate SQL Percentile having NULL values)
Let’s add a NULL value in the student marks and rerun the query from example 1.
让我们在学⽣标记中添加⼀个NULL值，然后重新运⾏⽰例1中的查询。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81), (93),(NULL)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
As shown in the following screenshot, you always get zero for the NULL values.
如以下屏幕截图所⽰，对于NULL值，您总会得到零。
⽰例3：PERCENT_RANK函数来计算具有重复值SQL Percentile (Example 3: PERCENT_RANK function to calculate SQL Percentile having duplicate values)
Suppose multiple students are having similar marks, and we want to calculate the percent rank. It is important to know that the function will return the same or different rank for a student with similar marks.
假设多个学⽣的分数相似，并且我们要计算排名百分⽐。 重要的是要知道，该功能将为具有相似分数的学⽣返回相同或不同的等级。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(81), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
We have two students with marks 81. In the following example, we can see that percent_rank function returns the same value.
我们有两个标记为81的学⽣。在下⾯的⽰例中，我们可以看到percent_rank函数返回相同的值。
Here is a catch, Let’s say two students have got the highest marks 93 and we want to calculate the percent_rank for these.
这⾥有个问题，假设有两个学⽣的最⾼分是93，我们要计算这些分数的percent_rank。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(93), (93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
In this example, we have the highest marks 93, and two students have the same marks. Previously, we get percent rank 1
for the highest rank. Now, we do not get the rank 1, and it calculates the percent rank according to the similar highest marks.
在此⽰例中，我们的最⾼分是93，⽽两个学⽣的分是相同的。 以前，我们获得最⾼排名的百分⽐排名1。 现在，我们没有获得等级1，⽽是根据相似的最⾼分数计算百分⽐等级。
⽰例4：PERCENT_RANK函数以降序计算带有标记SQL Percentile (Example 4: PERCENT_RANK function to calculate SQL Percentile with marks in descending order)
In previous examples, we did not specify ascending or descending order for the marks. By default, it sorts the data in ascending order. We can specify the DESC clause to sort the data in descending order.
在前⾯的⽰例中，我们没有指定标记的升序或降序。 默认情况下，它将按升序对数据进⾏排序。 我们可以指定DESC⼦句对数据进⾏降序排序。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81),(93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks desc) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
In the descending order, we can see that the highest marks get 0 ranks and the lowest marks get rank 1.以降序排列，我们可以看到最⾼的标记获得0等级，最低的标记获得1等级。
In the descending order, for the student with similar highest marks always get zero rank.
对于降序排列，得分最⾼的学⽣总是获得零分。
WITH Student
AS (SELECT Marks
FROM(VALUES(75), (59), (69), (88), (72), (86), (93),(60), (67), (49), (81),(93)) Val(Marks)),
Percentiles
AS (SELECT Marks,
PERCENT_RANK() OVER(
ORDER BY Marks desc) AS Percent_Rank
FROM Student)
SELECT *
FROM Percentiles;
Similar to example 3, two students with the lowest marks do not get percent_rank 1.
与⽰例3相似，得分最低的两个学⽣也没有获得percent_rank 1。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。