group by not group aggregate？

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;

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

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

演示：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

这对于评论来说有点长了。

我不明白这些问题。

为什么结果是整数而不是浮点数？

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

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

当我单独运行CTE时，这些值是0.45。数据和逻辑指示相同的值。

为什么结果对他们中的任何一个都是不正确的？

我想你的意思是“为了所有人”。无论如何，结果在我看来都是正确的。

Answer 2

如果您对演示中的数据运行此查询：

SELECT 
    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
ORDER BY
    2;

您将获得：

| weight | id |
|--------|----|
|    0.5 |  1 |
|   0.25 |  1 |
|   0.25 |  1 |
|   0.75 |  1 |
|    0.5 |  1 |
|   0.75 |  2 |
|    0.5 |  2 |
|   0.25 |  2 |
|    0.5 |  2 |
|   0.25 |  2 |

如果你手工计算平均值，你会得到：

For id=1 ==> 0.5+0.25+0.25+0.75 + 0.5 = 2.25 ==> 2.25 /5= 0.45

For id=2 ==> 0.75 + 0.5 + 0.25 + 0.5 + 0.25 = 2.25 ==> 2.25 /5= 0.45

在我看来，这个查询运行得很好。

请解释为什么这些结果对您来说是错误的，以及您希望从上述数据和查询中获得什么？