70126

Итоговые запросы. Агрегатные функции

Лабораторная работа

Информатика, кибернетика и программирование

Среднее арифметическое значений выражения для всех записей в группе countвыражение Количество записей в группе для которых значение выражения отлично от NULL mxвыражение Максимальное значение выражения в группе minвыражение Минимальное значение выражения в группе...

Русский

2014-10-15

68 KB

3 чел.

4. Итоговые запросы

4.1. Агрегатные функции

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

Далее перечислены агрегатные функции, поддерживаемые в PostgreSQL. Полный список агрегатных функций выводится в psql командой \da.

avg(выражение) Среднее арифметическое значений выражения для всех записей в группе

count(выражение) Количество записей в группе, для которых значение выражения отлично от NULL

max(выражение) Максимальное значение выражения в группе

min(выражение) Минимальное значение выражения в группе

stddev(выражение) Среднеквадратичное отклонение значений выражения в группе

sum(выражение) Сумма значений выражения в группе

variance(выражение) Дисперсия значений выражения в группе

Термин выражение означает любой столбец в итоговом наборе или любое выражение, выполняющее операцию с этим столбцом.

При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако возможно задать заголовки для значений итоговых функций и других столбцов по своему усмотрению. Для этого достаточно после имени столбца в операторе SELECT указать выражение вида AS заголовок_столбца.

Count(параметр) – возвращает количество записей, указанных в параметре. Если требуется получить количество всех записей итогового набора, то в качестве параметра следует указать символ звездочки (*). Если в качестве параметра указать имя столбца, то функция вернет количество записей, в которых этот столбец имеет значения, отличные от NULL. Чтобы узнать, сколько различных значений содержит столбец, перед его именем следует указать ключевое слово DISTINCT.

Например:

SELECT count(location) AS set_locs,

count(ALL location) AS all_locs,

count(DISTINCT location) AS unique_locs,

count(*) AS all_rows

FROM subjects;

Результат запроса:

set_locs all_locs unique_locs all_rows

15 15 7 16

Примеры использования других агрегатных функций:

SELECT AVG(retail) AS средняя_цена

FROM stock;

SELECT MIN(reatail * 28.8) AS         минимальная_цена_в_долларах

FROM stock;

SELECT SUM(retail) AS общая_стоимость_редких_книг

FROM stock

WHERE stock < 10;

4.2. Группировка записей

Предложение GROUP BY в инструкции SELECT задает столбцы, используемые для формирования групп из выбранных строк. Строки каждой группы содержат одно и то же значение заданного столбца (столбцов). Выражение за ключевым словом GROUP BY может быть простым полем таблицы, оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей производится по совпадению значений во всех перечисленных выражениях. Появление секции GROUP BY в запросе SQL приводит к тому, что все записи с одинаковым значением выражений, заданных в предложении GROUP BY, группируются в одну запись. Если предложение GROUP BY расположено после предложения WHERE, то создаются группы из строк, выбранных после применения WHERE.

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

Выведем количество книг, хранящихся в базе данных booktown для каждого издательства:

SELECT name AS publisher,

count(isbn) AS number_of_books

FROM editions AS e INNER JOIN publishers AS p

ON (e.publisher_id = p.id)

GROUP BY name;

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

Получим количество книг, написанных авторами по каждой теме:

SELECT last_name, first_name, subject,

count(title) AS number_of_books

FROM books AS b INNER JOIN authors AS a

 ON (b.author_id = a.id)

INNER JOIN subjects AS s

 ON(b.subject_id = s.id)

GROUP BY last_name, first_name, subject;

4.3. Отбор групп записей

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

Разница между предложениями HAVING и WHERE заключается в том, что условие отбора, заданное в предложении WHERE, применяется к отдельным записям перед объединением их в группы, а условие отбора предложения HAVING применяется к группам строк. Секция WHERE не может содержать агрегатных функций. Условия же секции HAVING, наоборот, основаны на агрегатных функциях, а не на условиях для отдельных записей.

Выведем количество книг, хранящихся в базе данных booktown, для тех издательств, которые представлены двумя и более книгами:

SELECT name AS publisher,

count(isbn) AS number_of_books

FROM editions AS e INNER JOIN publishers AS p

ON (e.publisher_id = p.id)

GROUP BY name

HAVING count(isbn)>1;


Лабораторная работа 4

Выполнить следующие запросы к базе данных booktown:

  1.  Общее количество книг на складе (stock).
  2.  Количество книг с розничной ценой выше 30.
  3.  Выручка магазина при реализации всех книг.
  4.  Средняя цена (cost) книги.
  5.  Средняя цена (cost) для имеющихся книг на складе (stock).
  6.  Даты наиболее ранней и наиболее поздней публикаций.
  7.  Количество совершенных покупок.
  8.  Количество покупателей, совершавших покупки.
  9.  Количество покупателей с различными фамилиями, совершавших покупки.
  10.  Количество авторов, написавших какие-либо книги.
  11.  Количество изданий, выпущенных в твердой и мягкой обложке.
  12.  Количество первых изданий, вторых и так далее. В таком же порядке.
  13.  Количество книг, которые приобрел каждый покупатель. Отсортировать по покупателю.
  14.  Список покупателей-однофамильцев с подсчетом их количества.
  15.  Количество кодов isbn, которые были изданы каждым издательством. Отсортировать по названию издательства.
  16.  Количество книг различных названий, которые выпустило каждое издательство. Отсортировать по названию издательства.
  17.  Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
  18.  Количество кодов isbn, которые были изданы каждым издательством, находящимся в городе New York. Издательства, выпустившие менее двух изданий (кодов isbn), не выводить. Отсортировать так, чтобы сначала шли издательства, выпустившие наибольшее количество изданий (кодов isbn).
  19.  Первая дата публикации для каждого автора. Упорядочить по датам.
  20.  Фамилии авторов, чьи произведения не имеют изданий выше второго.
  21.  Фамилии авторов, все издания которых имеются в наличии в количестве не менее 50 экземпляров.
  22.  Фамилии авторов, все книги которых выходили только в бумажной обложке. Отсортировать по фамилии автора.
  23.  Наибольшее количество книг, написанных одним автором.
  24.  Наименьшее количество изданий, выпущенных одним издательством.


 

А также другие работы, которые могут Вас заинтересовать

54683. Мій край – моя історія. Оріхів -місто серця мого 61.5 KB
  Нас зацікавила історія нашого міста району яким воно було у давнину коли одержало статус міста хто був першим головою міста На всі ці питання можна одержати відповідь із книжок Оріхів. Статус міста Оріхів одержав у 1801 році 19 лютого і став центром Мелітопольського повіту Таврійської губернії. В розпорядження міста була віддана земля 2602 десятини.
54685. Монополизм: сущность, формы, последствия. Естественная монополия 22.63 KB
  Рынок, где доминирует монополия, находится в резком контрасте со свободным рынком, на котором конкурирующие продавцы предлагают для продажи стандартизированный товар. Доступ других фирм на монополизированный рынок затруднен или невозможен, так как существуют барьеры, не позволяющие конкурентам войти в отрасль.
54686. Oберегu моєї оселі 123 KB
  Мета: Пропаганда літератури із народознавства, історії нашого народу, його мови, звичаїв, пробудити цікавість до народної символіки, прикмет, до невмирущих скарбів народу.
54687. Мереживо осінніх барв 252 KB
  Багату палітру кольорів дарує нам осінь коли природа одягається у розкішне вбрання. Яке розмаїття фарб Осінь міняє зелене вбрання на червоне золоте. Після спекотного літа після серпневих теплих днів настала золота осінь. Листя жовкне денеде: Панна осінь в гості йде.
54688. До нас у гості завітала Осінь 85 KB
  У святковій радісній атмосфері розширити збагатити та узагальнити знання дітей про пору року осінь викликати у дітей позитивні емоції від виконання пісень віршів осінньої тематики; розвивати увагу спостережливість музикальність артистизм; виховувати естетичні смаки дружні стосунки між дітьми любов до рідної природи бажання оберігати її. Дійові особи: Ведучі Осінь осінні Місяці Овочі Білочки Зайчики Лисичка Їжачок Жабка Ведмедик діти. Діти: Осінь.
54689. Свято Осені 45.5 KB
  Осінь – це пора, яку часто звуть чудовою, замріяною, золотою. Вона особливо м’яка, ніжна, як гарна мелодія. Слухаєш, спостерігаєш природу в перші осінні дні й відчуваєш всю її урочисту красу. У ній поєдналися чарівність барв теплого літечка з першими подихами наступних холодів зими.
54690. ОСІНЬ ЩЕДРА, ОСІНЬ ЗОЛОТАВА 142 KB
  ОСІНЬ ЩЕДРА ОСІНЬ ЗОЛОТАВА МЕТА: узагальнити знання учнів про осінь її особливості прикмети; використовуючи художнє слово навчати складати невеличкі описи казки вірші про явища природи на основі безпосередніх вражень від спостережень в природі та набутих знань; вчити вдивлятися в навколишній світ більше спілкуватися з приро доюбережно ставитися до неї; вчити аналізувати картину музичний твір зістав ляти різні способи зображення одного й того ж природного явища; вчити знаходи ти цікаві особливості явищ природи поєднання...
54691. Осенние посиделки 69.5 KB
  Познакомить учащихся с явлениями, происходящими в природе осенью, с народными традициями, связанными с этим временем года. Развивать чувства прекрасного, любви к окружающей природе, необходимости её охраны. Воспитывать у учащихся чувство товарищества, взаимопомощи.