微软认证考试70-461 Work with Data 数据处理 --27%比重--（4）

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第二节Work with Data 数据处理 。

第一部分直通车

第二部分直通车

第三部分直通车

第四部分：Implement aggregate queries. May include but not limited to: new analytic functions; grouping sets; spatial aggregates; apply ranking functions. 实现聚合查询。可能包含但不仅限于：新的分析函数；grouping sets等效项；静态聚合地理方法(CollectionAggregate，ConvexHullAggregate，EnvelopeAggregate，UnionAggregate）；排名函数应用。

新的分析函数CUME_DIST，PERCENT_RANK ，LEAD，FIRST_VALUE，PERCENTILE_CONT，LAG，LAST_VALUE，PERCENTILE_DISC

CUME_DIST和PERCENT_RANK函数

CUME_DIST，计算某个值在 SQL Server 2012 中的一组值内的累积分布。也即，CUME_DIST 计算某指定值在一组值中的相对位置。对于行r，假定采用升序，r 的 CUME_DIST 是值低于或等于r 的值的行数除以在分区或查询结果集中求出的行数。

CUME_DIST 返回的值范围大于 0 并小于或等于 1。 关联值始终计算为相同的累积分布值。默认情况下包含 NULL 值，且该值被视为最低的可能值。 

PERCENT_RANK，计算 SQL Server 2012 中一组行内某行的相对排名。 使用 PERCENT_RANK 计算一个值在查询结果集或分区中的相对位置。

PERCENT_RANK 返回的值范围大于 0 并小于或等于 1。 任何一组中第一行的 PERCENT_RANK 都为 0。默认情况下包含 NULL 值，且该值被视为最低的可能值。

看一组SQL语句：

WITH test
as
(
    select NULL as score
    UNION ALL
    select NULL
    UNION ALL
    select 10
    UNION ALL
    select 40
    UNION ALL
    select 40
    UNION ALL
    select 50
    UNION ALL
    select 50
    UNION ALL
    select 60
    UNION ALL
    select 90
    UNION ALL
    select 90    
)
select ROW_NUMBER() over(order by score) as rownum
,score,cume_dist()over(order by score) as cum
,PERCENT_RANK() over(order by score) as per_rnk
,RANK() over(order by score) as rnk
from test

把一组数据放进CTE临时表进行CUME_DIST和PERCENT_RANK计算，结果：

rownum    score   cum   per_rnk                           rnk
1                 NULL    0.2      0                                        1
2                 NULL    0.2      0                                        1
3                10           0.3      0.222222222222222    3
4                40           0.5      0.333333333333333    4
5                40           0.5      0.333333333333333    4
6                50           0.7      0.555555555555556    6
7                50           0.7      0.555555555555556    6
8                60           0.8      0.777777777777778    8
9                90           1         0.888888888888889    9
10             90           1          0.888888888888889    9

首先，NULL都会被当作最小值。

cume_dist的计算方法：小于等于当前行值的行数/总行数。

比如，第3行值为10，有3行的值小于等于10,总行数10行，因此CUME_DIST为3/10=0.3 。

再比如，第4行值为40，行值小于等于40的共5行，总行数10行，因此CUME_DIST为5/10=0.5 。

PERCENT_RANK的计算方法：当前RANK值-1/总行数-1 。

比如，第4行的RANK值为4，总行数10行，因此PERCENT_RANK为4-1/10-1= 0.333333333333333。

再比如，第7行的RANK值为6，总行数10行，因此PERCENT_RANK为6-1/10-1=0.555555555555556。

LEAD和LAG函数

LEAD

访问相同结果集的后续行中的数据，而不使用 SQL Server 2012 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。

语法：LEAD ( scalar_expression [ ,offset ] , [ default ] )     OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression，要返回的值基于指定的偏移量。 这是一个返回单个（标量）值的任何类型的表达式。scalar_expression 不能为分析函数

offset默认值为1， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset 不能是负数值或分析函数。

default默认值为NULL， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset 不能是负数值或分析函数。

LAG

访问相同结果集的先前行中的数据，而不使用 SQL Server 2012 中的自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。

下面看一组SQL语句：

WITH test
as
(
    select NULL as score
    UNION ALL
    select 10
    UNION ALL
    select 20
    UNION ALL
    select 30
    UNION ALL
    select 40
    UNION ALL
    select 50
)
select ROW_NUMBER() over(order by score) as rownum
,score
,LEAD(score) over(order by score) as nextscore1
,LEAD(score,1) over(order by score) as nextscore2
,LEAD(score,1,0) over(order by score) as nextscore3
,LEAD(score,2) over(order by score) as nextscore4
,LAG(score) over(order by score) as previousscore1
,LAG(score,1) over(order by score) as previousscore2
,LAG(score,1,0) over(order by score) as previousscore3
,LAG(score,2) over(order by score) as previousscore4
from test

结果前半部分：

rownum    score    nextscore1    nextscore2    nextscore3    nextscore4
1                 NULL    10                   10                     10                     20
2                10           20                   20                     20                     30
3                20           30                   30                     30                     40
4                30           40                   40                     40                     50
5                40           50                   50                     50                     NULL
6                50           NULL             NULL               0                       NULL

LEAD(score)over(order by score)与LEAD(score,1)over(order by score)相同，基于SCORE升序取下一个score。

LEAD(score,Y)over(order by score)公式：按score升序排列第X行计算LEAD(score,Y)over(order by score)为第X+Y行的值。

对第X行求LEAD(score)over(order by score)，值为第X+1行的值。见列nextscore1和nextscore2。

对第X行求LEAD(score，2)over(order by score)，值为第X+2行的值。见列nextscore4。

对于第6行，由于没有第7行数据，所以LEAD求出的值为NULL。如果指定默认值，则返回指定默认值。，如LEAD(score,1,0)最后一行返回0。见netsocre3列。

结果后半部分：

rownum    score    previousscore1    previousscore2    previousscore3    previousscore4
1                NULL    NULL                     NULL                           0                             NULL
2               10           NULL                     NULL                          NULL                     NULL
3               20            10                         10                                  10                           NULL
4               30            20                         20                                   20                         10
5               40            30                         30                                   30                          20
6               50           40                          40                                   40                          30

跟LEAD非常相似，只是LAG是往前求值。

FIRST_VALUE和LAST_VALUE

看下组SQL语句：

WITH test
as
(
    select '乐可乐可的部落格' as name,10 as score
    UNION ALL
    select '乐可乐可的部落格',15
    UNION ALL
    select '乐可乐可的部落格',20
    UNION ALL
    select '微软认证',30
    UNION ALL
    select '微软认证',40
    UNION ALL
    select '微软认证',40
)
select name,score
,FIRST_VALUE(score)over(order by name) as fst
,LAST_VALUE(score)over(order by name) as Lst
from test

结果：

name                         score    fst    Lst
乐可乐可的部落格    15         15    10
乐可乐可的部落格    20         15    10
乐可乐可的部落格    10         15    10
微软认证                    40         15    30
微软认证                    40         15    30
微软认证                    30         15    30

FIRST_VALUE(score)over(order by name) as fst，取按name升序排列的第一行score，见红色字体。

LAST_VALUE(score)over(order by name) as Lst，取按name升序排列的相同name最后一行score，见蓝色和紫色字体。

PERCENTILE_CONT和PERCENTILE_DISC

看下面一组SQL语句：

WITH test
as
(
    select N'乐可乐可的部落格' as name,10 as score
    UNION ALL
    select N'乐可乐可的部落格',20
    UNION ALL
    select N'乐可乐可的部落格',30
    UNION ALL
    select N'乐可乐可的部落格',40
    UNION ALL
    select N'乐可乐可的部落格',50
    UNION ALL
    select N'微软认证',60
    UNION ALL
    select N'微软认证',70
    UNION ALL
    select N'微软认证',80
    UNION ALL
    select N'微软认证',90
    UNION ALL
    select N'微软认证',100
)
select name,score
,PERCENT_RANK() over(partition by name order by score) as per_rnk
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_5
,PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_6
,PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_7
,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_75
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_5
,PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_6
,PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_7
,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_75
from test

运行结果：

name                          score    per_rnk    percont0_5    percont0_6    percont0_7    percont0_75    perdist0_5    perdist0_6    perdist0_7    perdist0_75
乐可乐可的部落格    10           0                 30                       34                     38                        40                     30                 30                  40                       40
乐可乐可的部落格    20           0.25           30                       34                      38                       40                      30                30                   40                       40
乐可乐可的部落格    30           0.5              30                       34                      38                       40                      30                30                   40                       40
乐可乐可的部落格    40           0.75            30                       34                      38                      40                        30               30                   40                       40
乐可乐可的部落格    50           1                 30                       34                      38                       40                       30                30                   40                       40
微软认证                    60            0                80                       84                      88                       90                       80                80                   90                       90
微软认证                    70            0.25          80                        84                      88                      90                       80                 80                  90                       90
微软认证                    80            0.5             80                       84                      88                      90                       80                 80                  90                        90
微软认证                    90            0.75          80                        84                      88                      90                       80                 80                  90                       90
微软认证                  100           1                80                        84                       88                     90                        80                80                  90                        90

简单理解，PERCENT_RANK前面介绍过，求出score百分比后，PERCENTILE_CONT和PERCENTILE_DISC就是根据百分比求出对应的score。但不同的是，某一百分比没有对应的score时，PERCENTILE_CONT会根据百分比的偏差计算出一个新值，此值可能并不存在于score中。PERCENTILE_DISC得出的是偏向于最近百分比对应的score值，因此此值肯定存在于score中。

如PERCENTILE_CONT(0.6)对应的值为34，偏差值我猜测是这样计算出的：百分比75%和50%PERCENTILE_CONT计算出的值分别是40和30.那么等式：(40-30)/(0.75-0.5)=新偏差值/0.6-0.5。PERCENTILE_CONT(0.6)在50%上的偏差值=4。所以，PERCENTILE_CONT(0.6)对应PERCENTILE_CONT(0.5)+4=34。

再看PERCENTILE_DISC(0.6)，直接将最近的0.5计算出的，PERCENTILE_DISC值取过来了。

grouping sets，cube，rollup

使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。GROUPING SETS 可以生成等效于由简单 GROUP BY、ROLLUP 或 CUBE 操作生成的结果。GROUPING SETS、ROLLUP 或 CUBE 的不同组合可以生成等效的结果集。

比如,

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

和

SELECT customer, NULL as year, SUM(sales)
FROM T 
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T 
GROUP BY year

是等效的。

GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)或者GROUP BY C1, C2, …, Cn-1, Cn WITH ROLLUP
和 
GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)
    ,(C1, C2, ..., Cn-1)
    ...
    ,(C1, C2)
    ,(C1)
    ,() )
