主页 / dba / 问题 / 223120
Accepted
Gerrit
Asked: 2018-11-22 08:06:07 +0800 CST

从最后一个值中减去缺失值

  • 772

好吧，作为一个非英语母语的人来说，把它写下来虽然困难，但请耐心等待。

我有一个源表如下：

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')
,(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')

我的查询结果为每个邮箱 (ObjectSID) 每次扫描 (ScanTime) 1 行

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 月份的大小为 12GB。邮箱被删除，并且在 8 月没有列出（因为它已被删除）我想要 -12GB 的增长（负 12GB）
但是，由于邮箱被删除，它没有显示为新的 row_Number（因为 Mailbox = NULL == 没有新的 ROW_Number() )
现在问题出现在 SSRS 中的分组：
如果我将每个数据库的所有邮箱分组，我看到数据库如果有正增长，
但所有邮箱大小的总和正在减少。
即
数据库__旧大小___增长__新大小
数据库1 __ 10GB __ _4GB ____9GB

我需要某种连接，如果在左表中输入，而不是在右表中输入，则从 0 中减去）
我希望这是可以理解的一半

提前非常感谢大家。

sql-server sql-server-2016

1 个回答

  1. Best Answer

    您没有指定您的 RDBMS 或版本，但我查看了您在此处提出的另一个问题并注意到您至少使用了 SQL 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'[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')

    我的解决方案

    ;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 |
    • 0

相关问题