空账号的设备的user_id 映射表开发

1 场景前提：

我们现在有三张表：

ods.mall_app_log_dtl : 用户行为日志表

        贴源层的用户行为日志

dws.mall_app_device_account_bind ： 设备账号绑定关系权重表（截至今天的数据）

dws.mall_app_device_tmpid ： 空账号设备映射表（截至昨天的数据）

2 需求：

2.1 我们要将今天(2022-02-13)的用户行为日志

查找出来没有登录过一次账号的设备(deviceId)

2.2 再去表dws.mall_app_device_account_bind中

查找之前这个设备(deviceId)有没有记录过账户，如果没有记录登陆过任何设备，就保留下这个设备(deviceId)

2.3 我们再根据这个设备(deviceId)到昨天(2022-02-12)的空账号映射表里，匹配留下新的设备(deviceId)

2.4 上面过滤出来的设备(deviceId)，就是新的空账号设备，接着我们需要从dws.mall_app_device_tmpid 这个空账号映射表里，查到最大的user_id,接着这个id，来给每个新的空账号设备设置一个自增的新的user_id

3 sql代码

-- 建表  ：空设备 <---> user_id 映射表
create table dws.mall_app_device_tmpid(
   user_id     bigint,
   device_id   string
)
partitioned by (dt string)
stored as orc
tblproperties(
 'orc.compress'='snappy'
)
;


-- 计算代码

-- 最终结果插入到 T日“空设备-userid映射表”
INSERT INTO TABLE dws.mall_app_device_tmpid PARTITION(dt='2022-02-13')

SELECT
    o4.device_id,
    -- 5.为这些新的空设备，生成在前日最大id基础上，递增的user_id
    row_number() over() + o5.max_userid  as user_id
FROM 
(
   SELECT
     o1.device_id
   FROM 
   (  -- 1.从T日的行为日志中，找出完全不带任何account的设备id
      SELECT
         deviceid as device_id,
      FROM ods.mall_app_log
      WHERE dt='2022-02-13'
      group by deviceid
      having max(if(trim(account)='',null,account)) is null
   ) o1
   
   -- 2.拿着这些设备id去关联 T日“设备-账号绑定表”，留下关联不上的
   LEFT JOIN 
   ( 
      SELECT
         device_id
      FROM dws.mall_app_device_account_bind
      WHERE dt='2022-02-13'
   ) o2
   
   ON o1.device_id=o2.device_id
   
   -- 3.拿着上一步筛选出来的设备id 关联  T-1日“空设备-userid映射表”，留下关联不上的
   LEFT JOIN 
   
   (
      SELECT
        device_id
      FROM dws.mall_app_device_tmpid 
      WHERE dt='2022-02-12'
   ) o3
   
   ON o1.device_id=o3.device_id
   
   WHERE o2.device_id is null and o3.device_id is null
) o4 
-- 4.给每个新的空设备关联上 前日的空设备最大ID
JOIN 
(
   SELECT  
       max(device_id) as max_userid
   FROM dws.mall_app_device_tmpid 
   WHERE dt='2022-02-12'
) o5