Раздел 3: Получение и экспорт выборок из базы
английских ключевых слов (продолжение)

1. Объединение результатов выборок

Для примера этого запроса мы возьмем синтаксис запроса двух выборок – одной по слову «flights», а другой по слову «railway» и используем оператор union для объединения результатов в общей выборке.

Пример выборки с объединенными результатами (при ограничении вывода в 100 результатов для ускорения отработки запроса):

(select
  "keyword"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
limit
  100)
union
(select
  "keyword"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'railway')
limit
  100);

В этом запросе синтаксис выборок был взят в круглые скобки, поскольку есть ограничение вывода в 100 результатов, но если нужно объединить полные выборки, то нужно убрать limit 100 и скобки:

select
  "keyword"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
union
select
  "keyword"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'railway');

Примечание: При использовании оператора union происходит объединение уникальных словосочетаний. Т.е. если в выборке есть словосочетание, в котором есть оба слова одновременно, то в объединенной выборке оно будет присутствовать только один раз. Но если нужно объединение неуникальных словосочетаний, то вместо оператора union нужно использовать оператор union all.

2. Вывод количественной информации о словосочетаниях

Для того, чтобы вывести количество слов, а также количества знаков во фразах, содержащих какое-либо слово (в нашем примере это слово «flights»), воспользуйтесь такой командой (также с ограничением в 100 результатов):

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
limit
  100;

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array
("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
limit
  100;

где в результирующей выборке появятся две дополнительные колонки — колонка «KeywordLength» – это длина фразы в знаках, включая пробелы и колонка «WordCount» – это количество слов во фразе.

3. Сортировка фраз по алфавиту

Отсортируем по алфавиту фразы, содержащие слово «flights», для которых также выведем длину фразы в знаках и количество слов (с ограничением в 100 результатов). Используем синтаксис предыдущего запроса с добавлением выражения с оператором order by:

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
order by
  "keyword" desc /* asc */
limit
  100;

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array
("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
order by
  "keyword" desc /* asc */
limit
  100;

Этот запрос выведет три колонки результатов, где в колонке KeywordLength будет длина фразы в знаках, в колонке WordCount — количество слов. Фразы выведены по алфавиту в порядке от большего к меньшему (убывания). Для этого используется параметр desc. Для сортировки от меньшего к большему (возрастания) нужно использовать параметр asc. В нашем примере он закомментирован.

4. Сортировка по нескольким колонкам

Для того, чтобы отсортировать данные таблицы по нескольким колонкам, используйте синтаксис предыдущего запроса, модифицируя выражение с оператором order by:

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
order by
  "WordCount" asc,
  "KeywordLength" asc,
  "keyword" asc
limit
  100;

select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array
("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
order by
  "WordCount" asc,
  "KeywordLength" asc,
  "keyword" asc
limit
  100;

В этом примере все колонки будут отсортированы по возрастанию.

5. Подсчет количества записей по условию

Для того, чтобы узнать общее количество записей (строк) в базе, в которых содержится определенное слово (в нашем примере «flights»), нужно сделать следующий запрос:

select
  count(*)
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights');

В результате выведется число:

6. Получение списка слов базы и подсчет уникальных слов

Следующие запросы будут особенно интересны тем, кто хочет эффективнее настроить фильтры для выборки.

Для того, чтобы получить список всех слов, которые встречаются в базе вместе с искомым словом (в нашем примере «flights»), нужно сделать такой запрос:

select
  regexp_split_to_table("keyword", '\s+') as "Word"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights');

В колонке Word будет сформирован список слов, которые используются во фразах вместе со словом «flights». Это неупорядоченный список неуникальных слов.

Полезнее развить эту идею и узнать, какие уникальные слова сколько раз встречаются в базе. Для этого сделаем такой запрос:

with "WordTable" as
(
select
  regexp_split_to_table("keyword", '\s+') as "Word"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
)
select
  "Word",
  count("Word") as "Cnt"
from
  "WordTable"
group by
  "Word"
order by
  "Cnt" desc
limit
  100;

Как видно, мы использовали предыдущий запрос, обрамив его круглыми скобками. В запросе добавлена группировка слов для формирования списка уникальных слов, а также столбец Cnt с подсчетом количества употреблений каждого уникального слова в порядке убывания.

7. Экспорт списка словосочетаний с количественной информацией в файл .csv

Для того, чтобы экспортировать не только список словосочетаний, содержащих искомое слово (в нашем примере «flights»), но и колонки с количеством слов и знаков в этих словосочетаниях, нужно выполнить такую команду:

copy
(
select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
limit
  100
)
to
  'c:\temp\all.csv' HEADER DELIMITER ',' QUOTE '"' FORCE QUOTE "keyword" csv ENCODING 'UTF8';

copy
(
select
  "keyword",
  length("keyword") as "KeywordLength",
  array_length(regexp_split_to_array
("keyword", '\s+'), 1) as "WordCount"
from
  "eng_data_table"
where
  to_tsvector('english', "keyword") @@ to_tsquery('english', 'flights')
limit
  100
)
to
  'c:\temp\all.csv' HEADER DELIMITER ',' QUOTE '"' FORCE QUOTE "keyword" csv ENCODING 'UTF8';

где c:\temp\all.csv — это адрес файла, в который будет записана выборка, в качестве разделителя выбрана запятая, ключевое слово в файле .csv принудительно заключено в двойные кавычки, а кодировка выбрана 'UTF8' (как наиболее универсальная).

Если вы пользуетесь программой MS Excel для просмотра файлов в формате .csv, то вам лучше посмотреть, какой формат разделителя колонок программа правильно интерпретирует и если это, например, точка с запятой (;), то вместо запятой, которая дана в примере, вам нужно заключить в одинарные кавычки точку с запятой: ... HEADER DELIMITER ';' ...

Принудительное заключение ключевого слова в двойные кавычки помогает избежать некоторых проблем при наличии специальных символов в составе словосочетаний, но при этом Excel интерпретирует файл .csv, в котором словосочетания взяты в двойные кавычки, правильно и выводит их в колонке без кавычек.

На этом шаге мы заканчиваем основной раздел инструкции по созданию базы английских ключевых слов в PostgreSQL и получению наиболее необходимых выборок. В дополнительном разделе инструкции информация о работе с другими базами.

Если в инструкции есть неточности или непонятные места, просим написать о них в нашу службу поддержки.

Раздел 1. Установка программного обеспечения базы данных PosrgreSQL и импорт базы ключевых слов

Раздел 2. Получение и экспорт выборок из базы английских ключевых слов (начало)

Раздел 4. Работа с другими базами слов (дополнение)

Скачать базу английских ключевых слов