Условия в агрегатных функциях
Я решал некоторые задачи SQL и запутался в следующем:
CREATE TABLE data (
len INT
);
INSERT INTO data (len)
VALUES
(NULL),
(100),
(200),
(300),
(400);
предположим, мы создали такую таблицу и хотим посчитать все значения меньше 200. Для этого я использую этот онлайн-компилятор.
моя первая попытка дает 1, как и ожидалось
SELECT COUNT(*) FILTER (WHERE len < 200)
FROM data
потом я подумал, могу ли я упростить это и просто сделать
SELECT COUNT(len < 200) FROM data
но он дает 4 в качестве ответа.
Итак, мой вопрос: можем ли мы использовать логические выражения внутри агрегатных функций? Если ответ НЕТ, то почему?
Попробуйте SELECT COUNT(case when len < 200 then 1 else null end) FROM data Выход — 1. Count(value) — подсчитывает не нулевые значения. Значение (len<200) имеет значение true или false, а не null.
@ValNik, это выглядит неаккуратно, я думаю, нам следует избегать конструкций if-else, насколько это возможно =). Собственно вопрос возник потому что я попробовал написать ваш код как SELECT count(len < 200) и он не работает и я просто пытаюсь понять почему (что происходит с SELECT COUNT(len < 200)
@NickW, да, ты прав. Я просто пытаюсь понять ошибку, с которой столкнулся. Если бы это было рабочее задание, я бы просто написал WHERE, как вы предлагали.
Функция COUNT будет подсчитывать ненулевые записи. Ваше условие len < 200 возвращает ненулевые логические результаты почти для каждой записи в данных. то есть. Значения TRUE и FALSE участвуют в подсчете результатов. Единственная запись, которая не учитывается, — это значение NULL в таблице, поскольку NULL < 200 возвращает NULL.
Вы можете использовать логическое выражение в агрегатной функции. Вы должны знать, как работает агрегатная функция. Я о функции count(). И выражение (len<200) имеет значение true или false, а не null. Count() считает ненулевые значения. И вы можете избежать конструкции if-else, если хотите;)
@ValNik, ELSE NULL в выражении CASE избыточно. Добиться желаемого результата можно и с помощью COUNT(NULLIF(len < 200, FALSE)). Выбор между функционально эквивалентным кодом должен определяться читабельностью; т. е. какая конструкция наиболее четко передает намерение и минимизирует когнитивную нагрузку.
См. пример без if-else SELECT sum(cast(len<200 as int)) FROM data Или SELECT sum((len<200)::int) FROM data
@JohnH, да, вы правы, ELSE null избыточен (по умолчанию). Я хотел выделить именно count(null).
Ответы 2
COUNT(expression) вычисляет количество строк, для которых expression не является NULL. COUNT(*) вычисляет общее количество строк, поскольку * — это значение строки, которое никогда не бывает NULL ни для одной строки, даже если каждый столбец в строке имеет значение NULL. Оператор < может вернуть один из трех результатов: TRUE, FALSE или NULL. Имейте в виду, что NULL — это не значение, это состояние. Выражение COUNT(len < 200) возвращает NULL только тогда, когда len равно NULL; следовательно, любое значение len будет способствовать подсчету. COUNT(NULL) всегда будет возвращать 0 независимо от количества строк.
count(*) подсчитывает все входные строки независимо от их содержимого. Период. Это соответствует стандарту SQL и не зависит от значения символа * в других контекстах.
В Postgres есть отдельная (более быстрая) реализация для count(*). Инструкция:
count(*) →bigintВычисляет количество входных строк.
count("any") →bigintВычисляет количество входных строк, в которых входное значение не является нулевой.
Определение варианта count("any") также отвечает на вопрос, почему ваш второй запрос считает все строки с ненулевым значением len.
Чтобы на самом деле упростить ваш запрос и сделать его быстрее или значительно быстрее для больших таблиц и выборочных критериев, соответствующих индексу:
SELECT count(*) FROM data WHERE len < 200;
Видеть:
Привет! Почему бы вам просто не использовать «SELECT COUNT(*) FROM data WHERE len <200»? Кажется, вы слишком усложняете то, что должно быть очень простым запросом.