oracle 数组分组

场景：
qc_basic_info 个人档案表存在一个POPULATION_TYPE字段，字段存的是0-9的人群分类数组字符串，因为有的存的是‘2，3’，有的存的是‘2,3,4’，需求是统计每个人群分类的人数，所以需要先把数组拆分出来再分组统计

				
				WITH split_population AS (
	SELECT
		t.rec_province_id,
		t.rec_city_id,
		t.rec_district_id,
		t.rec_town_id,
		t.rec_village_id,
		t.basic_id,
		t.is_delete,
		t.status,
		TRIM(
		REGEXP_SUBSTR( t.POPULATION_TYPE, '[^,]+', 1, LEVEL )) AS population_type 
	FROM
		qc_basic_info t CONNECT BY PRIOR t.id = t.id 
		AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL 
		AND LEVEL <= LENGTH( t.POPULATION_TYPE ) - LENGTH(
		REPLACE ( t.POPULATION_TYPE, ',', '' )) + 1 
	),
	filtered_data AS (
	SELECT
		* 
	FROM
		split_population sp
		LEFT JOIN sys_rec rec1 ON rec1.rec_code = sp.rec_province_id
		LEFT JOIN sys_rec rec2 ON rec2.rec_code = sp.rec_city_id
		LEFT JOIN sys_rec rec3 ON rec3.rec_code = sp.rec_district_id
		LEFT JOIN sys_rec rec4 ON rec4.rec_code = sp.rec_town_id
		LEFT JOIN sys_rec rec5 ON rec5.rec_code = sp.rec_village_id
		LEFT JOIN QY_PROTOCOL_MAIN t1 ON sp.basic_id = t1.basic_id 
		AND t1.is_delete = '0' 
		AND t1.PROTOCOL_STATE = '1' 
		AND t1.source_signing = '1' 
	WHERE
		sp.is_delete = '0' 
		AND sp.status = '0' 
		AND NVL( t1.id, '0' ) != '0' 
		AND sp.rec_village_id IN ( SELECT DISTINCT t.REC_CODE FROM SYS_REC t JOIN SYS_MANAGE_REC t1 ON t1.rec_code = t.rec_code WHERE t1.org_code = '510185002001' ) 
	) SELECT
	'已签约' AS NAME,
	population_type AS typename,
	COUNT( * ) AS sum 
FROM
	filtered_data 
GROUP BY
	population_type 
ORDER BY
	typename