Здравствуйте, уважаемые читатели блога о контент-анализе!
Сегодня будет пост о создании базовой матрицы контент-анализа в MICROSOFT SQL SERVER.
В MS SQL Server я совершенно не профи, но кое что могу показать. Этот пост сугубо для непрофессионалов в области SQL. В нём будет рассказано, как можно создать базовую матрицу контент-анализа, аналогичную создаваемой в программе ЛЕКТА (ЕЁ ПРИМЕР ВОТ ТУТ), используя MS SQL сервер. Матрица отражает присутствие каждого слова из группы слов в каждом из текстов. Есть в тексте слово — в ячейке 1, нет слова — 0. Далее, можно, например, факторизовать матрицу, дабы редуцировать количество переменных до меньшего количество факторов — по сути, просто выделить более общие темы, представленные группами слов из вашего словаря на основе корреляций переменных исходной матрицы контент-анализа.
На MS SQL сервере такую работу провести совсем нетрудно, а главное, нет необходимости вводить в словарь контент-анализа все возможные словоформы слов словаря — достаточно одной формы. Остальные словоформы MS SQL сервер найдет сам. Например, в YOSHIKODER надо позаботиться и о внесении всех словоформ слов словаря.
Кстати, полнофункциональная демо версия MS SQL сервера действует пол года.
Установить сервер от MICROSOFT можно ЗДЕСЬ.
Разбираться с задачей будем на основе многострадального массива текстов о Нижнем Новгороде (это массив из 250 глубинных интервью). В принципе, тематика для нас абсолютно не важна — нам нужны только тексты. Массив фрагментов интервью находится тут — nn texts. Файлик сохраняем в формате CSV. Обязательно как-нибудь назовите первый столбец на латинице.
Для того, чтобы работать с текстами, первоначально нам необходимо загрузить их на сервер. Для этого удобнее использовать мастер импорта и экспорта MS SQL сервера (устанавливается вместе с сервером). В списке программ находим мастер, открываем. Видим следующее и нажимаем ДАЛЕЕ:
В качестве источника выбираем НЕСТРУКТУРИРОВАННЫЙ ФАЙЛ
Выбираем наш файл на жестком диске через поле ОБЗОР.
Переходим в подменю ДОПОЛНИТЕЛЬНО и задаём максимальное количество символов в ячейках с нашими текстами. Например, 1500. Если тексты в таблице будут длиннее — экспорт не будет произведен. После этого нажимаем ДАЛЕЕ.
В подменю СТОЛБЦЫ в качестве разделителя лучше указать вертикальную черту:
Далее нужно выбрать источник данных SQL SERVER NATIVE CLIENT 11.0, потом ввести название сервера (вы его задаёте при установке MS SQL сервера) — у меня aryumin. Далее выбрать название базы данных — я для этого поста создал базу данных под названием nn. (База данных создаётся через MS SQL Sever Managment studio (она также устанавливается при установке сервера).
Нажимаем далее, далее, далее и потом ГОТОВО.
Тексты успешно импортированы.
Теперь заходим в MS SQL SERVER MANAGMENT STUDIO, создаём новый запрос (кнопка создать запрос), подключающий нас к базе данных nn
USE nn
GO
Выделяем запрос и нажимаем ВЫПОЛНИТЬ
Ниже на скрине вы видите запрос
SELECT * FROM [dbo].[nn texts]
Это запрос покажет нам всю импортированную на сервер таблицу. Выделим его и нажмём ВЫПОЛНИТЬ
Собственно вот и наши тексты из таблицы.
Для дальнейшей работы нам во-первых потребуется создать дополнительный столбец и создать на нём уникальный индекс:
ALTER TABLE [dbo].[nn texts]
ADD ID INT IDENTITY(1,1) NOT NULL
Теперь нам надо создать уникальный индекс на добавленном столбце. Выполняем запрос:
CREATE UNIQUE INDEX nn ON [dbo].[nn texts](id)
Теперь создаём каталог полнотекстового индекса:
CREATE FULLTEXT CATALOG nn_catalog
Теперь нам надо создать полнотекстовый индекс на столбце с нашими текстами. Для этого нажимаем правой кнопкой мыши на название нашей таблицы в обозревателе объектов и переходим в меню ПОЛНОТЕКСТОВЫЙ ИНДЕКС/ОПРЕДЕЛИТЬ ПОЛНОТЕКСТОВЫЙ ИНДЕКС.
В мастере полнотекстового индекса выбираем столбец таблицы, которому будет присвоен индекс:
Дальше:
Теперь выберем созданный нами каталог полнотекстового индекса nn_catalog:
Снова ДАЛЕЕ, ДАЛЕЕ и ГОТОВО. Получаем сообщение о том, что всё ОК (надеюсь и у вас ОК 🙂 )
Теперь наша задача просмотреть присутствие слов или групп слов с учетом всех их словоформ в каждом из текстов. Соответственно, словари могут быть самыми разными. Для поиска наиболее часто встречающихся слов можете воспользоваться лемматизатором. Например, вот ЭТИМ.
Допустим, у нас всего три группы слов (разумеется, если это словарь наиболее частотных слов, у вас их может быть и сто, и двести, и триста, пр). Группы неполные — пишу их просто для примера, наполняя всего несколькими основными словами по теме:
- Достопримечательности и культура Нижнего Новгорода: достопримечательности, памятник, кремль, музей, выставка, экспозиция, художник, картина, консерватория, филармония
- Транспортная инфраструктура: дороги, пробки, машины, авто, автомобили, парковки, парковаться, трамвай, троллейбус, метро, пассажир, трасса, шоссе
- Зоны отдыха: парк, сквер
Теперь делаем запрос к таблице, назначая значения трём переменным (переменные соответствуют каждой группе слов)
Таким образом — вот запрос:
DECLARE @culture varchar(200), @dorogi varchar(200), @parki varchar(200)
SET @culture = ‘достопримечательности, памятник, кремль, музей, выставка, экспозиция, художник, картина, консерватория, филармония’
SET @dorogi = ‘дороги, пробки, машины, авто, автомобили, парковки, парковаться, трамвай, троллейбус, метро, пассажир, трасса, шоссе’
SET @parki = ‘парк, сквер’
SELECT ID, texts_nn,
(SELECT 1 WHERE FREETEXT(texts_nn, @culture)) AS ‘культура’,
(SELECT 1 WHERE FREETEXT(texts_nn, @dorogi)) AS ‘транспорт’,
(SELECT 1 WHERE FREETEXT(texts_nn, @parki)) AS ‘отдых’
FROM [dbo].[nn texts]
Результат виден под запросом. В строке, где слово из группы встречается в тексте, стоит 1, если не встречается — NULL
Можно выгрузить таблицу, нажав на нее правой кнопкой мыши и выбрав КОПИРОВАТЬ С ЗАГОЛОВКАМИ или СОХРАНИТЬ РЕЗУЛЬТАТ КАК:
Ну а дальше можете посчитать частотность слов каждой группы в EXCEL или приступить к качественной интерпретации. Если хотите просто посмотреть частотность каждой из категорий слов, можете использовать вот такой запрос:
Вот сам запрос:
SELECT ‘достопримечательности’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn,’достопримечательности, памятник, кремль, музей, выставка, экспозиция, художник, картина, консерватория, филармония’)
UNION ALL
SELECT ‘транспорт’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn, ‘дороги, пробки, машины, авто, автомобили, парковки, парковаться, трамвай, троллейбус, метро, пассажир, трасса, шоссе’)
UNION ALL
SELECT ‘пакрки’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn, ‘парк, сквер’)
Для компактности можно также присвоить слова категорий переменным:
Вот сам запрос:
DECLARE @culture varchar(200), @dorogi varchar(200), @parki varchar(200)
SET @culture = ‘достопримечательности, памятник, кремль, музей, выставка, экспозиция, художник, картина, консерватория, филармония’
SET @dorogi = ‘дороги, пробки, машины, авто, автомобили, парковки, парковаться, трамвай, троллейбус, метро, пассажир, трасса, шоссе’
SET @parki = ‘парк, сквер’
SELECT ‘достопримечательности’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn, @culture)
UNION ALL
SELECT ‘транспорт’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn, @dorogi)
UNION ALL
SELECT ‘пакрки’ AS ‘наименование тематики’, COUNT(texts_nn) AS ‘количество сообщений’
FROM [dbo].[nn texts] where FREETEXT(texts_nn, @parki)
Под компактностью я подразумеваю вынос рядов слов за пределы самого запроса — вначале создаём переменные и задаём им группы слов в качестве значений, а уже потом ищем. Если у вас много слов категорий, то это разумное решение.
Как я уже сказал, дальше можно, например, факторизовать матрицу, как это описано ТУТ. Это актуально в частности, если вы создали словарь наиболее часто встречающихся слов, и хотите их интегрировать в более крупные тематические группы.
Вот 🙂
Если есть какие-то вопросы — пишите в комментариях.
Спасибо!
