Как сократить время выполнения представления

У меня есть 3 таблицы: пользовательская, административная и пользовательская. Обе таблицы admin и cust имеют внешний ключ для таблицы user_account. По сути, у каждого пользователя есть запись о пользователе, и тип пользователя определяется тем, есть ли у него запись в таблице admin или cust.

user                   admin                   cust
 user_id         user_id | admin_id      user_id | cust_id
---------       ---------|----------    ---------|---------
 1               1       | a             2       | dd
 2               4       | b             3       | ff
 3            
 4            

Затем у меня есть таблица login_history, которая записывает user_id и отметку времени входа каждый раз, когда пользователь входит в приложение.

  login_history
 user_id | login_on
---------|---------------------
 1       | 2022-01-01 13:22:43
 1       | 2022-01-02 16:16:27
 3       | 2022-01-05 21:17:52
 2       | 2022-01-11 11:12:26
 3       | 2022-01-12 03:34:47

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

login_counts_view
 week_start_date | admin_count | cust_count
-----------------|-------------|------------
 2022-01-01      | 1           | 1
 2022-01-08      | 0           | 2
 2022-01-15      | 0           | 0
 .
 .
 .
 2022-12-31      | 0           | 0

Обратите внимание, что первая неделя (01.01.2022) имеет только 1 счетчик для admin_count, несмотря на то, что администратор с user_id 1 вошел в систему дважды на этой неделе.

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

CREATE VIEW login_counts_view  AS
SELECT 
    week_start_dates.week_start_date::text AS week_start_date,
    count(distinct a.user_id) AS admin_count,
    count(distinct c.user_id) AS cust_count
FROM (
    SELECT 
        to_char(i::date, 'YYYY-MM-DD') AS week_start_date 
    FROM 
        generate_series(date_trunc('year', NOW()), to_char(NOW(), 'YYYY-12-31')::date, '1 week') i
) week_start_dates

LEFT JOIN login_history l ON l.login_on::date BETWEEN week_start_dates.week_start_date::date AND (week_start_dates.week_start_date::date + INTERVAL '6 day')::date
LEFT JOIN admin a ON a.user_id = l.user_id 
LEFT JOIN cust c ON c.user_id = l.user_id 
GROUP BY week_start_date;

Есть ли у кого-нибудь советы о том, как сделать этот запрос более эффективным?

3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
0
0
26
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Идея

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

Затем используйте соединения с целыми числами, представляющими псевдонедели, вместо значений дат и сравнений.

Реализация

Представление для реализации этого следующее:

CREATE VIEW login_counts_view_fast  AS
          WITH RECURSIVE Numbers(i) AS ( SELECT 0 UNION ALL SELECT i + 1 FROM Numbers WHERE i < 52 )     
        SELECT CAST ( date_trunc('year', NOW()) AS DATE) + 7 * n.i  week_start_date
             , count(distinct lw.admin_id)                          admin_count
             , count(distinct lw.cust_id)                           cust_count
          FROM (
                     SELECT i FROM Numbers 
               ) n
     LEFT JOIN (
                     SELECT admin_id
                          , cust_id
                          , base
                          , pit
                          , pit-base                     delta
                          , (pit-base) / (3600 * 24 * 7) week  
                       FROM (
                                  SELECT a.user_id  admin_id
                                       , c.user_id  cust_id
                                       , CAST ( EXTRACT ( EPOCH FROM l.login_on )                 AS INTEGER ) pit
                                       , CAST ( EXTRACT ( EPOCH FROM date_trunc('year', NOW())  ) AS INTEGER ) base
                                    FROM login_history l
                               LEFT JOIN admin         a ON a.user_id = l.user_id
                               LEFT JOIN cust          c ON c.user_id = l.user_id  
                            ) le
               ) lw
                        ON lw.week = n.i
      GROUP BY n.i
;     

Некоторые замечания:

  • Значения эпохи — это количество секунд, прошедших с абсолютной базовой даты и времени (в частности, 01.01.1970 0h00).
  • CASTS необходимы для преобразования двойных чисел в целые числа и временных меток в даты, как это предписано сигнатурами функций даты postgresql, и для обеспечения целочисленной арифметики.
  • Рекурсивный подзапрос — это генератор последовательных целых чисел. Возможно, его можно заменить вызовом generate_series (не проверено)

Оценка

Посмотрите это в действии в эта бд рабочий пример

План запроса указывает на экономию 50-70% времени выполнения.

Вау, большое спасибо! Это действительно работает. Я на самом деле забыл, что в таблице login_history также есть login_success, который может быть либо Y, либо N, чтобы указать, была ли попытка входа успешной или нет, поэтому я немного изменил его, чтобы он работал с этим флагом, добавив login_success в левые объединенные запросы выбора и добавил ФИЛЬТР(ГДЕ lw.login_success='Y') для различных подсчетов.

user3842536 23.04.2022 03:24

Могу я также добавить дополнительный вопрос? Что, если мне нужен список недель в произвольном диапазоне дат? или, например, со 2 февраля 2020 г. по 1 февраля 2021 г.?

user3842536 23.04.2022 03:39

Измените рекурсивный подзапрос, чтобы сгенерировать номера фрагментов: для дат начала и окончания вычислите номера фрагментов так же, как это делается для записей login_history, и используйте их в качестве начального значения и условия завершения. В частности, WITH RECURSIVE Numbers(i) AS ( SELECT -99 UNION ALL SELECT i + 1 FROM Numbers WHERE i < -46 ) SELECT i FROM Numbers; за указанные вами даты. Полное вычисление см. в здесь.

collapsar 23.04.2022 04:01

Другие вопросы по теме