Блог

Главная - Вопросы по программированию - SQL Server: Лучший способ автоматической передачи обновлений в сводную таблицу на основе изменений из другой таблицы

SQL Server: Лучший способ автоматической передачи обновлений в сводную таблицу на основе изменений из другой таблицы

#sql-server

Вопрос:

В настоящее время я работаю в базе данных SQL server, где у меня есть таблица User , схема которой выглядит следующим образом:

Имя пользователя Категория
пользователь1 Азартные игры
пользователь2 Азартные игры
пользователь3 Спорт

Моя сводная таблица UserCategoryCount представляет собой простое groupby утверждение о том, сколько пользователей принадлежит к каждой категории, и выглядит следующим образом:

Категория Нумераторы
Азартные игры 2
Спорт 1

Новые записи постоянно загружаются в User , и я хочу иметь возможность передавать обновления в User таблице в UserCategoryCount сводную таблицу. Я знаю, что могу создать простой оператор представления, который выполняет a groupby в User таблице, но я хотел UserCategoryCount бы иметь собственную таблицу, которая автоматически изменяется в зависимости от новых пользователей, загружаемых в User таблицу.

Моей первой мыслью было создать триггер, который определит, когда User таблица была обновлена. До сих пор самым простым, но дрянным решением, которое я могу придумать, является создание триггера, который просто удаляет и обновляет UserCategoryCount :

 CREATE TRIGGER TRG_Add_User
ON User
AS
BEGIN
DELETE FROM UserCategoryCount
INSERT INTO UserCategoryCount (category, numUsers)
SELECT Category, Count(Category) as numUsers
FROM User GROUP BY Category
END
GO

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

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

1. Да, это действительно «халтурный» способ. Его можно улучшить и сделать более эффективным, используя виртуальные вставленные и удаленные таблицы, доступные для триггеров. Но зачем беспокоиться? Объясните, почему вы отказались от идеи представления. Логически представление-это типичный подход. И мне трудно поверить, что строки постоянно добавляются в таблицу пользователей. Это звучит как преждевременная оптимизация.

2. Я не уверен, почему представление не работает для вас — это логичный подход, попытка сохранить сводку в отдельной таблице просто приводит к ненужному дублированию и накладным расходам. Какую проблему вы решаете с помощью отдельной таблицы?

3. @Stu Я пытаюсь смоделировать задание ETL, в котором я преобразую входные данные User таблицы и передаю их в отдельную таблицу. Я предполагаю, что это можно рассматривать не столько как сводную таблицу, сколько как преобразованную таблицу, которая на практике должна существовать в отдельной базе данных или хранилище данных? Также @SMor Да, я пытаюсь работать с виртуальными вставленными таблицами, доступными через триггеры, так как это похоже на способ, которым я могу фактически преобразовать выходные данные в отдельную таблицу.

4. Нужно ли обновлять целевую таблицу в режиме реального времени? Процесс ETL обычно выполняется не в режиме реального времени, а либо в одночасье, либо пакетами; не будет ли лучше подходить к запланированному заданию?

5. Это действительно нужно выполнять в режиме реального времени. Извините за мое неправильное использование терминологии. Это скорее потоковая работа, чем работа ETL.

Ответ №1:

Для начала, ваш триггер серьезно испорчен: он не использует таблицы inserted или deleted и вместо этого каждый раз пересчитывает все это, это будет очень плохо сказываться на производительности. Он также не указывает, предназначен ли он для вставок, обновлений или удалений.

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

 CREATE OR ALTER VIEW dbo.UserCategoryCount
WITH SCHEMABINDING
AS
SELECT
    u.Category,
    COUNT_BIG(*) AS numUsers
FROM dbo.User u
GROUP BY u.Category;

GO

CREATE UNIQUE CLUSTERED INDEX CX_UserCategoryCount ON dbo.UserCategoryCount (Category);

Существуют некоторые ограничения на индексированные представления, в том числе:

  • Они должны быть привязаны к схеме, и поэтому базовые столбцы не могут быть изменены
  • Все таблицы должны состоять из двух частей: схемы и таблицы
  • Разрешены только соединения INNER или CROSS , нет LEFT/RIGHT/FULL/APPLY или производные таблицы, CTE или подзапросы.
  • Если есть GROUP BY , вы должны добавить COUNT_BIG , и единственным другим разрешенным агрегатом является SUM
Метки: