从最后值中减去缺失值

Question

好吧，如果作为一个非英语母语的我很难把这个写下来，那就太好了，所以请容忍我。

我有一个资料来源表如下：

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

正如您从名称中可以想象到的那样，我每个月都会将Exchange-Information写到我们公司的每个邮箱的数据库中。

样本数据

Insert into Mailbox Values 
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

我的查询结果为每扫描1行(ScanTime) /邮箱(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]

我的问题是：

如果删除邮箱(ObjectSID)，则新大小为0。因此，最后的增长应该是负的。

也就是说，用户A的邮箱7月份的大小为12 in。邮箱已被删除，没有在8月份列出(因为它已被删除)，我希望增长-12 it (负12 it)

但是，由于邮箱被删除，它没有显示为新的row_Number(因为邮箱=空==没有新的ROW_Number() )

现在，SSRS中的分组出现了问题：

如果我将所有邮箱按数据库分组，我会看到，对于数据库来说，如果有一个正增长，

但是所有邮箱大小的总和都在减少。

i.e

Database__Old Size___Growth__New尺寸

Database1 __ 10 __ _4GB____9GB

我需要某种联接，如果在左表中输入，而不是在右边，那么从0减法)

我希望这是可以理解的。

先谢谢大家。

Answer 1

您没有指定RDBMS或版本，但我查看了您在这里提出的另一个问题，注意到您至少使用了Server 2016。下面的内容应该适用于SQL 2012及更高版本。我在样本数据中添加了更多的行，以显示另一个月的扫描(12月)，这样我就可以确保我只提供了一次丢失邮箱的减法。

您会注意到，ObjectSID 333没有11月份或12月份的扫描数据。我们在11月份的扫描中反映了下降的情况。

看看我的解决方案，让我知道我是否误解了您的需求，或者您在结果中看到了错误。

--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'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-12-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-12-19')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

我的解决方案

;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

结果

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