oracle 循环拆分,批量字符串的拆分和统计，怎么实现

这种需求如果按这种设计数据量大的话， 效率一定是大问题

SQL> select * from t;

ID SEL

---------- ------------------------

1 [1]1~[2]2~[3]1~[4]2~[5]1

1 [6]1~[7]2~[8]1~[9]1

1 [1]1~[2]2~[3]1~[4]2~[5]1

1 [6]1~[7]2~[8]1~[9]2

2 [6]1~[7]2~[8]1~[9]1

2 [1]1~[2]2~[3]1~[4]2~[5]1

6 rows selected.

SQL>

SQL> select  id,

2          substr(flag, 1, instr(flag, ']')) shop_id,

3          sum(decode(substr(flag, instr(flag, ']') + 1), 1, 1, 0)) goods_1_cnt,

4          sum(decode(substr(flag, instr(flag, ']') + 1), 2, 1, 0)) goods_2_cnt

5  from

6  (select a1.id,

7          a1.sel,

8          substr('~' || a1.sel || '~',

9                instr('~' || a1.sel || '~', '~', 1, b1.rn) + 1,

10                instr('~' || a1.sel || '~', '~', 1, b1.rn + 1) - instr('~' || a1.sel || '~', '~', 1, b1.rn) - 1) flag

11  from

12  t a1,

13  (select level rn from (select max(length(sel) - length(replace(sel, '~', '')) + 1) len from t) connect by level <= len) b1

14  where length(a1.sel) - length(replace(a1.sel, '~', '')) + 1 >= b1.rn)

15  group by id, substr(flag, 1, instr(flag, ']'))

16  order by 1, 2;

ID SHOP_ID                                                                                                  GOODS_1_CNT GOODS_2_CNT

---------- -------------------------------------------------------------------------------------------------------- ----------- -----------

1 [1]                                                                                                                2           0

1 [2]                                                                                                                0           2

1 [3]                                                                                                                2           0

1 [4]                                                                                                                0           2

1 [5]                                                                                                                2           0

1 [6]                                                                                                                2           0

1 [7]                                                                                                                0           2

1 [8]                                                                                                                2           0

1 [9]                                                                                                                1           1

2 [1]                                                                                                                1           0

2 [2]                                                                                                                0           1

2 [3]                                                                                                                1           0

2 [4]                                                                                                                0           1

2 [5]                                                                                                                1           0

2 [6]                                                                                                                1           0

2 [7]                                                                                                                0           1

2 [8]                                                                                                                1           0

2 [9]                                                                                                                1           0

18 rows selected.

SQL>