SQL Server 2008 R2:数据透视表查询性能

Question

表：产品

create table Product
(
    productID int,
    productName varchar(20),
    productsalesdate DATETIME,
    producttype varchar(20)
);

插入：

insert into product values(1,'PenDrive','2010-01-01','Electronic');
insert into product values(1,'Computer','2016-01-01','Electronic');
insert into product values(1,'Laptop','2011-02-02','Electronic');
insert into product values(2,'textbook','2014-02-02','books');
insert into product values(2,'notebook','2016-01-01','books');
insert into product values(3,'Car','2016-01-01','Vehicle');
insert into product values(3,'Bike','2016-01-07','Vehicle');

第一次尝试：在这种情况下，我得到了错误的产品类型的总和

SELECT productID, FirstSale,LastSale, [Electronic],[books],[Vehicle]
FROM
(
    SELECT 
        productID, 
        MIN(ProductSalesdate) as FirstSale,
        MAX(ProductSalesdate) as LastSale,
        productType
    FROM
        Product
    Group by productID,productType
) a 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;   

第二次尝试：在这次尝试中，我已经解决了求和问题，但对于大量记录，执行查询需要更多时间。

SELECT productID,FirstSale,LastSale ,[Electronic],[books],[Vehicle]
FROM
(
    SELECT a.ProductID, a.FirstSale, a.LastSale, b.ProductType
    FROM Product b
    inner join
    (
        SELECT 
            productID, 
            MIN(ProductSalesdate) as FirstSale,
            MAX(ProductSalesdate) as LastSale
        FROM
            Product
        Group by productID
    ) as a 
    ON a.ProductID = b.ProductID
) ab 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;   

注意：第二个查询工作正常，但问题在于性能，因为我加入了两个相同的表，因为要在透视查询中获得productType的计数。

问题：如何优化我第二次尝试的第二个查询？

Answer 1

下面使用一个临时表来存储派生表ab。我的猜测是，它将改进第二个查询的执行计划。

SELECT a.ProductID, a.FirstSale, a.LastSale, b.ProductType
INTO #ab
FROM Product b
inner join
(
    SELECT 
        productID, 
        MIN(ProductSalesdate) as FirstSale,
        MAX(ProductSalesdate) as LastSale
    FROM
        Product
    Group by productID
) as a 
ON a.ProductID = b.ProductID;
SELECT productID,FirstSale,LastSale ,[Electronic],[books],[Vehicle]
FROM #ab AS ab 
PIVOT
(
    COUNT(productType) 
    FOR productType IN ( [Electronic],[books],[Vehicle] )
) AS pvt;
DROP TABLE #ab;

---

编辑：只是为了体育，我写了以下脚本，在#产品中有15k行。整个脚本在大约1秒内执行完毕。我仍然不明白你的查询怎么花了5.5分钟。如下：

SET NOCOUNT ON;
CREATE TABLE #product (
    product_id INT,
    product_name VARCHAR(20),
    product_sales_date DATE,
    product_type VARCHAR(20)
);
DECLARE @cnt INT=0;
WHILE @cnt<15000
BEGIN
    INSERT INTO #product(
        product_id,
        product_name,
        product_sales_date,
        product_type
    )
    SELECT 
        product_id=ROUND(20*RAND(),0),
        product_name=LEFT(NEWID(),20),
        product_sales_date=DATEADD(DAY,ROUND((-10+20*RAND()), 0),GETDATE()),
        product_type=
            CASE ROUND(2*RAND(),0)
                WHEN 0 THEN 'Electronic'
                WHEN 1 THEN 'books'
                ELSE 'Vehicle'
            END;
    SET @cnt=@cnt+1;
END
SELECT a.product_id, a.first_sale, a.last_sale, b.product_type
INTO #ab
FROM #product b
inner join
(
    SELECT 
        product_id, 
        MIN(product_sales_date) as first_sale,
        MAX(product_sales_date) as last_sale
    FROM
        #product
    GROUP BY
        product_id
) as a 
ON a.product_id= b.product_id;
SELECT product_id,first_sale,last_sale,[Electronic],[books],[Vehicle]
FROM #ab AS ab 
PIVOT
(
    COUNT(product_type) 
    FOR product_type IN ( [Electronic],[books],[Vehicle] )
) AS pvt;
DROP TABLE #ab;
DROP TABLE #product;

Answer 2

看起来你正试图做这样的事情。。不确定为什么需要额外的联接或临时表。。

SELECT * FROM
(
    SELECT  productID, 
            productType,
            MIN(ProductSalesdate) as FirstSale,
            MAX(ProductSalesdate) as LastSale,
            COUNT(productType) AS ProductCount
    FROM Product
    GROUP BY productID,productType
) t
PIVOT 
(
    SUM(ProductCount)
    FOR productType IN ([Electronic],[books],[Vehicle])
) p

您将获得0计数的NULLS，但您可以很容易地将这些值合并为0