# data-center

**Repository Path**: zhaoyb_java/data-center

## Basic Information

- **Project Name**: data-center
- **Description**: flink开发的实时埋点系统
- **Primary Language**: Java
- **License**: MulanPSL-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 3
- **Forks**: 2
- **Created**: 2024-03-18
- **Last Updated**: 2025-03-20

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 基于flink开发的实时埋点系统，详细说明文档见：
文档说明链接: [基于Flink的用户埋点系统]( https://blog.csdn.net/qq_33661044/article/details/136535303)


**ClickHouse 建表语句**

**页面浏览统计表**

```sql
CREATE database DataCore;
drop table if exists DataCore.dws_traffic_page_view_window;
create table if not exists DataCore.dws_traffic_page_view_window
(
    win_start_time     DateTime  COMMENT '窗口起始时间',
    win_end_time     DateTime COMMENT '窗口结束时间',
    app_ver      String COMMENT '版本号' ,
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid   String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    event   String COMMENT '事件类型' ,
    sub_event   String COMMENT '事件子类型',
    is_new      UInt64 COMMENT '新老用户状态标记' ,
    code    String COMMENT '平台code' ,
    uv      UInt64 COMMENT '独立用户访问数' ,
    pv      UInt64 COMMENT '用户访问数',
    during_time UInt64 COMMENT '当前窗口页面访问总时长' ,
    create_time DateTime COMMENT '创建时间',
    ts      UInt64 COMMENT '时间戳' 
) engine = MergeTree() partition by toYYYYMMDD(win_start_time)
order by (win_start_time, win_end_time,app_ver,os,qid,group_qid,asc_qid,sub_event,is_new,code);
```

#### 1 流量域版本-渠道-平台粒度页面行为各窗口汇总表

**ClickHouse 建表语句**

**用户行为 actions 日志记录表**

```sql
drop table if exists DataCore.dws_traffic_actions_window;
create table if not exists DataCore.dws_traffic_actions_window
(
    win_start_time     DateTime  COMMENT '窗口起始时间',
    win_end_time     DateTime COMMENT '窗口结束时间',
    app_ver      String COMMENT '版本号' ,
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    is_new  UInt64 COMMENT '新老用户状态标记' ,
    code    String COMMENT '平台code' ,
    event   String COMMENT '事件类型' ,
    sub_event   String COMMENT '事件子类型',
    action      String COMMENT '动作类型',
    action_cnt   UInt64 COMMENT '动作次数' ,
    device_cnt   UInt64 COMMENT '动作设备次数' ,
    distinct_device_cnt   UInt64 COMMENT '去重动作设备次数' ,
    create_time DateTime COMMENT '创建时间',
    ts      UInt64 COMMENT '时间戳' 
) engine = MergeTree() partition by (toYYYYMMDD(win_start_time))
order by (win_start_time, win_end_time,app_ver,os,qid,group_qid,asc_qid,sub_event,action,is_new,code);
```

#### 2 用户域版本-渠道-平台粒度用户启动明细表

**ClickHouse 建表语句**

**用户注册表**

```sql
drop table if exists DataCore.dws_user_register_window;
create table if not exists DataCore.dws_user_register_window
(
    app_ver      String COMMENT '版本号', 
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    code      String COMMENT '平台code' ,
    device_id  String COMMENT '设备号(用来标识用户)',
    register_time DateTime  COMMENT '注册时间',
    create_time DateTime  COMMENT '创建时间',
    ts      UInt64 COMMENT '时间戳'
) engine = ReplacingMergeTree(ts) partition by toYYYYMMDD(register_time)
order by (app_ver,os,qid,group_qid,asc_qid,code,device_id);
```

**用户启动表**

```sql
drop table if exists DataCore.dws_user_start_window;
create table if not exists DataCore.dws_user_start_window
(
    app_ver      String COMMENT '版本号', 
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    code      String COMMENT '平台code' ,
    device_id  String COMMENT '设备号(用来标识用户)',
    start_time   DateTime  COMMENT '用户启动时间',
    create_time DateTime  COMMENT '创建时间',
    ts      UInt64 COMMENT '时间戳'
) engine = ReplacingMergeTree(ts) partition by toYYYYMMDD(start_time )
order by (app_ver,os,qid,group_qid,asc_qid,code,device_id);
```

**用户安装表**

```sql
drop table if exists DataCore.dws_user_install_window;
create table if not exists DataCore.dws_user_install_window
(
    win_start_time     DateTime  COMMENT '窗口起始时间',
    win_end_time     DateTime COMMENT '窗口结束时间',
    app_ver      String COMMENT '版本号', 
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    code    String COMMENT '平台code' ,
    install_cnt      UInt64 COMMENT '安装次数',
    distinct_install_cnt UInt64 COMMENT '去重安装次数',
    create_time DateTime  COMMENT '创建时间', 
    ts      UInt64 COMMENT '时间戳' 
) engine = MergeTree() partition by toYYYYMMDD(win_start_time)
order by (win_start_time, win_end_time, app_ver,os,qid,group_qid,asc_qid,code);
```

#### 3 用户域版本-渠道-平台活跃用户各窗口汇总表

**ClickHouse 建表语句**

**活跃用户统计表**

```sql
drop table if exists DataCore.dws_user_active_window;
create table if not exists DataCore.dws_user_active_window
(
    win_start_time     DateTime  COMMENT '窗口起始时间',
    win_end_time     DateTime COMMENT '窗口结束时间',
    app_ver      String COMMENT '版本号', 
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    code    String COMMENT '平台code' ,
    dau      UInt64 COMMENT '活跃用户数' ,
    start_cnt      UInt64 COMMENT '启动次数',
    cold_start_cnt UInt64 COMMENT '冷启动次数' ,
    hot_start_cnt UInt64 COMMENT '热启动次数',
    use_time UInt64 COMMENT '使用总时长' ,
    create_time DateTime  COMMENT '创建时间', 
    ts      UInt64 COMMENT '时间戳' 
) engine = MergeTree() partition by toYYYYMMDD(win_start_time)
order by (win_start_time, win_end_time, app_ver,os,qid,group_qid,asc_qid, code);
```

#### 4 用户域版本-渠道-平台新增用户各窗口汇总表

**ClickHouse 建表语句**

**新增用户统计表**

```sql
drop table if exists DataCore.dws_user_new_window;
create table if not exists DataCore.dws_user_new_window
(
    win_start_time     DateTime  COMMENT '窗口起始时间',
    win_end_time     DateTime COMMENT '窗口结束时间',
    app_ver      String COMMENT '版本号' ,
    os      String COMMENT '操作系统',
    qid     String COMMENT '渠道',
    group_qid      String COMMENT '渠道分组',
    asc_qid      String COMMENT '归因渠道',
    code    String COMMENT '平台code' ,
    dnu      UInt64 COMMENT '新增用户数', 
    start_cnt      UInt64 COMMENT '启动次数',
    cold_start_cnt UInt64 COMMENT '冷启动次数' ,
    hot_start_cnt UInt64 COMMENT '热启动次数',
    use_time UInt64 COMMENT '使用总时长',  
    create_time DateTime  COMMENT '创建时间',
    ts      UInt64 COMMENT '时间戳' 
) engine = MergeTree() partition by toYYYYMMDD(win_start_time)
order by (win_start_time, win_end_time, app_ver,os,qid,group_qid,asc_qid,code);
```

#### 5 支付域支付方式-客户端-商品 支付订单各窗口汇总表

**ClickHouse 建表语句**

**支付订单统计表**

```sql
drop table if exists DataCore.dws_trade_order_window;
CREATE TABLE DataCore.dws_trade_order_window
(
    `win_start_time` DateTime COMMENT '窗口起始时间',
    `win_end_time` DateTime COMMENT '窗口结束时间',
    `os` String COMMENT '操作系统',
    `product_id` String COMMENT '商品id',
    `way_code` String COMMENT '支付方式,ALI_APP、WX_APP、APPLE_APP',
    `code` String COMMENT '平台code',
    `pay_amount` Decimal64(6) COMMENT '支付金额',
    `refund_amount` Decimal64(6) COMMENT '退款金额',
    `tax_amount` Decimal64(6) COMMENT '扣税金额',
    `total_amount` Decimal64(6) COMMENT '总收益',
    `order_num` UInt64 COMMENT '订单总数量',
    `device_num` UInt64 COMMENT '付费用户总数量',
    `first_day_device_num` UInt64 COMMENT '首日付费用户总数量',
    `first_day_pay_amount` Decimal64(6) COMMENT '首日付费用户支付金额',
    `first_day_order_num` UInt64 COMMENT '首日付费用户订单总数量',
    `create_time` DateTime COMMENT '创建时间',
    `ts` UInt64 COMMENT '时间戳'
)ENGINE = MergeTree() PARTITION BY toYYYYMMDD(win_start_time)
ORDER BY (win_start_time,win_end_time,os,product_id, way_code, code);
```

**索引**

```sql
CREATE INDEX idx_sub_event ON DataCore.dws_traffic_actions_window (sub_event) TYPE minmax GRANULARITY 8192;
CREATE INDEX idx_sub_event ON DataCore.dws_traffic_page_view_window  (sub_event) TYPE minmax GRANULARITY 8192;
```

#### 6 流量域版本-渠道-平台粒度用户浏览明细表

**ClickHouse 建表语句**

**用户浏览详情表**

```sql
**drop** **table** **if** **exists** DataCore.dws_user_page_view_detail;
**create** **table** **if** **not** **exists** DataCore.dws_user_page_view_detail
(
    app_ver             **String** **COMMENT** '版本号' ,
    os                  **String** **COMMENT** '操作系统',
    os_version          **String** **COMMENT** '操作系统版本号',
    device              **String** **COMMENT** '手机型号', 
    device_brand        **String** **COMMENT** '机型品牌', 
    qid                 **String** **COMMENT** '渠道',
    group_qid           **String** **COMMENT** '渠道分组',
    asc_qid             **String** **COMMENT** '归因渠道',
    is_new              **UInt64** **COMMENT** '新老用户状态标记' ,
    code                **String** **COMMENT** '平台code' ,
    device_id           **String** **COMMENT** '设备号(用来标识用户)',
    event               **String** **COMMENT** '事件类型' ,
    sub_event           **String** **COMMENT** '事件子类型',
    last_sub_event      **String** **COMMENT** '上一个事件类型',
    during_time         **UInt64** **COMMENT** '页面浏览时间' ,
    create_time         **DateTime** **COMMENT** '创建时间',
    ts                  **UInt64** **COMMENT** '时间戳',
    lab_code            **String** **COMMENT** '实验code',
    lab_group_code      **String** **COMMENT** '实验分组code'
) **engine** =MergeTree() **partition** **by** **toYYYYMMDD**(create_time)
**order** **by** (app_ver,os,qid,group_qid,asc_qid,is_new,code,device_id,event,sub_event,last_sub_event,create_time);
```

#### 7 流量域版本-渠道-平台粒度用户动作明细表

**ClickHouse 建表语句**

**用户动作详情表**

```sql
**drop** **table** **if** **exists** DataCore.dws_user_page_action_detail;
**create** **table** **if** **not** **exists** DataCore.dws_user_page_action_detail
(
    app_ver             **String** **COMMENT** '版本号' ,
    os                  **String** **COMMENT** '操作系统',
    os_version          **String** **COMMENT** '操作系统版本号',
    device              **String** **COMMENT** '手机型号', 
    device_brand        **String** **COMMENT** '机型品牌', 
    qid                 **String** **COMMENT** '渠道',
    group_qid           **String** **COMMENT** '渠道分组',
    asc_qid             **String** **COMMENT** '归因渠道',
    is_new              **UInt64** **COMMENT** '新老用户状态标记' ,
    code                **String** **COMMENT** '平台code' ,
    device_id           **String** **COMMENT** '设备号(用来标识用户)',
    event               **String** **COMMENT** '事件类型' ,
    sub_event           **String** **COMMENT** '事件子类型',
    action_type         **String** **COMMENT** '动作类型,展现传show、点击传click、关闭传close',
    create_time         **DateTime** **COMMENT** '创建时间',
    ts                  **UInt64** **COMMENT** '时间戳' ,
    lab_code            **String** **COMMENT** '实验code',
    lab_group_code      **String** **COMMENT** '实验分组code'
) **engine** = MergeTree() **partition** **by** **toYYYYMMDD**(create_time)
**order** **by** (app_ver,os,qid,group_qid,asc_qid,is_new,code,device_id,event,sub_event,action_type,create_time);
```