使用PieCloudDB统计每日仅移动端、仅桌面端及双端用户的消费与数量

我有一张记录电商网站桌面端与移动端用户消费数据的表（正在学习PieCloudDB Database），需要统计每日仅使用mobile、仅使用desktop以及同时使用双端的用户数量，以及对应的总消费金额。

原始数据 #

期望结果 #

目前想不到解决方法，恳请技术人员提供帮助。

以下是适用于PieCloudDB Database的SQL语句，可实现需求：

WITH user_daily_stats AS (
    -- 统计每个用户每日的平台类型和总消费金额
    SELECT
        user_id,
        date,
        CASE
            WHEN COUNT(DISTINCT platform) = 2 THEN 'both'
            ELSE MAX(platform)
        END AS user_platform,
        SUM(amount) AS user_total_amount
    FROM
        your_table_name
    GROUP BY
        user_id, date
),
all_date_platform AS (
    -- 生成所有日期和三种平台类型的组合，确保每个日期都有三类记录
    SELECT
        d.date,
        p.platform
    FROM
        (SELECT DISTINCT date FROM your_table_name) d
    CROSS JOIN
        (SELECT 'mobile' AS platform UNION ALL SELECT 'desktop' UNION ALL SELECT 'both') p
)
-- 左连接统计最终结果，无数据的补0
SELECT
    adp.date,
    adp.platform,
    COALESCE(SUM(CASE WHEN uds.user_platform = adp.platform THEN uds.user_total_amount ELSE 0 END), 0) AS total_amount,
    COALESCE(COUNT(DISTINCT CASE WHEN uds.user_platform = adp.platform THEN uds.user_id ELSE NULL END), 0) AS total_users
FROM
    all_date_platform adp
LEFT JOIN
    user_daily_stats uds ON adp.date = uds.date
GROUP BY
    adp.date, adp.platform
ORDER BY
    adp.date, adp.platform;

逻辑说明 #

1. user_daily_stats CTE：按用户和日期分组，通过判断当日用户使用的平台数量，标记用户当日的平台类型（both表示双端，否则为对应单端），同时计算该用户当日的总消费金额。
2. all_date_platform CTE：提取所有存在消费记录的日期，与mobile、desktop、both三种平台类型做笛卡尔积，确保每个日期都能输出三类统计项，避免缺失无数据的类型。
3. 主查询：将日期-平台组合表与用户日统计表左连接，按日期和平台分组统计：
   • 用CASE匹配对应平台类型的用户数据，求和得到总消费金额，统计去重用户数。
   • 用COALESCE将无数据的统计项补为0，匹配期望结果格式。

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