时间戳

首页 > 解决方案 > T-SQL 查询：查找最频繁的值对

sql - T-SQL 查询：查找最频繁的值对

问题描述

我有文本列，其中的数值用分号分隔。我试图弄清楚如何获得出现在同一行中的最频繁的一对值。我在 Python 中找到了一个非常相似的问题的解决方案 Find the most most oftenoccus of pairs in a list of lists，但我不知道如何使用 SQL 重写它 在下面的示例中它返回 2 和 3 因为这对在输入集中出现 3 次：

Input rows      Output
----------      -------
';1;2;3;5'    |  '2;3'     
';2;3'        |  '1;2'
';3;4;5;1;2'  |  '1;3' 
';1;5;2'      |  '1;5'

原始数据：

查询结果

标签： sqlsql-servertsql

解决方案

您可以尝试以下方法。首先，使用OPENJSON()，得到所有可能的组合。解析 JSON 数组时OPENJSON，JSON 文本中元素的索引作为键（从 0 开始）返回。然后，用 计算最频繁的对DENSE_RANK()。

输入：

CREATE TABLE #Items (
   Id int,
   ItemValues varchar(max)
)
INSERT INTO #Items
   (Id, ItemValues)
VALUES   
   (1, '1;2;3;5'),
   (2, '2;3'),
   (3, '3;4;5;1;2'),
   (4, '1;5;2')

陈述：

;WITH combinationsCTE AS (
   SELECT 
      CASE 
         WHEN s1.[value] <= s2.[value] THEN CONCAT(s1.[value], ';', s2.[value])
         ELSE CONCAT(s2.[value], ';', s1.[value]) 
      END AS PairValue
   FROM #Items i
   CROSS APPLY (SELECT [key], [value] FROM OPENJSON('["' +  REPLACE(i.ItemValues,';','","') + '"]')) s1
   CROSS APPLY (SELECT [key], [value] FROM OPENJSON('["' +  REPLACE(i.ItemValues,';','","') + '"]')) s2
   WHERE (s1.[key] < s2.[key])
), rankingCTE AS (
   SELECT 
      PairValue, 
      DENSE_RANK() OVER (ORDER BY COUNT(PairValue) DESC) AS PairRank
   FROM combinationsCTE
   GROUP BY PairValue
)
SELECT PairValue
FROM rankingCTE
WHERE PairRank = 1

输出：

PairValue
1;2
1;5
2;3
2;5

推荐阅读