MYSQL 카운트/수량화 비율의 각 단어별 그룹화
다음과 같은 데이터를 가진 MYSQL/MariaDB 테이블이 있습니다.
language phrase tries success
----------- --------------- ----- -------
Spanish hola como estas 5 3
Spanish como estas tu 3 1
Spanish adios me voy 2 0
Spanish adios me voy 1 0
Spanish adios me voy 2 1
Spanish como voy 3 2
English hello how are you 5 2
English hello who are you 3 0
English how good are you 5 1
English be good 3 3
French au revoir 4 0
French merci beaucoup 2 1
French merci beaucoup 5 2
French beaucoup des choses 2 2
French voir et revoir 3 2
언어별, 문구에 포함된 단어별로 그룹화하여 각 단어의 '성공'을 수치화하고 싶다.예를 들어 '스페인어'에서는 'como'가 3열로 나타나며, 'como'가 11번과 6번으로 나타나기 때문에 'como'가 스팬에서 'como'의 비율이다.ish는 0.54로 각 단어에 대해 이 작업을 수행하므로 다음과 같이 끝납니다.
language word tries successes ratio
-------- ------ ----- --------- -----
spanish hola 5 3 0.60
spanish como 11 6 0.54
spanish estas 8 4 0.54
spanish tu 3 1 0.33
spanish adios 5 1 0.20
spanish me 5 1 0.20
spanish voy 8 3 0.38
english hello 8 2 0.25
english how 10 3 0.30
english are 13 3 0.23
english you 13 3 0.23
english who 3 0 0
english good 8 4 0.54
english be 3 3 1
french au 4 0 0
french revoir 7 2 0.29
french merci 7 3 0.43
french beaucoup 9 5 0.55
french des 2 2 1
french choses 2 2 1
french voir 3 2 0.66
french et 3 2 0.66
SQL에서 이 작업을 수행하는 방법에 대해 고민하고 있습니다.
참고로 실제 테이블은 약 50,000개의 행과 구문을 포함하고 있으며, 1에서 7단어까지 포함할 수 있으며, 가장 일반적인 단어는 2에서 4단어입니다.
먼저 현악기의 말을 반복해야 한다.SQL에서 재귀 쿼리를 사용하여 이 작업을 수행합니다.나머지는 집약입니다.
with recursive words (language, word, tries, success, rest) as
(
select
language,
substring_index(concat(trim(phrase), ' '), ' ', 1) as word,
tries,
success,
substring(trim(phrase), instr(concat(trim(phrase), ' '), ' ') + 1) as rest
from mytable
union all
select
language,
substring_index(concat(rest, ' '), ' ', 1) as word,
tries,
success,
substring(rest, instr(concat(rest, ' '), ' ') + 1) as rest
from words
where rest <> ''
)
select
language,
word,
sum(tries) as sum_tries,
sum(success) as sum_success,
sum(success) / sum(tries) as ratio
from words
group by language, word
order by language, word;
데모: https://dbfiddle.uk/ ?rdbms = adb _ 10 . 2 & deb = dd68b85cc 868697 edebd0a9 55b87f
다음은 MariaDB의 재귀 쿼리 설명입니다.https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/
솔루션 1: 시퀀스 스토리지 엔진
새로운 MariaDB 버전에는 시퀀스 스토리지 엔진이 내장되어 있습니다.예: (가상) 테이블seq_1_to_1000에 1 ~ 1000 의 시퀀스 번호를 나타냅니다.이를 사용하여 구문을 단어별로 행으로 분할할 수 있습니다.다음 쿼리를 고려합니다.
select
t.*,
s.seq,
substring_index(substring_index(t.phrase, ' ', s.seq), ' ', -1) as word
from tbl t
join seq_1_to_1000 s
on s.seq <= char_length(t.phrase) - char_length(replace(t.phrase, ' ', '')) + 1
char_length(t.phrase) - char_length(replace(t.phrase, ' ', '')) + 1단어 수를 알려드리겠습니다.따라서 시퀀스 테이블에서 구문의 단어 수만큼 행을 결합합니다. s.seq이제 열을 구에서 단어의 위치로 사용할 수 있습니다.substring_index(substring_index(t.phrase, ' ', s.seq), ' ', -1)이 단어를 추출합니다.원래 테이블에서 처음 두 행은 다음과 같이 변환됩니다.
language phrase tries success seq word
-------- --------------- ----- ------- --- -----
Spanish hola como estas 5 3 1 hola
Spanish hola como estas 5 3 2 como
Spanish hola como estas 5 3 3 estas
Spanish como estas tu 3 1 1 como
Spanish como estas tu 3 1 2 estas
Spanish como estas tu 3 1 3 tu
한 단어에 한 줄씩만 있으면 언어 및 단어별로 그룹화하여 합계를 계산할 수 있습니다.
select
t.language,
substring_index(substring_index(t.phrase, ' ', s.seq), ' ', -1) as word,
sum(t.tries) as tries,
sum(t.success) as success,
sum(t.success) / sum(t.tries) as ratio
from tbl t
join seq_1_to_1000 s
on s.seq <= char_length(t.phrase) - char_length(replace(t.phrase, ' ', '')) + 1
group by t.language, word
최종 결과:
language word tries success ratio
-------- -------- ----- ------- ------
English are 13 3 0.2308
English be 3 3 1
English good 8 4 0.5
English hello 8 2 0.25
English how 10 3 0.3
English who 3 0 0
English you 13 3 0.2308
French au 4 0 0
French beaucoup 9 5 0.5556
French choses 2 2 1
French des 2 2 1
French et 3 2 0.6667
French merci 7 3 0.4286
French revoir 7 2 0.2857
French voir 3 2 0.6667
Spanish adios 5 1 0.2
Spanish como 11 6 0.5455
Spanish estas 8 4 0.5
Spanish hola 5 3 0.6
Spanish me 5 1 0.2
Spanish tu 3 1 0.3333
Spanish voy 8 3 0.375
제한 사항
- 이것은 MariaDB 전용 솔루션입니다.oracles MySQL의 경우 영속적인 시퀀스 테이블을 작성하거나 재귀적인 CTE를 사용하여 "인라인"으로 생성해야 합니다.
seq_1_to_1000한 구절당 최대 1000단어까지만 사용할 수 있습니다.하지만, 만약 당신이 더 많이 가질 수 있다면, 그 수를 늘리기만 하면 된다.확실하지 않은 경우 사용seq_1_to_10000000000000001000으로 충분합니다.TB 스트링주의: 실제 테이블이 아니므로 ON 절로 인해 필요한 시퀀스 번호만 생성됩니다.- 는 같은해야 합니다.
<word>[(<space><word>)*].
솔루션 2: 재귀적 CTE
또한 재귀 CTE(MariaDB 10.2 및 MySQL 8.0 이후 지원)를 사용하여 구문을 단어로 분할할 수도 있습니다.다음 사항을 고려하십시오.
with recursive rcte as (
select language, phrase, tries, success, 1 as pos
from tbl t
union all
select language, phrase, tries, success, pos + 1
from rcte
where pos <= char_length(phrase) - char_length(replace(phrase, ' ', ''))
)
select *
from rcte
order by language, phrase, pos
그럼 하겠습니다.1 as pos 늘립니다.pos + 1해 주세요가 재귀할 수 있습니다.은 그 에 도달할 됩니다.where pos <= char_length(phrase) - char_length(replace(phrase, ' ', '')) 모든 만큼 ', '복제'를 '복제'를 '복제'는 '복제'를 하다.pos시퀀스 번호를 지정합니다.
2개의 문구에 대한 결과의 예:
language phrase tries success pos
-------- ------------------ ----- ------- ---
English be good 3 3 1
English be good 3 3 2
English hello how are you 5 2 1
English hello how are you 5 2 2
English hello how are you 5 2 3
English hello how are you 5 2 4
솔루션 #1과 같은 단어 추출 기술을 사용할 수 있게 되었습니다.
with recursive rcte as (
select language, phrase, tries, success, 1 as pos
from tbl t
union all
select language, phrase, tries, success, pos + 1
from rcte
where pos <= char_length(phrase) - char_length(replace(phrase, ' ', ''))
)
select
language,
substring_index(substring_index(phrase, ' ', pos), ' ', -1) as word,
sum(tries) as tries,
sum(success) as success,
sum(success) / sum(tries) as ratio
from rcte
group by language, word
db <> fiddle 데모
언급URL : https://stackoverflow.com/questions/57964349/mysql-grouping-by-each-word-in-phrases-to-count-quantify-ratio
'programing' 카테고리의 다른 글
| JavaScript 인코딩과 동등한 것은 무엇입니까?PHP의 URI 컴포넌트? (0) | 2022.10.05 |
|---|---|
| JavaScript/jQuery를 사용하여 iframe의 내용에 액세스하려면 어떻게 해야 합니까? (0) | 2022.10.05 |
| SQL: *condition*이 다른 행에 있는 경우 행을 업데이트합니다. (0) | 2022.10.05 |
| 오늘 자정 타임 스탬프 구해요? (0) | 2022.10.05 |
| PHP에서 문자열의 마지막 문자를 가져오려면 어떻게 해야 합니까? (0) | 2022.10.05 |