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.  Наименьшее количество изданий, выпущенных одним издательством.


 

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

57674. Великобританія та Україна 62.5 KB
  Yes, you’re right. But today I propose you to watch a TV programme about another one British park. Be ready to discuss the programme and we’ll practice the dialogues to improve your communicative skills.
57675. Одяг. Професії 110.5 KB
  Well, you are pupils and you are wearing white shirts or blouses, black skirts or trousers. And what about people of different professions. Your home task was to make up short dialogues using the pictures.
57676. Їжа та напої 1.04 MB
  Of course,you are right! Our mood depends on our meals. As you guessed, The topic of our today lesson is Food and Drinks. We will speak, read, listen, write about food and drinks during our lesson.
57677. Healthy way of life. What does it mean to ве healthy? 89.5 KB
  You see the Sun on the blackboard, it’s our health. What is health for you? What do you associate with health? And the first task is “Associations”. Write your associations with the word “Health” on these stripes-sunrays and stick to our Sun.
57678. In sound body - sound mind 129.5 KB
  Good health is a great gift. The English proverb says, «Health is not valued till illness comes». The most important thing in the world is our health. Each person is a creator of the human body. Now look at the blackboard. Here you see the main words of the proverb.
57679. Veterans Day 188.5 KB
  Veterans Day is observed with ceremonies at war monuments and cemeteries throughout the nation. Almost every village has a monument to veterans who served in one of the country’s wars.
57680. ЗІРКИ МУЗИКИ 108.5 KB
  It’s true because we can’t imagine our life without music. People all over the world are fond of music. They listen to music, they dance to music, they learn to play musical instruments. People make their own music too.
57681. The Beauty of Future Cities 40.5 KB
  Show the students some photos of the future cities. It can be initial slides of your power point presentation and ask them to guess what the theme of the lesson is. Right you are. Today we are going to have a talk about megacities of today and their future.
57682. Books are Our Friends. The World of Books 44.5 KB
  Objectives: Pupils’ learning outcomes: practical to present and give practice in the use of new words; will learn 8 new words; to present and give practice in the use of “be fond of”, will be able to express their attitude to reading and “be interested in” in the micro dialogues...