SQL Query - SUM (CASE WHEN x THEN 1 ELSE 0) для нескольких столбцов

Я смотрю, есть ли лучший подход к запросу ниже. То, что я пытаюсь сделать, - создать сводный отчет, скомпилировать статистику по дате.

 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

Это только образец запроса (который используется в отчете), так как существует ряд других столбцов, и логика для других статистических данных является более сложной. Существует ли более элегантный подход к получению такого рода информации/написанию такого отчета?

Ответ 1

Я бы изменил запрос следующими способами:

Сделайте агрегацию в подзапросах. Это может использовать дополнительную информацию о таблице для оптимизации group by.
Объедините второй и третий подзапросы. Они объединяются в одну колонку. Для этого необходимо использовать left outer join для обеспечения доступности всех данных.
Используя count(<fieldname>), вы можете исключить сравнения с is null. Это важно для второго и третьего вычисленных значений.
Чтобы объединить второй и третий запросы, ему нужно подсчитать id из таблицы mde. Они используют 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;

Ответ 2

Я думаю, вы должны сделать подзапрос, чтобы делать группировку. В этом случае внутренний подзапрос возвращает несколько строк, и вам не нужен оператор CASE. Поэтому я думаю, что это будет быстрее:

select Detail.ReceiptDate AS 'DATE',
       SUM(TotalMailed),
       SUM(TotalReturnMail),
       SUM(TraceReturnedMail)

from
(

select SentDate AS 'ReceiptDate', 
       count('TotalMailed') AS TotalMailed, 
       0 as TotalReturnMail, 
       0 as TraceReturnedMail
from MailDataExtract
where sentdate is not null
GROUP BY SentDate

UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 
       0 AS TotalMailed, 
       count(TotalReturnMail) as TotalReturnMail, 
       0 as TraceReturnedMail
from MailDataExtract MDE
where MDE.ReturnMailDate is not null
GROUP BY  MDE.ReturnMailDate

UNION ALL

select MDE.ReturnMailDate AS 'ReceiptDate', 
       0 AS TotalMailed, 
       0 as TotalReturnMail, 
       count(TraceReturnedMail) as TraceReturnedMail

from MailDataExtract MDE
    inner join DTSharedData.dbo.ScanData SD 
        ON SD.ScanDataID = MDE.ReturnScanDataID
   where MDE.ReturnMailDate is not null AND SD.ReturnMailTypeID = 1
GROUP BY MDE.ReturnMailDate

) as Detail
GROUP BY Detail.ReceiptDate
ORDER BY 1