SQL 查询 - 多列的 SUM(CASE WHEN x THEN 1 ELSE 0)

Question

我想看看下面的查询是否有更好的方法。我想做的是创建一个总结报告，按日期编译统计数据。

 SELECT CAST(Detail.ReceiptDate AS DATE) AS 'DATE'
, SUM(CASE WHEN Detail.Type = 'TotalMailed' THEN 1 ELSE 0 END) AS 'TOTALMAILED'
, SUM(CASE WHEN Detail.Type = 'TotalReturnMail' THEN 1 ELSE 0 END) AS 'TOTALUNDELINOTICESRECEIVED'
, SUM(CASE WHEN Detail.Type = 'TraceReturnedMail' THEN 1 ELSE 0 END) AS 'TRACEUNDELNOTICESRECEIVED'
FROM
(
select SentDate AS 'ReceiptDate', 'TotalMailed' AS 'Type'
from MailDataExtract
where sentdate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TotalReturnMail' AS 'Type'
from MailDataExtract MDE
where MDE.ReturnMailDate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TraceReturnedMail' AS 'Type'
from MailDataExtract MDE
    inner join DTSharedData.dbo.ScanData SD ON SD.ScanDataID = MDE.ReturnScanDataID
where MDE.ReturnMailDate is not null AND SD.ReturnMailTypeID = 1
) AS Detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1   

这只是查询的一个示例(在报告中使用)，因为还有许多其他列，其他统计数据的逻辑要复杂得多。是否有更优雅的方法来获取此类信息/编写此类报告？

Answer 1

我将通过以下方式更改查询:

在子查询中进行聚合。这可以利用有关表的更多信息来优化 group by .

合并第二个和第三个子查询。它们聚合在同一列上。这需要使用 left outer join以确保所有数据都可用。

通过使用 count(<fieldname>)您可以消除与 is null 的比较.这对于第二个和第三个计算值很重要。

要合并第二个和第三个查询，它需要从 mde 中计算一个 id。表。这些使用 mde.mdeid .

以下版本使用 union all 遵循您的示例:

SELECT CAST(Detail.ReceiptDate AS DATE) AS "Date",
       SUM(TOTALMAILED) as TotalMailed,
       SUM(TOTALUNDELINOTICESRECEIVED) as TOTALUNDELINOTICESRECEIVED,
       SUM(TRACEUNDELNOTICESRECEIVED) as TRACEUNDELNOTICESRECEIVED
FROM ((select SentDate AS "ReceiptDate", COUNT(*) as TotalMailed,
              NULL as TOTALUNDELINOTICESRECEIVED, NULL as TRACEUNDELNOTICESRECEIVED
       from MailDataExtract
       where SentDate is not null
       group by SentDate
      ) union all
      (select MDE.ReturnMailDate AS ReceiptDate, 0,
              COUNT(distinct mde.mdeid) as TOTALUNDELINOTICESRECEIVED,
              SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED
       from MailDataExtract MDE left outer join
            DTSharedData.dbo.ScanData SD
            ON SD.ScanDataID = MDE.ReturnScanDataID
       group by MDE.ReturnMailDate;
      )
     ) detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1;

以下使用 full outer join 做类似的事情:

SELECT coalesce(sd.ReceiptDate, mde.ReceiptDate) AS "Date",
       sd.TotalMailed, mde.TOTALUNDELINOTICESRECEIVED,
       mde.TRACEUNDELNOTICESRECEIVED
FROM (select cast(SentDate as date) AS "ReceiptDate", COUNT(*) as TotalMailed
      from MailDataExtract
      where SentDate is not null
      group by cast(SentDate as date)
     ) sd full outer join
    (select cast(MDE.ReturnMailDate as date) AS ReceiptDate,
            COUNT(distinct mde.mdeID) as TOTALUNDELINOTICESRECEIVED,
            SUM(case when sd.ReturnMailTypeId = 1 then 1 else 0 end) as TRACEUNDELNOTICESRECEIVED
     from MailDataExtract MDE left outer join
          DTSharedData.dbo.ScanData SD
          ON SD.ScanDataID = MDE.ReturnScanDataID
     group by cast(MDE.ReturnMailDate as date)
    ) mde
    on sd.ReceiptDate = mde.ReceiptDate
ORDER BY 1;