比多个SELECT语句更好的方法？ [英] Better way than multiple SELECT statements?
问题描述
我正在创建一个显示饼图的网络应用。为了在单个HTTP请求中从 PostgreSQL 9.3 数据库获取图表的所有数据，我将多个 SELECT 语句与 UNION ALL —这是一部分：
I'm creating a web app that displays a pie chart. In order to get all the data for the chart from a PostgreSQL 9.3 database in a single HTTP request, I'm combining multiple SELECT statements with UNION ALL — here's a portion:
SELECT 'spf' as type, COUNT(*)
FROM (SELECT cai.id
FROM common_activityinstance cai
JOIN common_activityinstance_settings cais ON cai.id = cais.activityinstance_id
JOIN common_activitysetting cas ON cas.id = cais.id
JOIN quizzes_quiz q ON q.id = cai.activity_id
WHERE cai.end_time::date = '2015-09-12'
AND q.name != 'Exit Ticket Quiz'
AND cai.activity_type = 'QZ'
AND (cas.key = 'disable_student_nav' AND cas.value = 'True'
OR cas.key = 'pacing' AND cas.value = 'student')
GROUP BY cai.id
HAVING COUNT(cai.id) = 2) sub
UNION ALL
SELECT 'spn' as type, COUNT(*)
FROM common_activityinstance cai
JOIN common_activityinstance_settings cais ON cai.id = cais.activityinstance_id
JOIN common_activitysetting cas ON cas.id = cais.id
WHERE cai.end_time::date = '2015-09-12'
AND cai.activity_type = 'QZ'
AND cas.key = 'disable_student_nav'
AND cas.value = 'False'
UNION ALL
SELECT 'tp' as type, COUNT(*)
FROM (SELECT cai.id
FROM common_activityinstance cai
JOIN common_activityinstance_settings cais ON cai.id = cais.activityinstance_id
JOIN common_activitysetting cas ON cas.id = cais.id
WHERE cai.end_time::date = '2015-09-12'
AND cai.activity_type = 'QZ'
AND cas.key = 'pacing' AND cas.value = 'teacher') sub;
这会产生一个很小的响应，用于发送回客户端：
This produces a nice, small response for sending back to the client:
type | count
------+---------
spf | 100153
spn | 96402
tp | 84211
我想知道是否可以提高查询效率。每个SELECT语句几乎都使用相同的JOIN操作。有没有一种方法可以不对每个新的SELECT重复JOIN吗？
我实际上更喜欢一行3列的行。
I wonder if my queries can be made more efficient. Each SELECT statement uses mostly the same JOIN operations. Is there a way to not repeat the JOIN for each new SELECT?
And I would actually prefer a single row with 3 columns.
或者，通常，是否有比我正在做的完全不同但更好的方法？
Or, in general, is there some entirely different but better approach than what I'm doing?
推荐答案
您可以捆绑大部分费用在 CTE 的单个主查询中并重用结果
这将返回一个具有三列的单行，该行以每种类型命名（按评论中的要求）：
You can bundle most of the cost in a single main query in a CTE and reuse the result several times.
This returns a single row with three columns named after each type (as requested in the comment):
WITH cte AS (
SELECT cai.id, cai.activity_id, cas.key, cas.value
FROM common_activityinstance cai
JOIN common_activityinstance_settings s ON s.activityinstance_id = cai.id
JOIN common_activitysetting cas ON cas.id = s.id
WHERE cai.end_time::date = '2015-09-12' -- problem?
AND cai.activity_type = 'QZ'
AND (cas.key = 'disable_student_nav' AND cas.value IN ('True', 'False') OR
cas.key = 'pacing' AND cas.value IN ('student', 'teacher'))
)
SELECT *
FROM (
SELECT count(*) AS spf
FROM (
SELECT c.id
FROM cte c
JOIN quizzes_quiz q ON q.id = c.activity_id
WHERE q.name <> 'Exit Ticket Quiz'
AND (c.key, c.value) IN (('disable_student_nav', 'True')
, ('pacing', 'student'))
GROUP BY 1
HAVING count(*) = 2
) sub
) spf
, (
SELECT count(key = 'disable_student_nav' AND value = 'False' OR NULL) AS spn
, count(key = 'pacing' AND value = 'teacher' OR NULL) AS tp
FROM cte
) spn_tp;
应该适用于Postgres 9.3。在Postgres 9.4中，您可以使用新的聚合 FILTER 子句：
Should work for Postgres 9.3. In Postgres 9.4 you can use the new aggregate FILTER clause:
count(*) FILTER (WHERE key = 'disable_student_nav' AND value = 'False') AS spn
, count(*) FILTER (WHERE key = 'pacing' AND value = 'teacher') AS tp
两种语法变体的详细信息：
Details for both syntax variants:
- How can I simplify this game statistics query?
标记为问题的条件？可能是性能大问题，具体取决于 cai.end_time 的数据类型。一方面，它不是 可精 。如果它是 timestamptz 类型，则该表达式很难索引，因为结果取决于会话的当前时区设置-执行时也会导致不同的结果
The condition marked problem? may be big performance problem, depending on the data type of cai.end_time. For one, it's not sargable. And if it's a timestamptz type, the expression is hard to index, because the result depends on the current time zone setting of the session - which can also lead to different results when executed in different time zones.
比较：
- 从同一表中保留两个查询
- < a href = https://stackoverflow.com/questions/30894296/subtract-hours-from-the-now-function/30896121#30896121>从now（）函数中减去小时数 b $ b
- 完全忽略Rails和PostgreSQL中的时区
- Sustract two queries from same table
- Subtract hours from the now() function
- Ignoring timezones altogether in Rails and PostgreSQL
您只需要命名应该用来定义日期的时区即可。以我在维也纳的时区为例：
You just have to name the time zone that is supposed to define your date. Taking my time zone in Vienna as example:
WHERE cai.end_time >= '2015-09-12 0:0'::timestamp AT TIME ZONE 'Europe/Vienna'
AND cai.end_time < '2015-09-13 0:0'::timestamp AT TIME ZONE 'Europe/Vienna'
您还可以提供简单的 timestamptz 值。您甚至可以：
You can provide simple timestamptz values as well. You could even just:
WHERE cai.end_time >= '2015-09-12'::date
AND cai.end_time < '2015-09-12'::date + 1
但是第一个变量不取决于当前时区设置。
上面的链接中有详细说明。
But the first variant does not depend on the current time zone setting.
Detailed explanation in the links above.
现在查询可以使用您的索引了，如果有很多查询，应该会更快表格中的不同日期。
Now the query can use your index and should be much faster if there are many different days in your table.
这篇关于比多个SELECT语句更好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
