Получение наиболее часто используемых слов из столбца строк в SQL

Итак, мы имеемэта база данных заполнен кучей строк, в этом случае заголовки сообщений.

Что я хочу сделать, это:

Разделить строку на словаПосчитайте, сколько раз слова появляются в строкахДай мне 50 лучших словНет этого таймаута в запросе data.se

Я пытался использовать информацию изэтот ТАК вопрос адаптирован к data.se следующим образом:

select word, count(*) from (
select (case when instr(substr(p.Title, nums.n+1), ' ') then substr(p.Title, nums.n+1)
             else substr(p.Title, nums.n+1, instr(substr(p.Title, nums.n+1), ' ') - 1)
        end) as word
from (select ' '||Title as string
      from Posts p
     )Posts cross join
     (select 1 as n union all select 2 union all select 10
     ) nums
where substr(p.Title, nums.n, 1) = ' ' and substr(p.Title, nums.n, 1) <> ' '
) w
group by word
order by count(*) desc

К сожалению, это дает мне массу ошибок:

'substr' не является распознанным именем встроенной функции. Неверный синтаксис рядом с '|'. Неверный синтаксис рядом с 'nums'.

Итак, учитывая столбец строк в SQL с переменным количеством текста в каждой строке, как я могу получить список наиболее часто используемых слов X?

 Blorgbeard26 мая 2016 г., 03:18
Этот запрос использует специальный синтаксис SQLite, тогда как вам нужен синтаксис SQL Server.
 jmac26 мая 2016 г., 03:21
Бонусные баллы, если вы напишите запрос в проводнике данных и сможете выполнить его без сбоев в Stack Overflow на португальском языке! Мы выдаем бонусные баллы, верно?

Ответы на вопрос(3)

Решение для запроса (Функция разделения не требуется)

PostgreSQL

select word, count(*) from 
(
    -- get 1st words
    select split_part(title, ' ', 1) as word
    from posts

    union all

    -- get 2nd words
    select split_part(title, ' ', 2) as word
    from posts

    union all

    -- get 3rd words
    select split_part(title, ' ', 3) as word
    from posts

    -- can do this as many times as the number of words in longest title

) words
where word is not null
and word NOT IN ('', 'and', 'for', 'of', 'on')
group by word
order by count desc
limit 50;

для краткой версии, см .:https://dba.stackexchange.com/a/82456/95929

С теперь доступныSTRING_SPLIT Функция (начиная с SQL Server 2016, уровень совместимости 130) этот запрос становится намного проще:

SELECT TOP 50
       value [word]
     , COUNT(*) [#times]
FROM posts p
CROSS APPLY STRING_SPLIT(p.title, ' ')
GROUP BY value
ORDER BY COUNT(*) DESC

Посмотрите это в действии наСтек Exchange Data Explorer где он все еще работает менее 2 минут для текущего количества сообщений в базе данных переполнения стека. В Stack Overflow em Português он работает без опасений за страшный тайм-аут.

Результаты похожи на то, что вы видели вответ от Феликса:

Решение Вопроса

Как сказал Blogbeard, указанный вами запрос не работает с SQL Server. Вот один из способов посчитать самое употребляемое слово. Это основано на функции,DelimitedSplitN4K, написанный Джеффом Моденом и улучшенный членами сообщества SQL Server Central.

ОНЛАЙН ДЕМО

WITH E1(N) AS (
    SELECT 1 FROM (VALUES
        (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b)
SELECT TOP 50
    x.Item,
    COUNT(*)
FROM Posts p
CROSS APPLY (
    SELECT 
        ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = LTRIM(RTRIM(SUBSTRING(p.Title, l.N1, l.L1)))
        FROM (
            SELECT s.N1,
                L1 = ISNULL(NULLIF(CHARINDEX(' ',p.Title,s.N1),0)-s.N1,4000)
            FROM(
                SELECT 1 UNION ALL
                SELECT t.N+1 
                FROM(
                    SELECT TOP (ISNULL(DATALENGTH(p.Title)/2,0))
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                    FROM E4
                ) t(N)
                WHERE SUBSTRING(p.Title ,t.N,1) = ' '
            ) s(N1)
        ) l(N1, L1)
) x
WHERE x.item <> ''
GROUP BY x.Item
ORDER BY COUNT(*) DESC

Поскольку создание функции запрещено, я написал это так. Вот определение функции, если вы заинтересованы:

CREATE FUNCTION [dbo].[DelimitedSplitN4K](
    @pString NVARCHAR(4000), 
    @pDelimiter NCHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
cteTally(N) AS(
    SELECT TOP (ISNULL(DATALENGTH(@pString)/2,0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (
    SELECT 1 UNION ALL 
    SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(
    SELECT s.N1,
        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,4000)
    FROM cteStart s
)
SELECT 
    ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
    Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;

А вот как вы бы это использовали:

SELECT TOP 50
    x.Item,
    COUNT(*)
FROM Posts p
CROSS APPLY dbo.DelimitedSplitN4K(p.Title, ' ') x
WHERE LTRIM(RTRIM(x.Item)) <> ''
GROUP BY x.Item
ORDER BY COUNT(*) DESC

Результат:

Item             
-------- ------- 
to       3812411 
in       3331522 
a        2543636 
How      1770915 
the      1534298 
with     1341632 
of       1297468 
and      1166664 
on       970554  
from     964449  
for      886007  
not      835979  
is       704724  
using    703007  
I        633838  
-        632441  
an       548450  
when     449169  
file     409717  
how      358745  
data     335271  
do       323854  
can      310298  
get      305922  
or       266317  
error    263563  
use      258408  
value    254392  
it       251254  
my       238902  
function 235832  
by       231025  
Android  228308  
as       216654  
array    209157  
working  207445  
does     207274  
Is       205613  
multiple 203336  
that     197826  
Why      196979  
into     196591  
after    192056  
string   189053  
PHP      187018  
one      182360  
class    179965  
if       179590  
text     174878  
table    169393  

Ваш ответ на вопрос