趣味SQL 計算相鄰相同文字出現次數

sql rdbms

一級屠豬士 2022-02-20 00:27:59 ‧ 1008 瀏覽

分享至

句子是: 庭院深深深幾許繼光香香香雞酒香不怕巷子深香香深深有幾何
將句子裡面有相鄰相同的文字,計算出現次數.
要得到的結果是:

 a  | ?column?
----+----------
 庭 |        1
 院 |        1
 深 |        3
 幾 |        1
 許 |        1
 繼 |        1
 光 |        1
 香 |        3
 雞 |        1
 酒 |        1
 香 |        1
 不 |        1
 怕 |        1
 巷 |        1
 子 |        1
 深 |        1
 香 |        2
 深 |        2
 有 |        1
 幾 |        1
 何 |        1
(21 rows)

不見得需要 table, 可以直接使用字串, 或者是參考

謝謝幾位大大精彩的回應!
一種查詢的方式

海綿寶寶 iT邦大神 1 級 ‧ 2022-02-20 08:42:16 檢舉

不會純問
可否提示一下原始 table 結構及資料

㊣浩瀚星空㊣ iT邦大神 1 級 ‧ 2022-02-20 19:15:13 檢舉

這題我就跳過了。因為我並不太喜歡用SQL來寫程式。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 8 級 ‧ 2022-02-20 10:13:44

CREATE TABLE TMP (
SETSTR NVARCHAR(50));

INSERT INTO TMP
VALUES (N'庭院深深深幾許繼光香香香雞酒香不怕巷子深香香深深有幾何'),
(N'何幾有深深香香深子巷怕不香酒雞香香香光繼許幾深深深院庭');

;WITH CTE_X1 AS (
SELECT ROW_NUMBER() OVER (ORDER BY SETSTR DESC) AS NO,SETSTR
FROM TMP),

CTE_X2 AS (
SELECT NO,SETSTR,SUBSTRING(SETSTR, 1, 1) AS STRNEW,1 AS NUM
FROM CTE_X1
UNION ALL
SELECT NO,SETSTR,SUBSTRING(SETSTR, NUM + 1, 1) AS STRNEW,NUM + 1 AS NUM
FROM CTE_X2
WHERE LEN(SETSTR) - NUM > 0),

CTE_X3 AS (
SELECT M.NO,M.STRNEW,1 + SUM(M.FY) OVER (ORDER BY M.NO, M.NUM) AS FR
FROM (
SELECT NO,STRNEW,NUM,
CASE WHEN STRNEW = LAG(STRNEW, 1, STRNEW) OVER (ORDER BY NO, NUM) THEN 0 ELSE 1 END AS FY
FROM CTE_X2) M)

SELECT NO,STRNEW,COUNT(FR) AS COUNTER
FROM CTE_X3
GROUP BY NO,FR,STRNEW

Demo

回應
分享
檢舉

登入發表回應

石頭

iT邦高手 1 級 ‧ 2022-02-20 20:19:20

這是一個 island and gap problem 解法可以參考下面

SQL Server 版本

DECLARE @v NVARCHAR(MAX) = N'庭院深深深幾許繼光香香香雞酒香不怕巷子深香香深深有幾何'
;WITH CTE AS (
   SELECT 1 startNum,LEN(@v) endNum
   UNION ALL
   SELECT startNum + 1, endNum
   FROM CTE 
   WHERE startNum + 1 <= endNum
), CTE1 AS (
  SELECT SUBSTRING(@v, startNum, 1) c,
         startNum - ROW_NUMBER() OVER(PARTITION BY SUBSTRING(@v, startNum, 1) order by startNum) grp,
         startNum
  FROM CTE
)
SELECT c,COUNT(*) cnt
FROM CTE1
GROUP BY grp,c
ORDER BY MIN(startNum)

sqlfiddle

Oracle 版本

WITH CTE AS (
 SELECT '庭院深深深幾許繼光香香香雞酒香不怕巷子深香香深深有幾何' temp 
 FROM DUAL
), CTE1 AS (
 SELECT REGEXP_SUBSTR(temp, '.', 1, level) c,
        level  - ROW_NUMBER() OVER(PARTITION BY REGEXP_SUBSTR(temp, '.', 1, level) order by level) grp,
        ROW_NUMBER() OVER(ORDER BY Level) rn
 FROM CTE 
 CONNECT BY level <= REGEXP_COUNT(temp, '.')
)
SELECT c,COUNT(*) cnt
FROM CTE1
GROUP BY grp,c
ORDER BY MIN(rn)

sqlfiddle