Unpivot 和 Apply的应用比较

最新推荐文章于 2024-07-18 16:16:20 发布

原创最新推荐文章于 2024-07-18 16:16:20 发布 · 523 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #table #pivot #object #sql #2010

SQL 专栏收录该内容

79 篇文章

订阅专栏

本文对比了SQL中的UNPIVOT和APPLY操作的效果与性能，通过实例展示了两者如何转换宽表为长表，并分析了各自的适用场景及执行计划差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在SQL中， UNPIVOT 和 APPLY 能取得相同数据效果。

看一个例子：

IF OBJECT_ID('tempdb..#QSales','U') is not null
drop table #QSales
GO
create table #QSales(   
[Year] int,
Q1Sales int,
Q2Sales int,
Q3Sales int,
Q4Sales int)
GO
insert #QSales select 2009, 7, 29, 71, 8
union all select 2010, 2, 8, NULL, 4
union all select 2011, 6, 5,3,null

select *from #QSalesunpivot (Sales for Descript in (Q1Sales,Q2Sales,Q3Sales,Q4Sales)) P

同样的结果，可以取得，使用APPLY

select [Year],Sales,Descript
from #QSales
CROSS apply (values (N'Q1Sales',Q1Sales)
                   ,(N'Q2Sales',Q2Sales)
				   ,(N'Q3Sales',Q3Sales)
				   ,(N'Q4Sales',Q4Sales)) P(Descript,Sales)

where Sales is not null

那一个好呢？我们来比较以下执行计划：

UNPIVOT:

APPLY:

显然，后者要好。而且完全可以用OUTER 代替 CROSS.

对于多列数据， APPLY 就更有优势。

select PatientID,TestDate,Cholesterol,Sodium,Glucosefrom #BloodTestsunpivot (TestDate for DateDesc in (TestDate1,TestDate2,TestDate3)) U1unpivot (Cholesterol for CholDesc in (Chol1,Chol2,Chol3)) U2unpivot (Sodium for SodDesc in (Sod1,Sod2,Sod3)) U3unpivot (Glucose for GlucDesc in (Gluc1,Gluc2,Gluc3)) U4where right(DateDesc,1)=right(CholDesc,1)  and right(CholDesc,1)=right(SodDesc,1)  and right(SodDesc,1)=right(GlucDesc,1)

select PatientID,TestDate,Cholesterol,Sodium,Glucosefrom #BloodTestscross apply (select TestDate1,Chol1,Sod1,Gluc1 union all             select TestDate2,Chol2,Sod2,Gluc2 union all             select TestDate3,Chol3,Sod3,Gluc3) X(TestDate,Cholesterol,Sodium,Glucose)

上面这个，清晰显示，APPLY更加简洁，高效。

那么， UNPIVOT没有用处么？

也不是。现在看这样一个表。

StudentID Test1 Test2 Test3 Test4 Test5 Test6 Test7 Test8 Test9 Test10 Test11 Test12
--------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------ ------ ------
        1    96    87 NULL   100    99    91    73    90    90     91     93     92
        2    83    84    89    79    88 NULL    93    82    85     86     88     82
        3   100   100    98    91   100    95    96    96    99    100     98    100

select StudentID      ,AverageScore=avg(TestScore*1.0)      ,MinScore=min(TestScore)      ,MaxScore=max(TestScore)      ,StdDev=stdev(TestScore)from #Scoresunpivot (TestScore for Descript in (Test1,Test2,Test3,Test4,Test5,Test6                                   ,Test7,Test8,Test9,Test10,Test11,Test12)) Pgroup by StudentID

上述查询，可以得到：

StudentID AverageScore MinScore MaxScore           StdDev
--------- ------------ -------- -------- ----------------
        1    91.090909       73      100 7.18963900977711
        2    85.363636       79       93 3.95658254741963
        3    97.750000       91      100 2.80016233295663

下面查询

;with UnpivotedScores as(  select StudentColumn='Student'+convert(varchar(10),StudentID)        ,TestScore        ,TestID=convert(int,substring(TestColumnName,5,len(TestColumnName)))  from #Scores  unpivot (TestScore for TestColumnName in (Test1,Test2,Test3,Test4,Test5,Test6                                           ,Test7,Test8,Test9,Test10,Test11,Test12)) U)select * from UnpivotedScorespivot (max(TestScore) for StudentColumn in (Student1,Student2,Student3)) P

可以得到

TestID Student1 Student2 Student3
------ -------- -------- --------
     1       96       83      100
     2       87       84      100
     3     NULL       89       98
     4      100       79       91
     5       99       88      100
     6       91     NULL       95
     7       73       93       96
     8       90       82       96
     9       90       85       99
    10       91       86      100
    11       93       88       98
    12       92       82      100

注意，这里使用了CTE, 而且一个是PIVOT,另一个是UNPIVOT,

是否很美丽呢？

select StudentID      ,AverageScore=avg(TestScore*1.0)      ,MinScore=min(TestScore)      ,MaxScore=max(TestScore)      ,StdDev=stdev(TestScore)from #Scoresunpivot (TestScore for Descript in (Test1,Test2,Test3,Test4,Test5,Test6                                   ,Test7,Test8,Test9,Test10,Test11,Test12)) Pgroup by StudentID