SQL查询语句优化：统计不同省份用户数据

SELECT\n\tPROV_DESC,\n\tCOUNT(DISTINCT CASE WHEN A.IS_INNET = '1' AND A.DAY_ID = '21' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n\tCOUNT(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),\n\tCOUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n\tCOUNT(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),\n\tCOUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n\tCOUNT(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),\n\tCOUNT(DISTINCT CASE WHEN A.IS_INNET = '1' AND A.DAY_ID = '19' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n\tCOUNT(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),\n\tCOUNT(DISTINCT CASE WHEN A.IS_THIS_DEV = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n\tCOUNT(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),\n\tCOUNT(DISTINCT CASE WHEN A.IS_THIS_BREAK = '1' AND A.USER_ID IS NOT NULL THEN A.USER_ID END)\nFROM\n\t(\n\tSELECT\n\t\tUSER_ID,\n\t\tPROV_ID,\n\t\tPRODUCT_ID,\n\t\tDAY_ID,\n\t\tIS_INNET,\n\t\tIS_THIS_DEV,\n\t\tIS_THIS_BREAK\n\tFROM\n\t\tzq_dwa.dwa_d_cus_al_cb_user_info\n\tWHERE\n\t\tMONTH_ID = '202307'\n\t\tAND is_zq_develop = '1'\n\tUNION ALL\n\tSELECT\n\t\tUSER_ID,\n\t\tPROV_ID,\n\t\tPRODUCT_ID,\n\t\tDAY_ID,\n\t\tIS_INNET,\n\t\tIS_THIS_DEV,\n\t\tIS_THIS_BREAK\n\tFROM\n\t\tzq_dwa.dwa_d_cus_al_cb_user_info\n\tWHERE\n\t\tMONTH_ID = '202306'\n\t\tAND is_zq_develop = '1'\n\t) A\nINNER JOIN\n\t(\n\tSELECT\n\t\tUSER_ID\n\tFROM\n\t\tdwd.DWD_D_PRD_CB_USER_PRODUCT\n\tWHERE\n\t\tMONTH_ID = '202307'\n\t\tAND DAY_ID = '21'\n\t\tAND PRODUCT_ID IN (\n\t\tSELECT\n\t\t\tproduct_id\n\t\tFROM\n\t\t\tdim_yx_5gepn_0708\n\t\tUNION ALL\n\t\tSELECT\n\t\t\tb.product_id_b\n\t\tFROM\n\t\t\t(\n\t\t\tSELECT\n\t\t\t\tproduct_id,\n\t\t\t\tprov_id\n\t\t\tFROM\n\t\t\t\tdwd.DWD_D_PRD_CB_COMP_PRODUCT\n\t\t\tWHERE\n\t\t\t\trelation_type_code IN ('25', '28')\n\t\t\t\tAND MONTH_ID = '202306'\n\t\t\t\tAND day_id = '30'\n\t\t\t) a\n\t\t\tLEFT JOIN (\n\t\t\tSELECT DISTINCT\n\t\t\t\tproduct_id_a,\n\t\t\t\tproduct_id_b\n\t\t\tFROM\n\t\t\t\tdwd.DWD_M_PRD_CB_PRODUCTLIMIT\n\t\t\tWHERE\n\t\t\t\tMONTH_ID = '202306'\n\t\t\t) b ON b.product_id_a = a.product_id\n\t\t)\n\t\tAND SUBSTRING(end_date, 1, 8) >= '20230721'\n\t\tAND SUBSTRING(start_date, 1, 8) <= '20230721'\n\tUNION ALL\n\tSELECT\n\t\tUSER_ID\n\tFROM\n\t\tdwd.DWD_D_PRD_CB_USER_PRODUCT\n\tWHERE\n\t\tMONTH_ID = '202307'\n\t\tAND DAY_ID = '19'\n\t\tAND PRODUCT_ID IN (\n\t\tSELECT\n\t\t\tproduct_id\n\t\tFROM\n\t\t\tdim_yx_5gepn_0708\n\t\tUNION ALL\n\t\tSELECT\n\t\t\tb.product_id_b\n\t\tFROM\n\t\t\t(\n\t\t\tSELECT\n\t\t\t\tproduct_id,\n\t\t\t\tprov_id\n\t\t\tFROM\n\t\t\t\tdwd.DWD_D_PRD_CB_COMP_PRODUCT\n\t\t\tWHERE\n\t\t\t\trelation_type_code IN ('25', '28')\n\t\t\t\tAND MONTH_ID = '202306'\n\t\t\t\tAND day_id = '30'\n\t\t\t) a\n\t\t\tLEFT JOIN (\n\t\t\tSELECT DISTINCT\n\t\t\t\tproduct_id_a,\n\t\t\t\tproduct_id_b\n\t\t\tFROM\n\t\t\t\tdwd.DWD_M_PRD_CB_PRODUCTLIMIT\n\t\t\tWHERE\n\t\t\t\tMONTH_ID = '202306'\n\t\t\t) b ON b.product_id_a = a.product_id\n\t\t)\n\t\tAND SUBSTRING(end_date, 1, 8) >= '20230719'\n\t\tAND SUBSTRING(start_date, 1, 8) <= '20230719'\n\t) DWD_D_PRD_CB_USER_PRODUCT ON A.USER_ID = DWD_D_PRD_CB_USER_PRODUCT.USER_ID\nLEFT JOIN DIM_PROV B ON A.PROV_ID = B.PROV_ID\nGROUP BY PROV_DESC\nORDER BY (CASE\n\tWHEN prov_desc = '山东' THEN 1\n\tWHEN prov_desc = '河南' THEN 2\n\tWHEN prov_desc = '江苏' THEN 3\n\tWHEN prov_desc = '湖北' THEN 4\n\tWHEN prov_desc = '湖南' THEN 5\n\tWHEN prov_desc = '海南' THEN 6\n\tWHEN prov_desc = '重庆' THEN 7\n\tWHEN prov_desc = '四川' THEN 8\n\tWHEN prov_desc = '贵州' THEN 9\n\tWHEN prov_desc = '北京' THEN 10\n\tWHEN prov_desc = '山西' THEN 11\n\tWHEN prov_desc = '浙江' THEN 12\n\tWHEN prov_desc = '广东' THEN 13\n\tWHEN prov_desc = '西藏' THEN 14\n\tWHEN prov_desc = '甘肃' THEN 15\n\tWHEN prov_desc = '新疆' THEN 16\n\tWHEN prov_desc = '江西' THEN 17\n\tWHEN prov_desc = '云南' THEN 18\n\tWHEN prov_desc = '陕西' THEN 19\n\tWHEN prov_desc = '青海' THEN 20\n\tWHEN prov_desc = '宁夏' THEN 21\n\tWHEN prov_desc = '天津' THEN 22\n\tWHEN prov_desc = '内蒙古' THEN 23\n\tWHEN prov_desc = '上海' THEN 24\n\tWHEN prov_desc = '福建' THEN 25\n\tWHEN prov_desc = '广西' THEN 26\n\tWHEN prov_desc = '河北' THEN 27\n\tWHEN prov_desc = '辽宁' THEN 28\n\tWHEN prov_desc = '吉林' THEN 29\n\tWHEN prov_desc = '黑龙江' THEN 30\n\tWHEN prov_desc = '安徽' THEN 31\nEND);