如何基于ClickHouse按OS计算两类不同事件的用户留存？

需求背景 #

需要统计两类事件的用户留存，按OS分组计算：

• login事件：带有os属性，取值为android或ios
• payment事件：无os属性（字段值为''）

数据示例 #

uid      date       event   os
('user1', 2023-12-15, 'login', 'ios')
('user1', 2023-12-16, 'login', 'android')
('user1', 2023-12-16, 'payment', '')

现有错误SQL及问题 #

错误SQL示例：

select client, uid, retention(date='xxx' and event = 'login', ......) 
from data 
group by os, uid;

问题：直接按os分组会把空字符串单独划分为一组，导致payment事件无法归属到对应用户的ios/android组，留存计算结果不符合预期。

期望结果 #

ios     | user1 | [1, 0, 1]   // ios组用户次日留存情况
android | user1 | [1, 1, 0]   // android组用户首日留存情况 

核心需求：将payment事件的空os值分别归属到对应用户的ios/android组（即''与ios归为一组、''与android归为一组），再调用retention()计算留存。

解决方案 #

思路 #

先为每个用户的所有事件统一标记对应的OS分组：提取用户所有login事件的os值，将该用户的所有事件（包括payment）关联到这些OS分组，再按分组后的OS和用户ID聚合计算留存。

具体实现SQL #

支持用户多OS场景（如用户切换设备登录）

WITH user_os_mapping AS (
    -- 提取每个用户所有登录事件的OS，生成用户-OS列表
    SELECT 
        uid,
        groupArray(DISTINCT os) AS user_os_list
    FROM data
    WHERE event = 'login'
    GROUP BY uid
)
SELECT
    os_group AS client,
    uid,
    -- 按需调整retention的参数，示例为：首日登录、次日活跃、第三日支付的留存逻辑
    retention(
        date = '2023-12-15' AND event = 'login',  -- 基准事件：首日登录
        date = '2023-12-16' AND (event = 'login' OR event = 'payment'),  -- 次日活跃（登录/支付）
        date = '2023-12-17' AND event = 'payment'  -- 第三日支付
    ) AS retention_result
FROM (
    -- 将所有事件与用户的OS分组绑定，空os的payment事件会被分配到对应OS组
    SELECT 
        d.uid,
        d.date,
        d.event,
        os_map.os AS os_group
    FROM data d
    LEFT JOIN user_os_mapping os_map ON d.uid = os_map.uid
    -- 展开用户的OS列表，一个用户多个OS时生成对应多条记录
    ARRAY JOIN os_map.user_os_list AS os
)
GROUP BY os_group, uid;

限定用户单一OS场景（如取首次登录的OS）

如果用户仅需按首次登录的OS分组，可简化为：

WITH user_first_os AS (
    -- 获取每个用户首次登录的OS
    SELECT 
        uid,
        first_value(os) OVER (PARTITION BY uid ORDER BY date) AS os_group
    FROM data
    WHERE event = 'login'
    GROUP BY uid, os, date
    LIMIT 1 BY uid
)
SELECT
    os_group AS client,
    uid,
    retention(
        date = '2023-12-15' AND event = 'login',
        date = '2023-12-16' AND (event = 'login' OR event = 'payment'),
        date = '2023-12-17' AND event = 'payment'
    ) AS retention_result
FROM data d
LEFT JOIN user_first_os ufo ON d.uid = ufo.uid
GROUP BY os_group, uid;

说明 #

• 第一个SQL适用于用户可能切换OS登录的场景，会将用户的所有事件分配到其使用过的每个OS组中计算留存。
• 第二个SQL仅按用户首次登录的OS分组，确保一个用户只归属到一个OS组。
• 可根据实际留存周期需求，调整retention()函数内的条件参数。

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