sql - group by 不分组聚合？

Question

假设我正在尝试构建一个民意调查应用程序，这样我就可以创建一个民意调查模板，给它多个部分/问题，将多个人分配给给定问题的不同副本，创建不同的措施(幸福感、成功、绿色)并为不同的问题分配不同的权重以应用于所有这些措施。

像这样:

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE opinion_poll_templates (
  id SERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE opinion_poll_instances (
  id SERIAL NOT NULL PRIMARY KEY,
  template_id INTEGER NOT NULL REFERENCES opinion_poll_templates(id)
);

CREATE TABLE section_templates (
  id SERIAL NOT NULL PRIMARY KEY,
  opinion_poll_id INTEGER NOT NULL REFERENCES opinion_poll_templates(id)
);

CREATE TABLE section_instances (
  id SERIAL NOT NULL PRIMARY KEY,
  opinion_poll_id INTEGER NOT NULL REFERENCES opinion_poll_instances(id),
  template_id INTEGER NOT NULL REFERENCES section_templates(id)
);

CREATE TABLE question_templates (
  id SERIAL NOT NULL PRIMARY KEY,
  section_id INTEGER NOT NULL REFERENCES section_templates(id)
);

CREATE TABLE measure_templates (
  id SERIAL NOT NULL PRIMARY KEY,
  opinion_poll_id INTEGER NOT NULL REFERENCES opinion_poll_templates(id)
);

CREATE TABLE answer_options (
  id SERIAL NOT NULL PRIMARY KEY,
  question_template_id INTEGER NOT NULL REFERENCES question_templates(id),
  weight FLOAT8
);

CREATE TABLE question_instances (
  id SERIAL NOT NULL PRIMARY KEY,
  template_id INTEGER NOT NULL REFERENCES question_templates(id),
  opinion_poll_id INTEGER NOT NULL REFERENCES opinion_poll_instances(id),
  section_id INTEGER NOT NULL REFERENCES section_instances(id),
  answer_option_id INTEGER NOT NULL REFERENCES answer_options(id),
  contributor_id INTEGER
);

CREATE TABLE measure_instances (
  id SERIAL NOT NULL PRIMARY KEY,
  opinion_poll_id INTEGER NOT NULL REFERENCES opinion_poll_instances(id),
  template_id INTEGER NOT NULL REFERENCES measure_templates(id),
  total_score INTEGER
);

CREATE TABLE scores (
  id SERIAL NOT NULL PRIMARY KEY,
  question_template_id INTEGER NOT NULL REFERENCES question_templates(id),
  measure_template_id INTEGER NOT NULL REFERENCES measure_templates(id),
  score INTEGER NOT NULL
);

现在假设我对每个 measureInstance(分配给民意调查的每个度量)交叉问题、跨用户平均值感兴趣？

WITH weighted_score AS (
  SELECT AVG(answer_options.weight), measure_instances.id
  FROM question_instances
  INNER JOIN answer_options ON question_instances.template_id = answer_options.question_template_id
  INNER JOIN scores ON question_instances.template_id = scores.question_template_id
  INNER JOIN measure_instances ON measure_instances.template_id=scores.measure_template_id
  WHERE measure_instances.opinion_poll_id = question_instances.opinion_poll_id
  GROUP BY measure_instances.id
)
UPDATE measure_instances
SET total_score=(SELECT avg FROM weighted_score
WHERE weighted_score.id = measure_instances.id)*100
RETURNING total_score;

这似乎不仅没有按预期分组，而且产生了错误的结果。

为什么结果是整数而不是 float ？为什么结果不按度量实例分组，而是在所有实例中都相同？为什么其中任何一个的结果都不正确？

演示:http://sqlfiddle.com/#!15/dcce8/1

编辑:在准确解释我想要什么的过程中，我意识到问题的根源在于我只是简单地添加百分比，而不是将跨问题标准化为百分比。

我新的和改进的 sql 是:

WITH per_question_percentage AS (  
  SELECT SUM(answer_options.weight)/COUNT(question_instances.id) percentage, question_templates.id qid, opinion_poll_instances.id oid
  FROM question_instances
  INNER JOIN answer_options ON question_instances.answer_option_id = answer_options.id
  INNER JOIN question_templates ON question_templates.id = question_instances.template_id
  INNER JOIN opinion_poll_instances ON opinion_poll_instances.id = question_instances.opinion_poll_id
  GROUP BY question_templates.id, opinion_poll_instances.id
), max_per_measure AS (
  SELECT SUM(scores.score), measure_instances.id mid, measure_instances.opinion_poll_id oid
  FROM measure_instances
  INNER JOIN scores ON scores.measure_template_id=measure_instances.template_id
  GROUP BY measure_instances.id, measure_instances.opinion_poll_id
), per_measure_per_opinion_poll AS (
  SELECT per_question_percentage.percentage * scores.score score, measure_instances.id mid, measure_instances.opinion_poll_id oid
  FROM question_instances
  INNER JOIN scores ON question_instances.template_id = scores.question_template_id
  INNER JOIN measure_instances ON measure_instances.template_id = scores.measure_template_id
  INNER JOIN max_per_measure ON measure_instances.id = max_per_measure.mid
  INNER JOIN per_question_percentage ON per_question_percentage.qid = question_instances.template_id
  WHERE measure_instances.opinion_poll_id = question_instances.opinion_poll_id AND question_instances.opinion_poll_id = per_question_percentage.oid
  GROUP BY measure_instances.id, measure_instances.opinion_poll_id, per_question_percentage.percentage, scores.score
) 
UPDATE measure_instances
SET total_score = subquery.result*100
FROM (SELECT SUM(per_measure_per_opinion_poll.score)/max_per_measure.sum result, per_measure_per_opinion_poll.mid, per_measure_per_opinion_poll.oid
      FROM  max_per_measure, per_measure_per_opinion_poll
      WHERE per_measure_per_opinion_poll.mid = max_per_measure.mid 
      AND per_measure_per_opinion_poll.oid = max_per_measure.oid
      GROUP BY max_per_measure.sum, per_measure_per_opinion_poll.mid, per_measure_per_opinion_poll.oid)
      AS subquery(result, mid, oid)
WHERE measure_instances.id = subquery.mid
AND measure_instances.opinion_poll_id = subquery.oid
RETURNING total_score;

这是规范的sql吗？对于这种 CTE 链接(或其他)，有什么我应该注意的吗？有没有更有效的方法来实现同样的目标？

Answer 1

评论有点长。

我不明白这些问题。

为什么结果是整数而不是 float ？

因为 measure_instances.total_score 是一个整数，这就是 returning 子句返回的内容。

为什么结果没有按度量实例分组，而是在所有实例中都相同？

当我独立运行 CTE 时，值为 0.45。数据和逻辑决定相同的值。

为什么其中任何一个的结果都不正确？

我认为您的意思是“为所有人”。无论如何，结果对我来说都是正确的。