用户购买平台：没有数据的组合也要显示 0

题意

消费表记录用户每天在桌面端或手机端的消费金额。对每一天，分别统计「只用手机端」「只用桌面端」「两端都用」的用户数和总消费金额；某天某类没有用户时也要输出一行，金额和人数为 0。

表结构

• Spending：user_id、spend_date、platform（desktop / mobile）、amount；(user_id, spend_date, platform) 为主键

思路

分三步：一是按（日期, 用户）分组，给每个用户当天打上平台标签：只用了一种平台就是那个平台，否则是 both，同时算出他当天的总金额；二是构造「每个日期 × 三种平台」的完整组合表；三是用组合表 LEFT JOIN 用户标签结果，再按日期和平台聚合，空值补 0。

参考解法

select
    spend_date, platform,
    ifnull(sum(total_am),0) total_amount,
    ifnull(sum(total_u),0) total_users
from
(
    select p.spend_date, p.platform, t.total_am, t.total_u
    from
    (
        select distinct spend_date, "desktop" platform from Spending
        union
        select distinct spend_date, "mobile" platform from Spending
        union
        select distinct spend_date, "both" platform from Spending
    ) p
    left join
    (
        select spend_date, 
            if(count(distinct platform)=1, platform, 'both') plat,
            sum(amount) total_am,
            count(distinct user_id) total_u
        from Spending
        group by spend_date, user_id
    ) t
    on p.platform = t.plat and p.spend_date = t.spend_date
) temp
group by spend_date, platform