sql - 在 SQL 中计算文档频率

Question

如何使用SQL计算文档频率？

文档频率是术语出现的文档(行)数，而不是术语的总数(即术语频率)。

我可以这样计算词频:

create table countries (
  iso char(2) primary key,
  name text not null unique
);

insert into countries values 
('GS', 'South Georgia and the South Sandwich Islands'),
('ZA', 'South Africa');

select
  term
  , count(*) as term_frequency
from 
  countries
  , regexp_split_to_table(name, '[^\.\w]') term
where 
  term <> ''
group by
  term;

但是我不太确定如何获取文档频率(对于“South”应该是 2 而不是 3)。

输出应该是这样的:

term     document_frequency
---------------------------
South    2
Georgia  1
and      1
the      1
Sandwich 1
Islands  1
Africa   1

Answer 1

因此计算每个术语的不同文档的数量:

select term, count(DISTINCT iso) as doc_frequency
from   countries
     , regexp_split_to_table(name, '[^\.\w]') term
where  term <> ''
group  by term;