2023年7月各省份用户发展情况统计

SELECT\n\tb.prov_desc,\n\tNVL(a.innet_count,0) AS innet_count,\n\tNVL(a.day_count,0) AS day_count,\n\tNVL(a.month_count,0) AS month_count,\n\tNVL(c.last_month_dev_count,0) AS last_month_dev_count,\n\tNVL(a.day_break_count,0) AS day_break_count,\n\tNVL(a.month_break_count,0) AS month_break_count,\n\tNVL(d.innet_count_zq,0) AS innet_count_zq,\n\tNVL(d.day_count_zq,0) AS day_count_zq,\n\tNVL(d.month_count_zq,0) AS month_count_zq,\n\tNVL(e.last_month_dev_count_zq,0) AS last_month_dev_count_zq,\n\tNVL(d.day_break_count_zq,0) AS day_break_count_zq,\n\tNVL(d.month_break_count_zq,0) AS month_break_count_zq\nFROM\n\t(SELECT prov_id, prov_desc FROM DIM_PROV) b\nLEFT JOIN\n\t(SELECT\n\t a.prov_id,\n\t COUNT(DISTINCT CASE WHEN a.is_innet = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS innet_count,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS day_count,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND a.user_id IS NOT NULL THEN a.user_id END) AS month_count,\n\t COUNT(DISTINCT CASE WHEN a.is_this_break = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS day_break_count,\n\t COUNT(DISTINCT CASE WHEN a.is_this_break = '1' AND a.user_id IS NOT NULL THEN a.user_id END) AS month_break_count\n\tFROM\n\t (SELECT user_id, prov_id, product_id, day_id, is_innet, is_this_dev, is_this_break FROM zq_dwa.dwa_d_cus_al_cb_user_info WHERE month_id = '202307') a\n\tINNER JOIN\n\t (SELECT user_id FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE month_id = '202307' AND day_id = '24' AND product_id IN (SELECT product_id FROM dim_yx_5gepn_0708 UNION ALL SELECT b.product_id_b FROM (SELECT product_id, prov_id FROM dwd.DWD_D_PRD_CB_COMP_PRODUCT WHERE relation_type_code IN ('25', '28') AND month_id = '202306' AND day_id = '30') a LEFT JOIN (SELECT DISTINCT product_id_a, product_id_b FROM dwd.DWD_M_PRD_CB_PRODUCTLIMIT WHERE month_id = '202306') b ON b.product_id_a = a.product_id) AND SUBSTRING(end_date, 0, 8) >= '20230724' AND SUBSTRING(start_date, 0, 8) <= '20230724') dwd_d_prd_cb_user_product ON a.user_id = dwd_d_prd_cb_user_product.user_id\n\tLEFT JOIN DIM_PROV b ON a.prov_id = b.prov_id\n\tGROUP BY a.prov_id) a ON a.prov_id = b.prov_id\nLEFT JOIN\n\t(SELECT\n\t a.prov_id,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND day_id >= '1' AND day_id <= '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS last_month_dev_count\n\tFROM\n\t (SELECT user_id, prov_id, product_id, day_id, is_innet, is_this_dev, is_this_break FROM zq_dwa.dwa_d_cus_al_cb_user_info WHERE month_id = '202306') a\n\tINNER JOIN\n\t (SELECT user_id FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE month_id = '202307' AND day_id = '24' AND product_id IN (SELECT product_id FROM dim_yx_5gepn_0708 UNION ALL SELECT b.product_id_b FROM (SELECT product_id, prov_id FROM dwd.DWD_D_PRD_CB_COMP_PRODUCT WHERE relation_type_code IN ('25', '28') AND month_id = '202306' AND day_id = '30') a LEFT JOIN (SELECT DISTINCT product_id_a, product_id_b FROM dwd.DWD_M_PRD_CB_PRODUCTLIMIT WHERE month_id = '202306') b ON b.product_id_a = a.product_id) AND SUBSTRING(end_date, 1, 8) >= '20230624' AND SUBSTRING(start_date, 1, 8) <= '20230624') dwd_d_prd_cb_user_product ON a.user_id = dwd_d_prd_cb_user_product.user_id\n\tLEFT JOIN DIM_PROV b ON a.prov_id = b.prov_id\n\tGROUP BY a.prov_id) c ON c.prov_id = b.prov_id\nLEFT JOIN\n\t(SELECT\n\t a.prov_id,\n\t COUNT(DISTINCT CASE WHEN a.is_innet = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS innet_count_zq,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS day_count_zq,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND a.user_id IS NOT NULL THEN a.user_id END) AS month_count_zq,\n\t COUNT(DISTINCT CASE WHEN a.is_this_break = '1' AND a.day_id = '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS day_break_count_zq,\n\t COUNT(DISTINCT CASE WHEN a.is_this_break = '1' AND a.user_id IS NOT NULL THEN a.user_id END) AS month_break_count_zq\n\tFROM\n\t (SELECT user_id, prov_id, product_id, day_id, is_innet, is_this_dev, is_this_break FROM zq_dwa.dwa_d_cus_al_cb_user_info WHERE month_id = '202307' AND is_zq_develop = '1') a\n\tINNER JOIN\n\t (SELECT user_id FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE month_id = '202307' AND day_id = '24' AND product_id IN (SELECT product_id FROM dim_yx_5gepn_0708 UNION ALL SELECT b.product_id_b FROM (SELECT product_id, prov_id FROM dwd.DWD_D_PRD_CB_COMP_PRODUCT WHERE relation_type_code IN ('25', '28') AND month_id = '202306' AND day_id = '30') a LEFT JOIN (SELECT DISTINCT product_id_a, product_id_b FROM dwd.DWD_M_PRD_CB_PRODUCTLIMIT WHERE month_id = '202306') b ON b.product_id_a = a.product_id) AND SUBSTRING(end_date, 0, 8) >= '20230724' AND SUBSTRING(start_date, 0, 8) <= '20230724') dwd_d_prd_cb_user_product ON a.user_id = dwd_d_prd_cb_user_product.user_id\n\tLEFT JOIN DIM_PROV b ON a.prov_id = b.prov_id\n\tGROUP BY a.prov_id) d ON d.prov_id = b.prov_id\nLEFT JOIN\n\t(SELECT\n\t a.prov_id,\n\t COUNT(DISTINCT CASE WHEN a.is_this_dev = '1' AND day_id >= '1' AND day_id <= '24' AND a.user_id IS NOT NULL THEN a.user_id END) AS last_month_dev_count_zq\n\tFROM\n\t (SELECT user_id, prov_id, product_id, day_id, is_innet, is_this_dev, is_this_break FROM zq_dwa.dwa_d_cus_al_cb_user_info WHERE month_id = '202306' AND is_zq_develop = '1') a\n\tINNER JOIN\n\t (SELECT user_id FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE month_id = '202307' AND day_id = '24' AND product_id IN (SELECT product_id FROM dim_yx_5gepn_0708 UNION ALL SELECT b.product_id_b FROM (SELECT product_id, prov_id FROM dwd.DWD_D_PRD_CB_COMP_PRODUCT WHERE relation_type_code IN ('25', '28') AND month_id = '202306' AND day_id = '30') a LEFT JOIN (SELECT DISTINCT product_id_a, product_id_b FROM dwd.DWD_M_PRD_CB_PRODUCTLIMIT WHERE month_id = '202306') b ON b.product_id_a = a.product_id) AND SUBSTRING(end_date, 1, 8) >= '20230624' AND SUBSTRING(start_date, 1, 8) <= '20230624') dwd_d_prd_cb_user_product ON a.user_id = dwd_d_prd_cb_user_product.user_id\n\tLEFT JOIN DIM_PROV b ON a.prov_id = b.prov_id\n\tGROUP BY a.prov_id) e ON e.prov_id = b.prov_id\nORDER BY (CASE WHEN prov_desc = '山东' THEN 1 WHEN prov_desc = '河南' THEN 2 WHEN prov_desc = '江苏' THEN 3 WHEN prov_desc = '湖北' THEN 4 WHEN prov_desc = '湖南' THEN 5 WHEN prov_desc = '海南' THEN 6 WHEN prov_desc = '重庆' THEN 7 WHEN prov_desc = '四川' THEN 8 WHEN prov_desc = '贵州' THEN 9 WHEN prov_desc = '北京' THEN 10 WHEN prov_desc = '山西' THEN 11 WHEN prov_desc = '浙江' THEN 12 WHEN prov_desc = '广东' THEN 13 WHEN prov_desc = '西藏' THEN 14 WHEN prov_desc = '甘肃' THEN 15 WHEN prov_desc = '新疆' THEN 16 WHEN prov_desc = '江西' THEN 17 WHEN prov_desc = '云南' THEN 18 WHEN prov_desc = '陕西' THEN 19 WHEN prov_desc = '青海' THEN 20 WHEN prov_desc = '宁夏' THEN 21 WHEN prov_desc = '天津' THEN 22 WHEN prov_desc = '内蒙古' THEN 24 WHEN prov_desc = '上海' THEN 24 WHEN prov_desc = '福建' THEN 25 WHEN prov_desc = '广西' THEN 26 WHEN prov_desc = '河北' THEN 27 WHEN prov_desc = '辽宁' THEN 28 WHEN prov_desc = '吉林' THEN 29 WHEN prov_desc = '黑龙江' THEN 30 WHEN prov_desc = '安徽' THEN 31 END);