使用ClickHouse实现，累计用户计算模型-阿里云开发者社区

使用ClickHouse实现，累计用户计算模型

2017-04-06 3768

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 问题描述：根据用户标识和历史库的匹配结果，识别是否是新增用户，单位：天要求：历史库每天累加更新，要考录用户历史数据库的幂等性及回补数据策略输出：用户pushidpushid对应的uid（如果当天没有没有登录就没有对应的pushid则从历史库中匹配）pushid当天和uid是否有对应关系用户新增时间用户历史所有投资次数当天用户投资次数用户每次投资时间（rechargeTime）说明：用户标识有两个 pushid、uid，pushid表示用户的注册id，登录的时候才会存在，uid是用户访问的cookie（会频繁变化）。

问题描述：根据用户标识和历史库的匹配结果，识别是否是新增用户，单位：天

要求：历史库每天累加更新，要考录用户历史数据库的幂等性及回补数据策略

输出：

用户pushid
pushid对应的uid（如果当天没有没有登录就没有对应的pushid则从历史库中匹配）
pushid当天和uid是否有对应关系
用户新增时间
用户历史所有投资次数
当天用户投资次数
用户每次投资时间（rechargeTime）

说明：

用户标识有两个 pushid、uid，pushid表示用户的注册id，登录的时候才会存在，uid是用户访问的cookie（会频繁变化）。

因此在业务中要关联两者之间的关系。

创建历史库：

CREATE TABLE IF NOT EXISTS `db_name`.`table_name` (
    partition Date DEFAULT '1970-01-01',
    pushid String DEFAULT '',
    opTime DateTime DEFAULT 0,
    rechargeTime DateTime DEFAULT 0, # rechargeTime如果不是默认值则表示用户发生投资时间
    appkey String DEFAULT '',
    uid String DEFAULT '',
    ver UInt64 DEFAULT 0
)
ENGINE = ReplacingMergeTree(partition, (pushid, rechargeTime), 8192, ver)

利用ReplacingMergeTree实现数据幂等性，当重复入库数据时会去除重复项，保证数据执行多次时数据不重复。

ver表示版本号，当数据重复时，会以最大的版本号为准，版本号可以是一个递增的数字，业务中数据的版本号是插入的时的时间戳。

*其中：(pushid, rechargeTime)中的rechargeTime表示用户复投时间，如果业务中没有对用户发生某一个行为特殊要求则可以删除。

历史库更新代码(每天更新)：

INSERT INTO db_name.table_name SELECT 
    partition, 
    pushid, 
    opTime, 
    rechargeTime, 
    appkey, 
    uid, 
    ver
FROM 
(
    SELECT 
        partition, 
        jhd_pushid AS pushid, 
        jhd_opTime AS opTime, 
        jhd_opTime AS rechargeTime, 
        jhd_datatype AS appkey, 
        jhd_userkey AS uid, 
        CAST(835664 AS UInt64) AS ver
    FROM ncf_h5.userevent 
    WHERE (partition = toDate('2017-03-28')) AND (jhd_pushid != '') AND (jhd_opType = 'page') AND (visitParamExtractString(jhd_map, 'uri') LIKE '%/pay_result%')
    UNION ALL 
    SELECT 
        partition, 
        jhd_pushid AS pushid, 
        min(jhd_opTime) AS opTime, 
        toDateTime('1970-01-01 00:00:00') AS rechargeTime, 
        jhd_datatype AS appkey, 
        jhd_userkey AS uid, 
        CAST(835664 AS UInt64) AS ver
    FROM ncf_h5.userevent 
    WHERE (partition = toDate('2017-03-28')) AND (jhd_pushid != '')
    GROUP BY 
        jhd_datatype, 
        partition, 
        pushid, 
        jhd_userkey
    UNION ALL 
    SELECT 
        partition, 
        jhd_pushid AS pushid, 
        jhd_opTime AS opTime, 
        jhd_opTime AS rechargeTime, 
        jhd_datatype AS appkey, 
        jhd_userkey AS uid, 
        CAST(835664 AS UInt64) AS ver
    FROM ncf_ws.userevent 
    WHERE (partition = toDate('2017-03-28')) AND (jhd_pushid != '') AND (jhd_opType = 'page') AND (visitParamExtractString(jhd_map, 'uri') LIKE '%/success%')
    UNION ALL 
    SELECT 
        partition, 
        jhd_pushid AS pushid, 
        min(jhd_opTime) AS opTime, 
        toDateTime('1970-01-01 00:00:00') AS rechargeTime, 
        jhd_datatype AS appkey, 
        jhd_userkey AS uid, 
        CAST(835664 AS UInt64) AS ver
    FROM ncf_ws.userevent 
    WHERE (partition = toDate('2017-03-28')) AND (jhd_pushid != '')
    GROUP BY 
        jhd_datatype, 
        partition, 
        pushid, 
        jhd_userkey
)

