Início / dba / Perguntas / 46020
Accepted
Ali Razeghi - AWS
Asked: 2013-07-10 10:54:01 +0800 CST

Criando visualização indexada GROUP BY Epoch Date

  • 772

Eu tenho algumas tabelas grandes com cerca de 6 bilhões de linhas que eu queria otimizar. A chave agrupada é Epoch (data e hora unix, que é o número de segundos que se passaram após 1970) e ID do cliente. Esta tabela registra dados de uso por cliente por tipo de produto.

Por exemplo, se for para uma Telco, TypeID 1 é uma chamada local e o valor é quantos minutos usados ​​para esse cliente. TypeID2 é uma chamada internacional e é o valor de quantos minutos foram usados ​​naquela hora para aquele cliente. Digamos que TypeID3 seja uma tarifa com desconto especial para chamadas domésticas.

Os dados são armazenados em intervalos de 1 hora. Quero que a exibição indexada armazene o valor agregado de 24 horas, portanto, quando executarmos uma consulta para 1 dia por cliente, ela deverá procurar apenas 1 linha na exibição indexada em vez de 24 linhas na tabela base.

Esta é a tabela base:

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

Não nos importamos com Aggregate ou RowID para fins de relatório, então imagino que a visualização indexada ficará assim:

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)

EDITAR:

Dados básicos de amostra (deixei de fora as colunas que não precisamos neste caso, apenas assuma que as colunas de ID estão lá). Cada "TypeID" terá um valor atribuído a ele, cujo valor pode ser 0.

Por exemplo,

    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)

Vamos supor que todas as outras colunas VALUE sejam 0 pelo restante do dia, já que o sistema caiu e ninguém pôde usar seus telefones depois das 2h. Quero que minha exibição indexada registre a coluna de valor agregada por dia, por customerID e TypeID.

Amostra seria:

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 respostas

  1. Best Answer

    Acho que há algum mal-entendido sobre o que você está tentando fazer aqui.

    Como seu design atual é retornar todas as 24 linhas da tabela base, presumivelmente todos os campos suplementares também são retornados (para exibir em uma grade ou algo assim).

    Para agregar totalmente a Valuecoluna, todas as colunas suplementares não podem ser incluídas na SELECTlista. Como alternativa, se essas colunas forem incluídas na GROUP BYcláusula, a exibição representaria apenas uma agregação parcial, pois haveria uma linha para cada combinação exclusiva das colunas na GROUP BYlista de colunas.

    A única maneira de ver algo assim útil é se as colunas suplementares não estiverem incluídas na exibição e houver algum outro processo que exija apenas os valores agregados diários, sem os dados da linha base. Tal visão poderia ser definida assim:

    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);

    Infelizmente, você não pode ir além e converter a EpochDaycoluna em uma data real dentro da exibição indexada porque DATEADDnão é determinística (consulte o comentário de Aaron abaixo para saber o motivo), então você teria que convertê-la na SELECTconsulta real em relação à exibição. Mas isso não é muito difícil.

    De qualquer forma, como eu disse antes, não tenho certeza de como isso seria útil para seu aplicativo específico.

    • 5

relate perguntas