以逗号分隔的计数值

Question

我有下表：

A   V
-   -
A1  B, C
A2  C, 2W
A3  D
A4  10K, 10W, 2A

我想计算V中的值，并按出现次数排序。预期输出：

O  COUNT(*)
-  -------
W       12
K       10
A        2
C        2
B        1
D        1

Answer 1

您可以使用REGEX_SUBSTR()和REGEXP_REPLACE()函数的组合以及分层查询，假设所有逗号分隔的元素，无论何时修剪空白，都是一个正整数+一个字母的形式，如

WITH t2 AS
(
SELECT TRIM(REGEXP_SUBSTR(v,'[^,]+',1,level)) AS v
FROM t 
CONNECT BY level <= REGEXP_COUNT(v,',')+1
AND PRIOR SYS_GUID() IS NOT NULL
AND PRIOR a = a
)
SELECT NVL(SUM(REGEXP_REPLACE(v,'[^[:digit:]]')),
CASE WHEN COUNT(*)>1 THEN COUNT(*) END)||REGEXP_REPLACE(v,'[^[:alpha:]]') AS o
FROM t2 
GROUP BY REGEXP_REPLACE(v,'[^[:alpha:]]')    

演示

编辑(取决于问题的最后一次编辑(：只需删除连接运算符，并为COUNT聚合(如(设置条件

SELECT REGEXP_REPLACE(v,'[^[:alpha:]]') AS o,
NVL(SUM(REGEXP_REPLACE(v,'[^[:digit:]]')),COUNT(*)) AS count 
FROM t2 
GROUP BY REGEXP_REPLACE(v,'[^[:alpha:]]') 
ORDER BY count DESC, o

演示