Canonical SQL Templates

Use ${bizdate} as yyyyMMdd. Do not mix these templates' CTEs across scenarios.

S1: Playback screenshot user list

WITH adview_events AS (
  SELECT dt event_dt, machinecode mid, subsessionid, CAST(clienttimestamp AS BIGINT) adview_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adView'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), capture_events AS (
  SELECT dt event_dt, machinecode mid, loginuid uid, subsessionid, CAST(clienttimestamp AS BIGINT) capture_ts
  FROM loghubods.simpleevent_log
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='userCaptureScreen'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), close_events AS (
  SELECT dt event_dt, machinecode mid, subsessionid, CAST(clienttimestamp AS BIGINT) close_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adCloseBtnTap'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), matched AS (
  SELECT DISTINCT a.mid,c.uid,c.capture_ts
  FROM adview_events a JOIN capture_events c ON a.event_dt=c.event_dt AND a.mid=c.mid AND a.subsessionid=c.subsessionid AND c.capture_ts>=a.adview_ts
  WHERE NOT EXISTS (SELECT 1 FROM close_events x WHERE x.event_dt=a.event_dt AND x.mid=a.mid AND x.subsessionid=a.subsessionid AND x.close_ts>a.adview_ts AND x.close_ts<=c.capture_ts)
), latest_uid AS (
  SELECT mid,uid FROM (SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY capture_ts DESC) rn FROM matched) t WHERE rn=1
)
SELECT 'adPlay_capture' AS `type`, mid, uid, 0 AS risk_level FROM latest_uid;

For the count, keep all CTEs and replace the final select with SELECT COUNT(*) FROM latest_uid.

S2: Landing screenshot user list

WITH landing_capture_events AS (
  SELECT machinecode mid, loginuid uid, CAST(clienttimestamp AS BIGINT) capture_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adUserCaptureScreen'
    AND machinecode IS NOT NULL AND machinecode<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), latest_uid AS (
  SELECT mid,uid FROM (SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY capture_ts DESC) rn FROM landing_capture_events) t WHERE rn=1
)
SELECT 'adlanding_capture' AS `type`, mid, uid, 0 AS risk_level FROM latest_uid;

S3/S4: Dynamic-tier output contract

Build risk_events(mid, uid, event_dt, event_ts, ...) with the scenario-specific keys documented in risk-strategies.md, then use this exact suffix:

, user_risk AS (
  SELECT mid, COUNT(*) behavior_cnt, MAX(event_dt) last_event_dt FROM risk_events GROUP BY mid
), latest_uid AS (
  SELECT mid,uid FROM (
    SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY event_ts DESC) rn FROM risk_events
  ) t WHERE rn=1
), current_risk_users AS (
  SELECT r.mid,u.uid,
    CASE
      WHEN r.behavior_cnt>=3 THEN '${type_3}'
      WHEN r.behavior_cnt=2 AND r.last_event_dt BETWEEN TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyymmdd'),-29,'dd'),'yyyymmdd') AND '${bizdate}' THEN '${type_2}'
      WHEN r.behavior_cnt=1 AND r.last_event_dt BETWEEN TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyymmdd'),-2,'dd'),'yyyymmdd') AND '${bizdate}' THEN '${type_1}'
    END AS risk_type
  FROM user_risk r LEFT JOIN latest_uid u ON r.mid=u.mid
)
SELECT risk_type AS `type`,mid,uid,0 AS risk_level
FROM current_risk_users WHERE risk_type IS NOT NULL;

S3 risk_events builder

Use ad_action_log_own.adView with ownAdSystemType='ownPlatform'; join simpleevent_log.userActiveEnd on same day, mid, sessionid, and subsessionid; require 0 <= active_end_ts-adview_ts <= 10000; exclude close and homepage pageview in that interval; require later same-session useractive_log.path='pages/swiper/index'. Set event_ts=active_end_ts.

Types: ${type_1}=adview10s_hide_return_risk_1_forbidden_3, ${type_2}=adview10s_hide_return_risk_2_forbidden_30, ${type_3}=adview10s_hide_return_risk_3_forbidden_forever.

S4 risk_events builder

Use own-platform adSelfLandingView and adSelfLandingHide on same day, mid, sessionid, subsessionid, pqtid; require 0 <= hide_ts-view_ts <= 10000; exclude pqtid present in ad_own_open_conv; require later same-session useractive_log.path in the four documented landing paths. Set event_ts=hide_ts.

Types: ${type_1}=adlanding10s_hide_no_open_return_risk_1_forbidden_3, ${type_2}=adlanding10s_hide_no_open_return_risk_2_forbidden_30, ${type_3}=adlanding10s_hide_no_open_return_risk_3_forbidden_forever.

Shared-table write behavior

For a target table with type, mid, uid, risk_level, use INSERT OVERWRITE only after unioning: unrelated existing types, existing permanent type-3 users, newly computed type-3 users, and current dynamic type-1/type-2 users excluding permanent mids. Do not retain expired type-1/type-2 rows.

sql-templates.md 5.1 KB Trvalý odkaz Historie Surový