主页 / dba / 问题 / 208818
Accepted
Mark D
Asked: 2018-06-06 08:54:44 +0800 CST

在 sql server 中使用汇总数据透视

  • 772

是否可以使用游标创建动态表，然后使用这些列来聚合 SQL Server 2008 中的数据？

以下表为例。

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235);

我想做的是用这种格式创建一个表

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

我能想到的唯一方法是聚合垂直表。

通过执行以下查询

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost) 
FROM BillingData;

但是我必须将这些列动态地加入到帐单表中，特别是因为帐单数据可能每个月都不一样。例如：

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

不同于

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

因此，虽然 BillingId、Walks、Baths、Litter change、Groom、Scratching、Usage、Cost 列适用于 4 月，但 May 的列将只是 BillingId、Pilling、Groom、Exercise、Usage 和 Cost。

我相信数据透视表可能是我在这里需要的，但我怀疑它可能需要是动态的，因为每个月的列都需要不同。

我不确定执行此操作的最佳方法。一些帮助将不胜感激。

sql-server sql-server-2008

1 个回答

  1. Best Answer

    这可以通过PIVOT并且可以动态完成，但是在您尝试动态执行此操作之前，您应该尝试使用查询的静态或硬编码版本获得所需的结果，然后将其转换为动态 sql .

    由于您使用的是 SQL Server 2008，并且您希望为Usage和提供一个总列Cost，因此我将首先查看sum(<your column) over(...). 这将允许您在对数据进行透视之前在一个步骤中聚合您的数据。

    为了获得静态版本，我首先从类似于以下的查询开始：

    select 
    b.BillingId,
    bd.Feature,
    bd.Usage,
    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
from Billing b
inner join BillingData bd
    on b.BillingId = bd.BillingId
where b.BillYear = 2018 and b.BillMonth = 4

    请参阅 SQL 小提琴。此查询为您提供要转换的基本数据：

    | BillingId |       Feature | Usage | TotalUsage | TotalCost |
|-----------|---------------|-------|------------|-----------|
|         1 |         Walks |    25 |        583 |  14037.95 |
|         1 |         Baths |     5 |        583 |  14037.95 |
|         1 |         Walks |   500 |        583 |  14037.95 |
|         1 |         Baths |    53 |        583 |  14037.95 |
|         2 |         Baths |    53 |        344 |   1591.21 |
|         2 | Litter change |    53 |        344 |   1591.21 |
|         2 |         Groom |    25 |        344 |   1591.21 |
|         2 |    Scratching |   213 |        344 |   1591.21 |

    包括你BillingId的，Features你最终想要在一个新列中的每一个，然后是Usage,TotalUsage和TotalCostfor each BillingId。为您提供每个帐户的sum(<yourcolumn> over(partition by bd.BillingId)价值，而无需使用GROUP BY. 获得此数据后，您可以应用该PIVOT功能：

    select 
    BillingId,
    Walks = IsNull(Walks, 0),
    Baths = IsNull(Baths, 0),
    [Litter Change] = IsNull([Litter Change], 0),
    Groom = IsNull(Groom, 0),
    Scratching = IsNull(Scratching, 0),
    Usage = TotalUsage,
    Cost = TotalCost
from
(
    select 
        b.BillingId,
        bd.Feature,
        bd.Usage,
        TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
        TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
    from Billing b
    inner join BillingData bd
        on b.BillingId = bd.BillingId
    where b.BillYear = 2018 and b.BillMonth = 4
) x
pivot
(
    sum(Usage)
    for Feature in ([Walks], [Baths], [Litter Change], [Groom], [Scratching])
) piv;

    请参阅 SQL Fiddle 进行演示。这给出了一个结果：

    | BillingId | Walks | Baths | Litter Change | Groom | Scratching | Usage |     Cost |
|-----------|-------|-------|---------------|-------|------------|-------|----------|
|         1 |   525 |    58 |             0 |     0 |          0 |   583 | 14037.95 |
|         2 |     0 |    53 |            53 |    25 |        213 |   344 |  1591.21 |

    现在您已经获得了您正在寻找的最终结果，您可以开始将查询转换为动态 SQL。为了执行此操作，您将需要获取要成为列的值的列表，即Feature值。这是通过使用所需的BillYearand查询表BillMonth，并将值连接成一个字符串，然后获取该列列表并执行完整的 sql 字符串来完成的。完整的代码可能类似于：

    DECLARE 
    @BillYear int = 2018,
    @BillMonth int = 4,
    @colsNull AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ', ' + QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear 
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(bd.Feature)+',0) as '+ QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear 
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

set @query = N'SELECT BillingId, ' + @colsNUll + N', TotalUsage, TotalCost 
            from 
             (
                select 
                    b.BillingId,
                    bd.Feature,
                    bd.Usage,
                    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
                    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
                from Billing b
                inner join BillingData bd
                    on b.BillingId = bd.BillingId
                where b.BillYear = '+cast(@BillYear as nvarchar(4))+N' 
                  and b.BillMonth = '+cast(@BillMonth as nvarchar(2))+N'
            ) x
            pivot 
            (
                sum(Usage)
                for Feature in (' + @cols + N')
            ) p '

exec sp_executesql @query;

    请参阅 SQL Fiddle with Demo。您会注意到列有两个变量 - 一个@cols在函数内部使用PIVOT，然后@colsNull这与第一个类似，但它将nulls最终选择列表中的 替换为零 - 如果不这样做，您可以排除使用它不需要它。如果您执行此操作，BillingMonth = 4您将获得与静态版本相同的结果：

    | BillingId | Baths | Groom | Litter change | Scratching | Walks | TotalUsage | TotalCost |
|-----------|-------|-------|---------------|------------|-------|------------|-----------|
|         1 |    58 |     0 |             0 |          0 |   525 |        583 |  14037.95 |
|         2 |    53 |    25 |            53 |        213 |     0 |        344 |   1591.21 |

    然后，如果您更改，BillingMonth = 5您无需更改查询（Demo）即可获得结果：

    | BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost |
|-----------|----------|-------|---------|------------|-----------|
|         3 |        0 |     0 |      11 |         11 |        10 |
|         4 |        1 |     5 |       0 |          6 |     75.12 |
    • 7

相关问题