SQL 语句优化：根据省份统计用户数据

SELECT PROV_DESC, \n SUM(CASE WHEN IS_INNET = '1' AND A.DAY_ID = '23' AND A.USER_ID IS NOT NULL THEN 1 ELSE 0 END),\n COUNT(DISTINCT CASE WHEN SUBSTRING(a.open_date, 1, 8) <= '20230723' AND a.open_date >= '20230701' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n COUNT(DISTINCT CASE WHEN SUBSTRING(a.open_date, 1, 8) <= '20230623' AND a.open_date >= '20230601' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n COUNT(DISTINCT CASE WHEN SUBSTRING(a.open_date, 1, 6) = '202306' AND A.USER_ID IS NOT NULL THEN A.USER_ID END),\n SUM(CASE WHEN a.IS_THIS_break = '1' AND A.USER_ID IS NOT NULL THEN 1 ELSE 0 END)\nFROM (\n SELECT USER_ID, PROV_ID, PRODUCT_ID, DAY_ID, IS_INNET, IS_THIS_DEV, IS_THIS_break\n FROM zq_dwa.DWA_D_MRT_CB_USER_WORK_PHONE\n WHERE MONTH_ID = '202307' AND is_hd = '0' AND is_hyk = '0' AND is_yx = '1'\n) A\nLEFT JOIN DIM_PROV b ON A.PROV_ID = B.PROV_ID\nLEFT JOIN (\n SELECT open_date, USER_ID, PROV_ID, PRODUCT_ID, DAY_ID, month_id\n FROM zq_dwa.dwa_d_cus_al_cb_user_info\n WHERE MONTH_ID = '202307' AND DAY_ID = '23'\n) c ON c.USER_ID = A.USER_ID\nLEFT JOIN (\n SELECT USER_ID, PRODUCT_ID, DAY_ID, MONTH_ID, prov_id\n FROM zq_dwa.DWA_D_MRT_CB_USER_WORK_PHONE\n WHERE MONTH_ID = '202306' AND is_hd = '0' AND is_hyk = '0' AND is_yx = '1' and day_id = '23'\n) d ON d.USER_ID = A.USER_ID\nLEFT JOIN (\n SELECT open_date, USER_ID, PROV_ID, PRODUCT_ID, DAY_ID, month_id\n FROM zq_dwa.dwa_d_cus_al_cb_user_info\n WHERE MONTH_ID = '202306' AND DAY_ID = '30'\n) e ON e.USER_ID = A.USER_ID\nGROUP BY PROV_DESC\nORDER BY CASE \n WHEN prov_desc='山东' THEN 1 \n WHEN prov_desc='河南' THEN 2 \n WHEN prov_desc='江苏' THEN 3 \n WHEN prov_desc='湖北' THEN 4 \n WHEN prov_desc='湖南' THEN 5 \n WHEN prov_desc='海南' THEN 6 \n WHEN prov_desc='重庆' THEN 7 \n WHEN prov_desc='四川' THEN 8 \n WHEN prov_desc='贵州' THEN 9 \n WHEN prov_desc='北京' THEN 10 \n WHEN prov_desc='山西' THEN 11 \n WHEN prov_desc='浙江' THEN 12 \n WHEN prov_desc='广东' THEN 13 \n WHEN prov_desc='西藏' THEN 14 \n WHEN prov_desc='甘肃' THEN 15 \n WHEN prov_desc='新疆' THEN 16 \n WHEN prov_desc='江西' THEN 17 \n WHEN prov_desc='云南' THEN 18 \n WHEN prov_desc='陕西' THEN 19 \n WHEN prov_desc='青海' THEN 20 \n WHEN prov_desc='宁夏' THEN 21 \n WHEN prov_desc='天津' THEN 22 \n WHEN prov_desc='内蒙古' THEN 23 \n WHEN prov_desc='上海' THEN 24 \n WHEN prov_desc='福建' THEN 25 \n WHEN prov_desc='广西' THEN 26 \n WHEN prov_desc='河北' THEN 27 \n WHEN prov_desc='辽宁' THEN 28 \n WHEN prov_desc='吉林' THEN 29 \n WHEN prov_desc='黑龙江' THEN 30 \n WHEN prov_desc='安徽' THEN 31 \nEND