数据导出代码：

数据格式：

pushid、是否当天登录、uid、新增时间、用户历史所有投资次数、当天用户投资次数、用户每次投资时间

SELECT 
    pushid, 
    1 AS isfind, 
    uids, 
    earliest, 
    recharge_n, 
    recharge_today, 
    recharge_arr
FROM 
(
    SELECT 
        pushid, 
        CAST(earliest AS String) AS earliest, 
        recharge_n, 
        recharge_today, 
        arrayMap(lambda(tuple(x), CAST(x AS String)), arrayFilter(lambda(tuple(x), x != '1970-01-01 00:00:00'), recharge_arr)) AS recharge_arr, 
        arrayFilter(lambda(tuple(x), x != ''), uids) AS uids
    FROM 
    (
        SELECT pushid, groupUniqArray(uid) AS uids
        FROM ncf_common.user_pushid 
        WHERE partition = toDate('2017-04-04')
        GROUP BY pushid
    ) 
    ANY LEFT JOIN 
    (
        SELECT 
            pushid, 
            min(opTime) AS earliest, 
            sumIf(1, rechargeTime != '1970-01-01 00:00:00') AS recharge_n, 
            sumIf(1, toDate(rechargeTime) = toDate('2017-04-04')) AS recharge_today, 
            groupArray(rechargeTime) AS recharge_arr
        FROM ncf_common.user_pushid 
        WHERE (partition <= toDate('2017-04-04')) AND (partition >= (toDate('2017-04-04') - 365))
        GROUP BY pushid
    ) USING (pushid)
) 
ARRAY JOIN uids
UNION ALL 
SELECT 
    pushid, 
    0 AS isfind, 
    uids, 
    CAST(earliest AS String) AS earliest, 
    recharge_n, 
    recharge_today, 
    arrayMap(lambda(tuple(x), CAST(x AS String)), arrayFilter(lambda(tuple(x), x != '1970-01-01 00:00:00'), recharge_arr)) AS recharge_arr
FROM 
(
    SELECT 
        pushid, 
        groupUniqArray(uid) AS uids, 
        min(opTime) AS earliest, 
        sumIf(1, rechargeTime != '1970-01-01 00:00:00') AS recharge_n, 
        sumIf(1, toDate(rechargeTime) = toDate('2017-04-04')) AS recharge_today, 
        arrayFilter(lambda(tuple(x), x != '1970-01-01 00:00:00'), groupArray(rechargeTime)) AS recharge_arr
    FROM 
    (
        SELECT 
            pushid, 
            uid, 
            opTime, 
            rechargeTime
        FROM 
        (
            SELECT 
                jhd_userkey AS uid, 
                groupUniqArray(jhd_pushid) AS pushids, 
                'ncf_ws' AS appkey
            FROM ncf_ws.userevent 
            WHERE partition = toDate('2017-04-04')
            GROUP BY jhd_userkey
            HAVING (length(pushids) = 1) AND has(pushids, '')
            UNION ALL 
            SELECT 
                jhd_userkey AS uid, 
                groupUniqArray(jhd_pushid) AS pushids, 
                'ncf_h5' AS appkey
            FROM ncf_h5.userevent 
            WHERE partition = toDate('2017-04-04')
            GROUP BY jhd_userkey
            HAVING (length(pushids) = 1) AND has(pushids, '')
        ) 
        ALL INNER JOIN 
        (
            SELECT 
                pushid, 
                uid, 
                opTime, 
                rechargeTime, 
                appkey
            FROM ncf_common.user_pushid 
            WHERE partition < toDate('2017-04-04')
        ) USING (uid)
    ) 
    GROUP BY pushid
) 
ARRAY JOIN uids

使用ClickHouse实现，累计用户计算模型

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像