一条不常见的SQL查询用MongoDB来实现

select
  `country_code`,
  count(id) as user_count,
  count(if(recharge_count > 0, id, null)) as pay_count,
  count(distinct channel_id) as channels
from
  `users`
where
  `created_at` between '2020-03-07 00:00:00' and '2023-03-07 23:59:59'
group by
  country_code;

如下

db.getCollection("users").aggregate([
    {
        $match: {
            created_at: {
                $gte: new ISODate("2020-03-07T00:00:00"),
                $lte: new ISODate("2023-03-07T23:59:59"),
            }
        }
    },
    {
        $group: {
            _id: "$country_code",
            user_count: {$sum: 1},
            pay_count: {
                $sum: {$cond: {if: {$gte: ["$recharge_count", 0]}, then: 1, else: 0}}
            },
            channel_ids: {$addToSet: "$channel_id"}
        }
    },
    {
        $project: {
            _id: 0,
            country_code: "$_id",
            user_count: 1,
            pay_count: 1,
            channels: {$size: "$channel_ids"}
        }
    }
])

还有这个没解决

count(distinct if(online=1,channel_id,null)) as online_channels

？？？

本作品采用《CC 协议》，转载必须注明作者和本文链接