Агрегатные функции не разрешены в WHERE - при объединении таблиц PostgreSQL
В игре, использующей PostgreSQL 9.3.10, некоторые игроки заплатили за "VIP-статус", который указывается в столбце vip, содержащем дату из будущего:
# \d pref_users
Column | Type | Modifiers
------------+-----------------------------+--------------------
id | character varying(32) | not null
first_name | character varying(64) | not null
last_name | character varying(64) |
vip | timestamp without time zone |
Также игроки могут оценивать других игроков, установив для столбца nice значение true, false или оставив значение null:
# \d pref_rep
Column | Type | Modifiers
-----------+-----------------------------+-----------------------------------------------------------
id | character varying(32) | not null
author | character varying(32) | not null
nice | boolean |
Я рассчитываю "репутацию" VIP-игроков, выдавая SQL JOIN:
# select u.id, u.first_name, u.last_name,
count(nullif(r.nice, false))-count(nullif(r.nice, true)) as rep
from pref_users u, pref_rep r
where u.vip>now()and u.id=r.id group by u.id order by rep asc;
id | first_name | last_name | rep
-------------------------+--------------------------------+--------------------
OK413274501330 | ali | salimov | -193
OK357353924092 | viktor | litovka | -137
DE20287 | sergej warapow |
Мой вопрос, пожалуйста, следующий:
Как найти всех игроков с отрицательным рейтингом, которые оценили других игроков?
(Фоном является то, что я добавил возможность оценивать других - всем VIP-игрокам. До этого только игроки с положительным рейтингом могли оценивать других).
Я пробовал следующее, но получаю ошибку ниже:
# select count(*) from pref_rep r, pref_users u
where r.author = u.id and u.vip > now() and
u.id in (select id from pref_rep
where (count(nullif(nice, false)) -count(nullif(nice, true))) < 0);
ERROR: aggregate functions are not allowed in WHERE
LINE 1: ...now() and u.id in (select id from pref_rep where (count(null...
^
ОБНОВИТЬ:
Я пытаюсь это с временной таблицей сейчас -
Сначала я заполняю его всеми VIP-пользователями с отрицательной оценкой, и это хорошо работает:
# create temp table my_temp as select u.id, u.first_name, u.last_name,
count(nullif(r.nice, false))-count(nullif(r.nice, true)) as rep
from pref_users u, pref_rep r
where u.vip>now() and u.id=r.id group by u.id;
SELECT 362
Но тогда мой SQL JOIN возвращает слишком много одинаковых строк, и я не могу найти, какое условие там отсутствует:
# select u.id, u.first_name, u.last_name
from pref_rep r, pref_users u, my_temp t
where r.author=u.id and u.vip>now()
and u.id=t.id and t.rep<0;
id | first_name | last_name
-------------------------+--------------------------------+----------------------------
OK400153108439 | Vladimir | Pelix
OK123283032465 | Edik | Lehtik
OK123283032465 | Edik | Lehtik
OK123283032465 | Edik | Lehtik
OK123283032465 | Edik | Lehtik
OK123283032465 | Edik | Lehtik
OK123283032465 | Edik | Lehtik
Та же проблема (несколько строк с одинаковыми данными) я получаю для утверждения:
# select u.id, u.first_name, u.last_name
from pref_rep r, pref_users u
where r.author = u.id and u.vip>now()
and u.id in (select id from my_temp where rep < 0);
Интересно, какое условие здесь может отсутствовать?
2 ответа
Прежде всего, я бы написал ваш первый запрос так:
select
u.id, u.first_name, u.last_name,
sum(case
when r.nice=true then 1
when r.nice=false then -1
end) as rep
from
pref_users u inner join pref_rep r on u.id=r.id
where
u.vip>now()
group by
u.id, u.first_name, u.last_name;
(он такой же, как и ваш, но мне он понятнее).
Чтобы найти игроков с отрицательным рейтингом, вы можете использовать тот же запрос, что и раньше, просто добавив предложение HAVING:
having
sum(case
when r.nice=true then 1
when r.nice=false then -1
end)<0
чтобы найти игроков с отрицательным рейтингом, которые оценили игроков, одно из решений заключается в следующем:
select
s.id, s.first_name, s.last_name, s.rep
from (
select
u.id, u.first_name, u.last_name,
sum(case
when r.nice=true then 1
when r.nice=false then -1
end) as rep
from
pref_users u inner join pref_rep r on u.id=r.id
where
u.vip>now()
group by
u.id, u.first_name, u.last_name
having
sum(case
when r.nice=true then 1
when r.nice=false then -1
end)<0
) s
where
exists (select * from pref_rep p where p.author = s.id)
в конце концов, предложение has может быть удалено из внутреннего запроса, и вы можете просто использовать это выражение where для внешнего запроса:
where
rep<0
and exists (select * from pref_rep p where p.author = s.id)
Вы забыли упомянуть, что pref_users.id определяется как PRIMARY KEY - иначе ваш первый запрос не будет работать. Это также означает, что id уже проиндексирован.
Лучший запрос во многом зависит от типичного распределения данных.
При условии, что:
- ... большинство пользователей не получают отрицательных оценок.
- ... большинство пользователей вообще не голосуют.
- ... некоторые или многие из тех, кто голосует, делают это часто.
Было бы полезно определить несколько возможных кандидатов и рассчитать только общий рейтинг для тех, кто пришел к окончательному выбору, вместо того, чтобы рассчитывать общее количество для каждого пользователя, а затем фильтровать только несколько.
SELECT *
FROM ( -- filter candidates in a subquery
SELECT *
FROM pref_users u
WHERE u.vip > now()
AND EXISTS (
SELECT 1
FROM pref_rep
WHERE author = u.id -- at least one rating given
)
AND EXISTS (
SELECT 1
FROM pref_rep
WHERE id = u.id
AND NOT nice -- at least one neg. rating received
)
) u
JOIN LATERAL ( -- calculate total only for identified candidates
SELECT sum(CASE nice WHEN true THEN 1 WHEN false THEN -1 END) AS rep
FROM pref_rep
WHERE id = u.id
) r ON r.rep < 0;
Индексы
Очевидно, вам нужен индекс на pref_rep.author помимо (также предполагается!) PRIMARY KEY индексы на обоих id колонны.
Если ваши таблицы большие, то заплатят более продвинутые индексы.
С одной стороны, вас интересуют только VIP-пользователи (u.vip > now()). Простой индекс на vip пошел бы долгий путь. Или даже частичный многоколонный индекс, который включает в себя id и усекает более старые кортежи из индекса:
CREATE INDEX pref_users_index_name ON pref_users (vip, id)
WHERE vip > '2015-04-21 18:00';
Рассмотрим детали:
Если (и только если) отрицательные голоса составляют меньшинство, частичный индекс pref_rep может также оплатить:
CREATE INDEX pref_rep_downvote_idx ON pref_rep (id)
WHERE NOT nice;
Тест производительности с EXPLAIN ANALYZE Повторите пару раз, чтобы исключить эффекты кэширования.