Раздел 4: Работа с другими базами слов

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

В этом разделе мы расскажем, как делать более сложные выборки, выборки из двух баз, а также как работать с базами на других языках.

1. Использование simple в текстовом поиске

В примерах, которые приведены в предыдущем разделе, английский был указан как язык текстовой информации. Соответственно, при поиске используются правила для английского языка, например, нормализация слов (приведение к лексеме, т.е. общей форме для разных словоформ) и стоп-слова (слова, которые не участвуют в поиске).

Для других языков есть набор своих правил. Если вы хотите работать с базой на другом языке, то вам нужно указать этот язык в условии запроса, которое начинается с to_tsvector.

Если у вас данные в базе на нескольких языках или вы по какой-то причине не хотите, чтобы применялись нормализация слов запроса и стоп-слова, то в качестве языка вы можете указать simple, для которого отсутствуют нормализация и стоп-слова:

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

Выборка по одному слову из таблицы в PostgreSQL с языком simple

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

CREATE INDEX "simple_data_table_idx" ON "eng_data_table" USING gin ((to_tsvector('simple'::regconfig, ("keyword")::text)));

Если вы визуально сравните команду индексирования с той, которую мы делали раньше для индексирования таблицы с английскими словами, то единственное отличие, это указание на язык.

Если вы сравните результаты поиска словоформ flight и flights, то в случае использования английского языка количество найденных результатов будет одним и тем же, а поиск артикля the вернет ноль результатов. Но если в качестве языка указан simple, то количество результатов по поиску flight и flights будет разным, а поиск словосочетаний, содержащих артикль the, вернет большое количество результатов.

Для одной и той же таблицы может быть создано несколько индексов, т.е. можно создать отдельные индексы с указанием english, german, french, simple и т.д. При запросе PostgreSQL выберет оптимальный, важно, чтобы индекс был создан.

Если запрос работает очень долго, то это означает, что индекс не создан.

2. Создание таблицы данных на других языках.

Для наших примеров создадим две таблицы - с названием rus_table1 и rus_table2. Cинтаксис создания таблицы рассмотрен в первом разделе, например, для создания таблицы rus_table1 напишем:

CREATE TABLE "rus_table1" (
"keyword" VARCHAR NOT NULL
) WITH (fillfactor = 100, oids = false);

Создание таблицы для русской базы

Создадим два файла со словами – очень коротких, исключительно для наших примеров: rus_keywords1.txt и rus_keywords2.txt:

Тестовые таблицы с русскими словами

Теперь нужно скопировать данные из rus_keywords1.txt в rus_table1, а из rus_keywords2.txt – в rus_table2.

Если вы попытаетесь скопировать неанглийские слова в таблицу без указания кодировки, например, такой командой:

copy "rus_table1" from 'c:\Temp\rus_keywords1.txt';

Ошибка при копировании русских слов без кодировки

то PostgreSQL вернет ошибку, потому, что для других языков нужно обязательно указать кодировку, для русского языка это кодировка WIN1251:

copy "rus_table1" from 'c:\Temp\rus_keywords1.txt' encoding 'WIN1251';

Копирование русских слов с указанием кодировки Win1251

При копировании данных на большинстве европейских языков (например, французском, немецком, испанском, итальянском, датском, голландском) используется кодировка WIN1252.

Если текстовый файл с неанглийскими словами записан в кодировке UTF-8, то при копировании данных в таблицу можно либо указать кодировку UTF-8, либо не указывать вообще никакой кодировки, поскольку UTF-8 – кодировка по умолчанию.

Подробнее о кодировках.

Следующим шагом нужно оптимизировать вновь созданную таблицу с помощью такого запроса:

vacuum "rus_table1";

Далее создадим индекс таблицы с указанием русского в качестве языка для текстовой информации в таблице:

CREATE INDEX "rus_table1_idx" ON "rus_table1" USING gin
((to_tsvector('russian'::regconfig, ("keyword")::text)));

И завершим создание оптимизированной таблицы запросом:

vacuum analyze "rus_table1";

для эффективного использования индекса таблицы.

3. Сравнение содержания двух таблиц

3.1. Для получения списка слов, которые одновременно находятся в обеих таблицах, нужно применить следующий запрос:

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

3.2. Получим список уникальных слов, которые составлен из слов в двух таблицах:

select rus_table1.keyword from rus_table1
union
select rus_table2.keyword from rus_table2;

Получение уникального списка из двух таблиц

3.3. Получим список слов, которые есть в первой таблице, но отсутствуют во второй:

select
  rus_table1.keyword
from
  rus_table1
left join
  rus_table2
on
  rus_table1.keyword = rus_table2.keyword
where
  rus_table2.keyword is NULL;

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

3.4. Подобным образом для получения слов, уникальных для второй таблицы, сделаем запрос:

select
  rus_table2.keyword
from
  rus_table2
left join
  rus_table1
on
  rus_table1.keyword = rus_table2.keyword
where
  rus_table1.keyword is NULL;

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

Напоминаем, что для сохранения результата выборки запрос нужно заключить в круглые скобки, перед открывающей скобкой написать copy, а после закрывающей скобки указать путь к файлу, в который будут сохранены результаты выборки. Синтаксис экспорта выборки описан в разделе 2. По умолчанию выборка экспортируется в кодировке UTF-8, но можно указать и другую.

© 2014 - 2017 Букварикс