-- user_relation 测试：验证 SELECT 逻辑
-- 使用: python fetch_daily.py tasks/00_表的开发/loghubods.user_relation/01_基本数据.sql --date 20260302

SELECT  apptype
       ,from_mid
       ,machinecode                                                          AS to_mid
       ,clickobjectid                                                        AS item_id
       ,CAST(clienttimestamp / 1000 AS BIGINT)                               AS ts
       ,CASE WHEN root_mid LIKE 'weixin_openid_%'
                  OR root_mid RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'
             THEN root_mid
        END                                                                 AS root_mid
       ,CONCAT('{"depth":', CAST(usersharedepth AS BIGINT), '}')             AS ext_json
FROM (
    SELECT  apptype, shareid, rootshareid, machinecode, clickobjectid, clienttimestamp, usersharedepth
           ,REGEXP_REPLACE(shareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') AS from_mid
           ,REGEXP_REPLACE(rootshareid, '(-[0-9a-f]{4})?-[0-9]{13}[0-9]*$', '') AS root_mid
    FROM    loghubods.user_share_log
    WHERE   dt = '${dt}'
    AND     topic = 'click'
) t
WHERE   from_mid LIKE 'weixin_openid_%'
OR      from_mid RLIKE '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$'
LIMIT   100
;
