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

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

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

Ответы

Ответ 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