Вычисляемый столбец Postgresql

У меня есть вопрос, на который я не нашел ответа.

Вот пример:

Таблица: интересы

  • id: целое число - первичный ключ
  • имя: имя интереса - уникальное
  • популярность: интерес популярность

Таблица: пользователи

  • id: целое число - первичный ключ
  • электронная почта: строка - уникальная
  • интересы: массив целых чисел — внешние ключи таблицы «интересы»

Я хочу, чтобы столбец «популярность» таблицы «интересы» вычислялся благодаря таблице «пользователи».

Пользователи таблицы

  id | email           | interests
  0  | toto0.gmail.com | [ 0 ]
  1  | toto1.gmail.com | [ 0, 1 ]
  2  | toto2.gmail.com | [ 1 ]
  3  | toto2.gmail.com | [ 2 ]

Таблица интересов

  id | name         | popularity
  0  | 'interest 0' | 2
  1  | 'interest 1' | 2
  2  | 'interest 2' | 1
  3  | 'interest 3' | 0

Я пробовал это:

  UPDATE interests SET popularity = (SELECT COUNT(*) from public.users where ARRAY[interests] @> ARRAY[interests.id]);

Но я не хочу запускать запрос для обновления таблицы интересов. Я имею в виду, что я хочу, чтобы столбец «популярность» заполнялся автоматически, когда пользователь подписывается на него.

Если я создам такое «представление», оно должно работать так, как я хочу?

  CREATE VIEW interests_popularity (id, popularity) AS
     SELECT COUNT(1) as popularity, interest.id
     FROM public.user, interest
     WHERE ARRAY[public.user.interests] @> ARRAY[interest.id]
     GROUP BY interest.id;

Есть ли более эффективный способ сделать это?


postgresql sql postgresql-9.4
person Xavier S.    schedule 07.05.2015    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Константин предложил использовать триггер для повышения популярности в таблице «Интересы» каждый раз, когда пользователь подписывается на определенный интерес. Это может быть достаточно эффективным, пока слишком много сеансов не ожидают блокировки на уровне строки для определенного интереса, который обновляется несколькими сеансами.

Если бы вы сняли ограничение уникальности для таблицы «interests», вы могли бы записывать новые подписки «на лету» со вставкой, которая ничего не блокировала бы. Чтобы получить общую статистику, вам нужно будет выполнить SELECT id, count(*) FROM interests GROUP BY id, что может быть достаточно быстро для вас, если вы периодически обновляете всю таблицу, чтобы перегруппировать ее для устранения дубликатов.

person pstef    schedule 07.05.2015
comment
Я думаю, что буду использовать задание cron для вычисления таблицы «интересы» один или два раза в день. - person Xavier S.; 07.05.2015

arrow_upward
0
arrow_downward

Я изменил свою модель, чтобы справиться с этой проблемой.

Есть отдельная таблица, и когда мне нужно получить проценты по популярности, я просто должен рассчитывать на «УНИКАЛЬНЫЙ» ключ.

person Xavier S.    schedule 30.05.2015