Как посчитать все одним запросом

SQL — Урок 8. Группировка записей и функция COUNT()

Давайте вспомним, какие сообщения и в каких темах у нас имеются. Для этого можно воспользоваться привычным запросом:

А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией COUNT(). Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце.

В нашем примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic:

SELECT COUNT(id_topic) FROM posts;

Итак, в наших темах имеется 4 сообщения. Но что, если мы хотим узнать сколько сообщений имеется в каждой теме. Для этого нам понадобится сгруппировать наши сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY. Наш запрос теперь будет выглядеть так:

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема — отдельная группа) и для каждой группы подсчитать количество строк:

Ну вот, в теме с у нас 3 сообщения, а с — одно. Кстати, если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL.

Предположим, что нас интересуют только те группы, в которых больше двух сообщений. В обычном запросе мы указали бы условие с помощью оператора WHERE, но этот оператор умеет работать только со строками, а для групп те же функции выполняет оператор HAVING:

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;

В результате имеем:

В уроке 4 мы рассматривали, какие условия можно задавать оператором WHERE, те же условия можно задавать и оператором HAVING, только надо запомнить, что WHERE фильтрует строки, а HAVING — группы.

Итак, сегодня мы узнали, как создавать группы и как подсчитать количество строк в таблице и в группах. Вообще вместе с оператором GROUP BY можно использовать и другие встроенные функции, но их мы будем изучать позже.

Научись программировать на Python прямо сейчас!

• Научись программировать на Python прямо сейчас
• Бесплатный курс

Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.

Подсчет данных при помощи запроса

В этой статье описано, как подсчитать данные, возвращаемые запросом. Например, в форме или отчете можно подсчитать количество элементов в одном или нескольких полях таблицы или элементах управления. Вы также можете вычислять средние значения, находить наибольшее и наименьшее значения, самую давнюю и самую последнюю дату. Кроме того, в Access предусмотрено средство, называемое строкой итогов, с помощью которого можно подсчитывать данные в таблице, не изменяя структуру запроса.

Выберите нужное действие

• Способы подсчета данных
• Подсчет данных с помощью строки итогов
• Подсчет данных с помощью итогового запроса
• Справочные сведения об агрегатных функциях

Способы подсчета данных

Подсчитать количество элементов в поле (столбце значений) можно с помощью функции Число. Функция Число принадлежит к ряду функций, называемых агрегатными. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Кроме функции Число, в Access есть следующие агрегатные функции:

• Сумма для суммирования столбцов чисел;
• Среднее для вычисления среднего значения в столбце чисел;
• Максимум для нахождения наибольшего значения в поле;
• Минимум для нахождения наименьшего значения в поле;
• Стандартное отклонение для оценки разброса значений относительно среднего значения;
• Дисперсия для вычисления статистической дисперсии всех значений в столбце.

В Access предусмотрено два способа добавления функции Count и других агрегатных функций в запрос. Вы можете:

• Открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов позволяет использовать агрегатные функции в одном или нескольких столбцах в результатах запроса без необходимости изменять его структуру.
• Создать итоговый запрос. В итоговом запросе вычисляются промежуточные итоги по группам записей. Например, если вы хотите вычислить промежуточную сумму всех продаж по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж. С другой стороны, с помощью строки итогов можно вычислить общий итог для одного или нескольких столбцов (полей) данных.

Примечание: Ниже в разделах этой статьи подробно описано применение функции Сумма, однако следует помнить, что вы можете использовать другие агрегатные функции в строках итогов и запросах. Дополнительные сведения об использовании других агрегатных функций см. ниже в разделе Справочные сведения об агрегатных функциях.

Дополнительные сведения о способах использования других агрегатных функций см. в разделе Отображение итогов по столбцу в таблице.

В следующих разделах описаны шаги, которые следует выполнить, чтобы добавить строку итогов, а также описывается использование итогового запроса для подсчета данных. Следует обратить внимание на то, что функция Число работает с большим числом типов данных, чем другие агрегатные функции. Функцию Число можно использовать для любого типа полей, кроме тех, которые содержат сложные повторяющиеся скалярные данные, например поле с многозначными списками.

С другой стороны, многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция Сумма работает только с типами данных «Число», «Действительное» и «Денежный». Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе Справочные сведения об агрегатных функциях.

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

Подсчет данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма, Минимум, Максимум или Среднее. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.

Создание простого запроса на выборку

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть». Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов. На рисунке показана типичная таблица в конструкторе запросов.
3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе. Вы можете включить поля, содержащие описательные данные, например имена и описания, но следует обязательно добавить поле, содержащее подсчитываемые значения. Каждое поле отображается в столбце в бланке запроса.
4. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
5. При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
6. При необходимости вы можете сохранить запрос.

Добавление строки итогов

1. Откройте запрос в режиме таблицы. Если база данных имеет формат ACCDB, щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы. -или- Если используется база данных в формате MDB, созданная в более ранней версии Access, на вкладке Главная в группе Режимы щелкните стрелку под кнопкой Режим и выберите значение Режим таблицы. -или- Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.
2. На вкладке Главная в группе Записи нажмите кнопку Итоги. Под последней строкой данных в таблице появится новая строка Итог.
3. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

Скрытие строки итогов

• На вкладке Главная в группе Записи нажмите кнопку Итоги.

Дополнительные сведения об использовании строки итогов см. в разделе Отображение итогов по столбцу в таблице.

Подсчет данных с помощью итогового запроса

Когда нужно подсчитать некоторые или все записи, возвращаемые запросом, то вместо строки итогов можно воспользоваться итоговым запросом. Например, вы можете подсчитать общее число сделок или число сделок в отдельном городе.

Как правило, итоговый запрос применяется вместо строки итогов тогда, когда требуется использовать значение результата в другой части базы данных, например в отчете.

Подсчет всех записей в запросе

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу, которую вы хотите использовать в запросе, и нажмите кнопку «Закрыть». Таблица появится в окне в верхней части конструктора запросов.
3. Дважды щелкните поля, которые вы хотите использовать в запросе, и убедитесь, что включено поле, количество в которое нужно подсчитать. Можно подсчитать поля большинства типов данных, за исключением полей, содержащих сложные повторяющиеся скалярные данные, такие как поле многомерных списков.
4. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
5. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
6. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
7. При необходимости вы можете сохранить запрос.

Подсчет записей в группе или категории

1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку «Закрыть». Таблица (или таблицы) появится в окне в верхней части конструктора запросов.
3. Дважды щелкните поле, содержащее данные категории, а также поле, значения в котором вы хотите подсчитать. Запрос не может содержать других описательных полей.
4. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги. В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.
5. В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.
6. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить. Результаты запроса отображаются в режиме таблицы.
7. При необходимости вы можете сохранить запрос.

Справочные сведения об агрегатных функциях

В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов. Кроме того, при работе с проектом Access (внешней базой данных Access, которая подключается к базе данных Microsoft SQL Server) можно использовать расширенный набор агрегатных функций, предоставляемый SQL Server. Дополнительные сведения о них см. в электронной документации Microsoft SQL Server.

Поддерживаемые типы данных

Суммирует элементы в столбце. Подходит только для числовых и денежных данных.

«Число», «Действительное», «Денежный»

Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения.

«Число», «Действительное», «Денежный», «Дата/время»

Подсчитывает число элементов в столбце.

Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков.

Дополнительные сведения о списках, которые могут быть многоценными, см. в руководстве по полям, которые могут быть многоценными, а также к созданию или удалите многофаентное поле.

Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.

«Число», «Действительное», «Денежный», «Дата/время»

Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наименьшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.

«Число», «Действительное», «Денежный», «Дата/время»

Стандартное отклонение

Показывает, насколько значения отклоняются от среднего.

Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице.

«Число», «Действительное», «Денежный»

Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение.

Дополнительные сведения о функциях для расчета дисперсии см. в разделе Отображение итогов по столбцу в таблице.

«Число», «Действительное», «Денежный»

Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.

Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.

Синтаксис примерно такой:

функция OVER окно 

Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.

Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.

SELECT id, section, header, score, row_number() OVER () AS num FROM news; id | section | header | score | num ----+---------+-----------+-------+----- 1 | 2 | Заголовок | 23 | 1 2 | 1 | Заголовок | 6 | 2 3 | 4 | Заголовок | 79 | 3 4 | 3 | Заголовок | 36 | 4 5 | 2 | Заголовок | 34 | 5 6 | 2 | Заголовок | 95 | 6 7 | 4 | Заголовок | 26 | 7 8 | 3 | Заголовок | 36 | 8 

В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.

SELECT id, section, header, score, row_number() OVER (ORDER BY score DESC) AS rating FROM news ORDER BY id; id | section | header | score | rating ----+---------+-----------+-------+-------- 1 | 2 | Заголовок | 23 | 7 2 | 1 | Заголовок | 6 | 8 3 | 4 | Заголовок | 79 | 2 4 | 3 | Заголовок | 36 | 4 5 | 2 | Заголовок | 34 | 5 6 | 2 | Заголовок | 95 | 1 7 | 4 | Заголовок | 26 | 6 8 | 3 | Заголовок | 36 | 3 

Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.

Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:

SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section; id | section | header | score | rating_in_section ----+---------+-----------+-------+------------------- 2 | 1 | Заголовок | 6 | 1 6 | 2 | Заголовок | 95 | 1 5 | 2 | Заголовок | 34 | 2 1 | 2 | Заголовок | 23 | 3 4 | 3 | Заголовок | 36 | 1 8 | 3 | Заголовок | 36 | 2 3 | 4 | Заголовок | 79 | 1 7 | 4 | Заголовок | 26 | 2 

Если не указывать партицию, то партицией является весь запрос.

Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.

Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.

SELECT transaction_id, change FROM balance_change ORDER BY transaction_id; transaction_id | change ----------------+-------- 1 | 1.00 2 | -2.00 3 | 10.00 4 | -4.00 5 | 5.50 

и мы хотим узнать заодно, как менялся остаток на балансе при этом:

SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance FROM balance_change ORDER BY transaction_id; transaction_id | change | balance ----------------+--------+--------- 1 | 1.00 | 1.00 2 | -2.00 | -1.00 3 | 10.00 | 9.00 4 | -4.00 | 5.00 5 | 5.50 | 10.50 

Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).

Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
от начала до конца партиции.

SELECT transaction_id, change, sum(change) OVER () as result_balance FROM balance_change ORDER BY transaction_id; transaction_id | change | result_balance ----------------+--------+---------------- 1 | 1.00 | 10.50 2 | -2.00 | 10.50 3 | 10.00 | 10.50 4 | -4.00 | 10.50 5 | 5.50 | 10.50 

Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.

Оконные функции можно использовать сразу по несколько штук, они друг другу ничуть не мешают, чтобы вы там в них не написали.

SELECT transaction_id, change, sum(change) OVER (ORDER BY transaction_id) as balance, sum(change) OVER () as result_balance, round( 100.0 * sum(change) OVER (ORDER BY transaction_id) / sum(change) OVER (), 2 ) AS percent_of_result, count(*) OVER () as transactions_count FROM balance_change ORDER BY transaction_id; transaction_id | change | balance | result_balance | percent_of_result | transactions_count ----------------+--------+---------+----------------+-------------------+-------------------- 1 | 1.00 | 1.00 | 10.50 | 9.52 | 5 2 | -2.00 | -1.00 | 10.50 | -9.52 | 5 3 | 10.00 | 9.00 | 10.50 | 85.71 | 5 4 | -4.00 | 5.00 | 10.50 | 47.62 | 5 5 | 5.50 | 10.50 | 10.50 | 100.00 | 5 

Если у вас много одинаковых выражений после OVER, то можно дать им имя и вынести отдельно с ключевым словом WINDOW, чтобы избежать дублирования кода. Вот пример из мануала:

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC); 

Здесь w после слова OVER идет без уже скобок.

Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные фунции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Поэтому чтобы выбрать, например, топ 5 новостей в каждой группе, надо использовать подзапрос:

SELECT * FROM ( SELECT id, section, header, score, row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section FROM news ORDER BY section, rating_in_section ) counted_news WHERE rating_in_section  

SELECT id, section, header, score, row_number() OVER w AS rating, lag(score) OVER w - score AS score_lag FROM news WINDOW w AS (ORDER BY score DESC) ORDER BY score desc; id | section | header | score | rating | score_lag ----+---------+-----------+-------+--------+----------- 6 | 2 | Заголовок | 95 | 1 | 3 | 4 | Заголовок | 79 | 2 | 16 8 | 3 | Заголовок | 36 | 3 | 43 4 | 3 | Заголовок | 36 | 4 | 0 5 | 2 | Заголовок | 34 | 5 | 2 7 | 4 | Заголовок | 26 | 6 | 8 1 | 2 | Заголовок | 23 | 7 | 3 2 | 1 | Заголовок | 6 | 8 | 17 

Select COUNT(ID) FROM tab where Start_Date==01.01.2020 

with params as ( select date'2020-01-01' from#, 12 months# from dual ) select to_char (mon, 'Mon rr') month, count (id) from ( select add_months (trunc (from#, 'mm'), level-1) mon from params connect by level<=months#) left join tab t on (t.sdt <= last_day (mon) and t.edt >= mon) group by mon order by mon / 

MONTH COUNT(ID) --------------- ---------- Jan 20 1 Feb 20 1 Mar 20 2 Apr 20 2 May 20 2 Jun 20 2 Jul 20 2 Aug 20 3 Sep 20 2 Oct 20 2 Nov 20 1 Dec 20 0