离线数仓经验之谈一-OneID解决方案（建议收藏）

笔者是5年经验的离线数仓开发者，基于之前行业经验，准备整理下之前遇到的一些问题，本篇谈谈我对数仓中老生常谈的唯一标识（uid）的理解，直接上干货。

APP行为日志采集到数仓后，常采用设备信息作为用户的唯一标识

常用设备标识码

• ANDROID ID：设备ID，刷机重装手机都会改变。
• IMEI：International Mobile Equipment Identity，国际移动设备身份码的缩写。是由15位数字组成的“电子串号”，它与每台手机一一对应，每个IMEI在世界上都是唯一的。
• IDFA：Identifier For Advertising，iOS独有的广告标识符。
• IDFV：Vindor 标示符 （IDFV-identifierForVendor），来自于同一个开发商（jianshu.App1和jianshu.App2）应用在同一个设备上，此属性值是相同的，不同的运营商运用在同一个设备上值也不同

标识码规则

• ANDROID ID ：长度为 10~18 或者 35，一定可以采集到
• IMEI ：长度为 14~17 或者 8，手机不授权采集不到
• IDFA ：手机不授权获取不到
  以 - 分割后有5段的，第一段长度为 8，第二段长度为 4，第三段长度为 4，第四段长度为 4，第五段长度为 12
• IDFV ：一定可以采集到
  以 - 分割后有5段的，第一段长度为 8，第二段长度为 4，第三段长度为 4，第四段长度为 4，第五段长度为 12
  以 - 分割后有4段的，第一段长度为 5，第二段长度为 3，第三段长度为 13，第四段长度为 5

基于标识码特有的规则，为了使统计准确性更高，常采用安卓 imei->安卓id ，iOS idfa->idfv 的顺序赋予uid，此时又会引入另一个问题，用户在使用APP时存在授权和取消授权操作，导致安卓的imei、iOS的idfa获取不到，所以笔者采用sql方式来解决当天用户授权和不授权导致的数据差异。

select case when repairimei is null or repairimei in ('','null','NULL','00000000-0000-0000-0000-000000000000') then 6 else tag end as tag,
       case when lower(os) regexp 'ios' and (repairimei is null or repairimei in ('','null','NULL','00000000-0000-0000-0000-000000000000')) then imei
            when lower(os) regexp 'android' and (repairimei is null or repairimei in ('','null','NULL')) then deviceid
            else repairimei end as uid,
      repairimei,
      xxx,dt
from (
  -- 安卓
  select tag,
         first_value(imei) over(distribute by apptypeid,deviceid sort by tag asc) as repairimei,
         xxx,dt
  from (
    select 1 as tag,
    xxx,dt
    from log_center.xxx
    where dt='vardate'  
    and (imei is null or imei in ('','null','NULL'))
    and lower(os) regexp 'android' 
   
    union all
    select 0 as tag,
    xxx,dt
    from config_center.config_app_install_return_imei_android
    where dt='vardate'
  ) as a

  union all
  select 2 as tag,imei as repairimei,
  xxx,dt
  from log_center.xxx
  where dt='vardate'   
    and ((lower(os) regexp 'android' and imei is not null and imei not in ('','null','NULL')) or (lower(os) not regexp 'ios|android' or os is null or os in ('','null','NULL')))

  -- ios
  union all
  select tag,
         first_value(deviceid) over(distribute by apptypeid,imei sort by tag asc) as repairimei,
         xxx,dt
  from (
    select 4 as tag,
    xxx,dt
    from log_center.xxx
    where dt='vardate' 
    and lower(os) regexp 'ios'  
    and (deviceid is null or deviceid in ('','null','NULL','00000000-0000-0000-0000-000000000000'))
      
    union all
    select 3 as tag,
    xxx,dt
    from config_center.config_app_install_return_deviceid_ios
    where dt='vardate'
  ) as b

  union all
  select 5 as tag,deviceid as repairimei,
  xxx,dt
  from log_center.xxx
  where dt='vardate'   
  and lower(os) regexp 'ios' and deviceid is not null and deviceid not in ('','null','NULL','00000000-0000-0000-0000-000000000000')

) as a 
where tag not in (0,3);