用户画像(一):clickhouse AggregateFunction(groupBitmap, UInt64)

1.groupBitmap
用于存储UInt64数值,且自动去重,这对于我们存储uid非常的适合。

2.使用
以下均是笔者模拟的测试数据:
通过配置宽表,我们可以得出一些基本的画像规则,用来制定一些常用的标签属性,如性别,地理位置,星座等。

--字符型标签
select uid,
       case
           when birthday in ('1970-01-01', '1990-03-21', '2000-01-01') then '未知'
           when birthday >= toDate(concat(toString(toYear(birthday)) as yy, '-01-20')) and
                birthday <= toDate(concat(yy, '-02-18')) then '水瓶座'
           when birthday >= toDate(concat(yy, '-02-19')) and birthday <= toDate(concat(yy, '-03-20')) then '双鱼座'
           when birthday >= toDate(concat(yy, '-03-21')) and birthday <= toDate(concat(yy, '-04-19')) then '白羊座'
           when birthday >= toDate(concat(yy, '-04-20')) and birthday <= toDate(concat(yy, '-05-20')) then '金牛座'
           when birthday >= toDate(concat(yy, '-05-21')) and birthday <= toDate(concat(yy, '-06-21')) then '双子座'
           when birthday >= toDate(concat(yy, '-06-22')) and birthday <= toDate(concat(yy, '-07-22')) then '巨蟹座'
           when birthday >= toDate(concat(yy, '-07-23')) and birthday <= toDate(concat(yy, '-08-22')) then '狮子座'
           when birthday >= toDate(concat(yy, '-08-23')) and birthday <= toDate(concat(yy, '-09-22')) then '处女座'
           when birthday >= toDate(concat(yy, '-09-23')) and birthday <= toDate(concat(yy, '-10-23')) then '天秤座'
           when birthday >= toDate(concat(yy, '-10-24')) and birthday <= toDate(concat(yy, '-11-22')) then '天蝎座'
           when birthday >= toDate(concat(yy, '-11-23')) and birthday <= toDate(concat(yy, '-12-21')) then '射手座'
           when (birthday >= toDate(concat(yy, '-12-22')) and birthday <= toDate(concat(yy, '-12-31'))) or
                (birthday >= toDate(concat(yy, '-01-01')) and birthday <= toDate(concat(yy, '-01-19'))) then '摩羯座'
           else '未知' end as constellation
from test.user_wide_all;

新建画像的存储表

create table user_label_string on_cluster ck_cluster
(
    label_name  String,
    label_value String,
    bitmap_uid  AggregateFunction(groupBitmap, UInt64)
)
    engine = ReplicatedAggregatingMergeTree('/clickhouse/default/tables/{shard}/user_label_string', '{replica}')
        PARTITION BY label_name
        ORDER BY (label_name, label_value)
        SETTINGS index_granularity = 128;
create table user_label_string_all
(
    label_name  String,
    label_value String,
    bitmap_uid  AggregateFunction(groupBitmap, UInt64)
)
    engine = Distributed('ck_cluster', 'default', 'user_label_string', rand());

写入数据:

insert into user_label_string(bitmap_uid, label_name ,label_value)
SELECT groupBitmapState(toUInt64(uid)) as bm,
       'constellation',
       constellation
from (
select uid,
       case
           when birthday in ('1970-01-01', '1990-03-21', '2000-01-01') then '未知'
           when birthday >= toDate(concat(toString(toYear(birthday)) as yy, '-01-20')) and
                birthday <= toDate(concat(yy, '-02-18')) then '水瓶座'
           when birthday >= toDate(concat(yy, '-02-19')) and birthday <= toDate(concat(yy, '-03-20')) then '双鱼座'
           when birthday >= toDate(concat(yy, '-03-21')) and birthday <= toDate(concat(yy, '-04-19')) then '白羊座'
           when birthday >= toDate(concat(yy, '-04-20')) and birthday <= toDate(concat(yy, '-05-20')) then '金牛座'
           when birthday >= toDate(concat(yy, '-05-21')) and birthday <= toDate(concat(yy, '-06-21')) then '双子座'
           when birthday >= toDate(concat(yy, '-06-22')) and birthday <= toDate(concat(yy, '-07-22')) then '巨蟹座'
           when birthday >= toDate(concat(yy, '-07-23')) and birthday <= toDate(concat(yy, '-08-22')) then '狮子座'
           when birthday >= toDate(concat(yy, '-08-23')) and birthday <= toDate(concat(yy, '-09-22')) then '处女座'
           when birthday >= toDate(concat(yy, '-09-23')) and birthday <= toDate(concat(yy, '-10-23')) then '天秤座'
           when birthday >= toDate(concat(yy, '-10-24')) and birthday <= toDate(concat(yy, '-11-22')) then '天蝎座'
           when birthday >= toDate(concat(yy, '-11-23')) and birthday <= toDate(concat(yy, '-12-21')) then '射手座'
           when (birthday >= toDate(concat(yy, '-12-22')) and birthday <= toDate(concat(yy, '-12-31'))) or
                (birthday >= toDate(concat(yy, '-01-01')) and birthday <= toDate(concat(yy, '-01-19'))) then '摩羯座'
           else '未知' end as constellation
from test.user_wide_all
     ) group by constellation;

3.查询

SELECT
    label_name,
    label_value,
    bitmapCardinality(bitmap_uid) AS bm
FROM user_label_string_all
WHERE label_name = 'constellation'

Query id: dfc31066-c7dd-419a-ba5d-110339c2785d

┌─label_name────┬─label_value─┬───────bm─┐
│ constellation │ 双子座      │  79668 │
│ constellation │ 双鱼座      │  72569 │
│ constellation │ 处女座      │  87766 │
│ constellation │ 天秤座      │  82114 │
│ constellation │ 天蝎座      │  93031 │
│ constellation │ 射手座      │  72913 │
│ constellation │ 巨蟹座      │  83805 │
│ constellation │ 摩羯座      │ 146983 │
│ constellation │ 未知         │ 340169 │
│ constellation │ 水瓶座      │  76084 │
│ constellation │ 狮子座      │  73347 │
│ constellation │ 白羊座      │  62892 │
│ constellation │ 金牛座      │  69014 │
└───────────────┴─────────────┴──────────┘

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

高并发

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值