彩票走势图

UNPIVOT是否是将列转换为行的最佳办法(下)

原创|使用教程|编辑:龚雪|2013-12-13 09:29:39.000|阅读 253 次

概述:UNPIVOT的确是在执行将列转化为行的任务时比较常用的方法,其优势也比较突出。本文将会介绍包括UNPIVOT在内的多种方法,而其中有比UNPIVOT更有优势的VALUES结构。

# 慧都年终大促·界面/图表报表/文档/IDE等千款热门软控件火热促销中 >>

相关链接:

4. Dynamic SQL

使用dynamic SQL可以在以下情况里对所有的表创建一个通用查询:在primary key里没有包含到列,但是有可兼容的数据类型存在时。

DECLARE@table_name SYSNAME
SELECT@table_name ='dbo.Players'
 
DECLARE@SQL NVARCHAR(MAX)
SELECT@SQL ='
SELECT *
FROM '+ @table_name +'
UNPIVOT (
    value FOR code IN (
        '+ STUFF((
    SELECT', ['+ c.name+']'
    FROMsys.columns cWITH(NOLOCK)
    LEFTJOIN(
        SELECTi.[object_id], i.column_id
        FROMsys.index_columns iWITH(NOLOCK)
        WHEREi.index_id = 1
    ) iONc.[object_id] = i.[object_id]ANDc.column_id = i.column_id
    WHEREc.[object_id] = OBJECT_ID(@table_name)
        ANDi.[object_id]ISNULL
    FORXML PATH(''), TYPE).value('.','NVARCHAR(MAX)'), 1, 2,'') +'
    )
) unpiv'
 
PRINT @SQL
EXECsys.sp_executesql @SQL

其结果为:

SELECT*
FROM<table_name>
UNPIVOT (
    valueFORcodeIN(<unpivot_column>)
) unpiv

这个方法在速度上会比较慢一些,是因为UNPIVOT的自动生成查询需要从系统里进行额外的读取以及通过XML trick进行“行”的串联。

5. XML

一个更聪明的办法来执行 dynamic UNPIVOT,是通过对XML做一个小窍门:

SELECT
      p.PlayerID
    , GameCount = t.c.value('.','INT')
    , GameType = t.c.value('local-name(.)','VARCHAR(10)')
FROM(
    SELECT
          PlayerID
        , [XML] = (
                SELECTWin, Defeat, StandOff
                FORXML RAW('f'), TYPE
            )
    FROMdbo.Players
) p
CROSSAPPLY p.[XML].nodes('f/@*') t(c)

接下来就是属性的名称和值被解析。大多数情况下,XML的使用会导致一个更慢的执行计划:

XML执行计划

我们来对比一下通过执行 Compare Selected Results指令的结果:

Compare Selected Results

我们可以看到,在执行查询的速度上,UNPIVOT和VALUES没有太明显的区别。当然,这只是针对简单的将列转化为行的任务而言。

现在我们用UNPIVOT语句再来做另外一个实验:

SELECT
      PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROMdbo.Players
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        WHEREPlayerID = p.PlayerID
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

这次的执行计划的瓶颈是对多重数据的读取和排序:

多重数据的读取和排序

要解决这个瓶颈其实是相当的简单,我们可以使用来自外部查询的列的模块就能避免多重数据读取的问题:

SELECT
      p.PlayerID
    , GameType = (
        SELECTTOP1 GameType
        FROM(SELECTt = 1) t
        UNPIVOT (
            GameCountFORGameTypeIN(
                Win, Defeat, StandOff
            )
        ) unpvt
        ORDERBYGameCountDESC
    )
FROMdbo.Players p

这样一来读取多重的数据就被避免了,但是另外一个最消耗资源的操作—&mdash;排序,仍然存在:

排序

接下来就需要VALUES语句来发挥其作用了:

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            ORDERBYGameCountDESC
        )
FROMdbo.Players t

现在就如我们所预期的那样,执行计划被简化了,但是排序依然存在:

排序依然存在

让我们尝试使用aggregation功能来消除掉排序:

SELECT
      t.PlayerID
    , GameType = (
            SELECTTOP1 GameType
            FROM(
                VALUES
                      (Win, 'Win')
                    , (Defeat,  'Defeat')
                    , (StandOff,'StandOff')
            ) t (GameCount, GameType)
            WHEREGameCount = (
                SELECTMAX(Value)
                FROM(
                    VALUES(Win), (Defeat), (StandOff)
                ) t(Value)
            )
        )
FROMdbo.Players t

现在,执行计划就如下图所示了:

执行计划

结论:当我们需要在SQL SERVER里执行一个简单的将列转化为行的任务时,比较好的选择是使用 UNPIVOT或者VALUES结构。如果转换后的数据行是用作聚合或排序时,则最好使用VALUES结构,因为它能生成一个更有效率的执行计划。

>>点此免费下载试用dbForge Studio for SQL Server


标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn


为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
扫码咨询


添加微信 立即咨询

电话咨询

客服热线
023-68661681

TOP