当前位置: 代码迷 >> SQL >> 锋利的SQL2014:基于窗口的散布计算
  详细解决方案

锋利的SQL2014:基于窗口的散布计算

热度:107   发布时间:2016-05-05 10:47:00.0
锋利的SQL2014:基于窗口的分布计算

 

SQL Server2012开始,提供了四个排名分布函数,包括PERCENT_RANKCUME_DISTPERCENTILE_CONTPERCENTILE_DISC。其中PERCENT_RANK用于计算某行的相对排名,CUME_DIST用于计算行的累积分布(即相对位置),PERCENTILE_CONTPERCENTILE_DISC用于根据指定的比例返回组中相应的数值,如中位值等。换句话说,PERCENT_RANKCUME_DIST是根据数值计算比例,PERCENTILE_CONTPERCENTILE_DISC是根据指定的比例计算数值,类似于对PERCENT_RANKCUME_DIST的逆运算。

9.4.1 PERCENT_RANK函数

PERCENT_RANK用于计算某行的相对排名,返回一个01之间的一个小数值。该函数的OVER子句允许有分区和排序子句,并且排序子句是必选项。

下面通过一个示例来说明这种相对排名的计算方法,这里我们仍旧使用9.1节创建的Students表,下面的语句用于计算学生成绩在自己班级的绝对排名和相对排名,查询结果如表9-15所示。

SELECT ClassID, StudentName, Achievement,

      RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS A_Rank,

      PERCENT_RANK() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) ASP_Rank

FROM dbo.Students;

9-15                                           学生成绩在自己班级的绝对排名和相对排名

ClassID

StudentName

Achievement

A_Rank

P_Rank

1

Grace

99

1

0

1

Andrew

99

1

0

1

Margaret

89

3

0.666667

1

Janet

75

4

1

2

Robert

91

1

0

2

Steven

86

2

0.5

2

Michael

72

3

1

3

Ann

94

1

0

3

Ken

92

2

0.333333

3

Ina

80

3

0.666667

3

Laura

75

4

1

对于使用RANK函数获得绝对排名,我们在前面已经介绍过。现在看表中的P_Rank列的计算方法,公式如下:

(当前行的RANK排名-1/(分区内总行数-1

从表中可以看出,GraceAndrewRANK排名为第1,并且当前分区内共有4行,所以第一行和第二行的P_Rank列值均计算为:(1-1)/(4-1)=0。第三行MargaretP_Rank列值计算方法为:(3-1)/(4-1)=0.666667,表示班级内有66.667%的人高于自己的成绩。

9.4.2 CUME_DIST函数

CUME_DIST用于计算某个值在一组值内的累积分布,也可以说是该值在一组值中的相对位置。函数返回一个01之间的一个小数值。该函数的OVER子句允许有分区和排序子句,并且排序子句是必选项。

下面通过一个示例来说明这种相对位置的计算方法,仍旧使用9.1节创建的Students表。下面的语句中,C_Num列计算的是大于或等于自己学生成绩的人数,C_Dist列计算的是大于或等于自己学生成绩的人数占全班人数的比例,查询结果如表9-16所示。

SELECT ClassID, StudentName, Achievement,

      COUNT(*) OVER(PARTITION BY ClassID ORDER BY Achievement DESC) AS C_Num,

      CUME_DIST() OVER(PARTITION BY ClassID ORDER BY Achievement DESC) ASC_Dist

FROM dbo.Students;

9-16                           计算的是大于或等于自己学生成绩的人数及占全班人数的比例

ClassID

StudentName

Achievement

C_Num

C_Dist

1

Grace

99.00

2

0.5

1

Andrew

99.00

2

0.5

1

Margaret

89.00

3

0.75

1

Janet

75.00

4

1

2

Robert

91.00

1

0.333333333333333

2

Steven

86.00

2

0.666666666666667

2

Michael

72.00

3

1

3

Ann

94.00

1

0.25

3

Ken

92.00

2

0.5

3

Ina

80.00

3

0.75

3

Laura

75.00

4

1

表中C_Dist列的计算公司为:

C_Num/班级总人数

C_Num是大于或等于自己学生成绩的人数。表中第14行是班级1的数据,第一行和第二行C_Dist列值的计算方法是:2/4=0.5,表示班级中有50%的大于或等于自己的成绩。

9.4.3 PERCENTILE_CONTPERCENTILE_DISC函数

PERCENTILE_CONTPERCENTILE_DISC用于根据指定的比例返回一组数据中相应位置的值,所指定比例应当是一个01之间的小数。例如,比例为0.5(即中位值),这组数字是1391218,则这个数值应当是9

PERCENTILE_CONTPERCENTILE_DISC函数的语法格式如下,其中的WITHIN GROUP子句用于指定要排序的数值列表,OVER子句可以指定一个分区列。

PERCENTILE函数 (numeric_literal )

    WITHINGROUP ( ORDER BY order_by_expression [ ASC | DESC ] )

    OVER ( [<partition_by_clause> ] )

下面通过示例来说明这两个函数的使用方法,参考下面的语句。查询结果如表9-17所示。

SELECT ClassID, StudentName, Achievement,

            PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BYAchievement DESC)

                                 OVER(PARTITION BYClassID) AS P_Cont,

            PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BYAchievement DESC)

                                 OVER(PARTITION BYClassID) AS P_Disc,

           CUME_DIST() OVER(PARTITION BY ClassID

                               ORDER BY Achievement DESC) AS C_Dist

FROM dbo.Students;

9-17                                                         根据指定比例返回相应数值

ClassID

StudentName

Achievement

P_Cont

P_Disc

C_Dist

1

Grace

99

94

89

0.5

1

Andrew

99

94

89

0.5

1

Margaret

89

94

89

0.75

1

Janet

75

94

89

1

2

Robert

91

86

86

0.333333

2

Steven

86

86

86

0.666667

2

Michael

72

86

86

1

3

Ann

94

86

80

0.25

3

Ken

92

86

80

0.5

3

Ina

80

86

80

0.75

3

Laura

75

86

80

1

表中P_Cont列计算的是班级中50%位置的值,班级1共有4行,因此94Achievement列中第二行和第三行的平均值:(99+89)/2。也就是说,对于偶数行,PERCENTILE_CONT函数计算的是中间两个数值间的平均值。

PERCENTILE_DISC函数不会这样做,它始终返回的是已有值。注意表中的C_Dist列,该列计算的是值得累积分布。当为PERCENTILE_DISC指定一个比例值时,它会查找大于或等于该比例的累积分布值所对应的值。例如,在该示例中我们为函数指定的比例是0.6,在班级1中第一个大于或等于该值的累积分布是0.75,因此该函数返回了89。
  相关解决方案