devbox
木道寻08
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

我在用户画像的实战_用户画像实战例子

作者：木道寻08 | 2024-07-28 14:36:59

赞

踩

用户画像实战例子

目录

用户画像的作用

  • 用户画像(User Profile)就是企业通过收集与分析消费者社会属性、生活习 惯、消费行为等主要信息的数据之后，完美地抽象出一个用户的商业全貌，是企业应用大数据技术的基本方式
  • 用户画像为企业提供了足够的信息基础，能够帮助企业快速找到精准用户群 体以及用户需求等更为广泛的反馈信息
  • 作为大数据的根基，它完美地抽象出一个用户的信息全貌，为进一步精准、 快速地分析用户行为习惯、消费习惯等重要信息，提供了足够的数据基础

作用示例

很多app都会给我们一个年终报告，其实就是画像的一种体现
在这里插入图片描述

实战

下面是我的画像的表结构，当然了其中涉及到很多表（订单，用户信息，各种埋点，打赏，upv表等等）
涉及到了很多join，就不细说了（无非就是大家平常写的sql）

CREATE TABLE IF NOT EXISTS user_features
(
    user_id                        STRING   COMMENT '用户user_id',
    uid                            BIGINT   COMMENT '用户uid',
    token                          STRING   COMMENT '用户token',
    nickname                       STRING   COMMENT '用户昵称',
    mobile                         STRING   COMMENT '用户手机号',
    gender                         BIGINT   COMMENT '性别 0=女 1=男 2=未知',
    age                            BIGINT   COMMENT '年龄',
    constellation                  STRING   COMMENT '星座',
    create_time                    DATETIME COMMENT '注册日期',
    register_days                  BIGINT   COMMENT '注册天数',
    is_item                         BIGINT   COMMENT '是否是商品 0=不是 1=是',
    item_income_balance             DECIMAL  COMMENT '商品币收入余额',
    province                       STRING   COMMENT 'ip所在省份',
    city                           STRING   COMMENT 'ip所在城市',
    has_coupon_days                BIGINT   COMMENT '拥有优惠券的天数 -1=没有 0=昨天就是最后一天 1=有',
    charm_amount                   DECIMAL  COMMENT '魅力值余额(钻石收入)',
    diamond_amount                 DECIMAL  COMMENT '钻石余额',
    amount                      DECIMAL  COMMENT '币余额',
    star_diamond_amount            DECIMAL  COMMENT '砖石余额',
    star_amount                    DECIMAL  COMMENT '星动值(砖石收入)',
    login_days                     BIGINT   COMMENT '登录天数',
    last_login_date                DATETIME COMMENT '最后一次登录日期',
    keep_days                      BIGINT   COMMENT '留存天数(最后登陆时间-注册时间)',
    leave_days                     BIGINT   COMMENT '未登录天数(当下时间-最后登陆时间)',
    fans_counts                    BIGINT   COMMENT '粉丝总数',
    followers_counts               BIGINT   COMMENT '关注总数',
    publish_dongtai_counts         BIGINT   COMMENT '发布动态总数',
    loss_new_fans                  BIGINT   COMMENT '未登录期间，新增粉丝数',
    loss_chat_users                BIGINT   COMMENT '未登录期间，聊天用户数',
    loss_chat_same_users           BIGINT   COMMENT '未登录期间，同性聊天用数',
    loss_chat_diff_users           BIGINT   COMMENT '未登录期间，异性聊天用数',
    loss_receive_message_counts    BIGINT   COMMENT '未登录期间，接收聊天消息数',
    loss_recommend_dongtai_counts  BIGINT   COMMENT '曝光动态次数',
    loss_recommend_dongtai_users   BIGINT   COMMENT '曝光动态用户数',
    loss_click_dongtai_counts      BIGINT   COMMENT '点击动态数',
    loss_click_dongtai_users       BIGINT   COMMENT '点击动态用户数',
    loss_reply_dongtai_counts      BIGINT   COMMENT '评论动态数',
    loss_reply_dongtai_users       BIGINT   COMMENT '评论动态用户数',
    loss_zan_dongtai_counts        BIGINT   COMMENT '点赞动态数',
    loss_zan_dongtai_users         BIGINT   COMMENT '点赞动态用户数',
    loss_play_dongtai_users        BIGINT   COMMENT '播放动态视频用户数',
    loss_play_dongtai_counts       BIGINT   COMMENT '播放动态视频次数',
    loss_play_dongtai_times        BIGINT   COMMENT '播放动态视频时长，单位:秒',
    loss_view_my_profile_users     BIGINT   COMMENT '浏览我的主页用户数',
    login_num_3                    BIGINT   COMMENT '最近N天登录次数',
    login_num_5                    BIGINT  ,
    login_num_7                    BIGINT  ,
    login_num_14                   BIGINT  ,
    login_num_30                   BIGINT  ,
    login_num_60                   BIGINT  ,
    login_num_90                   BIGINT  ,
    login_num_120                  BIGINT  ,
    follow_users_3                 BIGINT   COMMENT '最近N天关注用户数',
    follow_users_7                 BIGINT  ,
    follow_users_14                BIGINT  ,
    follow_users_21                BIGINT  ,
    follow_users_30                BIGINT  ,
    follow_users_60                BIGINT  ,
    follow_users_90                BIGINT  ,
    follow_users_120               BIGINT  ,
    chat_users_3                   BIGINT   COMMENT '最近N天聊天用户数',
    chat_users_7                   BIGINT  ,
    chat_users_14                  BIGINT  ,
    chat_users_21                  BIGINT  ,
    chat_users_30                  BIGINT  ,
    chat_users_60                  BIGINT  ,
    chat_users_90                  BIGINT  ,
    chat_users_120                 BIGINT  ,
    chat_diff_users_3              BIGINT   COMMENT '最近N天异性聊天用户数',
    chat_diff_users_7              BIGINT  ,
    chat_diff_users_14             BIGINT  ,
    chat_diff_users_21             BIGINT  ,
    chat_diff_users_30             BIGINT  ,
    chat_diff_users_60             BIGINT  ,
    chat_diff_users_90             BIGINT  ,
    chat_diff_users_120            BIGINT  ,
    send_message_counts_3          BIGINT   COMMENT '最近N天发送消息数',
    send_message_counts_7          BIGINT  ,
    send_message_counts_14         BIGINT  ,
    send_message_counts_21         BIGINT  ,
    send_message_counts_30         BIGINT  ,
    send_message_counts_60         BIGINT  ,
    send_message_counts_90         BIGINT  ,
    send_message_counts_120        BIGINT  ,
    receive_message_counts_3       BIGINT   COMMENT '最近N天接收消息数',
    receive_message_counts_7       BIGINT  ,
    receive_message_counts_14      BIGINT  ,
    receive_message_counts_21      BIGINT  ,
    receive_message_counts_30      BIGINT  ,
    receive_message_counts_60      BIGINT  ,
    receive_message_counts_90      BIGINT  ,
    receive_message_counts_120     BIGINT  ,
    publish_dongtai_counts_3       BIGINT   COMMENT '最近N天发布动态数',
    publish_dongtai_counts_7       BIGINT  ,
    publish_dongtai_counts_14      BIGINT  ,
    publish_dongtai_counts_21      BIGINT  ,
    publish_dongtai_counts_30      BIGINT  ,
    publish_dongtai_counts_60      BIGINT  ,
    publish_dongtai_counts_90      BIGINT  ,
    publish_dongtai_counts_120     BIGINT  ,
    recommend_dongtai_counts_3     BIGINT   COMMENT '曝光动态次数',
    recommend_dongtai_counts_7     BIGINT  ,
    recommend_dongtai_counts_14    BIGINT  ,
    recommend_dongtai_counts_21    BIGINT  ,
    recommend_dongtai_counts_30    BIGINT  ,
    recommend_dongtai_counts_60    BIGINT  ,
    recommend_dongtai_counts_90    BIGINT  ,
    recommend_dongtai_counts_120   BIGINT  ,
    recommend_dongtai_users_3      BIGINT   COMMENT '曝光动态用户数',
    recommend_dongtai_users_7      BIGINT  ,
    recommend_dongtai_users_14     BIGINT  ,
    recommend_dongtai_users_21     BIGINT  ,
    recommend_dongtai_users_30     BIGINT  ,
    recommend_dongtai_users_60     BIGINT  ,
    recommend_dongtai_users_90     BIGINT  ,
    recommend_dongtai_users_120    BIGINT  ,
    click_dongtai_counts_3         BIGINT   COMMENT '点击动态数',
    click_dongtai_counts_7         BIGINT  ,
    click_dongtai_counts_14        BIGINT  ,
    click_dongtai_counts_21        BIGINT  ,
    click_dongtai_counts_30        BIGINT  ,
    click_dongtai_counts_60        BIGINT  ,
    click_dongtai_counts_90        BIGINT  ,
    click_dongtai_counts_120       BIGINT  ,
    click_dongtai_users_3          BIGINT   COMMENT '点击动态用户数',
    click_dongtai_users_7          BIGINT  ,
    click_dongtai_users_14         BIGINT  ,
    click_dongtai_users_21         BIGINT  ,
    click_dongtai_users_30         BIGINT  ,
    click_dongtai_users_60         BIGINT  ,
    click_dongtai_users_90         BIGINT  ,
    click_dongtai_users_120        BIGINT  ,
    reply_dongtai_counts_3         BIGINT   COMMENT '评论动态数',
    reply_dongtai_counts_7         BIGINT  ,
    reply_dongtai_counts_14        BIGINT  ,
    reply_dongtai_counts_21        BIGINT  ,
    reply_dongtai_counts_30        BIGINT  ,
    reply_dongtai_counts_60        BIGINT  ,
    reply_dongtai_counts_90        BIGINT  ,
    reply_dongtai_counts_120       BIGINT  ,
    reply_dongtai_users_3          BIGINT   COMMENT '评论动态用户数',
    reply_dongtai_users_7          BIGINT  ,
    reply_dongtai_users_14         BIGINT  ,
    reply_dongtai_users_21         BIGINT  ,
    reply_dongtai_users_30         BIGINT  ,
    reply_dongtai_users_60         BIGINT  ,
    reply_dongtai_users_90         BIGINT  ,
    reply_dongtai_users_120        BIGINT  ,
    zan_dongtai_counts_3           BIGINT   COMMENT '点赞动态数',
    zan_dongtai_counts_7           BIGINT  ,
    zan_dongtai_counts_14          BIGINT  ,
    zan_dongtai_counts_21          BIGINT  ,
    zan_dongtai_counts_30          BIGINT  ,
    zan_dongtai_counts_60          BIGINT  ,
    zan_dongtai_counts_90          BIGINT  ,
    zan_dongtai_counts_120         BIGINT  ,
    zan_dongtai_users_3            BIGINT   COMMENT '点赞动态用户数',
    zan_dongtai_users_7            BIGINT  ,
    zan_dongtai_users_14           BIGINT  ,
    zan_dongtai_users_21           BIGINT  ,
    zan_dongtai_users_30           BIGINT  ,
    zan_dongtai_users_60           BIGINT  ,
    zan_dongtai_users_90           BIGINT  ,
    zan_dongtai_users_120          BIGINT  ,
    play_dongtai_counts_3          BIGINT   COMMENT '播放动态视频次数',
    play_dongtai_counts_7          BIGINT  ,
    play_dongtai_counts_14         BIGINT  ,
    play_dongtai_counts_21         BIGINT  ,
    play_dongtai_counts_30         BIGINT  ,
    play_dongtai_counts_60         BIGINT  ,
    play_dongtai_counts_90         BIGINT  ,
    play_dongtai_counts_120        BIGINT  ,
    play_dongtai_times_3           BIGINT   COMMENT '播放动态视频时长，单位:秒',
    play_dongtai_times_7           BIGINT  ,
    play_dongtai_times_14          BIGINT  ,
    play_dongtai_times_21          BIGINT  ,
    play_dongtai_times_30          BIGINT  ,
    play_dongtai_times_60          BIGINT  ,
    play_dongtai_times_90          BIGINT  ,
    play_dongtai_times_120         BIGINT  ,
    play_dongtai_users_3           BIGINT   COMMENT '播放动态视频用户数',
    play_dongtai_users_7           BIGINT  ,
    play_dongtai_users_14          BIGINT  ,
    play_dongtai_users_21          BIGINT  ,
    play_dongtai_users_30          BIGINT  ,
    play_dongtai_users_60          BIGINT  ,
    play_dongtai_users_90          BIGINT  ,
    play_dongtai_users_120         BIGINT  ,
    dongtai_types_3                STRING   COMMENT '点击动态类型',
    dongtai_types_7                STRING  ,
    dongtai_types_14               STRING  ,
    dongtai_types_21               STRING  ,
    dongtai_types_30               STRING  ,
    dongtai_types_60               STRING  ,
    dongtai_types_90               STRING  ,
    dongtai_types_120              STRING  ,
    dongtai_cats_3                 STRING   COMMENT '点击动态品类',
    dongtai_cats_7                 STRING  ,
    dongtai_cats_14                STRING  ,
    dongtai_cats_21                STRING  ,
    dongtai_cats_30                STRING  ,
    dongtai_cats_60                STRING  ,
    dongtai_cats_90                STRING  ,
    dongtai_cats_120               STRING  ,
    dongtai_tags_3                 STRING   COMMENT '点击动态标签',
    dongtai_tags_7                 STRING  ,
    dongtai_tags_14                STRING  ,
    dongtai_tags_21                STRING  ,
    dongtai_tags_30                STRING  ,
    dongtai_tags_60                STRING  ,
    dongtai_tags_90                STRING  ,
    dongtai_tags_120               STRING  ,
    page_click_counts_3            BIGINT   COMMENT '最近N天页面点击次数',
    page_click_counts_7            BIGINT  ,
    page_click_counts_14           BIGINT  ,
    page_click_counts_21           BIGINT  ,
    page_click_counts_30           BIGINT  ,
    page_click_counts_60           BIGINT  ,
    page_click_counts_90           BIGINT  ,
    page_click_counts_120          BIGINT  ,
    page_usetime_3                 BIGINT   COMMENT '最近N天页面访问时长（单位：秒）',
    page_usetime_7                 BIGINT  ,
    page_usetime_14                BIGINT  ,
    page_usetime_21                BIGINT  ,
    page_usetime_30                BIGINT  ,
    page_usetime_60                BIGINT  ,
    page_usetime_90                BIGINT  ,
    page_usetime_120               BIGINT  ,
    view_itemprofile_users_3        BIGINT   COMMENT '最近N天，访问M个用户主页',
    view_itemprofile_users_7        BIGINT  ,
    view_itemprofile_users_14       BIGINT  ,
    view_itemprofile_users_21       BIGINT  ,
    view_itemprofile_users_30       BIGINT  ,
    view_itemprofile_users_60       BIGINT  ,
    view_itemprofile_users_90       BIGINT  ,
    view_itemprofile_users_120      BIGINT  ,
    quickly_order_click_num_3      BIGINT   COMMENT '最近N天极速下单点击次数',
    quickly_order_click_num_7      BIGINT  ,
    quickly_order_click_num_14     BIGINT  ,
    quickly_order_click_num_21     BIGINT  ,
    quickly_order_click_num_30     BIGINT  ,
    quickly_order_click_num_60     BIGINT  ,
    quickly_order_click_num_90     BIGINT  ,
    quickly_order_click_num_120    BIGINT  ,
    item_income                     DECIMAL  COMMENT '商品收入总额',
    item_from_users                 BIGINT   COMMENT '付费给商品的人数',
    item_first_trade_date           DATETIME COMMENT '商品第1次接单时间',
    item_last_trade_date            DATETIME COMMENT '商品最后1次接单时间',
    item_order_amount               DECIMAL  COMMENT '商品接单总额',
    item_order_num                  BIGINT   COMMENT '商品接单单数',
    item_avg_order_price            DECIMAL  COMMENT '平均每单金额',
    pay_amount                     DECIMAL  COMMENT '充值总额',
    pay_num                        BIGINT   COMMENT '充值次数',
    first_pay_date                 DATETIME COMMENT '第1次充值时间',
    last_pay_date                  DATETIME COMMENT '最后1次充值时间',
    each_pay_avg_amount            DECIMAL  COMMENT '平均每笔充值额',
    trade_amount                   DECIMAL  COMMENT '消费总额',
    trade_num                      BIGINT   COMMENT '消费次数',
    trade_to_users                 BIGINT   COMMENT '消费用户数',
    each_trade_avg_amount          DECIMAL  COMMENT '平均每笔消费额',
    first_trade_date               DATETIME COMMENT '第1次消费时间',
    last_trade_date                DATETIME COMMENT '最后1次消费时间',
    order_amount                   DECIMAL  COMMENT '下单总额',
    order_num                      BIGINT   COMMENT '下单次数',
    order_to_users                 BIGINT   COMMENT '下单用户数',
    each_order_avg_amount          DECIMAL  COMMENT '平均每笔下单金额',
    first_order_date               DATETIME COMMENT '第1次下单时间',
    last_order_date                DATETIME COMMENT '最后1次下单时间',
    live_room_amount               DECIMAL  COMMENT '直播打赏总额',
    live_room_num                  BIGINT   COMMENT '直播打赏次数',
    live_room_to_users             BIGINT   COMMENT '直播打赏用户数',
    each_live_room_avg_amount      DECIMAL  COMMENT '平均每笔直播打赏金额',
    first_live_room_date           DATETIME COMMENT '第1次直播打赏时间',
    last_live_room_date            DATETIME COMMENT '最后1次直播打赏时间',
    chatroom_amount                DECIMAL  COMMENT '聊天室打赏总额',
    chatroom_num                   BIGINT   COMMENT '聊天室打赏次数',
    chatroom_to_users              BIGINT   COMMENT '聊天室打赏用户数',
    each_chatroom_avg_amount       DECIMAL  COMMENT '平均每笔聊天室打赏金额',
    first_chatroom_date            DATETIME COMMENT '第1次聊天室打赏时间',
    last_chatroom_date             DATETIME COMMENT '最后1次聊天室打赏时间',
    trade_type_amount              STRING   COMMENT '消费类型金额详情',
    trade_type_counts              STRING   COMMENT '消费类型单数详情',
    trade_type_num                 BIGINT   COMMENT '消费类型数 MESSAGE消息打赏,NEWS动态打赏,ORDER订单',
    trade_cat_amount               STRING   COMMENT '下单品类金额详情 加载品类列表数据',
    trade_cat_counts               STRING   COMMENT '下单品类单数详情',
    trade_cat_num                  BIGINT   COMMENT '下单品类数',
    coupon_use                     BIGINT   COMMENT '优惠券使用率1(>=0.8),2(0.3~0.8),3(<=0.3)',
    loss_trade_amount              DECIMAL  COMMENT '交易总额',
    loss_trade_num                 BIGINT   COMMENT '交易单数',
    loss_trade_from_users          BIGINT   COMMENT '交易用户数',
    loss_order_amount              DECIMAL  COMMENT '接单总额',
    loss_order_num                 BIGINT   COMMENT '接单单数',
    loss_order_from_users          BIGINT   COMMENT '接单用户数',
    pay_amount_3                   DECIMAL  COMMENT '最近N天充值总额',
    pay_amount_7                   DECIMAL ,
    pay_amount_14                  DECIMAL ,
    pay_amount_21                  DECIMAL ,
    pay_amount_30                  DECIMAL ,
    pay_amount_60                  DECIMAL ,
    pay_amount_90                  DECIMAL ,
    pay_amount_120                 DECIMAL ,
    pay_num_3                      BIGINT   COMMENT '最近N天充值次数',
    pay_num_7                      BIGINT  ,
    pay_num_14                     BIGINT  ,
    pay_num_21                     BIGINT  ,
    pay_num_30                     BIGINT  ,
    pay_num_60                     BIGINT  ,
    pay_num_90                     BIGINT  ,
    pay_num_120                    BIGINT  ,
    trade_amount_3                 DECIMAL  COMMENT '最近N天交易总额',
    trade_amount_7                 DECIMAL ,
    trade_amount_14                DECIMAL ,
    trade_amount_21                DECIMAL ,
    trade_amount_30                DECIMAL ,
    trade_amount_60                DECIMAL ,
    trade_amount_90                DECIMAL ,
    trade_amount_120               DECIMAL ,
    trade_num_3                    BIGINT   COMMENT '最近N天交易单数',
    trade_num_7                    BIGINT  ,
    trade_num_14                   BIGINT  ,
    trade_num_21                   BIGINT  ,
    trade_num_30                   BIGINT  ,
    trade_num_60                   BIGINT  ,
    trade_num_90                   BIGINT  ,
    trade_num_120                  BIGINT  ,
    trade_type_amount_3            STRING   COMMENT '最近N天消费类型金额详情',
    trade_type_amount_7            STRING  ,
    trade_type_amount_14           STRING  ,
    trade_type_amount_21           STRING  ,
    trade_type_amount_30           STRING  ,
    trade_type_amount_60           STRING  ,
    trade_type_amount_90           STRING  ,
    trade_type_amount_120          STRING  ,
    trade_type_counts_3            STRING   COMMENT '最近N天消费类型单数详情',
    trade_type_counts_7            STRING  ,
    trade_type_counts_14           STRING  ,
    trade_type_counts_21           STRING  ,
    trade_type_counts_30           STRING  ,
    trade_type_counts_60           STRING  ,
    trade_type_counts_90           STRING  ,
    trade_type_counts_120          STRING  ,
    trade_type_num_3               BIGINT   COMMENT '最近N天交易类型数',
    trade_type_num_7               BIGINT  ,
    trade_type_num_14              BIGINT  ,
    trade_type_num_21              BIGINT  ,
    trade_type_num_30              BIGINT  ,
    trade_type_num_60              BIGINT  ,
    trade_type_num_90              BIGINT  ,
    trade_type_num_120             BIGINT  ,
    trade_to_users_3               BIGINT   COMMENT '最近N天交易用户数',
    trade_to_users_7               BIGINT  ,
    trade_to_users_14              BIGINT  ,
    trade_to_users_21              BIGINT  ,
    trade_to_users_30              BIGINT  ,
    trade_to_users_60              BIGINT  ,
    trade_to_users_90              BIGINT  ,
    trade_to_users_120             BIGINT  ,
    order_to_users_3               BIGINT   COMMENT '最近N天下单用户数',
    order_to_users_7               BIGINT  ,
    order_to_users_14              BIGINT  ,
    order_to_users_21              BIGINT  ,
    order_to_users_30              BIGINT  ,
    order_to_users_60              BIGINT  ,
    order_to_users_90              BIGINT  ,
    order_to_users_120             BIGINT  ,
    chatroom_to_users_3            BIGINT   COMMENT '最近N天聊天室打赏用户数',
    chatroom_to_users_7            BIGINT  ,
    chatroom_to_users_14           BIGINT  ,
    chatroom_to_users_21           BIGINT  ,
    chatroom_to_users_30           BIGINT  ,
    chatroom_to_users_60           BIGINT  ,
    chatroom_to_users_90           BIGINT  ,
    chatroom_to_users_120          BIGINT  ,
    live_room_to_users_3           BIGINT   COMMENT '最近N天直播打赏用户数',
    live_room_to_users_7           BIGINT  ,
    live_room_to_users_14          BIGINT  ,
    live_room_to_users_21          BIGINT  ,
    live_room_to_users_30          BIGINT  ,
    live_room_to_users_60          BIGINT  ,
    live_room_to_users_90          BIGINT  ,
    live_room_to_users_120         BIGINT  ,
    trade_cat_amount_3             STRING   COMMENT '最近N天下单品类金额详情',
    trade_cat_amount_7             STRING  ,
    trade_cat_amount_14            STRING  ,
    trade_cat_amount_21            STRING  ,
    trade_cat_amount_30            STRING  ,
    trade_cat_amount_60            STRING  ,
    trade_cat_amount_90            STRING  ,
    trade_cat_amount_120           STRING  ,
    trade_cat_counts_3             STRING   COMMENT '最近N天下单品类单数详情',
    trade_cat_counts_7             STRING  ,
    trade_cat_counts_14            STRING  ,
    trade_cat_counts_21            STRING  ,
    trade_cat_counts_30            STRING  ,
    trade_cat_counts_60            STRING  ,
    trade_cat_counts_90            STRING  ,
    trade_cat_counts_120           STRING  ,
    trade_cat_num_3                BIGINT   COMMENT '最近N天下单品类数',
    trade_cat_num_7                BIGINT  ,
    trade_cat_num_14               BIGINT  ,
    trade_cat_num_21               BIGINT  ,
    trade_cat_num_30               BIGINT  ,
    trade_cat_num_60               BIGINT  ,
    trade_cat_num_90               BIGINT  ,
    trade_cat_num_120              BIGINT  ,
    order_unpay_num_3              BIGINT   COMMENT '最近N天未支付订单数',
    order_unpay_num_7              BIGINT  ,
    order_unpay_num_14             BIGINT  ,
    order_unpay_num_21             BIGINT  ,
    order_unpay_num_30             BIGINT  ,
    order_unpay_num_60             BIGINT  ,
    order_unpay_num_90             BIGINT  ,
    order_unpay_num_120            BIGINT  ,
    order_cancel_num_3             BIGINT   COMMENT '最近N天取消订单数',
    order_cancel_num_7             BIGINT  ,
    order_cancel_num_14            BIGINT  ,
    order_cancel_num_21            BIGINT  ,
    order_cancel_num_30            BIGINT  ,
    order_cancel_num_60            BIGINT  ,
    order_cancel_num_90            BIGINT  ,
    order_cancel_num_120           BIGINT  ,
    is_live_enter                  BIGINT   COMMENT '是否进入过直播间',
    live_enter_num_3               BIGINT   COMMENT '最近N天进入直播间次数',
    live_enter_num_7               BIGINT  ,
    live_enter_num_14              BIGINT  ,
    live_enter_num_21              BIGINT  ,
    live_enter_num_30              BIGINT  ,
    live_enter_num_60              BIGINT  ,
    live_enter_num_90              BIGINT  ,
    live_enter_num_120             BIGINT  ,
    live_view_time_3               BIGINT   COMMENT '最近N天观看直播时长(单位:秒)',
    live_view_time_7               BIGINT  ,
    live_view_time_14              BIGINT  ,
    live_view_time_21              BIGINT  ,
    live_view_time_30              BIGINT  ,
    live_view_time_60              BIGINT  ,
    live_view_time_90              BIGINT  ,
    live_view_time_120             BIGINT  ,
    is_chatroom_enter              BIGINT   COMMENT '是否进入过聊天室',
    chatroom_enter_num_3           BIGINT   COMMENT '最近N天进入聊天室次数',
    chatroom_enter_num_7           BIGINT  ,
    chatroom_enter_num_14          BIGINT  ,
    chatroom_enter_num_21          BIGINT  ,
    chatroom_enter_num_30          BIGINT  ,
    chatroom_enter_num_60          BIGINT  ,
    chatroom_enter_num_90          BIGINT  ,
    chatroom_enter_num_120         BIGINT  ,
    chatroom_view_room_3           BIGINT   COMMENT '最近N天访问房间个数',
    chatroom_view_room_7           BIGINT  ,
    chatroom_view_room_14          BIGINT  ,
    chatroom_view_room_21          BIGINT  ,
    chatroom_view_room_30          BIGINT  ,
    chatroom_view_room_60          BIGINT  ,
    chatroom_view_room_90          BIGINT  ,
    chatroom_view_room_120         BIGINT  ,
    app_version                    STRING   COMMENT 'APP版本号',
    mobile_os                      BIGINT   COMMENT '0-安卓，1-IOS，2-其他',
    cpush_label_click_num_3        STRING   COMMENT '最近N天点击推送标签次数',
    cpush_label_click_num_7        STRING   COMMENT '7天',
    cpush_label_click_num_14       STRING   COMMENT '14天',
    cpush_label_click_num_21       STRING   COMMENT '21天',
    cpush_label_click_num_30       STRING   COMMENT '30天',
    cpush_label_click_num_60       STRING   COMMENT '60天',
    cpush_label_click_num_90       STRING   COMMENT '90天',
    cpush_label_click_num_120      STRING   COMMENT '120天',
    cpush_label_click_task_num_3   STRING   COMMENT '最近N天点击推送标签任务数',
    cpush_label_click_task_num_7   STRING   COMMENT '7天',
    cpush_label_click_task_num_14  STRING   COMMENT '14天',
    cpush_label_click_task_num_21  STRING   COMMENT '21天',
    cpush_label_click_task_num_30  STRING   COMMENT '30天',
    cpush_label_click_task_num_60  STRING   COMMENT '60天',
    cpush_label_click_task_num_90  STRING   COMMENT '90天',
    cpush_label_click_task_num_120 STRING   COMMENT '120天',
    cpush_type_click_num_3         STRING   COMMENT '最近N天点击推送类型次数',
    cpush_type_click_num_7         STRING   COMMENT '7天',
    cpush_type_click_num_14        STRING   COMMENT '14天',
    cpush_type_click_num_21        STRING   COMMENT '21天',
    cpush_type_click_num_30        STRING   COMMENT '30天',
    cpush_type_click_num_60        STRING   COMMENT '60天',
    cpush_type_click_num_90        STRING   COMMENT '90天',
    cpush_type_click_num_120       STRING   COMMENT '120天',
    cpush_type_click_task_num_3    STRING   COMMENT '最近N天点击推送类型任务数',
    cpush_type_click_task_num_7    STRING   COMMENT '7天',
    cpush_type_click_task_num_14   STRING   COMMENT '14天',
    cpush_type_click_task_num_21   STRING   COMMENT '21天',
    cpush_type_click_task_num_30   STRING   COMMENT '30天',
    cpush_type_click_task_num_60   STRING   COMMENT '60天',
    cpush_type_click_task_num_90   STRING   COMMENT '90天',
    cpush_type_click_task_num_120  STRING   COMMENT '120天',
    balance_trade_amount_3         STRING   COMMENT '最近N天金币消费金额（单位：元)',
    balance_trade_amount_7         STRING  ,
    balance_trade_amount_14        STRING  ,
    balance_trade_amount_21        STRING  ,
    balance_trade_amount_30        STRING  ,
    balance_trade_amount_60        STRING  ,
    balance_trade_amount_90        STRING  ,
    balance_trade_amount_120       STRING  ,
    balance_trade_amount           STRING  ,
    diamond_trade_amount_3         STRING   COMMENT '最近N天钻石消费金额（单位：元)',
    diamond_trade_amount_7         STRING  ,
    diamond_trade_amount_14        STRING  ,
    diamond_trade_amount_21        STRING  ,
    diamond_trade_amount_30        STRING  ,
    diamond_trade_amount_60        STRING  ,
    diamond_trade_amount_90        STRING  ,
    diamond_trade_amount_120       STRING  ,
    diamond_trade_amount           STRING  ,
    star_diamond_trade_amount_3    STRING   COMMENT '最近N天砖石消费金额（单位：元)',
    star_diamond_trade_amount_7    STRING  ,
    star_diamond_trade_amount_14   STRING  ,
    star_diamond_trade_amount_21   STRING  ,
    star_diamond_trade_amount_30   STRING  ,
    star_diamond_trade_amount_60   STRING  ,
    star_diamond_trade_amount_90   STRING  ,
    star_diamond_trade_amount_120  STRING  ,
    star_diamond_trade_amount      STRING  
) 
COMMENT '用户特征,含：充值消费表';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245
  • 246
  • 247
  • 248
  • 249
  • 250
  • 251
  • 252
  • 253
  • 254
  • 255
  • 256
  • 257
  • 258
  • 259
  • 260
  • 261
  • 262
  • 263
  • 264
  • 265
  • 266
  • 267
  • 268
  • 269
  • 270
  • 271
  • 272
  • 273
  • 274
  • 275
  • 276
  • 277
  • 278
  • 279
  • 280
  • 281
  • 282
  • 283
  • 284
  • 285
  • 286
  • 287
  • 288
  • 289
  • 290
  • 291
  • 292
  • 293
  • 294
  • 295
  • 296
  • 297
  • 298
  • 299
  • 300
  • 301
  • 302
  • 303
  • 304
  • 305
  • 306
  • 307
  • 308
  • 309
  • 310
  • 311
  • 312
  • 313
  • 314
  • 315
  • 316
  • 317
  • 318
  • 319
  • 320
  • 321
  • 322
  • 323
  • 324
  • 325
  • 326
  • 327
  • 328
  • 329
  • 330
  • 331
  • 332
  • 333
  • 334
  • 335
  • 336
  • 337
  • 338
  • 339
  • 340
  • 341
  • 342
  • 343
  • 344
  • 345
  • 346
  • 347
  • 348
  • 349
  • 350
  • 351
  • 352
  • 353
  • 354
  • 355
  • 356
  • 357
  • 358
  • 359
  • 360
  • 361
  • 362
  • 363
  • 364
  • 365
  • 366
  • 367
  • 368
  • 369
  • 370
  • 371
  • 372
  • 373
  • 374
  • 375
  • 376
  • 377
  • 378
  • 379
  • 380
  • 381
  • 382
  • 383
  • 384
  • 385
  • 386
  • 387
  • 388
  • 389
  • 390
  • 391
  • 392
  • 393
  • 394
  • 395
  • 396
  • 397
  • 398
  • 399
  • 400
  • 401
  • 402
  • 403
  • 404
  • 405
  • 406
  • 407
  • 408
  • 409
  • 410
  • 411
  • 412
  • 413
  • 414
  • 415
  • 416
  • 417
  • 418
  • 419
  • 420
  • 421
  • 422
  • 423
  • 424
  • 425
  • 426
  • 427
  • 428
  • 429
  • 430
  • 431
  • 432
  • 433
  • 434
  • 435
  • 436
  • 437
  • 438
  • 439
  • 440
  • 441
  • 442
  • 443
  • 444
  • 445
  • 446
  • 447
  • 448
  • 449
  • 450
  • 451
  • 452
  • 453
  • 454
  • 455
  • 456
  • 457
  • 458
  • 459
  • 460
  • 461
  • 462
  • 463
  • 464
  • 465
  • 466
  • 467
  • 468
  • 469
  • 470
  • 471
  • 472
  • 473
  • 474
  • 475
  • 476
  • 477
  • 478
  • 479
  • 480
  • 481
  • 482
  • 483
  • 484
  • 485
  • 486
  • 487
  • 488
  • 489
  • 490
  • 491
  • 492
  • 493
  • 494
  • 495
  • 496
  • 497
  • 498
  • 499
  • 500
  • 501
  • 502
  • 503
  • 504
  • 505
  • 506
  • 507
  • 508
  • 509
  • 510
  • 511
  • 512
  • 513
  • 514
  • 515
  • 516
  • 517
  • 518
  • 519
  • 520

遇到的问题

这个是天级别的，需要计算完按天更新存储到hbase（大部分公司都会把特征存到hbase）

但是数据量极大

  1. 看表可以发现指标为最近30天的用户行为数据占比较大
  2. 所以我将此拆分出来全量和最近31天有行为的用户
  3. 最近30天的用户行为数据，如果用农户最近31天未登录有操作，则不更新，数据量缩小为1/50
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/木道寻08/article/detail/894571
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号