sql - PostgreSQL 中用于表格报告的内部选择的 SQL 性能问题
问题描述
使用 PostgreSQL 数据库:
我有一个调查应用程序,用户可以在其中输入活动并回答有关他们活动的问题。调查本身被称为RECALLS_T
,输入的事件是EVENTS_T
,答案是ANSWERS_T
。答案用于提供的活动问题,存储在 中ACTIVITY_QUESTIONS_T
,由 Lookup ( LOOKUP_T
) 映射。
然后我需要运行一个基于事件的报告,其中每一行都是EVENTS_T
每次召回的事件(所有事件合并为所有召回)。但是,该报告中的某些列需要指示某些答案的值,否则这些单元格为 NULL。所以这是一份表格报告。
示例(首先是简单的平面内容,然后是复杂的表格内容):
RecallID | RecallDate | Event |..| WalkAlone | WalkWithPartner |..| ExerciseAtGym
256 | 10-01-19 | Exrcs |..| NULL | NULL |..| yes
256 | 10-01-19 | Walk |..| yes | NULL |..| NULL
256 | 10-01-19 | Eat |..| NULL | NULL |..| NULL
257 | 10-01-19 | Exrcs |..| NULL | NULL |..| yes
我的 SQL 对基于答案的列表列有内部选择,如下所示:
select
-- Easy flat stuff first
r.id as recallid, r.recall_date as recalldate, ... ,
-- Example of Tabulated Columns:
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=aq.answer_choice_id and aq.question_id=13
and aq.id=ans.activity_question_id and aq.activity_id=27 and ans.event_id=e.id)
as transportationotherintensity,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=66 and l.id=aq.answer_choice_id and aq.question_id=14
and aq.id=ans.activity_question_id and ans.event_id=e.id)
as commutework,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=67 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id)
as commuteschool,
(select l.description from answers_t ans, activity_questions_t aq, lookup_t l
where l.id=95 and l.id=aq.answer_choice_id and aq.question_id=14 and aq.id=ans.activity_question_id and ans.event_id=e.id)
as dropoffpickup,
SQL 工作并且报告被渲染,但是性能很差。我验证了它的比例很糟糕:没有可以修复它的特定项目的灵丹妙药。每个内部选择都会导致糟糕的表现。1,000 行的结果集需要 15 秒,但不应超过 2-3 秒。
请注意,这些索引已经存在:
ANSWERS_T
: 上ACTIVITY_QUESTION_ID
,EVENT_ID
EVENTS_T
: 上RECALL_ID
ACTIVITY_QUESTIONS_T
: 在ACTIVITY_ID
,QUESTION_ID
,ANSWER_CHOICE_ID
这些内部选择有什么问题吗?
解决方案
总结问题,您想使用条件聚合。在 PostgreSQL 中,您可以使用:
select ans.event_id,
max(l.description) filter (where aq.question_id = 13 and aq.activity_id = 27) as transportationotherintensity
max(l.description) filter (where l.id = 66 and aq.question_id = 14 and aq.activity_id = 67) as commutework,
. . .
from activity_questions_t aq join
lookup_t l
on l.id = aq.answer_choice_id join
answers_t ans
on aq.id = ans.activity_question_id
group by ans.event_id
推荐阅读
- reactjs - 推送被拒绝,无法编译 Node.js 应用程序(使用 Reactjs)
- maven-plugin - Generate JSON Schema as several files using Jsonix
- javascript - 如何判断循环何时完成。使用 JavaScript、Vue 和 AXIOS
- python-3.x - 如何解决 FailedPreconditionError?
- latex - 如何在表格环境中垂直居中文本?
- sql - 检查一列的值是否在不同表的另一列中
- java - FlinkCEP的执行时间
- r - 如何为R中具有非唯一值的人获取唯一键
- python - 在 CSV 中获取带条件的最大值
- java - spring cloud aws 项目不再有活跃的所有者了吗?