Sql 用户购买平台及金额统计

为此，您可以使用多个SELECT获取每天的统计数据，并使用GROUP BY和HAVING子句获取平台统计数据，然后在不同的平台上应用CROSS JOIN以获得所需的输出:

with cte as (
  select date, platform, sum(total_amount) as total_amount,
                         count(total_users) as total_users
  from (
    select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having min(platform) = 'mobile' and max(platform) = 'mobile'
  union all 
    select date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having min(platform) = 'desktop' and max(platform) = 'desktop'
  union all
    select date, 'both' as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
    from mytable
    group by user_id, date
    having count(case when platform = 'desktop' then 1 end) + count(case when platform = 'mobile' then 1 end) = count(*)
       and count(case when platform = 'desktop' then 1 end) >= 1
       and count(case when platform = 'mobile' then 1 end) >= 1
  ) as s
  group by date, platform
),
dates as (
  select distinct date from mytable
),
platforms as (
  select 'mobile' as platform union all
  select 'desktop' union all
  select 'both'
)
select d.date, p.platform, coalesce(c.total_amount, 0) as total_amount, coalesce(c.total_users, 0) as total_users
from dates d
cross join platforms p
left join cte c on c.date = d.date and c.platform = p.platform
order by d.date, total_amount;

结果:

date        platform    total_amount    total_users
2023-12-01  desktop     22.00           1
2023-12-01  both        60.67           1
2023-12-01  mobile      83.46           1
2023-12-02  both        0               0
2023-12-02  desktop     19.90           1
2023-12-02  mobile      107.44          2
2023-12-03  both        0               0
2023-12-03  desktop     0               0
2023-12-03  mobile      28.28           1

Demo on postgresql个