пятница, 13 ноября 2009 г.

Построение отчетов - Временные таблицы и виды

При проведении анализа данных и построении отчетов нередко требуется реализовать сложную логику обработки данных. Написание множества огромных и малопонятных (которые с течением времени становятся и вовсе непонятными) SQL-запросов становится тяжким роком разработчика. Необходимость расширить функциональность или изменить алгоритм вычисления способна привести к тому, что все запросы переписываются заново. Что ж, бывает и так. А если написанные запросы-монстры еще и работают медленно (что прямое следствие их запутанности), то становится совсем непонятно, как бы всего этого избежать. Вот об этом мы и поговорим.

После того, как я Вас немного придушил кошмарами, можете вздохнуть свободно: существуют способы сделать все намного удобнее и получить понятный код и отличную скорость работы. Достаточно лишь научиться ориентироваться в мире временных видов и таблиц PostgreSQL. Вы удивлены и полны сомнений? Наверное, Вы думаете о том, почему об их возможностях не пишут во всех справочниках и руководствах. Конечно, пишут, надо только внимательно читать. Временные виды и таблицы работают аналогичны обыкновенным, но они видны только в рабочем сеансе создавшего их пользователя и автоматически уничтожаются по завершении сеанса. Можно создавать временные объекты с именами существующих, это полезно в частности в том случае, когда нужно ограничить выбираемые из таблицы или вида записи, но не хочется менять клиентское приложение.

Попробуем сами

select count(*) from out
limit 1000000;
1000000
create temp view out as
select * from data.out
limit 1000;
select count(*) from out;
1000

А теперь, не закрывая первый сеанс, откроем второй и попробуем в нем обратиться к созданному виду

select count(*) from out;
ERROR: relation "out" does not exist
create temp view out as
select * from data.out
limit 500;
select count(*) from out;
500

Как мы и ожидали, такого вида нет. А это означает, что для каждого пользовательского подключения к базе данных можно создавать временные объекты с одним и тем же именем и каждый пользователь будет видеть только свои собственные объекты! Теперь в первом сеансе пользователь видит 1000 записей во временном виде, а во втором сеансе во временном виде с тем же названием только 500 записей.

Мы можем создать простые временные виды со всеми необходимыми условиями на выборку из каждой исходной таблицы, а потом проводить операции уже с этими видами. Поскольку временные виды будут содержать лишь необходимые данные, оптимизировать больше нечего. Разумеется, для исходных таблиц необходимо провести заранее все мероприятия из раздела "Оптимизация данных".

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

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

А вот пример использования временных объектов для построения отчета (выдернул из функции на pltcl):

# выбираем последнюю проверку каждой точки за указанный интервал
spi_exec "create temp view ${db_prefix}checks as
select distinct on (object_id) check_date, user_id, object_id
from cache.checks
where check_date between '$date_from'::date and '$date_to'::date
and typename='[quote $2]' and unitname='[quote $3]'
and object_id IN (select id from merch.objects_sales
where $sale_filter)
and user_id IN (select id from auth.users
where $user_filter)
order by object_id, check_date desc"

# выбираем вопросы, задававшиеся при последней проверке
spi_exec "create temp view ${db_prefix}questions_scores as
select d.*
from cache.questions_scores d
where check_date between '$date_from'::date and '$date_to'::date
and $question_filter and
(d.check_date, d.user_id, d.object_id) IN (select check_date,
user_id, object_id from ${db_prefix}checks)"

spi_exec "CREATE temp view ${db_prefix}questions_expand AS
SELECT s.id as object_id, macroregion, region,
case when s.code='' then 'Удалена' else s.code end as code,
town, address, dealer, subdealer, category, cluster,
trademark, person, phone, u.id as user_id, u.name as user,
c.check_date, q.id as question_id, q.text as question,
c.score_question, c.score
FROM ${db_prefix}questions_scores c
left join merch.objects_sales s on c.object_id=s.id
left join auth.users u on c.user_id =u.id
left join merch.questions q on c.question_id=q.id"

spi_exec "CREATE temp view ${db_prefix}questions_stat_short AS
select question_id, sum(score) as count_score, count(*) as count
from ${db_prefix}questions_scores c
group by question_id"

spi_exec "CREATE temp table ${db_prefix}questions_stat AS
select q.id as question_id, q.text as question, c.count_score, c.count
from ${db_prefix}questions_stat_short c
left join merch.questions q on c.question_id=q.id"


# выбираем ответы, полученные при последней проверке
spi_exec "create temp view ${db_prefix}answers_scores as
select check_date, user_id, object_id, question_id, answer_id,
score_answer, score, answer_text
from cache.answers_scores d
where check_date between '$date_from'::date and '$date_to'::date
and $question_filter and $answer_filter and
(check_date, user_id, object_id) IN (select check_date,
user_id, object_id from ${db_prefix}checks)"

spi_exec "CREATE temp view ${db_prefix}answers_expand AS
SELECT s.id as object_id, macroregion, region,
case when s.code='' then 'Удалена' else s.code end as code,
town, address, dealer, subdealer, category, cluster,
trademark, person, phone, u.id as user_id, u.name as user,
c.check_date, q.id as question_id, q.text as question,
a.id as answer_id, a.text as answer,
c.score_answer, c.score, a.is_score, c.answer_text
FROM ${db_prefix}answers_scores c
left join merch.objects_sales s on c.object_id=s.id
left join auth.users u on c.user_id =u.id
left join merch.questions q on c.question_id=q.id
left join merch.answers a on c.answer_id=a.id"


spi_exec "CREATE temp view ${db_prefix}answers_stat_short AS
select question_id, answer_id, sum(score) as count_score, count(*) as count
from ${db_prefix}answers_scores c
group by question_id, answer_id"

spi_exec "CREATE temp table ${db_prefix}answers_stat AS
select q.id as question_id, q.text as question,
a.id as answer_id, a.text as answer,
a.is_score, c.count_score, c.count
from ${db_prefix}answers_stat_short c
left join merch.questions q on c.question_id=q.id
left join merch.answers a on c.answer_id=a.id"


Такой набор простых sql-запросов достаточно просто писать, при том, что планировщик запросов построит эффективный план для выполнения каждого из них. В итоге получится набор нескольких таблиц, где лежат уже готовые для отображения в отчете данные. Такой итерационный подход позволяет строить эффективные алгоритмы анализа любой сложности. Залогом эффективности является указание в каждой итерации всех необходимых условий на выборку, дублирование одних и тех же условий делает алгоритм обработки однозначно определенным, исключая возможные варианты, что позволит планировщику запроса построить более эффективный план выполнения. То есть планировщик сам решит, когда и как эти условия применить, наша задача состоит в том, чтобы предельно четко сформулировать логику анализа данных.

P.S. На самом деле в оригинале я использовал не временные таблицы и виды, а обычные, что еще более эффективно при определенной архитектуре базы данных. Использование временных объектов это шаг к полному перепроектированию системы, поскольку после разложения на составляющие сложных запросов можно переходить к созданию функционалов.

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru