get url -> id, adv_id post url -> adv_id

1. dayyear,month,day,postion
2. platform ios,android,other
3. brand xiaomi,apple,sanxing
4. address 市

当前数据现状

mongodb data

1. 缺少广告唯一id

ios

{
    "_id": ObjectId("63631ec3d2835901448a2cb0"),
    "user_id": "0",
    "user_token": "21cbd11b513463a81620e70e67eee514",
    "app_plantform": "iOS",
    "app_versionname": "3.1.0",
    "app_versioncode": "1.0",
    "app_channel": "AppleStore",
    "sys_versioncode": "14.3",
    "phone_brand": "iPhone",
    "phone_model_name": "iPhone 12 Pro Max",
    "geo_location": {
        "city": "广州市",
        "country": "中国",
        "district": "增城区",
        "latitude": "23.12255269457367",
        "longitude": "113.61142888328",
        "province": "广东省"
    },
    "key": "0-21cbd11b513463a81620e70e67eee514-itemShowAD_related_ad-1666961404307",
    "obj_id": "itemShowAD_related_ad",
    "userID": NumberInt("0"),
    "time": 1666961404307,
    "eventType": "itemShow",
    "page": "pg_news_common_detail",
    "userToken": "21cbd11b513463a81620e70e67eee514",
    "data": {
        "imgUrl": "http://qn-cover.feheadline.com/upload-model/165932525838847089788351.jpg",
        "id": "2224",
        "videoUrl": "<null>",
        "actionType": "none"
    }
}

android

{
    "_id": ObjectId("63631f9b960a5f013f9607d1"),
    "user_id": "5732004",
    "user_token": "b342dcf3713462e39a93510c83cce34e",
    "app_plantform": "android",
    "app_versionname": "3.0.22",
    "app_versioncode": "30022",
    "app_channel": "huawei",
    "sys_versioncode": "10",
    "log_time": 1667440539898,
    "phone_brand": "HUAWEI",
    "phone_model_name": "VOG-AL00",
    "geo_location": { },
    "key": "5732004-b342dcf3713462e39a93510c83cce34e-itemShowAD_ad-1667440525465",
    "eventType": "itemShow",
    "obj_id": "itemShowAD_ad",
    "page": "pg_start",
    "time": 1667440525465,
    "userID": NumberInt("5732004"),
    "userToken": "b342dcf3713462e39a93510c83cce34e",
    "data": {
        "id": NumberInt("2238"),
        "actionType": "none",
        "imgUrl": "http://qn-cover.feheadline.com/event/16642428178349053336879.jpg",
        "videoUrl": "http://qn-cover.feheadline.com/event/166717998665669357428968.mp4"
    }
}

流程说明

Title: adv log count flow
MongodDb->SplitLog: get original adv logs 
SplitLog-->OneDayLogs: get one day logs
OneDayLogs->CountLog: count this day logs
CountLog-->Mysql: save count log

1. 从mongo获取上一次处理时间和当前时间内的所有日志数据
2. 将获取到的数据切分成adv_id->year->month->day->position->logs，处理时，对android和ios分别处理，结构有可能过不一致
3. 按天处理数据，并记录入库，其中start_time那一天做增加处理，其余做插入处理，地理位置和设备品牌统计做增加处理
4. 从数据库中返回处理记录给端页面

聚合接口设计

get:/adv-logs/api/count/:av_id

// 触发统计函数
// 返回统计信息
export interface AdvLogCount {
  // adv id
  adv_id: string;
  // year
  year: string;
  // month
  month: string;
  // day
  day: string;
  // adv position  group by position
  position: string;
  // adv pv
  pv: number;
  // adv uv
  uv: number;
  // click adv pv
  cpv: number;
  // click adv uv
  cuv: number;
  // last update time 'yyy-mm-dd'
  last_update_time: Date;
}
export interface AdvLogAddressCount {
  // 国家
  country: string;
  // 省
  province: string;
  // 市
  city: string;
  // adv id
  adv_id: string;
  // look
  pv: number;
  // user
  uv: number;
  // ip
  iv: number;
}

export interface AdvLogPlatformCount {
  // app platform name
  app_platform: string;
  // pv total
  count: number;
  // adv id
  adv_id: string;
}

export interface AdvLogBrandCount {
  // app platform name
  phone_brand: string;
  // pv total
  count: number;
  // adv id
  adv_id: string;
}

return {
    success: true,
    message: 'ok'
    data: {
        days: AdvLogCount[],
        address: AdvLogAddressCount[],
        platform: AdvLogPlatformCount[],
        brand: AdvLogBrandCount[]
    }
}

数据库设计

# 按天统计广告数据
create table fe_adv_log_day_count(

);
# 按地区统计广告数据
create table fe_adv_log_address_count(

);
# 按平台统计广告数据
create table fe_adv_log_ploatform_count(

);
# 按品牌统计广告数据
create table fe_adv_log_brand_count(

);

前端库选择

echarts