人群包穿透 AT 服务 · Service Click Rate & SP Order Rate（终版）

SELECT DISTINCT guid
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '2026-06-06' AND '2026-06-13'
  AND type='AT' AND scene='Popup'
  AND get_json_object(extra,'$.at_high_value_package_user')='true'
  AND get_json_object(extra,'$.at_high_value_package_user_style')='6.30.10-2'
  AND get_json_object(extra,'$.lever.at_popup')='Off'                       -- ★真穿透
  AND (CAST(split_part(version,'.',1) AS INT)*1000000
     + CAST(split_part(version,'.',2) AS INT)*1000
     + CAST(split_part(version,'.',3) AS INT)) >= 6030010;                  -- ★≥6.30.10

SELECT DISTINCT z.guid FROM (
  SELECT ns.guid, get_json_object(ns.extra,'$.reason') AS reason
  FROM v_dwd_cp_log_impress_other_log_di ns
  INNER JOIN (SELECT DISTINCT guid FROM dws_dmp_guid_group_di
              WHERE group_id=100000414) p ON p.guid=ns.guid                  -- 先join人群包再解析JSON(避免209万行/天全表JSON超时)
  WHERE ns.source=1 AND ns.pt BETWEEN '2026-06-06' AND '2026-06-13'
    AND ns.action='notshow' AND ns.category='at_popup'
    AND ns.version IN ('6.30.12','6.30.16','6.30.19','6.30.22','6.30.49','6.30.69')  -- ≥6.30.10存在版本
) z WHERE z.reason='AT Popup Lever Off';

-- 曝光(分母)；点击(分子)把 impress_kpis→click_kpis / impress_date→click_date
SELECT
  COUNT(DISTINCT ik.guid) AS impr_full,
  COUNT(DISTINCT CASE WHEN ik.domain IN (<144商家>) THEN ik.guid END) AS impr_144
FROM dws_log_domain_guid_extension_impress_kpis_d ik
INNER JOIN (<队列 guid 子查询>) c ON c.guid=ik.guid
WHERE ik.impress_date BETWEEN '2026-06-06' AND '2026-06-13';
-- Service Click Rate = click_uv / impr_uv（全服务，不按 type 过滤）

SELECT
  COUNT(DISTINCT j.guid) AS sp_user_full,                                     -- 分母
  COUNT(DISTINCT CASE WHEN array_length(j.cp_order_ids)>0 THEN j.guid END) AS order_user_full,  -- 分子
  COUNT(DISTINCT CASE WHEN j.domain IN (<144商家>) THEN j.guid END) AS sp_user_144,
  COUNT(DISTINCT CASE WHEN array_length(j.cp_order_ids)>0
                      AND j.domain IN (<144商家>) THEN j.guid END) AS order_user_144
FROM dws_log_sp_user_shopping_journey_detail_di j
INNER JOIN (<队列 guid 子查询>) c ON c.guid=j.guid
WHERE j.pt BETWEEN '2026-06-06' AND '2026-06-13'
  AND j.aff_id > 0 AND j.cps_score > 40;                                      -- ★SP池
-- SP Order Rate = order_user / sp_user

实验穿透商家(distinct domain, lever off) ∩ 对照 leveroff 商家(distinct domain) = 144 交集