时间戳

首页 > 解决方案 > 如何在 sqlite 中过滤 SUM(flag) OVER (...)

sql - 如何在 sqlite 中过滤 SUM(flag) OVER (...)

问题描述

WITH cte AS (
  SELECT Q.Question_id, Q.Question, PMA.part_model_ans, QP.part_total_marks, MA.answer_mark, Q.rowid, 
         Q.Question <> LAG(Q.Question, 1, '') OVER (PARTITION BY Q.Question_id ORDER BY Q.rowid) flag
  FROM QUESTIONS Q
  LEFT JOIN QUESTIONS_PART QP ON QP.question_id = Q.question_id
  LEFT JOIN PART_MODEL_ANSWER PMA ON PMA.part_id = QP.part_id
  LEFT JOIN MODEL_ANSWER MA ON MA.question_id = Q.question_id
)
SELECT Question_id, Question, part_model_ans, part_total_marks, answer_mark, 
       SUM(flag) OVER (PARTITION BY Question_id ORDER BY rowid) part
FROM cte
having part = 1
ORDER BY question_id

我想要做的是过滤部分，以便它只显示其中包含 1 的行但是在使用具有部分 == 1 之后，预期结果仍然相同我不知道为什么它不过滤，where 子句不能由于滥用聚合而使用

标签： sqldjangosqlitebackend

解决方案

您需要将条件放在WHERE子句中，但part它是窗口函数的结果，您不能直接在WHERE子句中使用它。

您可以使用子查询来做到这一点：

WITH cte AS (
  SELECT Q.Question_id, Q.Question, PMA.part_model_ans, QP.part_total_marks, MA.answer_mark, Q.rowid, 
         Q.Question <> LAG(Q.Question, 1, '') OVER (PARTITION BY Q.Question_id ORDER BY Q.rowid) flag
  FROM QUESTIONS Q
  LEFT JOIN QUESTIONS_PART QP ON QP.question_id = Q.question_id
  LEFT JOIN PART_MODEL_ANSWER PMA ON PMA.part_id = QP.part_id
  LEFT JOIN MODEL_ANSWER MA ON MA.question_id = Q.question_id
)
SELECT * 
FROM (
  SELECT Question_id, Question, part_model_ans, part_total_marks, answer_mark, 
         SUM(flag) OVER (PARTITION BY Question_id ORDER BY rowid) part
  FROM cte
)
WHERE part = 1
ORDER BY question_id

推荐阅读