在postgres中使用count和group获取数据

Question

我有一个表mail_details

|   mail_id   | sent_time          |  failed_time       | mail_type   |
|   (bigint)  |  (timestamp)       |    (timestamp)     |  (varvharing)
-----------------------------------------------------------------------
|    1        | 2020-02-05         |  null(default)     |   type-t       
|    2        | 2020-02-05         |  null(default)     |   type-t
|    3        | 2020-02-05         |  null(default)     |   type-m
|    4        | 2020-02-05         |  null(default)     |   type-p
|    5        | null(default)      |  2020-02-05        |   type-p
|    6        | 2020-02-05         |  null(default)     |   type-m
|    8        | 2020-02-05         |  null(default)     |   type-m
|    9        | null(default)      |  2020-02-05        |   type-m
|    10       | 2020-02-05         |  null(default)     |   type-n
|    11       | 2020-02-05         |  null(default)     |   type-n

无论何时发送给用户的邮件我更新了sent_time或发送的邮件失败，我都会更新failed_time

现在我想用respective date获取total number of mail sent (count) and the total number of mail failed (count)，其中mail_type=(type_t或type_p或type_m或type_n(

输出将类似

+---------------------+----------------+----------------+
|date                 |tatal_sent_mail |tatal_failed_mail|
+---------------------+----------------+----------------+
|2020-05-05           |8               |2               |
+---------------------+----------------+----------------+

我尝试过计数，但没有成功。任何帮助都会得到通知。

Answer 1

您可以使用聚合过滤器进行汇总，并合并列之间的日期。

SELECT
coalesce(sent_time, failed_time) AS date,
count(*) FILTER (WHERE sent_time IS NOT NULL) AS total_sent_mail,
count(*) FILTER (WHERE failed_time IS NOT NULL) AS total_failed_mail
FROM mail_details
WHERE mail_type IN ('type_t','type_p','type_m','type_n')
GROUP BY 1
ORDER BY 1;

如果您需要缺少日期的行，则需要使用generate_series((。

Answer 2

我会使用横向join和聚合来取消预览：

select time::date, sum(is_sent), sum(is_fail)
from mail_details md cross join lateral
(values (sent_time, (sent_time is not null)::int, 0)
(failed_time, 0, (failed is not null)::int)
) v(time, is_sent, is_fail)
where t.time is not null and
md.mail_type in ('type_t','type_p','type_m','type_n')
group by time::date

Answer 3

另一个在内联视图上使用FULL OUTER JOIN的

select sent_time as date,tatal_sent_mail,tatal_failed_mail
from 
(select sent_time,count(1) tatal_sent_mail
where mail_type in ('type_t','type_p','type_m','type_n')
group by sent_time) sent_data
FULL OUTER JOIN 
(select failed_time Final_date,count(1) tatal_failed_mail
where mail_type in ('type_t','type_p','type_m','type_n')
group by failed_time) failed_Data
ON sent_data.sent_time = failed_Data.failed_time

Answer 4

使用并集，这应该可以做到：

SELECT  dt,
SUM(CASE WHEN event == 'SENT' then 1 else 0 end) total_sent_mail,
SUM(CASE WHEN event == 'FAIL' then 1 else 0 end) total_failed_mail,
FROM
(   SELECT  'SENT' as event,
sent_time as dt 
FROM    mail_details
WHERE   sent_time IS NOT NULL   )
UNION ALL
(   SELECT  'FAIL' as event,
failed_time as dt
FROM    mail_details
WHERE   failed_time IS NOT NULL  )
GROUP BY dt