淘宝用户购物行为数据集

create table user_behavior_buffer(
   user_id bigint,
   item_id bigint,
   cate_id bigint,
   action_type string,
   action_time bigint
) using csv;

select *
from user_behavior_buffer
where rand() < 0.01
limit 10;

user_id   item_id   cate_id   action_type     action_time
1         3239041   2355072   pv              1511939239
100       2434962   3897386   pv              1511690835
1000001   3132168   154040    pv              1511881851
1000004   40945     4756105   pv              1511703467
1000011   404297    2578647   fav             1511603262
1000011   404297    2578647   buy             1511603941
100002    4928571   4806751   pv              1512046188
1000027   2070173   4217906   cart            1511704378
1000027   646869    3692903   cart            1512144238
1000028   2808995   246844    pv              1512315622

select
    count(*) as row_cnt,
    count(distinct user_id) as user_cnt,
    count(distinct item_id) as item_cnt,
    count(distinct cate_id) as cate_cnt,
    collect_set(action_type) as action_set,
    min(from_unixtime(action_time)) as min_action_time,
    max(from_unixtime(action_time)) as max_action_time
from user_behavior_buffer;

row_cnt    user_cnt  item_cnt  cate_cnt  action_set                 min_action_time      max_action_time
100150807  987994    4162024   9439      ["cart","fav","pv","buy"]  1902-05-08 06:32:46  2037-04-09 13:22:35

select *
from user_behavior_buffer
where to_date(from_unixtime(action_time)) = '1929-09-23'
limit 10;

user_id   item_id   cate_id   action_type  action_time
353279    1397732   1299190   pv           -1270954504
353279    986346    1879194   pv           -1270954491
753213    809825    2895013   pv           -1270897486

select 
    if(dt between '2017-11-25' and '2017-12-03', dt, 'other') as dt,
    count(*) as cnt
from(
    select *, to_date(from_unixtime(action_time)) as dt
    from user_behavior_buffer
) as t 
group by if(dt between '2017-11-25' and '2017-12-03', dt, 'other')
order by dt;

create table user_behavior(
    user_id      bigint     comment '用户 ID',
    item_id      bigint     comment '商品 ID',
    cate_id      bigint     comment '类目 ID',
    action_type  string     comment '行为类型',
    action_time  string  comment '行为事件'
) comment '用户行为日志表'
partitioned by(
    dt string comment '日期分区'
) stored as orc tblproperties('orc.compress' = 'SNAPPY');

insert overwrite table user_behavior partition(dt)
select
    user_id,
    item_id,
    cate_id,
    action_type,
    from_unixtime(action_time) as action_time,
    to_date(from_unixtime(action_time)) as dt
from user_behavior_buffer
where to_date(from_unixtime(action_time)) between '2017-11-25' and '2017-12-03';

drop table user_behavior_buffer;

select
    count(distinct user_id) as uv,
    max(user_id) as max_id,
    min(user_id) as min_id
from user_behavior

uv      max_id    min_id
987991  1018011   1

select
    dt,
    count(distinct user_id) as uv
from user_behavior
group by dt
order by dt;

select
    case    
        when cnt < 100 then floor(cnt / 10) * 10
        when cnt < 300 then floor(cnt / 100) * 100
        when cnt >= 300 then 300
    end as cnt,
    count(*) as uv
from(
    select
        user_id,
        count(*) as cnt
    from user_behavior
    group by user_id
) as t
group by case    
    when cnt < 100 then floor(cnt / 10) * 10
    when cnt < 300 then floor(cnt / 100) * 100
    when cnt >= 300 then 300
end

select
    count(distinct item_id) as item_cnt,
    max(item_id) as max_id,
    min(item_id) as min_id
from user_behavior

item_cnt    max_id    min_id
4161138     5163070   1

select
    case
        when uv < 10 then uv
        when uv < 100 then floor(uv / 10) * 10
        when uv < 300 then floor(uv / 100) * 100
        when uv >= 300 then 300
    end as uv,
    count(*) as item_cnt
from(
    select
        item_id,
        count(distinct user_id) as uv
    from user_behavior
    group by item_id
) as t
group by case
    when uv < 10 then uv
    when uv < 100 then floor(uv / 10) * 10
    when uv < 300 then floor(uv / 100) * 100
    when uv >= 300 then 300
end
order by uv

select
    count(distinct cate_id) as cate_cnt,
    max(cate_id) as max_id,
    min(cate_id) as min_id
from user_behavior

cate_cnt   max_id   min_id
9437       5162429  80

select
    case
        when item_cnt < 10 then item_cnt
        when item_cnt < 100 then floor(item_cnt / 10) * 10
        when item_cnt < 300 then floor(item_cnt / 100) * 100
        when item_cnt >= 300 then 300
    end as item_cnt,
    count(*) as cate_cnt
from(
    select
        cate_id,
        count(distinct item_id) as item_cnt
    from user_behavior
    group by cate_id
) as t
group by case
    when item_cnt < 10 then item_cnt
    when item_cnt < 100 then floor(item_cnt / 10) * 10
    when item_cnt < 300 then floor(item_cnt / 100) * 100
    when item_cnt >= 300 then 300
end
order by item_cnt

select
    cate_cnt,
    count(*) as item_cnt
