使用SQL子查询统计不同点击次数对应的广告数量

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

commercial列记录展示给用户的广告编号，clicks列记录用户是否点击（0表示未点击，1表示点击）。

需求是编写SQL查询，使用子查询统计每种点击次数对应的广告编号列表，预期结果如下：

目前已写出以下SQL语句：

SELECT  commercial, SUM(clicks)
FROM     table
GROUP BY commercial 
ORDER BY commercial ASC

执行后得到结果：

现在有两个疑问：

1. 如何通过子查询完成后续统计，得到预期的分组结果？
2. 能否不使用SUM和GROUP BY函数，得到上述中间统计结果？

1. 用子查询实现预期的分组统计 #

你可以先通过子查询算出每个广告的总点击次数，再基于这个子查询的结果，按点击次数分组，并用字符串聚合函数把同次数的广告编号拼接起来。另外要注意预期结果里包含了点击次数为3的行（虽然没有对应广告），需要额外生成这些行或者用左连接补全。

以MySQL为例，SQL语句如下：

SELECT 
    cc.clicks,
    GROUP_CONCAT(ccom.commercial ORDER BY ccom.commercial) AS commercial
FROM (
    SELECT 0 AS clicks UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) cc
LEFT JOIN (
    SELECT 
        commercial,
        SUM(clicks) AS total_clicks
    FROM `table`
    GROUP BY commercial
) ccom ON cc.clicks = ccom.total_clicks
GROUP BY cc.clicks
ORDER BY cc.clicks;

注：不同数据库的字符串聚合函数不同，比如PostgreSQL用STRING_AGG(ccom.commercial, ',' ORDER BY ccom.commercial)，SQL Server用STRING_AGG(ccom.commercial, ',') WITHIN GROUP (ORDER BY ccom.commercial)。

2. 不使用SUM和GROUP BY得到中间统计结果 #

可以用窗口函数或者相关子查询实现，两种方式都不需要外层的GROUP BY和SUM：

方式一：窗口函数（推荐） #

用COUNT()窗口函数统计每个广告的有效点击次数：

SELECT DISTINCT
    commercial,
    COUNT(CASE WHEN clicks = 1 THEN 1 END) OVER (PARTITION BY commercial) AS clicks
FROM `table`
ORDER BY commercial;

方式二：相关子查询 #

通过子查询逐个统计每个广告的有效点击次数：

SELECT DISTINCT
    t1.commercial,
    (SELECT COUNT(*) FROM `table` t2 WHERE t2.commercial = t1.commercial AND t2.clicks = 1) AS clicks
FROM `table` t1
ORDER BY t1.commercial;

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