如何统计orders表中各地区每日的用户首单总数量？

我有一张orders表，需要统计各地区每日的用户首单总数量。

首先我通过以下SQL获取每个唯一用户的首单信息：

SELECT  customer_id, 
    MIN(order_date) first_buy, 
    region
FROM orders
GROUP BY 1
ORDER BY 2, 1;

执行后得到结果：

customer_id, first_buy, region
BD-11500, 2017-01-02, Central
DB-13060, 2017-01-03, West
GW-14605, 2017-01-03, West
HR-14770, 2017-01-03, West
SC-20380, 2017-01-03, West
VF-21715, 2017-01-03, Central

从结果能看到2017-01-03日West地区有4个用户的首单，我期望得到如下格式的统计结果：

first_buy, region, count_user
2017-01-02, Central, 1
2017-01-03, West, 4
2017-01-03, Central, 1

解决方案 #

可以将获取用户首单信息的查询作为子查询，再按first_buy和region分组统计用户数量：

SELECT 
    first_buy,
    region,
    COUNT(customer_id) AS count_user
FROM (
    SELECT  
        customer_id, 
        MIN(order_date) first_buy, 
        region
    FROM orders
    GROUP BY customer_id
) AS user_first_orders
GROUP BY first_buy, region
ORDER BY first_buy, region;

也可以用窗口函数先标记每个用户的首单，再筛选后统计：

SELECT 
    order_date AS first_buy,
    region,
    COUNT(DISTINCT customer_id) AS count_user
FROM (
    SELECT 
        customer_id,
        order_date,
        region,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
    FROM orders
) AS ranked_orders
WHERE rn = 1
GROUP BY order_date, region
ORDER BY order_date, region;

这两种方式都能得到期望的统计结果。

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