如何在统计邮件计数时合并显示对应PROPERTY字段值？

我有一张名为list_all的表，结构及数据如下：

|   EMAIL     |    PROPERTY     |   ISP_GROUP    |  LAST_SENT_DATE |
--------------------------------------------------------------------
| email1      |    prop1        |   gmail        |  2024-06-25     |
| email2      |    prop3        |   yahoo        |  2024-06-02     |
| email3      |    prop2        |   other        |  2024-06-15     |
| email1      |    prop5        |   gmail        |  2024-06-16     |
| email1      |    prop6        |   gmail        |  2024-06-22     |
--------------------------------------------------------------------

目前我可以通过以下查询获取指定日期范围内各邮箱的出现次数：

SELECT 
    COUNT(`email`) as 'EMAIL COUNT'
    , `email` 
    , `isp_group`
FROM `list_all` 
WHERE `last_sent_date` BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY `email`, `isp_group`
ORDER BY `EMAIL COUNT` DESC

查询结果如下：

| EMAIL COUNT    |     email      |   isp_group   |
---------------------------------------------------
|     3          |     email1     |   gmail       |
|     1          |     email2     |   yahoo       |
|     1          |     email3     |   other       |
---------------------------------------------------

我希望在保留邮件计数的同时，将同一邮箱对应的所有PROPERTY合并到一个单元格中，期望得到如下结果：

| EMAIL COUNT    |     email      |   isp_group   |  PROPERTY         |
-----------------------------------------------------------------------
|     3          |     email1     |   gmail       |  prop1,prop5,prop6 |
|     1          |     email2     |   yahoo       |     prop3          |
|     1          |     email3     |   other       |     prop2          |
-----------------------------------------------------------------------

请问能否实现这样的需求？

解决方案 #

完全可以实现，不同数据库有对应的字符串聚合函数，以下是主流数据库的实现方式：

MySQL/MariaDB

使用GROUP_CONCAT()函数合并PROPERTY字段：

SELECT 
    COUNT(`email`) as 'EMAIL COUNT'
    , `email` 
    , `isp_group`
    , GROUP_CONCAT(`PROPERTY` SEPARATOR ',') as PROPERTY
FROM `list_all` 
WHERE `last_sent_date` BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY `email`, `isp_group`
ORDER BY `EMAIL COUNT` DESC

若需要对PROPERTY去重，可改用GROUP_CONCAT(DISTINCT PROPERTY SEPARATOR ',')。

PostgreSQL

使用STRING_AGG()函数：

SELECT 
    COUNT(email) as "EMAIL COUNT"
    , email 
    , isp_group
    , STRING_AGG(property, ',') as PROPERTY
FROM list_all 
WHERE last_sent_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY email, isp_group
ORDER BY "EMAIL COUNT" DESC

去重可使用STRING_AGG(DISTINCT property, ',')。

SQL Server（2017及以上版本）

使用STRING_AGG()函数：

SELECT 
    COUNT(email) as [EMAIL COUNT]
    , email 
    , isp_group
    , STRING_AGG(property, ',') as PROPERTY
FROM list_all 
WHERE last_sent_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY email, isp_group
ORDER BY [EMAIL COUNT] DESC

去重需结合子查询实现：

SELECT 
    COUNT(email) as [EMAIL COUNT]
    , email 
    , isp_group
    , STRING_AGG(property, ',') as PROPERTY
FROM (
    SELECT DISTINCT email, isp_group, property
    FROM list_all 
    WHERE last_sent_date BETWEEN '2024-06-01' AND '2024-06-30'
) t
GROUP BY email, isp_group
ORDER BY [EMAIL COUNT] DESC

Oracle

使用LISTAGG()函数：

SELECT 
    COUNT(email) as "EMAIL COUNT"
    , email 
    , isp_group
    , LISTAGG(property, ',') WITHIN GROUP (ORDER BY property) as PROPERTY
FROM list_all 
WHERE last_sent_date BETWEEN DATE '2024-06-01' AND DATE '2024-06-30'
GROUP BY email, isp_group
ORDER BY "EMAIL COUNT" DESC

去重需结合子查询：

SELECT 
    COUNT(email) as "EMAIL COUNT"
    , email 
    , isp_group
    , LISTAGG(property, ',') WITHIN GROUP (ORDER BY property) as PROPERTY
FROM (
    SELECT DISTINCT email, isp_group, property
    FROM list_all 
    WHERE last_sent_date BETWEEN DATE '2024-06-01' AND DATE '2024-06-30'
) t
GROUP BY email, isp_group
ORDER BY "EMAIL COUNT" DESC

内容的提问来源于stack exchange，提问作者John Beasley