#sql #postgresql #group-by #subquery
#sql #postgresql #группировать по #подзапрос
Вопрос:
Я определенно новичок в SQL, я ломал голову, чтобы написать сложный запрос со следующей структурой таблицы в Postgresql:
CREATE TABLE reports
(
reportid character varying(20) NOT NULL,
userid integer NOT NULL,
reporttype character varying(40) NOT NULL,
)
CREATE TABLE users
(
userid serial NOT NULL,
username character varying(20) NOT NULL,
)
Цель запроса — получить количество типов отчетов для каждого пользователя и отобразить его в одном столбце. Существует три разных типа отчетов.
Простой запрос с group-by решит проблему, но отобразит ее в разных строках:
select count(*) as Amount,
u.username,
r.reporttype
from reports r,
users u
where r.userid=u.userid
group by u.username,r.reporttype
order by u.username
Комментарии:
1. Какой результат вы ожидаете?
2. Существует только три разных типа отчетов: «type1», «type2», «type3», и запрос должен отображать в одной строке имя пользователя, количество отчетов «type1», количество отчетов «type2» и количество отчетов «type3». Извините за путаницу
3. В запросе в вашем примере вы используете c.userid и c.reporttype, но откуда берется этот c? Вы не создали никакого псевдонима с именем c или таблицы с именем c.
4. Вы правы, это была опечатка. Это должен быть r
Ответ №1:
SELECT
username,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid amp;amp; reports.reporttype = 'Type1'
) As Type1,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid amp;amp; reports.reporttype = 'Type2'
) As Type2,
(
SELECT
COUNT(*)
FROM reports
WHERE users.userid = reports.userid amp;amp; reports.reporttype = 'Type3'
) As Type3
FROM
users
WHERE
EXISTS(
SELECT
NULL
FROM
reports
WHERE
users.userid = reports.userid
)
Комментарии:
1. Это неплохо сработало, но есть ли способ игнорировать пользователей с нулевыми отчетами?
2. Вам не нужно обновлять свой вопрос, просто отметьте правильный ответ как решение.
3. Очень полезно. Спасибо, Магнус!
Ответ №2:
SELECT
u.username,
COUNT(CASE r.reporttype WHEN 1 THEN 1 END) AS type1Qty,
COUNT(CASE r.reporttype WHEN 2 THEN 1 END) AS type2Qty,
COUNT(CASE r.reporttype WHEN 3 THEN 1 END) AS type3Qty
FROM reports r
INNER JOIN users u ON r.userid = u.userid
GROUP BY u.username
Если диалект SQL вашего сервера требует присутствия ветви ELSE в выражениях CASE, добавьте ELSE NULL перед каждым END .
Комментарии:
1. Я голосую за, потому что я не большой поклонник подзапросов. Спасибо.
2. Я нахожу, что это намного легче читать и понимать, чем подзапрос, есть ли разница в производительности между ними?
3. @mappingdom: Я бы ожидал, что это решение будет быстрее, чем решение с подзапросом. С этим решением вы нажимаете на
reportsтаблицу один раз для каждого подсчета и еще раз дляEXISTSпроверки, тогда как если вы используете соединение, подобное приведенному здесь, каждая таблица будет затронута только один раз.
Ответ №3:
Если вы ищете «количество типов отчетов на пользователя», вы ожидаете увидеть число, либо 1, 2, либо 3 (учитывая, что существует три разных типа отчетов) для каждого пользователя. Вы не будете ожидать тип отчета (он просто будет подсчитан и не будет отображаться), поэтому вам не нужен reporttype ни в части запроса SELECT, ни в части GROUP BY.
Вместо этого используйте COUNT(DISTINCT r.reporttype) для подсчета количества различных типов отчетов, используемых каждым пользователем.
SELECT
COUNT(DISTINCT r.reporttype) as Amount
,u.username
FROM users u
INNER JOIN reports r
ON r.userid=u.userid
GROUP BY
u.username
ORDER BY u.username
Комментарии:
1. Это было не совсем то, что я искал. Существует только три разных типа отчетов: «type1», «type2», «type3», и запрос должен отображать в одной строке имя пользователя, количество отчетов «type1», количество отчетов «type2» и количество отчетов «type3». Извините за путаницу.