<legend id='xyA8n'><style id='xyA8n'><dir id='xyA8n'><q id='xyA8n'></q></dir></style></legend>
    • <bdo id='xyA8n'></bdo><ul id='xyA8n'></ul>

        <tfoot id='xyA8n'></tfoot>

      1. <small id='xyA8n'></small><noframes id='xyA8n'>

      2. <i id='xyA8n'><tr id='xyA8n'><dt id='xyA8n'><q id='xyA8n'><span id='xyA8n'><b id='xyA8n'><form id='xyA8n'><ins id='xyA8n'></ins><ul id='xyA8n'></ul><sub id='xyA8n'></sub></form><legend id='xyA8n'></legend><bdo id='xyA8n'><pre id='xyA8n'><center id='xyA8n'></center></pre></bdo></b><th id='xyA8n'></th></span></q></dt></tr></i><div id='xyA8n'><tfoot id='xyA8n'></tfoot><dl id='xyA8n'><fieldset id='xyA8n'></fieldset></dl></div>

        用于在列中显示词频的sql server函数

        Sql server function for displaying word frequency in a column(用于在列中显示词频的sql server函数)
        <i id='9rWF2'><tr id='9rWF2'><dt id='9rWF2'><q id='9rWF2'><span id='9rWF2'><b id='9rWF2'><form id='9rWF2'><ins id='9rWF2'></ins><ul id='9rWF2'></ul><sub id='9rWF2'></sub></form><legend id='9rWF2'></legend><bdo id='9rWF2'><pre id='9rWF2'><center id='9rWF2'></center></pre></bdo></b><th id='9rWF2'></th></span></q></dt></tr></i><div id='9rWF2'><tfoot id='9rWF2'></tfoot><dl id='9rWF2'><fieldset id='9rWF2'></fieldset></dl></div>
            <tbody id='9rWF2'></tbody>

              <bdo id='9rWF2'></bdo><ul id='9rWF2'></ul>

                • <small id='9rWF2'></small><noframes id='9rWF2'>

                  <legend id='9rWF2'><style id='9rWF2'><dir id='9rWF2'><q id='9rWF2'></q></dir></style></legend>

                  <tfoot id='9rWF2'></tfoot>
                  本文介绍了用于在列中显示词频的sql server函数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有一个表格,其中列出了一项调查中的自由文本输入,允许参与者输入他们的回答(关于他们希望在婚礼中使用的颜色)

                  I have a table that lists a freet text input from a survey where enterents were allowed to enter their responses (regarding colours they would like to have in their wedding)

                  我想写一个sql函数,把这个列的所有信息都收集起来,orders统计每个词出现的频率,按照这个计数对结果集进行排序.

                  I would like to write a sql function that gathers all the information from this column, and orders counts the frequency of each word, ordering the result set by this count.

                  Response
                  --------
                  Red and White
                  green
                  White and blue
                  Blue
                  Dark blue
                  

                  我希望上表的顺序如下

                  Response  Frequency
                  --------  ---------
                  Blue      3
                  White     2
                  And       2
                  Red       1
                  Green     1
                  

                  我可以在函数运行后去掉所有像and"这样的垃圾词.有谁知道产生这种行为的任何好的功能?

                  I can strip all the rubbish words like "and" after the function has run. Does anyone know any good functions that produce this behaviour?

                  推荐答案

                  好的,这是一种享受.首先是一个分离值的函数......

                  Okay this works a treat. Firstly a function to separate the values...

                  Alter Function dbo.SeparateValues    
                  
                  (    
                   @data VARCHAR(MAX),    
                   @delimiter VARCHAR(10)     
                  )     
                  RETURNS     
                  @tbldata TABLE(col VARCHAR(MAX))    
                  As    
                  --Declare @data VARCHAR(MAX) ,@delimiter VARCHAR(10)     
                  --Declare @tbldata TABLE(col VARCHAR(10))    
                  --Set @data = 'hello,how,are,you?,234234'    
                  --Set @delimiter = ','    
                  --DECLARE @tbl TABLE(col VARCHAR(10))    
                  Begin    
                  DECLARE @pos INT    
                  DECLARE @prevpos INT    
                  SET @pos = 1     
                  SET @prevpos = 0    
                  
                  WHILE @pos > 0     
                  BEGIN    
                  SET @pos = CHARINDEX(@delimiter, @data, @prevpos+1)    
                  if @pos > 0     
                  INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, @pos-@prevpos-1))))    
                  else    
                  INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, len(@data)-@prevpos))))    
                  SET @prevpos = @pos     
                  End    
                  
                  RETURN       
                  END    
                  

                  然后我就将它应用到我的桌子上...

                  then I just apply it to my table...

                  Select Count(*), sep.Col FROM (
                          Select * FROM (
                              Select value = Upper(RTrim(LTrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(response, ',', ' '), '.', ' '), '!', ' '), '+', ' '), ':', ' '), '-', ' '), ';', ' '), '(', ' '), ')', ' '), '/', ' '), '&', ''), '?', ' '), '  ', ' '), '  ', ' ')))) FROM Responses
                          ) easyValues
                          Where value <> '' 
                      ) actualValues 
                      Cross Apply dbo.SeparateValues(value, ' ') sep
                      Group By sep.Col
                      Order By Count(*) Desc
                  

                  好的,所以我使用嵌套表进行了 OTT,但我已经去除了所有废话字符,分离了值并保留了最常用单词的运行总数.

                  Okay, so I went OTT with my nested tables, but I've stripped out all the crap characters, separated the values and kept a running total of the most frequently used words.

                  这篇关于用于在列中显示词频的sql server函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)(超出最大存储过程、函数、触发器或视图嵌套级别(限制 32))
                  How to debug stored procedure in VS 2015?(如何在 VS 2015 中调试存储过程?)
                  How to find a text inside SQL Server procedures / triggers?(如何在 SQL Server 程序/触发器中查找文本?)
                  SQL Server stored procedure return code oddity(SQL Server 存储过程返回码奇怪)
                  Conditional SQL ORDER BY ASC/DESC for alpha columns(alpha 列的条件 SQL ORDER BY ASC/DESC)
                  Export stored procedure result set to Excel in SSMS(在SSMS中将存储过程结果集导出到Excel)
                  <legend id='l79VV'><style id='l79VV'><dir id='l79VV'><q id='l79VV'></q></dir></style></legend>

                • <small id='l79VV'></small><noframes id='l79VV'>

                  <tfoot id='l79VV'></tfoot>

                      <i id='l79VV'><tr id='l79VV'><dt id='l79VV'><q id='l79VV'><span id='l79VV'><b id='l79VV'><form id='l79VV'><ins id='l79VV'></ins><ul id='l79VV'></ul><sub id='l79VV'></sub></form><legend id='l79VV'></legend><bdo id='l79VV'><pre id='l79VV'><center id='l79VV'></center></pre></bdo></b><th id='l79VV'></th></span></q></dt></tr></i><div id='l79VV'><tfoot id='l79VV'></tfoot><dl id='l79VV'><fieldset id='l79VV'></fieldset></dl></div>

                      • <bdo id='l79VV'></bdo><ul id='l79VV'></ul>
                            <tbody id='l79VV'></tbody>