sql - 找出字符串中出现次数最多的字符

Question

使用 Oracle SQL 查询，我们可以执行以下操作吗？

      Input       Output
    'aaaabcd' --->  'a'
    '0001001' --->  '0'

也就是说，找出字符串中出现次数最多的字符？

Answer 1

是的，这可以通过使用 CONNECT BY 来实现.不过有点复杂:

SELECT xchar, xcount FROM (
    SELECT xchar, COUNT(*) AS xcount, RANK() OVER ( ORDER BY COUNT(*) DESC) AS rn
      FROM (
        SELECT SUBSTR('aaaabcd', LEVEL, 1) AS xchar
          FROM dual
       CONNECT BY LEVEL <= LENGTH('aaaabcd')
   ) GROUP BY xchar
) WHERE rn = 1;

我们在最里面的查询中所做的是将字符串分解为单独的字符。然后我们就得到了 COUNT()按字符分组，并使用 RANK()找到最大值(请注意，如果最常出现的字符存在平局，这将返回多个结果)。

上面的查询返回最常出现的字符和它出现的次数。

如果您有一个包含多个字符串的表，那么您需要执行以下操作:

WITH strlen AS (
  SELECT LEVEL AS strind
    FROM dual
 CONNECT BY LEVEL <= 30
)
SELECT id, xchar, xcount FROM (
    SELECT id, xchar, COUNT(*) AS xcount, RANK() OVER ( PARTITION BY id ORDER BY COUNT(*) DESC) AS rn
      FROM (
        SELECT s.id, SUBSTR(s.str, sl.strind, 1) AS xchar
          FROM strings s, strlen sl
         WHERE LENGTH(s.str) >= sl.strind
   ) GROUP BY id, xchar
) WHERE rn = 1;

哪里 30是一个魔数(Magic Number)，等于或大于最长字符串的长度。 See SQL Fiddle here.或者，您可以执行以下操作来避免魔数(Magic Number):

WITH strlen AS (
  SELECT LEVEL AS strind
    FROM dual
 CONNECT BY LEVEL <= ( SELECT MAX(LENGTH(str)) FROM strings )
)
SELECT id, xchar, xcount FROM (
    SELECT id, xchar, COUNT(*) AS xcount, RANK() OVER ( PARTITION BY id ORDER BY COUNT(*) DESC) AS rn
      FROM (
        SELECT s.id, SUBSTR(s.str, sl.strind, 1) AS xchar
          FROM strings s, strlen sl
         WHERE LENGTH(s.str) >= sl.strind
   ) GROUP BY id, xchar
) WHERE rn = 1;

Updated SQL Fiddle.