是等效的。注意WITH ROLLUP是旧版本的写法，GROUP BY ROLLUP 只能运行于兼容性100以上的版本。


GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)
和
GROUP BY GROUPING SETS (
     (C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included.
    ,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included.
    ,(C1, C3, ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2, C3, ..., Cn-2, Cn-1,)
    ,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included
    ,(C1  ..., Cn-2, Cn-1, Cn)
    …
    ,(C1, C2) -- 2 dimensions are included.
    ,…
    ,(C1, Cn)
    ,…
    ,(Cn-1, Cn)
    ,…
    ,(C1) -- 1 dimension included
    ,(C2)
    ,…
    ,(Cn-1)
    ,(Cn)
    ,() ) -- Grand total, 0 dimension is included.
是等效的。比如：
GROUP BY CUBE (C1, C2, C3)
等效于
GROUP BY GROUPING SETS ( (C1, C2, C3)
    ,(C1, C2)
    ,(C1, C3)
    ,(C2, C3)
    ,(C1)
    ,(C2)
    ,(C3)
    ,() )

小贴士：

• CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
  
• ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合

另外提一下聚合函数GROUPING。当用当结果集中的行是由GROUPING SETS，CUBE，或ROLLUP生成的，则值为1否则为0。

一个CUBE的例子：

with test
as
(
    select N'乐可乐可的部落格' as name,N'数据库' as category, 30 as totalcount
    union all
    select N'乐可乐可的部落格','.NET',20
    union all
    select N'微软认证',N'.NET',40
    union all
    select N'微软认证',N'WEB设计',30
)
select case when grouping(name)=1 then 'allnames' else name end as name
,case when grouping(category)=1 then 'allcategories' else category end as category
,sum(totalcount) as sum
from test
group by cube(name,category)

结果：

name                          category             sum
乐可乐可的部落格     .NET                    20
微软认证                     .NET                    40
allnames                   .NET                    60
微软认证                   WEB设计             30
allnames                  WEB设计             30
乐可乐可的部落格    数据库                  30
allnames                   数据库                 30
allnames                 allcategories      120
乐可乐可的部落格    allcategories      50
微软认证                    allcategories      70

如果没有用CASE WHEN判断GROUPING，则上面所有的allnames，allcategories会被NULL替代。

参考：http://msdn.microsoft.com/zh-cn/library/ms175939%28v=sql.90%29.aspx

http://msdn.microsoft.com/zh-cn/library/ms189305%28v=sql.90%29.aspx

http://msdn.microsoft.com/zh-cn/library/aa258902%28v=sql.80%29.aspx

http://msdn.microsoft.com/zh-cn/library/bb510427%28v=sql.105%29.aspx

静态聚合地理方法(CollectionAggregate，ConvexHullAggregate，EnvelopeAggregate，UnionAggregate）

参考：http://msdn.microsoft.com/zh-cn/library/hh403400(v=sql.110).aspx

排名函数应用

参见：第一部分

第五部分直通车