Что лучший способ состоит в том, чтобы хранить/вычислять пользовательские очки?
SELECT ID,
FIRST_VALUE(YEAR) OVER (PARTITION BY ID ORDER BY YEAR DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS year,
FIRST_VALUE(Value) OVER (PARTITION BY ID ORDER BY YEAR DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS value
FROM t
WHERE YEAR = 2030 OR YEAR < 2019
Я думаю, что это стандарт для first_value - postgres может потребоваться отдельное предложение?
4 ответа
Я думаю, что это, безусловно, большой вопрос. Мне приходилось создавать системы, которые ведут себя аналогично этому, особенно когда к таблице с оценками обращаются довольно часто (как в вашем сценарии). Вот мое предложение для вас:
Сначала создайте несколько таблиц, подобных следующей (я использую лучшие практики SQL Server, но назовите их так, как считаете нужным):
UserAccount UserAchievement
-Guid (PK) -Guid (PK)
-FirstName -UserAccountGuid (FK)
-LastName -Name
-EmailAddress -Score
Как только вы это сделаете, продолжайте и создайте представление, которое выглядит примерно так (нет, я не проверял этот SQL, но это должно быть хорошим началом):
SELECT [UserAccount].[FirstName] AS FirstName,
[UserAccount].[LastName] AS LastName,
SUM([UserAchievement].[Score]) AS TotalPoints
FROM [UserAccount]
INNER JOIN [UserAchievement]
ON [UserAccount].[Guid] = [UserAchievement].[UserAccountGuid]
GROUP BY [UserAccount].[FirstName],
[UserAccount].[LastName]
ORDER BY [UserAccount].[LastName] ASC
Я знаю, что вы упомянули некоторую озабоченность по поводу производительности и большого количества запросов, но если вы создадите такое представление, вам никогда не понадобится больше одного. Я рекомендую не делать это материализованным представлением; вместо этого просто индексируйте свои таблицы, чтобы нужные вам поиски (по существу, UserAccountGuid) включали быструю суммирование по всей таблице.
Я добавлю еще одно замечание - если ваша таблица UserAccount становится огромной, вы можете рассмотреть немного больше интеллектуальный запрос, который будет включать в себя имена учетных записей, для которых вы должны получить сводки. Это позволит не возвращать огромные наборы данных на ваш веб-сайт, когда вы только отображаете, как вы знаете, 3-10 пользовательских данных на странице. Я должен подумать немного о том, как сделать это элегантно, но я бы предложил держаться подальше от «IN»
Для очень высоких соотношений чтения / записи денормализация является очень допустимым вариантом. Вы можете использовать индексированное представление, и данные будут декларативно синхронизироваться (так что вам никогда не придется беспокоиться о плохих данных). Обратной стороной является то, что он синхронизирован ... поэтому обновления общего количества данных в хранилище являются синхронным аспектом фиксации действия оценки. Обычно это происходит довольно быстро, но это дизайнерское решение. Если вы денормализуете себя, вы можете выбрать, хотите ли вы иметь какую-то систему отложенного обновления.
Лично я бы выбрал индексированное представление для запуска, а затем вы можете легко заменить его конкретной таблицей, если вам нужно диктуют.
In the past we've always used some sort of nightly or perodic cron job to calculate the current score and save it in the database - sort of like a persistent view of the SUM on the activities table. Like most "best practices" they are simply guidelines and it's often better and more practical to deviate from a specific hard nosed practice on very specific areas.
Plus it's not really all that much of a deviation if you use the cron job as it's better viewed as a cache stored in the database.
Если у вас есть отдельная таблица оценок, вы можете обновлять ее каждый раз, когда элемент передается или пользователь публикует комментарий. Вы можете сделать это с помощью триггера или в коде сайтов.
Оценки пользователей будут обновляться непрерывно, и их можно будет быстро запросить для отображения.