TSQL—查询表列，为标记云提取流行词汇

Question

只是一个探索性的问题，看看是否有人这样做过，或者实际上这是可能的。

我们都知道标签云是什么，通常，标签云是由分配标签的人创建的。是否有可能，在SQL Server的当前功能自动创建，也许是通过触发器时，一个表有一个记录添加或更新，通过查看某列内的数据，并得到流行词?

这类似于这个问题:我如何通过mysql获得表中最流行的单词?但是，这是MySQL，不是MSSQL。

提前感谢。詹姆斯。

Answer 1

下面是将分隔字符串解析为行的好方法:
http://anyrest.wordpress.com/2010/08/13/converting-parsing-delimited-string-column-in-sql-to-rows/

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

T-SQL:与字符串连接相反-如何将字符串分割成多个记录

如果你想解析所有的单词，你可以使用空格' '作为分隔符，然后你得到每个单词的一行。

接下来，您只需按单词选择结果集GROUP并聚合COUNT

Answer 2

在我看来，是设计方法让这变得困难。允许用户分配标记并不意味着必须将标记存储为单个分隔的单词列表。您可以将该结构规范化为如下内容:

Create Table Posts ( Id ... not null primary key )
Create Table Tags( Id ... not null primary key, Name ... not null Unique )
Create Table PostTags
    ( PostId ... not null References Posts( Id )
    , TagId ... not null References Tags( Id ) )

现在你的问题变得微不足道了:

Select T.Id, T.Name, Count(*) As TagCount
From PostTags As PT
    Join Tags As T
        On T.Id = PT.TagId
Group By T.Id, T.Name
Order By Count(*) Desc

如果您坚持将标记存储为分隔值，那么唯一的解决方案是通过编写自定义split函数在其分隔符上分割值，然后进行计数。底部是一个Split函数的示例。使用它，您的查询将看起来像(使用逗号分隔符):

Select Tag.Value, Count(*) As TagCount
From Posts As P
    Cross Apply dbo.Split( P.Tags, ',' ) As Tag
Group By Tag.Value
Order By Count(*) Desc

拆分功能:

Create Function [dbo].[Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, DataLength(@Delimiter)/2) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, DataLength(@Delimiter)/2) <> @Delimiter Then @Delimiter Else '' End
            As List
            , DataLength(@Delimiter)/2 As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP (Coalesce(Len(@DelimitedList),1)) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.objects As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , Case
                        When CharIndex(@Delimiter, CL.list, N.Value + 1)                            
                            - CharIndex(@Delimiter, CL.list, N.Value)
                            - CL.DelimiterLen < 0 Then Len(CL.List)
                        Else CharIndex(@Delimiter, CL.list, N.Value + 1)                            
                            - CharIndex(@Delimiter, CL.list, N.Value)
                            - CL.DelimiterLen
                        End
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value < Len(CL.List)
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
)

Answer 3

Word或Tag cloud需要两个字段:一个字符串和一个表示该单词或字符串在集合中出现的次数的值。然后，您可以将结果传递给标记云工具，该工具将根据需要显示数据。

不带走之前的答案，因为他们回答了最初的挑战。然而，我有一个更简单的解决方案，使用两个函数(类似于@Thomas answer)，其中一个使用regex来"清理";单词。

两个函数是:

dbo。fnstripcharars (a, b)——使用正则表达式'b'清理字符串'a'

dbo。fnMakeTableFromList(a, b)——将单个字段'a'转换为以'b'分隔的表格列表

然后我将它们应用到单个SQL语句中，使用TOP n功能给我想传递给PowerBI或其他图形工具的前10个单词，用于实际显示单词或标签云。

SELECT TOP 10 b.[words], b.[total]
FROM
 (SELECT a.[words], count(*) AS [total]
  FROM
    (SELECT upper(l.item) AS [words]
     FROM dbo.MyTableWithWords AS c
       CROSS APPLY POTS.fnMakeTableFromList([POTS].fnStripChars(c.myColumnThatHasTheWords,'[^a-zA-Z ]'),' ') AS l) AS a
  GROUP BY a.[words]) AS b
ORDER BY 2 DESC

可以看到，正则表达式是[^a-zA-Z ]，它只给我字母字符和spaces。然后将空格用作make表函数的分隔符，以单独分隔每个单词。我应用count(*)，给我这个词出现的次数，这样我就有了我需要的一切，给我前10个结果。

注意，CROSS APPLY在这里很重要，所以我只得到实际"word "的数据。在找到的每个记录中。否则，它将遍历所有包含或不包含单词的记录，以从我想要的列中提取。

fnStripChars ()

FUNCTION [dbo].[fnStripChars]
(
    @String NVARCHAR(4000), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression = '%' + @MatchExpression + '%'
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
    
    RETURN @String
END

fnMakeTableFromList ()

FUNCTION [dbo].[fnMakeTableFromList](
  @List       VARCHAR(MAX),
  @Delimiter  CHAR(1))
  RETURNS TABLE
AS
RETURN (SELECT Item = CONVERT(VARCHAR, Item)
        FROM (SELECT Item = x.i.value('(./text())[1]','varchar(max)')
              FROM (SELECT [XML] = CONVERT(XML,'<i>' + REPLACE(@List,@Delimiter,'</i><i>') + '</i>').query('.')) AS a
                CROSS APPLY [XML].nodes('i') AS x(i)) AS y
        WHERE Item IS NOT NULL);

我已经用超过400K的记录测试了它，它能够在60秒内返回我的结果。我认为这是合理的。