使用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可以工作,并且可以呈现报告,但性能很差。我验证了它在比例上是糟糕的:没有一个特定项目的灵丹妙药可以修复它。每一个内部选择都会导致糟糕的性能。1000行的结果集需要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