Início / dba / Perguntas / 223120
Accepted
Gerrit
Asked: 2018-11-22 08:06:07 +0800 CST

subtrair o valor ausente do último valor

  • 772

Ok, então é meio difícil para mim, como um falante não nativo de inglês, escrever isso, então, por favor, tenha paciência comigo.

Eu tenho uma tabela de origem da seguinte forma:

CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO

Como você pode imaginar a partir do nome, vou escrever informações de troca neste banco de dados para cada caixa de correio em nossa empresa, todos os meses.

DADOS DE AMOSTRA

Insert into Mailbox Values 
 (111,N'Database1',N'[email protected]',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'[email protected]',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'[email protected]',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(333,N'Database1',N'[email protected]',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'[email protected]',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

Minha consulta resulta em 1 linha por varredura (ScanTime) por caixa de correio (ObjectSID)

WITH 
MBBB (  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,ROW
,[Database]) 
AS (SELECT  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,Scantime
,ROW_NUMBER() OVER(PARTITION BY ObjectSID ORDER BY ScanTime) ROW
,[Database]
FROM Mailbox),
Growth( [Database]
,ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,Growth) 
AS (select S.[Database]
,S.ObjectSID
,S.Itemsize_MB
,S.Itemsize_del_MB
,S.Arch_Itemsize_MB
,S.Arch_Itemsize_del_MB
,S.ScanTime
,ISNULL((S.Itemsize_MB+S.Itemsize_del_MB+S.Arch_Itemsize_MB+S.Arch_Itemsize_del_MB),0)-ISNULL((X.Itemsize_MB+X.Itemsize_del_MB+X.Arch_Itemsize_MB+X.Arch_Itemsize_del_MB),0) Growth
FROM MBBB S
LEFT JOIN MBBB X 
ON S.ObjectSID=X.ObjectSID 
AND S.Row=X.Row+1
where s.ROW  >= (select MAX(s.ROW)-3 
from MBBB s)) 

select  
g.[Database]
,g.ObjectSID
,SUM(g.Itemsize_MB + g.Itemsize_del_MB + g.Arch_Itemsize_MB + g.Arch_Itemsize_del_MB) as [Mailbox in MB]
,g.Growth
,g.ScanTime
from Growth g
Group By g.ObjectSID, g.[Database], g.ScanTime ,g.Growth
order by g.[Database]


Meu problema:
se uma caixa de correio (ObjectSID) for excluída, o novo tamanho será 0. Portanto, o crescimento deve ser negativo no último tamanho.
ou seja, a caixa de correio do usuário A tem 12 GB de tamanho em julho. A caixa de correio é excluída e não está listada em agosto (já que foi excluída) Eu quero um crescimento de -12 GB (12 GB negativos)
MAS, como a caixa de correio foi excluída, ela não está aparecendo como um novo número_linha (desde que caixa de correio = NULL == Sem novo ROW_Number() )
O problema vem agora com o agrupamento no SSRS:
Se eu agrupar todas as caixas de correio por banco de dados, vejo que para um banco de dados se tem um crescimento positivo,
mas a SOMA de todos os tamanhos de caixa de correio está diminuindo.
ou seja,
Banco de Dados__Tamanho Antigo___Crescimento__Novo Tamanho Banco de Dados1
__ 10GB __ _4GB____9GB

Eu preciso de algum tipo de junção, se entrada na tabela esquerda, e não na direita, subtraia de 0)
Espero que isso seja meio compreensível

Obrigado a todos antecipadamente.

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Você não especificou seu RDBMS ou versão, mas eu olhei para outra pergunta que você fez aqui e notei que você estava usando pelo menos o SQL Server 2016. O seguinte deve funcionar no SQL 2012 e posterior. Adicionei mais algumas linhas aos seus dados de amostra para mostrar mais um mês de verificações (dezembro) para ter certeza de que apresentaria a subtração de uma caixa de correio descartada apenas uma vez.

    Você notará que o ObjectSID 333 não tinha dados de varredura para novembro ou dezembro. Nós refletimos a queda durante a varredura de novembro.

    Dê uma olhada na minha solução e deixe-me saber se eu entendi mal seus requisitos ou você vê um erro nos resultados. 

    --demo setup
drop table if exists dbo.mailbox
CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO
Insert into Mailbox Values 
 (111,N'Database1',N'[email protected]',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'[email protected]',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'[email protected]',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(111,N'Database1',N'[email protected]',64533,16664,2,N'ARCH1',0,0,0,'2018-12-19')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(222,N'Database2',N'[email protected]',296,11,0,N'ARCH2',39139,10867,0,'2018-12-19')
,(333,N'Database1',N'[email protected]',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'[email protected]',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

    Minha solução

    ;WITH MailBoxAgg
AS (
    --Select all distinct ScanTimes from Mailbox and join
    --against Mailbox where the ScanTime <= one of the distict ScanTimes
    --If the Mailbox ScanTime <> Distinct ScanTime, that means the mailbox was dropped
    --so force 0 into [Mailbox in MB]
    SELECT [database]
        ,ObjectSID
        ,CASE 
            WHEN mb.ScanTime <> st.ScanTime
                THEN 0
            ELSE sum(Itemsize_MB + Itemsize_del_MB + Arch_Itemsize_MB + Arch_Itemsize_del_MB)
            END AS [Mailbox in MB]
        ,mb.ScanTime
        ,st.ScanTime AS stscan
    FROM (
        SELECT DISTINCT ScanTime
        FROM Mailbox
        ) st
    JOIN Mailbox mb
        ON mb.ScanTime = (
                SELECT max(ScanTime)
                FROM Mailbox
                WHERE [Database] = mb.[Database]
                    AND ObjectSID = mb.ObjectSID
                    AND ScanTime <= st.ScanTime
                )
    GROUP BY ObjectSID
        ,[Database]
        ,mb.ScanTime
        ,st.ScanTime
    )
SELECT [database]
    ,objectsid
    ,[Mailbox in MB]
    ,CASE
        --if the Mailbox ScanTime <> DistinctScanTime, get the
        --previous row value and negate it to represent negative 
        --growth for a dropped mailbox 
        WHEN scantime <> stscan
            THEN lag([mailbox in MB]) OVER (
                    PARTITION BY [database]
                    ,objectsid ORDER BY scantime
                    ) * - 1
        --if the previous row [mailbox in MB] is null,
        --this is the first row for the mailbox, so force current MB as growth
        WHEN lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                ) IS NULL
            THEN [mailbox in MB]
        --subtract the previous mailbox mb from the current to reflect growth
        ELSE [mailbox in MB] - lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                )
        END AS Growth
    ,ScanTime
    ,stscan
FROM MailBoxAgg

    Os resultados

    | database  | objectsid | Mailbox in MB | Growth | ScanTime   | stscan     |
|-----------|-----------|---------------|--------|------------|------------|
| Database1 | 111       | 16536         | 16536  | 2018-10-22 | 2018-10-22 |
| Database1 | 111       | 16542         | 6      | 2018-10-24 | 2018-10-24 |
| Database1 | 111       | 16666         | 124    | 2018-11-19 | 2018-11-19 |
| Database1 | 111       | 16666         | 0      | 2018-12-19 | 2018-12-19 |
| Database1 | 333       | 19874         | 19874  | 2018-10-22 | 2018-10-22 |
| Database1 | 333       | 20008         | 134    | 2018-10-24 | 2018-10-24 |
| Database1 | 333       | 0             | -20008 | 2018-10-24 | 2018-11-19 |
| Database1 | 333       | 0             | 0      | 2018-10-24 | 2018-12-19 |
| Database2 | 222       | 10878         | 10878  | 2018-10-22 | 2018-10-22 |
| Database2 | 222       | 10878         | 0      | 2018-10-24 | 2018-10-24 |
| Database2 | 222       | 10878         | 0      | 2018-11-19 | 2018-11-19 |
| Database2 | 222       | 10878         | 0      | 2018-12-19 | 2018-12-19 |
    • 0

relate perguntas