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

SELECT  apptype
       ,machinecode                                 AS mid
       ,CASE WHEN topic = 'share' THEN '分享'
             WHEN topic = 'click' THEN '回流'
        END                                         AS action_type
       ,CASE WHEN topic = 'share' THEN shareobjectid
             WHEN topic = 'click' THEN clickobjectid
        END                                         AS item_id
       ,CAST(clienttimestamp / 1000 AS BIGINT)       AS ts
       ,CAST(NULL AS STRING)                         AS scene_json
       ,CAST(NULL AS STRING)                         AS ext_json
FROM    loghubods.user_share_log
WHERE   dt = '${dt}'
AND     topic IN ('share', 'click')
AND     (machinecode LIKE 'weixin_openid_%'
         OR machinecode 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
;