from(
    select
        item_id,
        count(distinct cate_id) as cate_cnt
    from user_behavior
    group by item_id
) as t
group by cate_cnt
order by cate_cnt;

cate_cnt   item_cnt
1          4159733
2          1395
3          8
4          2

select
    action_type,
    count(*) as cnt
from user_behavior
group by action_type
order by cnt desc

select
    count(*) as buy_cnt,
    sum(is_click) as click_cnt
from(
    select
        dt,
        user_id,
        item_id,
        max(if(action_type = 'pv', 1, 0)) as is_click,
        max(if(action_type = 'buy', 1, 0)) as is_buy
    from user_behavior
    group by dt, user_id, item_id
) as t
where is_buy = 1;

buy_cnt   click_cnt
1966120   1053586

create table user_behavior_sample(
    user_id bigint comment '用户 ID',
    item_id bigint comment '商品 ID',
    label tinyint comment '是否购买'
) comment '样本表'
partitioned by(
    stage string comment '数据用途'
) stored as orc tblproperties('orc.compress' = 'SNAPPY');

insert overwrite table user_behavior_sample partition(stage)
select
    user_id,
    item_id,
    max(if(action_type = 'buy', 1, 0)) as label,
    case
        when dt = '2017-12-03' then 'test'
        when abs(hash(user_id) % 100) < 80 then 'train'
        else 'valid'
    end as stage
from user_behavior
where dt in('2017-12-02', '2017-12-03')
group by dt, user_id, item_id

select
    stage,
    count(*) as sample_cnt,
    count(distinct user_id) as uv,
    sum(label) as positive_sample_cnt,
    sum(1 - label) as negative_sample_cnt,
    avg(label) as cvr
from user_behavior_sample
group by stage;

stage   sample_cnt   uv       positive_sample_cnt    negative_sample_cnt   cvr
train   9280374      775983   201275                 9079099               0.021688242305751903
valid   2324445      194418   50543                  2273902               0.02174411526192274
test    11509180     966977   251597                 11257583              0.021860549578684146

insert overwrite table user_behavior_trace partition(stage)
-- 训练集 & 验证集足迹
select
    user_id,
    array_sort(
        collect_list(named_struct(
            'item_id', item_id,
            'cate_id', cate_id,
            'type', action_type,
            'time', action_time
        )),
        (left, right) -> case when left.time < right.time then -1 when left.time > right.time then 1 else 0 end
    ) as trace,
    if(abs(hash(user_id) % 100) < 80, 'train', 'valid') as stage
from user_behavior
where dt between '2017-11-25' and '2017-12-01'
group by user_id

union all
-- 测试集足迹
select
    user_id,
    array_sort(
        collect_list(named_struct(
            'item_id', item_id,
            'cate_id', cate_id,
            'type', action_type,
            'time', action_time
        )),
        (left, right) -> case when left.time < right.time then -1 when left.time > right.time then 1 else 0 end
    ) as trace,
    'test' as stage
from user_behavior
where dt between '2017-11-26' and '2017-12-02'
group by user_id;

select trace
from user_behavior_trace
where size(trace) = 2
limit 10;

user_id trace   stage
[{"item_id":3930302,"cate_id":2892802,"type":"pv","time":"2017-11-30 22:02:21"},{"item_id":4010057,"cate_id":1216617,"type":"pv","time":"2017-12-02 14:44:56"}]
[{"item_id":3802778,"cate_id":4082778,"type":"pv","time":"2017-11-27 18:46:39"},{"item_id":4043208,"cate_id":327651,"type":"pv","time":"2017-12-02 13:01:32"}]
[{"item_id":459809,"cate_id":4147847,"type":"pv","time":"2017-11-29 11:34:55"},{"item_id":3288029,"cate_id":2887965,"type":"pv","time":"2017-12-02 20:10:40"}]
[{"item_id":3880677,"cate_id":1464116,"type":"pv","time":"2017-12-02 20:09:59"},{"item_id":4206150,"cate_id":1464116,"type":"pv","time":"2017-12-02 20:10:11"}]
[{"item_id":3467328,"cate_id":672001,"type":"pv","time":"2017-11-30 10:13:09"},{"item_id":655657,"cate_id":2188684,"type":"pv","time":"2017-12-02 22:55:11"}]
[{"item_id":3889416,"cate_id":3566034,"type":"pv","time":"2017-11-26 19:35:10"},{"item_id":2097882,"cate_id":4462359,"type":"pv","time":"2017-12-02 22:08:00"}]
[{"item_id":1095210,"cate_id":1994940,"type":"pv","time":"2017-11-27 14:36:59"},{"item_id":2639165,"cate_id":1994940,"type":"pv","time":"2017-12-02 09:32:04"}]
[{"item_id":3465547,"cate_id":55312,"type":"pv","time":"2017-11-29 08:32:09"},{"item_id":848183,"cate_id":4589142,"type":"pv","time":"2017-12-02 22:08:54"}]
[{"item_id":2063180,"cate_id":1609851,"type":"cart","time":"2017-11-28 16:59:28"},{"item_id":2190431,"cate_id":1609851,"type":"pv","time":"2017-12-02 09:42:37"}]
[{"item_id":4699951,"cate_id":982926,"type":"pv","time":"2017-11-26 07:50:39"},{"item_id":918629,"cate_id":3860845,"type":"cart","time":"2017-12-02 11:21:34"}]