主页 / dba / 问题 / 103988
Accepted
Evaldas Buinauskas
Asked: 2015-06-14 06:16:32 +0800 CST

SQL Server 数据透视表按视图中指定的别名过滤基础表

  • 772

我的表结构如下：

CREATE TABLE [dbo].[Test] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [ProductID] [int] NOT NULL
   , [CountryID] [int] NOT NULL
   , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestData] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [TestID] [int] NOT NULL
   , [TimeSeries] [int] NOT NULL
   , [Values] [float] NULL
   , CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData]
   WITH CHECK ADD CONSTRAINT [FK_Test_TestData] FOREIGN KEY ([TestID]) REFERENCES [dbo].[Test]([ID])
GO

ALTER TABLE [dbo].[TestData] CHECK CONSTRAINT [FK_Test_TestData]
GO

部分测试数据：

INSERT INTO dbo.Test (ProductID, CountryID)
VALUES (1, 1), (1, 2), (1, 3), (1, 4)
    , (2, 1);

INSERT INTO dbo.TestData (TestID, TimeSeries, [Values])
VALUES (1, 2013, 0.5), (1, 2014, 1.9)
    , (2, 2013, 0.78), (2, 2014, 9.7)
    , (3, 2012, 0.77), (3, 2013, 5.6), (3, 2014, 1.54)
    , (4, 2011, 0.81), (4, 2014, 6.7);

我从中创建了 Pivoted 视图：

CREATE VIEW dbo.vw_PivotedTest
AS
SELECT ProductID, CountryID, [2011], [2012], [2013], [2014]
FROM (
    SELECT T.ProductID, T.CountryID, TD.TimeSeries, TD.[Values]
    FROM dbo.Test AS T
    LEFT JOIN dbo.TestData AS TD
        ON TD.TestID = T.ID
    ) AS S
PIVOT (
    AVG([Values])
    FOR TimeSeries IN ([2011], [2012], [2013], [2014])
    ) AS P;

我会这样查询：

SELECT ProductID, CountryID, [2011]
FROM dbo.vw_PivotedTest;

现在问题来了：SQL Server 是否可以只从 dbo.TestData 中选取行，其中 TimeSeries 值仅为 2011 而不是扫描所有内容？

我查看了执行计划，它似乎在扫描整个表并且没有过滤掉这些结果。

执行计划

sql-server sql-server-2014

1 个回答

  1. Best Answer

    不是现在，您将必须为其添加索引，并且永远无法从旋转视图中仅扫描 2011 列 - 向该视图添加索引是不可能的，并且会产生错误Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this view.。

    然后需要为底层数据创建索引，让 Pivot 算子运行得更快。让我们看看优化器是否有话要说。

    您的示例数据非常小，因此请像这样运行您的查询。

    SELECT ProductID, CountryID, [2011]
 FROM dbo.vw_PivotedTest 
 option (OPTIMIZE FOR UNKNOWN);

    这将优化更大集合的查询，您还可以GO 50在插入语句后添加到 dbo.TestData 表中以运行插入语句 50 次，从而添加更多数据，然后您可以跳过选项部分。

    然后您将从优化器获得以下索引建议：

    CREATE INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TestData] ([TestID])
INCLUDE ([TimeSeries],[Values]);

    这会有所帮助，但会扫描 ProductID - 这是有道理的，您也可以像这样创建该索引

    CREATE NONCLUSTERED INDEX idx2
ON [dbo].[TestData] ([TestID],[TimeSeries],[Values]);

    但是您最终会得到相同的查询计划和更昂贵的插入，因此几乎没有收获。

    QueryPlanWithAndWithoutInclude

    • 2

相关问题