多行的SQL加权平均值-

您将如何找到多行的真实加权平均值：

通过实际加权，我的意思是像这个计算器：https : //www.rapidtables.com/calc/math/weighted-average-calculator.html（而不是通过将值与权重相乘）。

每个答案的权重设置answers在 child-table 中，每个问题的值都在 child-table 中answer_items。我们想要每个question(a,b,c,d)的加权平均值。我们提前知道要查找哪些问题。

查询将包含 2 到 500k 个答案（所以最好是一个快速的解决方案:)）

CREATE TABLE `answers` (
  `id` int(10) NOT NULL,
  `weight` varchar(255) NOT NULL
);

INSERT INTO `answers` (`id`, `weight`) VALUES
(1, '0.7'),
(2, '1'),
(3, '0.7'),
(4, '0.9');

CREATE TABLE `answer_items` (
  `id` int(11) NOT NULL,
  `answer_id` int(11) NOT NULL,
  `question` varchar(5) NOT NULL,
  `value` int(11) NOT NULL
);

INSERT INTO `answer_items` (`id`, `answer_id`, `question`, `value`) VALUES
(1, 1, 'a', 2),
(2, 1, 'b', 4),
(3, 1, 'c', 2),
(4, 1, 'd', 3),
(5, 2, 'a', 4),
(6, 2, 'b', 2),
(7, 2, 'c', 4),
(8, 2, 'd', 1),
(9, 3, 'a', 3),
(10, 3, 'b', 4),
(11, 3, 'c', 1),
(12, 3, 'd', 5),
(13, 4, 'a', 5),
(14, 4, 'b', 2),
(15, 4, 'c', 3),
(16, 4, 'd', 3);


ALTER TABLE `answers`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `answer_items`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `answers`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

ALTER TABLE `answer_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

回答

select question, sum(ai.value * a.weight) / sum(a.weight)
from answer_items ai join
     answers a
     on ai.answer_id = a.id
group by question;