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

При проведении анализа данных и построении отчетов нередко требуется реализовать сложную логику обработки данных. Написание множества огромных и малопонятных (которые с течением времени становятся и вовсе непонятными) 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. На самом деле в оригинале я использовал не временные таблицы и виды, а обычные, что еще более эффективно при определенной архитектуре базы данных. Использование временных объектов это шаг к полному перепроектированию системы, поскольку после разложения на составляющие сложных запросов можно переходить к созданию функционалов.