主页 / dba / 问题 / 46020
Accepted
Ali Razeghi - AWS
Asked: 2013-07-10 10:54:01 +0800 CST

创建索引视图 GROUP BY Epoch Date

  • 772

我有几张包含大约 60 亿行的大表，我正在寻求优化。集群键是 Epoch（unix 日期时间，即 1970 年之后经过的秒数）和客户 ID。此表记录每个客户每种产品类型的使用数据。

例如，如果这是针对电信公司的，TypeID 1 是本地呼叫，值是该客户使用的分钟数。TypeID2 是国际电话，是该客户在该小时内使用了多少分钟的值。假设 TypeID3 是国内电话的特殊折扣费率。

数据以 1 小时的间隔存储。我希望索引视图存储聚合的 24 小时值，因此当我们对每个客户运行 1 天的查询时，它只需要在索引视图中查找 1 行，而不是在基表中查找 24 行。

这是基表：

ColRowID (bigint)
AggregateID (int)
Epoch (int)
CustomerID (int)
TypeID  (tinyint)
ErrorID (smallint)
Value (int)

出于报告目的，我们不关心 Aggregate 或 RowID，因此我认为索引视图将如下所示：

CREATE VIEW [ixvw_AggTbl]
WITH SCHEMABINDING
AS
SELECT Epoch, CustomerID, TypeID, ErrorID, SUM(Value)
FROM DBO.BaseTbl
-- GROUP BY Epoch  (what goes here?? Epoch/86400?  If I do that I have to 
-- put Epoch/86400 in the SELECT list as well)

编辑：

示例基础数据（在这种情况下，我省略了我们不需要的列，假设 ID 列在那里）。每个“TypeID”都将分配一个值，该值可以为 0。

例如，

    Epoch / Customer ID / TypeID / Value
    /* Epoch 90,000 is day 2 1am */

90000 (1am  day 2) / 1 / 1 / 200
90000 (1am  day 2) / 1 / 2 / 100
90000 (1am  day 2) / 1 / 3 / 120

/* Customer ID 2 as well */
90000 (1am  day 2) / 2 / 1 / 100
90000 (1am  day 2) / 2 / 2 / 50
90000 (1am  day 2) / 2 / 3 / 310

... (repeat for 30,000 customers)

/* Customer ID 1 2am day 1) */
93600 (2am day 2) / 1 / 1 / 150
93600 (2am day 2) / 1 / 2 / 0
93600 (2am day 2) / 1 / 3 / 550

/* Customer ID 2 2am day 2) */
93600 / 2 / 1 / 80
93600 / 2 / 2 / 150
93600 / 2 / 3 / 300
... (repeat for 30,000 customers)

假设所有其他 VALUE 列在当天剩余时间为 0，因为系统出现故障，并且在凌晨 2 点之后没有人可以使用他们的手机。我希望我的索引视图记录每天聚合 的值列，每个 customerID 和 TypeID。

样本将是：

172800 (Day 3 midnight) / 1 / 1 / 350  --Cust ID 1 aggregated all type id 1 in the past 24 hours
172800 (Day 3 midnight) / 1 / 2 / 100
172800 (Day 3 midnight) / 1 / 3 / 670
172800 (Day 3 midnight) / 2 / 1 / 180  --Cust ID 2 now
172800 (Day 3 midnight) / 2 / 2 / 200
172800 (Day 3 midnight) / 2 / 3 / 610
--Repeat by adding 86400 to the epoch to gather the summary data of the rows for the previous day.
sql-server materialized-view

1 个回答

  1. Best Answer

    我认为您在这里尝试做的事情存在一些误解。

    由于您当前的设计是返回基表中的所有 24 行，因此可能还会返回所有补充字段（以显示在网格中或其他内容中）。

    为了完全聚合Value列，不能将所有补充列都包含在SELECT列表中。或者，如果这些列包含在子句中，则视图将仅表示部分聚合，因为列列表GROUP BY中列的每个唯一组合将有一行。GROUP BY

    我看到这样有用的唯一方法是，如果补充列不包含在视图中，并且还有一些其他过程只需要每日汇总值，而不需要基本行数据。这样的视图可以这样定义：

    CREATE TABLE [dbo].[BaseTbl]
(
    ColRowID bigint NOT NULL,
    AggregateID int NOT NULL,
    Epoch int NOT NULL,
    CustomerID int NOT NULL,
    TypeID tinyint NOT NULL,
    ErrorID smallint NOT NULL,
    Value int NOT NULL,

    PRIMARY KEY CLUSTERED(Epoch, CustomerId)
);
GO

CREATE VIEW [dbo].[ixvw_AggTbl]
    WITH SCHEMABINDING
AS
    SELECT
        t.Epoch / 86400 AS EpochDay,
        CustomerID,
        TypeID,
        SUM(t.Value) AS TotalValue,
        COUNT_BIG(*) AS __RowCount
        FROM [dbo].[BaseTbl] t
        GROUP BY
            t.Epoch / 86400,
            CustomerID,
            TypeID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_ixvw_AggTbl
    ON [dbo].[ixvw_AggTbl](EpochDay, CustomerID, TypeID);

    不幸的是，您无法进一步将EpochDay列转换为索引视图中的实际日期，因为DATEADD它是不确定的（请参阅下面的 Aaron 评论了解原因），因此您必须在SELECT针对视图的实际查询中将其转换。但这并不太难。

    无论如何，正如我之前所说，我不确定这对您的特定应用程序有多大用处。

    • 5

相关问题