SQL语句优化：去除PROV_DESC并按省份排序

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