#sql #postgresql #subquery #common-table-expression
#sql #postgresql #подзапрос #common-table-expression
Вопрос:
Для огромного SQL-запроса, который выглядит следующим образом:
SELECT
...
(SELECT COUNT(*) FROM table1 WHERE field = 'bar' AND table1.table0_id = table0.id)
(SELECT COUNT(*) FROM table1 WHERE field = 'foobar' AND table1.table0_id = table0.id)
(SELECT COUNT(*) FROM table1 WHERE field = 'foo' AND table1.table0_id = table0.id)
...
FROM table0;
Есть ли способ избежать выполнения 3 запросов для каждой итерации в table0?
Спасибо
Комментарии:
1. Вам обязательно следует указать версию PostgreSQL. PostgreSQL быстро растет, и изменения даже между второстепенными версиями значительны.
2. @Abelisto: в Postgres никогда не бывает изменений между «второстепенными версиями». Второстепенная версия всегда содержит только исправления, никогда новые функции или несовместимые изменения.
3. @a_horse_with_no_name Извините, вероятно, я использовал неподходящий термин. Я имел в виду 9.x, где x… (как называется x?)
4. @Abelisto: 9.x считался основным выпуском : » Основной выпуск нумеруется путем увеличения либо первой, либо второй части номера версии, например, с 9.1 до 9.2 » — однако, начиная с 10.0, это изменится, и вторая цифра будет указывать на второстепенный выпуск.
Ответ №1:
Вы можете использовать условную агрегацию для упрощения вашего запроса
Вот правильный способ
SELECT coalesce(b_count,0),
coalesce(fb_count,0),
coalesce(f_count,0)
FROM table0
LEFT JOIN (SELECT table1.table0_id,
Count(CASE WHEN field = 'bar' THEN 1 END) AS b_count,
Count(CASE WHEN field = 'foobar' THEN 1 END) AS fb_count,
Count(CASE WHEN field = 'foo' THEN 1 END) AS f_count,
FROM table1
WHERE field IN ( 'bar', 'foobar', 'foo' )
GROUP BY table1.table0_id) table1
ON table1.table0_id = table0.id
Комментарии:
1. Кстати, для современного PostgreSQL
Count(CASE WHEN field = 'bar' THEN 1 END)
равноcount(*) filter (where field = 'bar')
2. @Abelisto — Можете ли вы указать точную версию, в которой поддерживается этот синтаксис, чтобы добавить в мой ответ
3. 9.4, первая соответствующая ссылка в предложении Google: The FILTER в Postgres 9.4
4. На самом деле намного быстрее, чем без использования условной агрегации, спасибо.
Ответ №2:
В Postgres 9.4
select
count(table0.id) filter (where field = 'bar'),
count(table0.id) filter (where field = 'foobar'),
count(table0.id) filter (where field = 'foo')
from table1
left join table0 on table1.table0_id = table0.id
group by table0.id;
Комментарии:
1. Это не то же самое, что запрос OP.
Inner Join
можно отфильтровать некоторые записи,table0
если вtable1
2. Изменено
INNER JOIN
наLEFT JOIN
надеюсь, вы не возражаете3. Пусть это будет, однако это не может повлиять на
count()
, но дополнительно дает пустые строки, если есть строки,table0
не связанные сtable1
.
Ответ №3:
Я не думаю, что вам нужно table0
. Вы можете использовать условную агрегацию:
SELECT table1.table0_id,
SUM(CASE WHEN field = 'bar' THEN 1 ELSE 0 END) as bar,
SUM(CASE WHEN field = 'foobar' THEN 1 ELSE 0 END) as foobar,
SUM(CASE WHEN field = 'foo' THEN 1 ELSE 0 END) as foo
FROM table1
GROUP BY table1.table0_id;
Если в них есть значения, table0
которых нет table1
, вы можете использовать left join
:
SELECT table2.id,
SUM(CASE WHEN field = 'bar' THEN 1 ELSE 0 END) as bar,
SUM(CASE WHEN field = 'foobar' THEN 1 ELSE 0 END) as foobar,
SUM(CASE WHEN field = 'foo' THEN 1 ELSE 0 END) as foo
FROM table0 LEFT JOIN
table1
ON table0.id = table1.table0_id
GROUP BY table2.id;
Вы также можете сократить SELECT
до:
SUM((field = 'bar')::int) as bar,
SUM((field = 'foobar')::int) as foobar,
SUM((field = 'foo')::int) as foo