WITH low_frequency_dim_poi AS 
(
    SELECT  a.ds
            ,a.poiid
            ,poi_name
            ,atag_id
            ,tag1
            ,tag2
            ,tag3
            ,tag4
            ,l2_srvline
            ,l3_srvline
            ,sale_srvline AS srv_line
            ,key_cate_name
            ,is_ad
            ,is_annual_fee
            ,is_has_sku
            ,is_cooperation
            ,city_name
            ,province_name
            ,district_name
            ,m_is_ad
            ,m_is_new_shop
            ,m_has_product
            ,social_crd_number
            ,is_private_social_crd_number
            ,is_public_social_crd_number
            ,c2.brand_code
            ,c2.brand_name
            ,c2.xy_brand_id
            ,c2.xy_brand_name
            ,CASE   WHEN is_cooperation > 0 THEN '保留-已合作'
                    WHEN is_private_social_crd_number > 0 THEN '保留-统代码'
                    WHEN m_is_ad > 0 THEN '保留-竞对'
                    WHEN is_public_social_crd_number > 0 THEN '清洗-统代码' --口腔&妇产&眼科
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '[省市区县\d]+(口腔医院|眼科医院|妇产医院|儿童医院)|妇幼保健|妇婴保健|[省市区县\d]+妇女儿童' THEN '清洗-关键词' --中医
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '[省市区县\d]+中医院|[省市区县\d]+中医医院|第[一二三四五六七八九十\d]+中医院|第[一二三四五六七八九十\d]+中医医院' THEN '清洗-关键词' --医疗 其他
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '人民医院|市立|省立|中西医结合医院|社区卫生服务站|社区卫生服务中心|社区门诊|防治所|公交站|停车场|校医院|大学|附属|门诊楼|急诊|住院楼|武警|解放军|战区|[空陆\d]+军医院|[空陆\d]+军特色医学中心|机关医院|市医院|区医院|第[一二三四五六七八九十\d]+医院|总医院|中心医院|[省市\d]+红十字会医院|[省市\d]+精神卫生中心|[省市\d]+公共卫生临床中心' THEN '清洗-关键词' --教培
                    WHEN l3_srvline IN ('公私立学校','幼儿教育') AND poi_name RLIKE '小学|初中|中学|附中|高中|大学|学院|附属|第[一二三四五六七八九十\d]+中学|第[一二三四五六七八九十\d]+小学|第[一二三四五六七八九十\d]+幼儿园|实验|师范|公学|初级|高级|外国语|初中部|高中部|机关|部队|教育部门|公立幼儿园|幼儿部' THEN '清洗-关键词'
                    ELSE '保留'
            END AS cleansing_tag
            ,AVG(日均泛搜含品牌搜PV) AS 日均泛搜含品牌搜PV
            ,AVG(日均泛搜含品牌搜PV_在投) AS 日均泛搜含品牌搜PV_在投
            ,AVG(日均泛搜含品牌搜PV_已合作) AS 日均泛搜含品牌搜PV_已合作
            ,AVG(详情页PV) AS 详情页PV
            ,AVG(详情页PV_在投) AS 详情页PV_在投
            ,AVG(详情页PV_已合作) AS 详情页PV_已合作
    FROM    (
                SELECT  poiid
                        ,is_ad
                        ,is_annual_fee
                        ,is_has_sku
                        ,biz_channel
                        ,CASE   WHEN COALESCE(is_ad,'0') + COALESCE(is_annual_fee,'0') + COALESCE(is_has_sku,'0') > 0 AND biz_channel = '1' THEN '1'
                                ELSE '0'
                        END AS is_cooperation
                        ,ds
                FROM    autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di
                WHERE   ds = MAX_PT('autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di')
            ) a
    LEFT JOIN   (
                    SELECT  poi_id
                            ,ds
                            ,MAX(social_crd_number) AS social_crd_number
                            ,MAX(
                                CASE    WHEN LENGTH(social_crd_number) = 18
                                            AND (
                                                    social_crd_number LIKE '5%'
                                                        OR social_crd_number LIKE '9%'
                                        ) THEN 1 END
                            ) AS is_private_social_crd_number
                            ,MAX(CASE    WHEN LENGTH(social_crd_number) = 18 AND social_crd_number LIKE '1%' THEN 1 END) AS is_public_social_crd_number
                    FROM    gd_info_cdm.dim_gd_info_shop
                    WHERE   ds >= '20260301'
                    GROUP BY poi_id
                             ,ds
                ) b
    ON      a.poiid = b.poi_id
    AND     a.ds = b.ds
    LEFT JOIN   (
                    SELECT  poi_id
                            ,MAX(is_ad) AS m_is_ad
                            ,MAX(is_new_shop) AS m_is_new_shop
                            ,MAX(has_product) AS m_has_product
                    FROM    kbdw.dwd_gd_info_sale_fbc_detail_df
                    WHERE   ds = MAX_PT('kbdw.dwd_gd_info_sale_fbc_detail_df')
                    GROUP BY poi_id
                ) d
    ON      a.poiid = d.poi_id
    LEFT JOIN   (
                    SELECT  ds
                            ,poi_id AS poiid
                            ,atag_id
                            ,city_name
                            ,province_name
                            ,district_name
                            ,kb_brand_name
                            ,brand_name
                            ,poi_name
                    FROM    autonavi_base_sp.ads_gd_info_acn_poi_select_ds
                    WHERE   ds >= '20260301'
                    AND     atag_id IS NOT NULL
                ) c1
    ON      a.poiid = c1.poiid
    AND     a.ds = c1.ds
    LEFT JOIN   (
                    SELECT  ds
                            ,poi_id AS poiid
                            ,brand_code
                            ,brand_name
                            ,xy_brand_id
                            ,xy_brand_name
                    FROM    kbads.ads_gd_info_sale_poi_shop_brand_level_report_ds
                    WHERE   ds >= '20260301'
                ) c2
    ON      a.poiid = c2.poiid
    AND     a.ds = c2.ds
    JOIN    (
                SELECT  0 AS c0
                        ,'低频' AS 高低频
                        ,MAX(srv_line) AS sale_srvline
                        ,MAX(l2_srvline) AS l2_srvline
                        ,MAX(l3_srvline) AS l3_srvline
                        ,MAX(level1_name) AS tag1
                        ,MAX(level2_name) AS tag2
                        ,MAX(level3_name) AS tag3
                        ,MAX(level4_name) AS tag4
                        ,MAX(
                            CASE    WHEN level3_name IN ('口腔医院','口腔门诊') THEN '口腔'
                                    WHEN level3_name IN ('口腔诊所') THEN '口腔'
                                    WHEN atagid IN ('622244','622247') THEN '其他诊所'
                                    WHEN l2_srvline IN ('美业') AND level1_name IN ('丽人') THEN '美业-生美'
                                    WHEN l2_srvline IN ('美业') AND level2_name IN ('医学美容') THEN '美业-医美'
                                    WHEN l2_srvline IN ('美业') AND level2_name IN ('植发') THEN '美业-植发'
                                    WHEN srv_line IN ('医疗','美业') THEN l2_srvline
                                    WHEN level3_name IN ('美术培训') THEN '美术培训'
                                    WHEN l2_srvline IN ('美业') THEN l2_srvline
                                    ELSE l3_srvline
                            END
                        ) AS key_cate_name
                        ,atagid
                        ,MAX(
                            CONCAT(COALESCE(level1_name,'-'),'-',COALESCE(level2_name,'-'),'-',COALESCE(level3_name,'-'),'-',COALESCE(level4_name,'-'))
                        ) AS atagl1234
                FROM    autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                WHERE   modified_date = (
                            SELECT  MAX(modified_date) AS modified_date
                            FROM    autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                        ) 
                AND     srv_line <> '旅行社'
                GROUP BY atagid
            ) b
    ON      c1.atag_id = b.atagid
    LEFT JOIN   (
                    SELECT  poiid
                            ,ds
                            ,SUM(COALESCE(detail_pv,0)) AS 详情页PV
                            ,SUM(CASE    WHEN is_ad > 0 THEN COALESCE(detail_pv,0) ELSE 0 END) AS 详情页PV_在投
                            ,SUM(
                                CASE    WHEN is_ad > 0
                                            OR is_annual_fee > 0
                                            OR is_has_sku > 0 THEN COALESCE(detail_pv,0) ELSE 0 END
                            ) AS 详情页PV_已合作
                            ,SUM(COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0)) AS 日均泛搜含品牌搜PV
                            ,SUM(
                                CASE    WHEN is_ad > 0 THEN COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0) ELSE 0 END
                            ) AS 日均泛搜含品牌搜PV_在投
                            ,SUM(
                                CASE    WHEN is_ad > 0
                                            OR is_annual_fee > 0
                                            OR is_has_sku > 0 THEN COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0) ELSE 0 END
                            ) AS 日均泛搜含品牌搜PV_已合作
                    FROM    autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di
                    WHERE   ds >= '20260201'
                    GROUP BY poiid
                             ,ds
                ) c3
    ON      a.poiid = c3.poiid
    AND     a.ds >= TO_CHAR(DATEADD(TO_DATE(c3.ds,'yyyymmdd'),-6,'dd'),'yyyymmdd')
    AND     a.ds <= c3.ds
    GROUP BY a.ds
             ,a.poiid
             ,poi_name
             ,atag_id
             ,tag1
             ,tag2
             ,tag3
             ,tag4
             ,l2_srvline
             ,l3_srvline
             ,sale_srvline
             ,key_cate_name
             ,is_ad
             ,is_annual_fee
             ,is_has_sku
             ,is_cooperation
             ,city_name
             ,province_name
             ,district_name
             ,m_is_ad
             ,m_is_new_shop
             ,m_has_product
             ,social_crd_number
             ,is_private_social_crd_number
             ,is_public_social_crd_number
             ,c2.brand_code
             ,c2.brand_name
             ,c2.xy_brand_id
             ,c2.xy_brand_name
)
,poi_detail AS 
(
    SELECT  a.ds
            ,a.poiid
            ,a.poi_name
            ,a.atag_id
            ,a.tag1
            ,a.tag2
            ,a.tag3
            ,a.tag4
            ,a.l2_srvline
            ,a.l3_srvline
            ,a.srv_line
            ,a.key_cate_name
            ,a.is_ad
            ,a.is_annual_fee
            ,a.is_has_sku
            ,a.is_cooperation
            ,a.city_name
            ,a.province_name
            ,a.district_name
            ,a.m_is_ad
            ,a.m_is_new_shop
            ,a.m_has_product
            ,a.social_crd_number
            ,a.is_private_social_crd_number
            ,a.is_public_social_crd_number
            ,a.brand_code
            ,a.brand_name
            ,a.xy_brand_id
            ,a.xy_brand_name
            ,a.cleansing_tag
            ,a.日均泛搜含品牌搜PV
            ,a.日均泛搜含品牌搜PV_在投
            ,a.日均泛搜含品牌搜PV_已合作
            ,a.详情页PV
            ,a.详情页PV_在投
            ,a.详情页PV_已合作
            ,累计泛搜含品牌搜pv_by类目 AS 累计泛搜含品牌搜pv_by类目
            ,合计泛搜含品牌搜PV_by类目 AS 合计泛搜含品牌搜PV_by类目
            ,累计泛搜含品牌搜pv_by类目 / 合计泛搜含品牌搜PV_by类目 AS 泛搜含品牌pv_by类目_rn
            ,CEIL(round(累计泛搜含品牌搜pv_by类目 / 合计泛搜含品牌搜PV_by类目 * 10,4)) / 10 AS 泛搜含品牌pv_by类目_分位数
            ,累计详情页PV_by类目 AS 累计详情页PV_by类目
            ,合计详情页PV_by类目 AS 合计详情页PV_by类目
            ,累计详情页PV_by类目 / 合计详情页PV_by类目 AS 详情页PV_by类目_rn
            ,CEIL(round(累计详情页PV_by类目 / 合计详情页PV_by类目 * 10,4)) / 10 AS 详情页PV_by类目_分位数
    FROM    (
                SELECT  a.*
                        ,SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY srv_line,l2_srvline,key_cate_name ORDER BY 日均泛搜含品牌搜PV DESC,poiid ) 累计泛搜含品牌搜pv_by类目
                        ,SUM(详情页PV) OVER (PARTITION BY srv_line,l2_srvline,key_cate_name ORDER BY 详情页PV DESC,poiid ) 累计详情页PV_by类目
                FROM    low_frequency_dim_poi a
                WHERE   cleansing_tag LIKE '%保留%'
            ) a
    LEFT JOIN   (
                    SELECT  srv_line
                            ,l2_srvline
                            ,key_cate_name
                            ,SUM(日均泛搜含品牌搜PV) AS 合计泛搜含品牌搜PV_by类目
                            ,SUM(详情页PV) AS 合计详情页PV_by类目
                    FROM    low_frequency_dim_poi
                    WHERE   cleansing_tag LIKE '%保留%'
                    GROUP BY srv_line
                             ,l2_srvline
                             ,key_cate_name
                ) b
    ON      a.srv_line = b.srv_line
    AND     a.key_cate_name = b.key_cate_name
    AND     a.l2_srvline = b.l2_srvline
)
,city_detail AS 
(
    SELECT      --维度：shopid*ds
            a.ds AS 日期
            ,a.city_name
            ,a.province_name
            ,COALESCE(city_tier,'C档') AS city_tier
            ,CASE   WHEN city_tier IN ('S档','A档','B档') THEN 'SAB档'
                    ELSE 'C档'
            END AS 城市分档
            ,srv_line
            ,key_cate_name AS 重点类目 
            -- ,CASE   WHEN key_cate_name LIKE '%口腔%' THEN '口腔'
            --         WHEN key_cate_name IN ('妇产','皮肤科','肛肠','眼科','综合医疗','美业-医美','美业-植发','中医') THEN key_cate_name
            --         WHEN key_cate_name IN ('驾驶培训','文印广告','亲子玩乐') THEN key_cate_name
            --         WHEN key_cate_name IN ('课后辅导','留学机构','职业培训','学历提升') THEN key_cate_name
            --         WHEN key_cate_name IN ('美术培训','舞蹈培训','运动培训','音乐培训') THEN '亲子教育-四大类目'
            --         ELSE '其他'
            -- END AS 重点类目
            ,COUNT(DISTINCT a.poiid) AS leads数
            ,COUNT(DISTINCT CASE    WHEN m_is_ad > 0
                        OR m_is_new_shop > 0
                        OR m_has_product > 0 THEN a.poiid END) AS M在线poi数
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 THEN a.poiid END) AS 合作poi数
            ,COUNT(DISTINCT CASE    WHEN is_ad > 0 THEN a.poiid END) AS 在投poi数
            ,COUNT(DISTINCT CASE    WHEN 泛搜含品牌pv_by类目_分位数 <= 0.7 THEN a.poiid END) AS 高流poi个数_70
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND 泛搜含品牌pv_by类目_分位数 <= 0.7 THEN a.poiid END) AS 高流合作poi个数_70
            ,COUNT(DISTINCT CASE    WHEN 详情页PV_by类目_分位数 <= 0.7 THEN a.poiid END) AS 高流poi个数_70_dpv
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND 详情页PV_by类目_分位数 <= 0.7 THEN a.poiid END) AS 高流合作poi个数_70_dpv
            ,SUM(日均泛搜含品牌搜PV) AS 日均泛搜含品牌搜PV
            ,SUM(日均泛搜含品牌搜PV_在投) AS 日均泛搜含品牌搜PV_在投
            ,SUM(日均泛搜含品牌搜PV_已合作) AS 日均泛搜含品牌搜PV_已合作
            ,SUM(详情页PV) AS 详情页PV
            ,SUM(详情页PV_在投) AS 详情页PV_在投
            ,SUM(详情页PV_已合作) AS 详情页PV_已合作
            ,SUM(日均泛搜含品牌搜PV_已合作) / SUM(日均泛搜含品牌搜PV) AS 城市年费填充率_泛搜pv
            ,SUM(日均泛搜含品牌搜PV_在投) / SUM(日均泛搜含品牌搜PV) AS 城市广告填充率_泛搜pv
            ,SUM(日均泛搜含品牌搜PV_在投) / SUM(日均泛搜含品牌搜PV_已合作) AS 城市在投率_泛搜pv
            ,SUM(详情页PV_已合作) / SUM(详情页PV) AS 城市年费填充率_dpv
            ,SUM(详情页PV_在投) / SUM(详情页PV) AS 城市广告填充率_dpv
            ,SUM(详情页PV_在投) / SUM(详情页PV_已合作) AS 城市在投率_dpv
    FROM    poi_detail a
    LEFT JOIN   (
                    SELECT  DISTINCT city_name
                            ,city_tier
                    FROM    kbreport.city_nf_tier
                    WHERE   district_name <> '其他行政区'
                ) t3
    ON      t3.city_name = a.city_name
    GROUP BY a.ds
             ,a.city_name
             ,a.province_name
             ,city_tier
             ,key_cate_name
             ,srv_line
)
SELECT  *
        ,ROW_NUMBER() OVER (PARTITION BY 重点类目 ORDER BY 日均泛搜含品牌搜PV DESC ) AS 同类目城市流量排名
        ,ROW_NUMBER() OVER (PARTITION BY 城市分档,重点类目 ORDER BY 日均泛搜含品牌搜PV DESC ) AS 同类目同档位城市流量排名
        ,ROW_NUMBER() OVER (PARTITION BY 城市分档,重点类目 ORDER BY 城市年费填充率_泛搜pv DESC ) AS 同类目同档位城市年费填充率排名
        ,ROW_NUMBER() OVER (PARTITION BY 城市分档,重点类目 ORDER BY 城市广告填充率_泛搜pv DESC ) AS 同类目同档位城市广告填充率排名
        ,SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均年费填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均广告填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均在投率_泛搜pv
        ,SUM(详情页PV_已合作) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(详情页PV) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均年费填充率_dpv
        ,SUM(详情页PV_在投) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(详情页PV) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均广告填充率_dpv
        ,SUM(详情页PV_在投) OVER (PARTITION BY 城市分档,重点类目 ) / SUM(详情页PV_已合作) OVER (PARTITION BY 城市分档,重点类目 ) AS 同类目同档位城市平均在投率_dpv
