SQL使用GROUP BY后如何统计客户的总交易次数？

客户交易样本数据集 #

原SQL及结果 #

你原来编写的SQL查询代码：

SELECT
Name, Shop, SUM(Amount), Category, Count(*) AS Num_of_Txn 
FROM Table
GROUP BY
Name, Shop, Category

执行后得到的结果：

这里的问题是，COUNT(*)统计的是当前分组（Name, Shop, Category）内的交易次数，而非客户的总交易次数。

解决方案与逻辑 #

要在按Name, Shop, Category分组的同时显示客户总交易次数，有两种常用实现方式：

方法1：使用窗口函数 #

窗口函数可在不改变现有分组的前提下，跨分组计算客户的总交易数：

SELECT
    Name,
    Shop,
    SUM(Amount) AS Total_Amount,
    Category,
    COUNT(*) OVER (PARTITION BY Name) AS Num_of_Txn
FROM Table
GROUP BY
    Name, Shop, Category

• COUNT(*) OVER (PARTITION BY Name)的作用是：以Name为分区维度，统计每个客户的所有交易记录总数，该结果会附加到每一行分组数据上。

方法2：先统计客户总交易数再关联 #

先单独统计每个客户的总交易次数，再通过关联查询合并到分组结果中：

WITH CustomerTotalTxn AS (
    -- 先统计每个客户的总交易数
    SELECT Name, COUNT(*) AS Total_Txn
    FROM Table
    GROUP BY Name
)
SELECT
    t.Name,
    t.Shop,
    SUM(t.Amount) AS Total_Amount,
    t.Category,
    ctt.Total_Txn AS Num_of_Txn
FROM Table t
JOIN CustomerTotalTxn ctt ON t.Name = ctt.Name
GROUP BY
    t.Name, t.Shop, t.Category, ctt.Total_Txn

预期结果 #

两种方法执行后都会得到如下结果：

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