要求不改变输出结果的情况下 将下列语句修改的规范一些： SELECTPROV_DESCCOUNTDISTINCT CASE WHEN AIS_INNET = ‘1’ AND ADAY_ID = ‘21’ AND AUSER_ID IS NOT NULL THEN AUSER_ID ENDCOUNTDISTINCT CASE WHEN AIS_THIS_DEV = ‘1’ AND ADAY_ID =

要求修改如下：

SELECT PROV_DESC, COUNT(DISTINCT CASE WHEN A.IS_INNET = '1' AND A.DAY_ID = '21' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.DAY_ID = '21' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.DAY_ID = '21' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' and day_id >= '1' and day_id <= '19' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_INNET = '1' AND A.DAY_ID = '19' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.DAY_ID = '19' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.DAY_ID = '19' AND A.USER_ID IS NOT NULL THEN A.USER_ID END), COUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END) FROM ( 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' UNION ALL 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 INNER JOIN ( SELECT USER_ID FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE MONTH_ID = '202307' AND DAY_ID = '21' 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) >= '20230721' AND SUBSTRING(start_date, 1, 8) <= '20230721' UNION ALL SELECT USER_ID FROM dwd.DWD_D_PRD_CB_USER_PRODUCT WHERE MONTH_ID = '202307' AND DAY_ID = '19' 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) >= '20230719' AND SUBSTRING(start_date, 1, 8) <= '20230719' ) DWD_D_PRD_CB_USER_PRODUCT ON A.USER_ID = DWD_D_PRD_CB_USER_PRODUCT.USER_ID LEFT JOIN DIM_PROV B ON A.PROV_ID = B.PROV_ID GROUP BY PROV_DESC ORDER 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 23 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)