SQL Server - Как получить среднюю оценку из другой таблицы и включить ее в текущую таблицу?

У меня таблица контрагентов вот так:

SELECT [ContractorID]
  ,[Contractor]
  ,[BWSLVendorCode]
  ,[PhoneNumber]
  ,[Address]
  ,[PreQualification]
  ,[GSTNumber]
  ,[Avg] FROM Contractor

И таблица Feedback, в которой хранятся индивидуальные оценки и комментарии для каждого подрядчика, например:

SELECT [ID]
  ,[ContractorID]
  ,[Rating]
  ,[Comments]
  ,[Timestamp] FROM Feedback

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

РЕДАКТИРОВАТЬ: рейтинг от 1 до 5

Источник

3 ответа

Решение

Это обновит Avg столбец в Contractor Таблица со средним значением рейтингов подрядчика в Feedback Таблица.

;with feedback_grouped 
AS 
( 
SELECT f.ContractorID, AVG(CAST(f.rating as decimal(3,2))) as avg_rating
   FROM   Feedback f
   GROUP BY f.ContractorID
)
UPDATE Contractor
SET avg=avg_rating
FROM Contractor c
INNER JOIN feedback_grouped 
ON c.ContractorID = feedback_grouped.ContractorID

Это группирует каждый отзыв по ContractorID и рассчитывает среднее Rating обновить оригинал Contractor Таблица

Убедитесь, что Avg колонка в Contractor стол decimal Тип данных, так как среднее может иметь десятичные значения. Вы можете изменить (CAST(f.rating as decimal(3,2))) к тому же десятичному типу данных, который вы используете в Avg колонка.

Источник

РЕДАКТИРОВАТЬ

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

CREATE TRIGGER [DBName].[updateAvg]
ON [DBName].Contractors
AFTER UPDATE,INSERT
AS
BEGIN   

;WITH avgRates AS (
SELECT [ContractorID]
  ,SUM([Rating]) as totalRating
  ,COUNT(*) as noOfRatings
FROM Feedback
GROUP BY ContractorID
        )

UPDATE C
SET Avg = A.totalRating / A.noOfRatings
FROM Contractor C
INNER JOIN avgRates A
ON C.ContractorID = A.ContractorID

END

Мой синтаксис может быть неправильным на CREATE TRIGGER, так что вы хотели бы сослаться на официальную документацию:

CREATE TRIGGER (Transact-SQL)

Источник

Сколько "подрядчиков" вы ожидаете в своей базе данных? А сколько отзывов на подрядчика? Я спрашиваю об этом, потому что вычисление Avg должно выполняться во время выполнения, чтобы вам не приходилось пересчитывать каждый раз, когда вводится новый отзыв...

Вместо того, чтобы сохранять Avg в таблице, возможно, у вас должно быть представление, которое вычисляет это и вызывает представление вместо таблицы. Такие как это:

CREATE VIEW ContractorView AS SELECT C.*, ((SELECT SUM(F1.Rating) FROM Feedback as F1 WHERE F1.ContractorID = C.ContractorID) / (SELECT COUNT(F2.ID) FROM Feedback as F2 WHERE F2.ContractorID = C.ContractorID)) as Avg FROM Contractors as C

Синтаксис что-то близко к этому.;)

Источник
Другие вопросы по тегам