FROM    city_detail a



--@exclude_input=autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di
--@exclude_input=autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
--MaxCompute SQL
--********************************************************************--
--author: 飘有
--create time: 2026-03-20 14:45:20
--********************************************************************--
insert overwrite table kbreport.dws_gd_poi_detail_ds partition(ds = '${bizdate}')
WITH low_frequency_dim_poi AS 
(
    SELECT  c1.ds
            ,c1.poiid
            ,poi_name
            ,atag_id
            ,tag1
            ,tag2
            ,tag3
            ,tag4
            ,l2_srvline
            ,l3_srvline
            ,sale_srvline AS srv_line
            ,key_cate_name
            ,COALESCE(is_ad,0) as is_ad
            ,COALESCE(is_annual_fee,0) as is_annual_fee
            ,COALESCE(is_has_sku,0) as is_has_sku
            ,COALESCE(is_cooperation,0) as is_cooperation
            ,city_name
            ,province_name
            ,district_name
            ,m_is_ad
            ,m_is_new_shop
            ,m_has_product
            ,social_crd_number
            ,is_private_social_crd_number
            ,is_public_social_crd_number
            ,c2.brand_code
            ,c2.brand_name
            ,c2.xy_brand_id
            ,c2.xy_brand_name
            ,leads_entity_id
            ,l.leads_id
            ,leads_status
            ,has_phone
            ,value_star
            ,leads_label_name
            ,employee_no
            ,employee_name
            ,team_type
            ,l2_dept_name
            ,l3_dept_name
            ,l4_dept_name
            ,is_work_protect
            ,COALESCE(ltv_180,0)*1.0 as ltv_180
            ,CASE   WHEN is_cooperation > 0 THEN '保留-已合作'
                    WHEN is_private_social_crd_number > 0 THEN '保留-统代码'
                    WHEN m_is_ad > 0 THEN '保留-竞对'
                    WHEN is_public_social_crd_number > 0 THEN '清洗-统代码' --口腔&妇产&眼科
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '[省市区县\d]+(口腔医院|眼科医院|妇产医院|儿童医院)|妇幼保健|妇婴保健|[省市区县\d]+妇女儿童' THEN '清洗-关键词' --中医
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '[省市区县\d]+中医院|[省市区县\d]+中医医院|第[一二三四五六七八九十\d]+中医院|第[一二三四五六七八九十\d]+中医医院' THEN '清洗-关键词' --医疗 其他
                    WHEN sale_srvline IN ('医疗') AND poi_name RLIKE '人民医院|市立|省立|中西医结合医院|社区卫生服务站|社区卫生服务中心|社区门诊|防治所|公交站|停车场|校医院|大学|附属|门诊楼|急诊|住院楼|武警|解放军|战区|[空陆\d]+军医院|[空陆\d]+军特色医学中心|机关医院|市医院|区医院|第[一二三四五六七八九十\d]+医院|总医院|中心医院|[省市\d]+红十字会医院|[省市\d]+精神卫生中心|[省市\d]+公共卫生临床中心' THEN '清洗-关键词' --教培
                    WHEN l3_srvline IN ('公私立学校','幼儿教育') AND poi_name RLIKE '小学|初中|中学|附中|高中|大学|学院|附属|第[一二三四五六七八九十\d]+中学|第[一二三四五六七八九十\d]+小学|第[一二三四五六七八九十\d]+幼儿园|实验|师范|公学|初级|高级|外国语|初中部|高中部|机关|部队|教育部门|公立幼儿园|幼儿部' THEN '清洗-关键词'
                    ELSE '保留'
            END AS cleansing_tag
            ,AVG(日均泛搜含品牌搜PV) AS 日均泛搜含品牌搜PV
            ,AVG(日均泛搜含品牌搜PV_在投) AS 日均泛搜含品牌搜PV_在投
            ,AVG(日均泛搜含品牌搜PV_已合作) AS 日均泛搜含品牌搜PV_已合作
            ,AVG(详情页PV) AS 详情页PV
            ,AVG(详情页PV_在投) AS 详情页PV_在投
            ,AVG(详情页PV_已合作) AS 详情页PV_已合作
            ,MAX(is_ad_30d) AS is_ad_30d
    FROM    (
                    SELECT  ds
                            ,poi_id AS poiid
                            ,atag_id
                            ,city_name
                            ,province_name
                            ,district_name
                            ,kb_brand_name
                            ,brand_name
                            ,poi_name
                    FROM    autonavi_base_sp.ads_gd_info_acn_poi_select_ds
                    WHERE   ds = '${bizdate}'
                    AND     atag_id IS NOT NULL
                ) c1 
    left join (
                SELECT  poiid
                        ,is_ad
                        ,is_annual_fee
                        ,is_has_sku
                        ,biz_channel
                        ,CASE   WHEN COALESCE(is_ad,'0') + COALESCE(is_annual_fee,'0') + COALESCE(is_has_sku,'0') > 0 AND biz_channel = '1' THEN '1'
                                ELSE '0'
                        END AS is_cooperation
                        ,ds
                FROM    autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di
                WHERE   ds = '${bizdate}'
            ) a
    ON      a.poiid = c1.poiid
    AND     a.ds = c1.ds
    LEFT JOIN   (
                    SELECT  poi_id
                            ,ds
                            ,MAX(social_crd_number) AS social_crd_number
                            ,MAX(
                                CASE    WHEN LENGTH(social_crd_number) = 18
                                            AND (
                                                    social_crd_number LIKE '5%'
                                                        OR social_crd_number LIKE '9%'
                                        ) THEN 1 END
                            ) AS is_private_social_crd_number
                            ,MAX(CASE    WHEN LENGTH(social_crd_number) = 18 AND social_crd_number LIKE '1%' THEN 1 END) AS is_public_social_crd_number
                    FROM    gd_info_cdm.dim_gd_info_shop
                    WHERE   ds = '${bizdate}'
                    GROUP BY poi_id
                             ,ds
                ) b
    ON      c1.poiid = b.poi_id
    AND     c1.ds = b.ds
    LEFT JOIN   (
                    SELECT  poi_id
                            ,MAX(is_ad) AS m_is_ad
                            ,MAX(is_new_shop) AS m_is_new_shop
                            ,MAX(has_product) AS m_has_product
                    FROM    kbdw.dwd_gd_info_sale_fbc_detail_df
                    WHERE   ds = '${bizdate}'
                    GROUP BY poi_id
                ) d
    ON      c1.poiid = d.poi_id
    left join (
            SELECT  poi_id
                    ,leads_id
                    ,has_contact_no
                    ,has_phone
                    ,value_star --算法星级
                    ,leads_label_code
                    ,leads_label_name
                    ,ds
            FROM    kbdw.dim_gd_info_leads
            WHERE   ds = '${bizdate}'
            AND     is_deleted = 0
            AND     effective = 1
            and atag_srvline IN ('美业','低频','医疗','教培')
    )l ON      c1.poiid = l.poi_id
    AND     c1.ds = l.ds
    LEFT JOIN   (
                SELECT  leads_entity_id
                        ,biz_id
                        ,leads_status
                        ,owner_id
                        ,gmt_create
                        ,gmt_expire
                        ,gmt_claim
                        ,latest_gmt_claim
                        ,latest_gmt_release
                        ,claim_cnt
                        ,release_cnt
                        ,d.employee_no
                        ,d.employee_name
                        ,d.team_type
                        ,l2_dept_name
                        ,l3_dept_name
                        ,l4_dept_name
                        ,c1.ds
                FROM    kbdw.dwd_gd_info_sale_leads_create_claim_df c1
                LEFT JOIN kbdw.dim_gd_sale_employee_bloc d
                ON      c1.owner_id = d.os_user_id
                AND     d.ds = c1.ds
                WHERE   c1.ds  = '${bizdate}'
            ) l2
    ON      l2.biz_id = l.leads_id
    AND     l2.ds = l.ds
    LEFT JOIN   (
                    SELECT  ds
                            ,poi_id AS poiid
                            ,brand_code
                            ,brand_name
                            ,xy_brand_id
                            ,xy_brand_name
                            ,is_work_protect
                    FROM    kbads.ads_gd_info_sale_poi_shop_brand_level_report_ds
                    WHERE   ds = '${bizdate}'
                ) c2
    ON      c1.poiid = c2.poiid
    AND     c1.ds = c2.ds
    LEFT JOIN   (
                    SELECT  poiid
                            ,max(预估180天消耗) as ltv_180
                    FROM    autonavi_base_sp.dm_dipin_brand_leads_all_value_detail_pred_ltv180d_202508
                    group by poiid
                ) c4
    ON      c1.poiid = c4.poiid
    JOIN    (
                SELECT  0 AS c0
                        ,'低频' AS 高低频
                        ,MAX(srv_line) AS sale_srvline
                        ,MAX(l1_srvline) AS l2_srvline
                        ,MAX(l2_srvline) AS l3_srvline
                        ,MAX(l3_srvline) AS l4_srvline
                        ,MAX(level1_name) AS tag1
                        ,MAX(level2_name) AS tag2
                        ,MAX(level3_name) AS tag3
                        ,MAX(level4_name) AS tag4
                        ,MAX(
                            CASE    
                                    WHEN l2_srvline IN ('素质教育')  THEN l3_srvline
                                    ELSE l2_srvline
                            END
                        ) AS key_cate_name
                        ,atagid
                        ,MAX(
                            CONCAT(COALESCE(level1_name,'-'),'-',COALESCE(level2_name,'-'),'-',COALESCE(level3_name,'-'),'-',COALESCE(level4_name,'-'))
                        ) AS atagl1234
                FROM    autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                WHERE   modified_date = (
                            SELECT  MAX(modified_date) AS modified_date
                            FROM    autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                        ) 
                AND     srv_line <> '旅行社'
                GROUP BY atagid
            ) b
    ON      c1.atag_id = b.atagid
    LEFT JOIN   (
                    SELECT  poiid
                            ,ds
                            ,SUM(COALESCE(detail_pv,0)) AS 详情页PV
                            ,SUM(CASE    WHEN is_ad > 0 THEN COALESCE(detail_pv,0) ELSE 0 END) AS 详情页PV_在投
                            ,SUM(
                                CASE    WHEN is_ad > 0
                                            OR is_annual_fee > 0
                                            OR is_has_sku > 0 THEN COALESCE(detail_pv,0) ELSE 0 END
                            ) AS 详情页PV_已合作
                            ,SUM(COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0)) AS 日均泛搜含品牌搜PV
                            ,SUM(
                                CASE    WHEN is_ad > 0 THEN COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0) ELSE 0 END
                            ) AS 日均泛搜含品牌搜PV_在投
                            ,SUM(
                                CASE    WHEN is_ad > 0
                                            OR is_annual_fee > 0
                                            OR is_has_sku > 0 THEN COALESCE(entrance_show_pv_pps,0) + COALESCE(entrance_show_pv_fs,0) ELSE 0 END
                            ) AS 日均泛搜含品牌搜PV_已合作
                            ,max(is_ad) as is_ad_30d
                    FROM    autonavi_base.dws_gd_info_localservice_poi_wide_index_overview_di
                    WHERE   ds >= '20260201'
                    GROUP BY poiid
                             ,ds
                ) c3
    ON      c1.poiid = c3.poiid
    AND     c1.ds >= TO_CHAR(DATEADD(TO_DATE(c3.ds,'yyyymmdd'),-29,'dd'),'yyyymmdd')
    AND     c1.ds <= c3.ds
    GROUP BY c1.ds
             ,c1.poiid
             ,poi_name
             ,atag_id
             ,tag1
             ,tag2
             ,tag3
             ,tag4
             ,l2_srvline
             ,l3_srvline
             ,sale_srvline
             ,key_cate_name
             ,is_ad
             ,is_annual_fee
             ,is_has_sku
             ,is_cooperation
             ,city_name
             ,province_name
             ,district_name
             ,m_is_ad
             ,m_is_new_shop
             ,m_has_product
             ,social_crd_number
             ,is_private_social_crd_number
             ,is_public_social_crd_number
             ,c2.brand_code
             ,c2.brand_name
             ,c2.xy_brand_id
             ,c2.xy_brand_name
            ,leads_entity_id
            ,l.leads_id
            ,leads_status
            ,has_phone
            ,value_star
            ,leads_label_name
            ,employee_no
            ,employee_name
            ,team_type
            ,l2_dept_name
            ,l3_dept_name
            ,l4_dept_name
            ,is_work_protect
            ,ltv_180
)
SELECT  a.poiid
        ,a.poi_name
        ,leads_entity_id
        ,leads_id
        ,leads_status
        ,has_phone
        ,value_star
        ,leads_label_name
        ,employee_no as  owner_employee_no
        ,employee_name  as  owner_employee_name
        ,team_type  as  owner_team_type
        ,l2_dept_name  as  owner_l2_dept_name
        ,l3_dept_name  as  owner_l3_dept_name
        ,l4_dept_name  as  owner_l4_dept_name
        ,a.atag_id
        ,a.tag1
        ,a.tag2
        ,a.tag3
        ,a.tag4
        ,a.l2_srvline
        ,a.l3_srvline
        ,a.srv_line
        ,a.key_cate_name
        ,a.is_ad
        ,a.is_annual_fee
        ,a.is_has_sku
        ,a.is_cooperation
        ,a.city_name
        ,a.province_name
        ,a.district_name
        ,a.m_is_ad
        ,a.m_is_new_shop
        ,a.m_has_product
        ,a.social_crd_number
        ,a.is_private_social_crd_number
        ,a.is_public_social_crd_number
        ,a.brand_code
        ,a.brand_name
        ,a.xy_brand_id
        ,a.xy_brand_name
        ,a.cleansing_tag
        ,a.日均泛搜含品牌搜PV as entrance_show_pv_fs
        ,a.日均泛搜含品牌搜PV_在投  as entrance_show_pv_fs_ading
        ,a.日均泛搜含品牌搜PV_已合作  as entrance_show_pv_fs_annual_fee
        ,a.详情页PV as detail_pv
        ,a.详情页PV_在投 as detail_pv_ading
        ,a.详情页PV_已合作 as detail_pv_annual_fee
        ,CEIL(round(累计泛搜含品牌搜pv_by类目 / 合计泛搜含品牌搜PV_by类目 * 10,4)) / 10 AS quantile_entrance_show_pv_fs_bycate
        ,CEIL(round(累计详情页PV_by类目 / 合计详情页PV_by类目 * 10,4)) / 10 AS quantile_detail_pv_bycate
        ,is_ad_30d
        ,is_work_protect
        ,a.ltv_180
        ,CEIL(round(累计LTV_by类目 / 合计LTV_by类目 * 10,4)) / 10 AS quantile_ltv_bycate
FROM    (
            SELECT  a.*
            FROM    low_frequency_dim_poi a
        ) a
LEFT JOIN   (
            SELECT  a.poiid
                    ,SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY srv_line,l2_srvline,key_cate_name ORDER BY 日均泛搜含品牌搜PV DESC,poiid ) 累计泛搜含品牌搜pv_by类目
                    ,SUM(详情页PV) OVER (PARTITION BY srv_line,l2_srvline,key_cate_name ORDER BY 详情页PV DESC,poiid ) 累计详情页PV_by类目
                    ,SUM(ltv_180) OVER (PARTITION BY srv_line,l2_srvline,key_cate_name ORDER BY ltv_180 DESC,poiid ) 累计LTV_by类目
            FROM    low_frequency_dim_poi a
            WHERE   cleansing_tag LIKE '%保留%'
        ) a1 
on a.poiid = a1.poiid        
LEFT JOIN   (
                SELECT  srv_line
                        ,l2_srvline
                        ,key_cate_name
                        ,SUM(日均泛搜含品牌搜PV) AS 合计泛搜含品牌搜PV_by类目
                        ,SUM(详情页PV) AS 合计详情页PV_by类目
                        ,SUM(ltv_180) AS 合计LTV_by类目
                FROM    low_frequency_dim_poi
                WHERE   cleansing_tag LIKE '%保留%'
                GROUP BY srv_line
                         ,l2_srvline
                         ,key_cate_name
            ) b
ON      a.srv_line = b.srv_line
AND     a.key_cate_name = b.key_cate_name
AND     a.l2_srvline = b.l2_srvline