Нахождение наибольшего и наименьшего из итоговых значений

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

Решение
Добавьте в запрос инструкцию LIMIT.

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

mysql> SELECT name, SUM(miles)
-> FROM driver_log
-> GROUP BY name;

+-------+--------------+
| name | SUM(miles) |
+-------+--------------+
| Ben | 362 |
| Henry | 911 |
| Suzi | 893 |
+-------+--------------+

Но выбрать только запись для водителя с наибольшим количеством миль так не удастся:

mysql> SELECT name, SUM(miles)
-> FROM driver_log
-> GROUP BY name
-> HAVING SUM(miles) = MAX(SUM(name));
ERROR 1111 at line 1: Invalid use of group function


Вместо этого упорядочим строки, сделав первым наибольшее значение SUM(), и применим инструкцию LIMIT для выбора первой записи:

mysql> SELECT name, SUM(miles) AS 'total miles'
-> FROM driver_log
-> GROUP BY name
-> ORDER BY 'total miles' DESC LIMIT 1;


+-------+------------+
| name | total miles |
+-------+------------+
| Henry | 911 |
+-------+------------+

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

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

mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states
-> GROUP BY letter ORDER BY count DESC LIMIT 1;


+-------+-------+
| letter | count |
+-------+-------+
| M | 8 |
+-------+-------+

Но и с буквы N начинаются названия восьми штатов. Если нужны все наиболее часто встречающиеся значения, которых может быть несколько, следует использовать два запроса:

mysql> SELECT LEFT(name,1) AS letter, @max:=COUNT(*) AS count FROM states
-> GROUP BY letter ORDER BY count DESC LIMIT 1;
mysql> SELECT LEFT(name,1) AS letter, COUNT(*) AS count FROM states
-> GROUP BY letter HAVING count = @max;


+-------+-------+
| letter | count |
+-------+-------+
| M | 8 |
| N | 8 |
+------+--------+

Добавлено: 23 Июля 2018 12:53:20 Добавил: Андрей Ковальчук

Управление порядком вывода итоговой информации

Задача
Вы хотите упорядочить результат запроса, выводящего итоговую информацию.

Решение
Если желаемый порядок недостижим при помощи инструкции GROUP BY, используйте инструкцию ORDER BY.

Обсуждение
В MySQL GROUP BY не только группирует, но и сортирует. Поэтому часто нет необходимости дополнительно использовать инструкцию ORDER BY в запросе, выводящем итоговую информацию. Если же вы хотите изменить порядок, задаваемый инструкцией GROUP BY по умолчанию, используйте ORDER BY. Например, чтобы вычислить количество дней за рулем и общее количество миль для каждого водителя из таблицы driver_log, выполним такой запрос:

mysql> SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
-> FROM driver_log GROUP BY name;


+-------+------+-------------+
| name | days | total miles |
+-------+------+-------------+
| Ben | 3 | 362 |
| Henry | 5 | 911 |
| Suzi | 2 | 893 |
+-------+------+-------------+

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

mysql> SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
-> FROM driver_log GROUP BY name
-> ORDER BY days DESC;


+-------+------+---------+
| name | days | mileage |
+-------+------+---------+
| Henry | 5 | 911 |
| Ben | 3 | 362 |
| Suzi | 2 | 893 |
+-------+------+---------+

mysql> SELECT name, COUNT(*) AS days, SUM(miles) AS mileage
-> FROM driver_log GROUP BY name
-> ORDER BY mileage DESC;


+-------+------+---------+
| name | days | mileage |
+-------+------+---------+
| Henry | 5 | 911 |
| Suzi | 2 | 893 |
| Ben | 3 | 362 |
+-------+------+---------+

Для ссылки в инструкции ORDER BY на итоговое значение необходимо использовать псевдоним или позицию столбца в списке вывода. Это касается даже версий MySQL 3.23.2 и выше, которые обычно разрешают применять выражения в инструкции ORDER BY; но эти выражения должны ссылаться на индивидуальные значения, а не на вычисленные.

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

mysql> SELECT DAYNAME(statehood), COUNT(*) FROM states
-> GROUP BY DAYNAME(statehood);


+--------------------------+-------------+
| DAYNAME(statehood) | COUNT(*) |
+--------------------------+-------------+
| Friday | 8 |
| Monday | 9 |
| Saturday | 11 |
| Thursday | 5 |
| Tuesday | 6 |
| Wednesday | 11 |
+--------------------------+------------+

Из списка, конечно, можно извлечь информацию о том, что ни один из штатов не вступил в Союз в воскресенье, но для этого требуется провести некоторый анализ результата. Вывод был бы более понятным, если бы применялось упорядочивание по дням недели. Можно добавить инструкцию ORDER BY для сортировки по числовому значению дня недели, но есть и другой способ достижения того же результата, причем без помощи ORDER BY: выполним группировку по DAYOFWEEK(), а не по DAYNAME():

mysql> SELECT DAYNAME(statehood), COUNT(*)
-> FROM states GROUP BY DAYOFWEEK(statehood);


+--------------------------+-------------+
| DAYNAME(statehood) | COUNT(*) |
+--------------------------+-------------+
| Monday | 9 |
| Tuesday | 6 |
| Wednesday | 11 |
| Thursday | 5 |
| Friday | 8 |
| Saturday | 11 |
+--------------------------+-------------+

Добавлено: 23 Июля 2018 12:52:25 Добавил: Андрей Ковальчук

Классификация некатегориальных данных

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

Решение
Для разбиения значений на категории используйте выражения.

Обсуждение
Группирование строк по результатам вычисления выражения часто используется для классификации значений, которые сами по себе не образуют никаких категорий. Такая возможность очень важна, так как инструкция GROUP BY отлично работает для столбцов с повторяющими значениями. Но, например, вам может понадобиться выполнить анализ численности населения, группируя записи таблицы states по значениям столбца pop. Получитсяне очень хорошо, так как в столбце много разных значений. На самом деле они вообще все разные, как видно из запроса:

mysql> SELECT COUNT(pop), COUNT(DISTINCT pop) FROM states;


+---------------+-----------------------------+
| COUNT(pop) | COUNT(DISTINCT pop) |
+---------------+-----------------------------+
| 50 | 50 |
+---------------+-----------------------------+

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

mysql> SELECT MIN(pop), MAX(pop) FROM states;


+-----------+-------------+
| MIN(pop) | MAX(pop) |
+-----------+-------------+
| 453588 | 29760021 |
+-----------+-------------+

Из результата видно, что если разделить значения pop на пять миллионов, можно получить шесть категорий – вполне разумное количество. (Категории будут иметь диапазоны от 1 до 5 000 000; от 5 000 001 до 10 000 000; и т. д.)

Чтобы определить категорию каждого значения, выполним деление на пять миллионов и воспользуемся целой частью результата:

mysql> SELECT FLOOR(pop/5000000) AS 'population (millions)',

-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;


+------------------------+---------------------+
| population (millions) | number of states |
+------------------------+---------------------+
| 0 | 35 |
| 1 | 8 |
| 2 | 4 |
| 3 | 2 |
| 5 | 1 |
+------------------------+---------------------+

Что-то не так… Выражение группирует значения численности населения в небольшое количество категорий, но почему-то неправильно определяет значение соответствующей категории. Давайте попробуем умножить результаты функции FLOOR() на пять:

mysql> SELECT FLOOR(pop/5000000)*5 AS 'population (millions)',
-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;


+-----------------------+----------------------+
| population (millions) | number of states |
+-----------------------+----------------------+
| 0 | 35 |
| 5 | 8 |
| 10 | 4 |
| 15 | 2 |
| 25 | 1 |
+-----------------------+----------------------+

Все равно неправильно! Максимальное население штата равнялось 29 760 021, такое значение должно было попасть в категорию для 30, а не 25 миллионов. Проблема в том, что выражение, формирующее категории, группирует значения по нижней границе каждого интервала. Чтобы добиться группирования по верхней границе, применим один прием: для сопоставления значению x соответствующей категории длины n воспользуйтесь выражением:

FLOOR((x+(n-1))/n)

В итоге запрос будет таким:
mysql> SELECT FLOOR((pop+4999999)/5000000)*5 AS 'population (millions)',
-> COUNT(*) AS 'number of states'
-> FROM states GROUP BY 1;


+------------------------+---------------------+
| population (millions) | number of states |
+------------------------+---------------------+
| 5 | 35 |
| 10 | 8 |
| 15 | 4 |
| 20 | 2 |
| 30 | 1 |
+-----------------------+----------------------+

Как видите, население большей части штатов США не превышает пяти миллионов.
Данный метод можно применять к любым видам числовых значений. Например, можно группировать записи таблицы mail в категории по 100 000 каждая:

mysql> SELECT FLOOR((size+99999)/100000) AS 'size (100KB)',
-> COUNT(*) AS 'number of messages'
-> FROM mail GROUP BY 1;


+---------------+-------------------------+
| size (100KB) | number of messages |
+---------------+-------------------------+
| 1 | 13 |
| 2 | 1 |
| 10 | 1 |
| 24 | 1 |
+--------------+-------------------------+

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

mysql> SELECT FLOOR(LOG10(pop)) AS 'log10(population)',
-> COUNT(*) AS 'number of states'-> FROM states GROUP BY 1;


+---------------------+---------------------+
| log10(population) | number of states |
+---------------------+---------------------+
| 5 | 7 |
| 6 | 36 |
| 7 | 7 |
+---------------------+---------------------+

Добавлено: 23 Июля 2018 06:37:03 Добавил: Андрей Ковальчук

Группирование по результатам выражения

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

Решение
Поместите выражение в инструкцию GROUP BY. В ранних версиях MySQL, не поддерживающих выражения в GROUP BY, используйте обходной маневр.

Обсуждение
Как и ORDER BY, инструкция GROUP BY может ссылаться на выражения начиная с версии MySQL 3.23.2. То есть можно использовать вычисления как основу для группирования. Например, чтобы вывести распределение длин названий штатов, выполните группирование по LENGTH(name):

mysql> SELECT LENGTH(name), COUNT(*)
-> FROM states GROUP BY LENGTH(name);


+------------------+-------------+
| LENGTH(name) | COUNT(*) |
+------------------+-------------+
| 4 | 3 |
| 5 | 3 |
| 6 | 5 |
| 7 | 8 |
| 8 | 12 |
| 9 | 4 |
| 10 | 4 |
| 11 | 2 |
| 12 | 4 |
| 13 | 3 |
| 14 | 2 |
+------------------+-------------+

В версиях до MySQL 3.23.2 выражения в инструкции GROUP BY не поддерживались, так что такой запрос не выполнился бы. Было показано, как обойти это ограничение для ORDER BY; то же самое можно сделать и для GROUP BY. Можно указать псевдоним выражения в списке столбцов вывода и сослаться в инструкции GROUP BY на этот псевдоним:

mysql> SELECT LENGTH(name) AS len, COUNT(*)
-> FROM states GROUP BY len;


+------+----------+
| len | COUNT(*) |
+----+-------------+
| 4 | 3 |
| 5 | 3 |
| 6 | 5 |
| 7 | 8 |
| 8 | 12 |
| 9 | 4 |
| 10 | 4 |
| 11 | 2 |
| 12 | 4 |
| 13 | 3 |
| 14 | 2 |
+----+------------+

Можно переписать инструкцию GROUP BY так, чтобы она ссылалась на столбец по его позиции в списке вывода:

mysql> SELECT LENGTH(name), COUNT(*)
-> FROM states GROUP BY 1;


+------------------+-------------+
| LENGTH(name) | COUNT(*) |
+------------------+-------------+
| 4 | 3 |
| 5 | 3 |
| 6 | 5 |
| 7 | 8 |
| 8 | 12 |
| 9 | 4 |
| 10 | 4 |
| 11 | 2 |
| 12 | 4 |
| 13 | 3 |
| 14 | 2 |
+------------------+-------------+

Естественно, способы, предложенные в качестве альтернативы использованию выражений, будут работать и в MySQL версии 3.23.2 и выше, и некоторые пользователи считают их наиболее удачными.

При желании вы можете выполнять группирование по нескольким выражениям. Чтобы найти те дни года, в которые в Союз вступило более одного штата, группируйте строки по месяцу и дню статуса штата, а затем примените функции HAVING и COUNT() для поиска неуникальных комбинаций:

mysql> SELECT MONTHNAME(statehood), DAYOFMONTH(statehood), COUNT(*)
-> FROM states GROUP BY 1, 2 HAVING COUNT(*) > 1;


+------------------------------+--------------------------------+-------------+
| MONTHNAME(statehood) | DAYOFMONTH(statehood) | COUNT(*) |
+------------------------------+--------------------------------+-------------+
| February | 14 | 2 |
| June | 1 | 2 |
| March | 1 | 2 |
| May | 29 | 2 |
| November | 2 | 2 |
+------------------------------+--------------------------------+--------------+

Добавлено: 23 Июля 2018 06:35:46 Добавил: Андрей Ковальчук

Устанавливаем уникальность значения

Задача
Вы хотите знать, уникальны ли значения таблицы.

Решение
Используйте инструкцию HAVING в сочетании с функцией COUNT().

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

Следующий запрос выводит дни, в которые работал только один водитель, и дни, в которые работало несколько водителей. HAVING и COUNT() используются для определения того, являются ли значения trav_date уникальными:

mysql> SELECT trav_date, COUNT(trav_date)
-> FROM driver_log
-> GROUP BY trav_date
-> HAVING COUNT(trav_date) = 1;


+--------------+-----------------------+
| trav_date | COUNT(trav_date) |
+--------------+-----------------------+
| 2001-11-26 | 1 |
| 2001-11-27 | 1 |
| 2001-12-01 | 1 |
+--------------+-----------------------+

mysql> SELECT trav_date, COUNT(trav_date)
-> FROM driver_log
-> GROUP BY trav_date
-> HAVING COUNT(trav_date) > 1;


+--------------+-----------------------+
| trav_date | COUNT(trav_date) |
+--------------+-----------------------+
| 2001-11-29 | 3 |
| 2001-11-30 | 2 |
| 2001-12-02 | 2 |
+--------------+-----------------------+

Такой прием можно применять и к комбинациям значений. Например, чтобы найти количество пар отправитель-получатель (srcuser-dstuser), которые обменялись всего одним письмом, будем искать комбинации, встречающиеся в таблице mail только один раз:

mysql> SELECT srcuser, dstuser
-> FROM mail
-> GROUP BY srcuser, dstuser
-> HAVING COUNT(*) = 1;


+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| gene | tricia |
| phil | barb |
| tricia | gene |
| tricia | phil |
+---------+---------+

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

Добавлено: 23 Июля 2018 06:34:55 Добавил: Андрей Ковальчук

Выбор групп только с определенными характеристиками

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

Решение
Используйте инструкцию HAVING.

Обсуждение
Вы уже умеете применять инструкцию WHERE для задания условий, которым должны удовлетворять отдельные записи, выбираемые запросом. Кажется естественным использовать WHERE и для написания условий для итоговых значений. Но ничего не выйдет. Если вы захотите узнать, какой водитель из таблицы driver_log работал больше трех дней, то, вероятно, начнете с такого запроса:

mysql> SELECT COUNT(*), name
-> FROM driver_log
-> WHERE COUNT(*) > 3
-> GROUP BY name;
ERROR 1111 at line 1: Invalid use of group function


Дело в том, что WHERE указывает на исходные ограничения, определяющие, какие строки следует выбирать, но значение функции COUNT() может быть получено лишь после того, как строки выбраны. Необходимо поместить выражение COUNT() в инструкцию HAVING, аналогичную WHERE, но применяемую к групповым характеристикам, а не отдельным записям. То есть HAVING работает с уже выбранным и сгруппированным набором строк, применяя к нему дополнительные условия, сформированные на основе результатов агрегирующей функции, которые не были известны в момент первичной выборки.

Предыдущий запрос можно переписать так:

mysql> SELECT COUNT(*), name
-> FROM driver_log
-> GROUP BY name
-> HAVING COUNT(*) > 3;


+------------+---------+
| COUNT(*) | name |
+------------+---------+
| 5 | Henry |
+-------------+--------+

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

mysql> SELECT COUNT(*) AS count, name
-> FROM driver_log
-> GROUP BY name
-> HAVING count > 3;


+-------+-------+
| count | name |
+-------+-------+
| 5 | Henry |
+-------+-------+

Добавлено: 23 Июля 2018 06:34:06 Добавил: Андрей Ковальчук

Итоги и значения NULL

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

Решение
Осознайте, каким образом агрегирующие функции обрабатывают значения NULL.

Обсуждение
Большинство агрегирующих функций игнорируют значения NULL. Предположим, у вас есть таблица expt, в которую записываются результаты тестов для испытуемых (subject), каждому из которых нужно пройти четыре теста (test); при этом указывается значение NULL, если результат (score) еще не получен:

mysql> SELECT subject, test, score FROM expt ORDER BY subject, test;


+---------+------+-------+
| subject | test | score |
+---------+-----+-------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | NULL |
| Jane | D | NULL |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | NULL |
+--------+------+-------+

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

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt GROUP BY subject;


+---------+--+------+----------+--------+----------+
| subject | n | total | average | lowest | highest |
+---------+--+------+----------+--------+----------+
| Jane | 2 | 97 | 48.5000 | 47 | 50 |
| Marvin | 3 | 150 | 50.0000 | 45 | 53 |
+---------+--+------+----------+--------+----------+

Из результатов в столбце n (количество тестов) видно, что запрос обработал только пять значений. Почему? Потому что в этом столбце выводятся только значения, соответствующие не-NULL-результатам тестов каждого испытуемого. Значения, представленные в других итоговых столбцах, также вычислены на основе только не-NULL-результатов тестов.

Вполне логично, что агрегирующие функции игнорируют значения NULL. Если бы они следовали обычным арифметическим правилам SQL, то в результате прибавления NULL к любому другому значению получалось бы значение NULL. Работать с агрегирующими функциями стало бы очень тяжело, поскольку вам приходилось бы каждый раз самостоятельно отфильтровывать значения NULL перед выполнением суммирования, чтобы избежать получения NULL-результата. Да, игнорируя NULL, агрегирующие функции становятся гораздо более привлекательными для использования.

Однако помните, что несмотря на то, что агрегирующие функции игнорируют значения NULL, некоторые из них все же могут получать это значение как результат. Так бывает, если нечего суммировать. Чуть-чуть изменим предыдущий запрос: теперь он выбирает только NULL-результаты тестов, так что агрегирующим функциям не с чем работать:

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> AVG(score) AS average,
-> MIN(score) AS lowest,
-> MAX(score) AS highest
-> FROM expt WHERE score IS NULL GROUP BY subject;


+---------+--+------+----------+--------+----------+
| subject | n | total | average | lowest | highest |
+---------+--+------+---------+---------+----------+
| Jane | 0 | 0 | NULL | NULL | NULL |
| Marvin | 0 | 0 | NULL | NULL | NULL |
+--------+---+------+---------+---------+----------+

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

Если складывать нечего, вы имеете дело с отношением 0/0, которое не определено. Поэтому для AVG() разумнее всего вернуть NULL. Аналогично, функциям MIN() и MAX() не с чем работать, поэтому они возвращают NULL. Если вы не хотите, чтобы эти функции выводили NULL, используйте IFNULL() для сопоставления им соответствующих значений:

mysql> SELECT subject,
-> COUNT(score) AS n,
-> SUM(score) AS total,
-> IFNULL(AVG(score),0) AS average,
-> IFNULL(MIN(score),'Unknown') AS lowest,-> IFNULL(MAX(score),'Unknown') AS highest
-> FROM expt WHERE score IS NULL GROUP BY subject;


+---------+--+------+----------+-----------+------------+
| subject | n | total | average | lowest | highest |
+---------+--+------+----------+-----------+------------+
| Jane | 0 | 0 | 0 | Unknown | Unknown |
| Marvin | 0 | 0 | 0 | Unknown | Unknown |
+---------+--+------+----------+-----------+------------+

Функция COUNT() несколько отличается в своей трактовке значений NULL от остальных агрегирующих функций. Как и другие агрегирующие функции, COUNT(выражение) считает только значения не-NULL, а COUNT(*) считает все строки независимо от их содержимого. Продемонстрируем разницу между двумя формами COUNT():

mysql> SELECT COUNT(*), COUNT(score) FROM expt;


+------------+------------------+
| COUNT(*) | COUNT(score) |
+------------+------------------+
| 8 | 5 |
+------------+------------------+

Теперь вы знаете, что в таблице expt восемь строк, но лишь в пяти из них заполнены значения score. Две формы COUNT() очень удобно использовать для подсчета недостающих значений – просто найдите разность значений:

mysql> SELECT COUNT(*) - COUNT(score) AS missing FROM expt;


+---------+
| missing |
+---------+
| 3 |
+---------+

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

mysql> SELECT subject,
-> COUNT(*) AS total,
-> COUNT(score) AS 'non-missing',
-> COUNT(*) - COUNT(score) AS missing
-> FROM expt GROUP BY subject;


+---------+------+--------------+---------+
| subject | total | non-missing | missing |
+---------+------+--------------+---------+
| Jane | 4 | 2 | 2 |
| Marvin | 4 | 3 | 1 |
+---------+------+--------------+---------+

Добавлено: 23 Июля 2018 06:33:16 Добавил: Андрей Ковальчук

Разбиение итогов на подгруппы

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

Решение
Используйте инструкцию GROUP BY для распределения строк по группам.

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

mysql> SELECT COUNT(*) FROM driver_log;


+------------+
| COUNT(*) |
+------------+
| 10 |
+------------+

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

mysql> SELECT name, COUNT(name) FROM driver_log GROUP BY name;


+-------+-----------------+
| name | COUNT(name) |
+-------+-----------------+
| Ben | 3 |
| Henry | 5 |
| Suz i | 2 |
+-------+------------------+

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

mysql> SELECT name,
-> SUM(miles) AS 'total miles',
-> AVG(miles) AS 'miles per day'
-> FROM driver_log GROUP BY name;


+-------+-------------+----------------+
| name | total miles | miles per day |
+-------+-------------+----------------+
| Ben | 362 | 120.6667 |
| Henry | 911 | 182.2000 |
| Suzi | 893 | 446.5000 |
+-------+-------------+----------------+

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

mysql> SELECT srcuser, COUNT(*) FROM mail
-> GROUP BY srcuser;


+---------+-------------+
| srcuser | COUNT(*) |
+---------+-------------+
| barb | 3 |
| gene | 6 |
| phil | 5 |
| tricia | 2 |
+---------+-------------+

Чтобы получить более конкретные данные и узнать, сколько сообщений каждый пользователь отправил с каждого хоста, используйте группировку по двум столбцам. В результате получатся вложенные группы (группы внутри групп):

mysql> SELECT srcuser, srchost, COUNT(*) FROM mail 
-> GROUP BY srcuser, srchost;


+---------+---------+-------------+
| srcuser | srchost | COUNT(*) |
+---------+---------+-------------+
| barb | saturn | 2 |
| barb | venus | 1 |
| gene | mars | 2 |
| gene | saturn | 2 |
| gene | venus | 2 |
| phil | mars | 3 |
| phil | venus | 2 |
| tricia | mars | 1 |
| tricia | saturn | 1 |
+--------+-----------+-------------+

В примерах данного раздела для получения групповых итогов использовались функции COUNT(), SUM() и AVG(). Можно применять и функции MIN() и MAX(). Будучи использованными в инструкции GROUP BY, они выводят наименьшее и наибольшее значения в группе. Сгруппируем строки таблицы mail по отправителям сообщений и будем выводить для каждой из них размер максимального сообщения и дату последнего сообщения:

mysql> SELECT srcuser, MAX(size), MAX(t) FROM mail GROUP BY srcuser;


+---------+------------+--------------------------+
| srcuser | MAX(size) | MAX(t) |
+---------+------------+--------------------------+
| barb | 98151 | 2001-05-14 14:42:21 |
| gene | 998532 | 2001-05-19 22:21:51 |
| phil | 10294 | 2001-05-17 12:49:23 |
| tricia | 2394482 | 2001-05-14 17:03:01 |
+---------+------------+--------------------------+

Можно выполнить группировку по нескольким столбцам и вывести наибольшее значение для каждой комбинации значений этих столбцов. Следующий запрос находит размер самого длинного сообщения среди сообщений каждой пары отправитель-получатель (srcuser-dstuser) из таблицы mail:

mysql> SELECT srcuser, dstuser, MAX(size) FROM mail GROUP BY srcuser, dstuser;


+---------+---------+------------+
| srcuser | dstuser | MAX(size) |
+---------+---------+------------+
| barb | barb | 98151 |
| barb | tricia | 58274 |
| gene | barb | 2291 |
| gene | gene | 23992 |
| gene | tricia | 998532 |
| phil | barb | 10294 |
| phil | phil | 1048 |
| phil | tricia | 5781 |
| tricia | gene | 194925 |
| tricia | phil | 2394482 |
+--------+-----------+------------+

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

mysql> SELECT name, MAX(miles) AS 'longest trip'
-> FROM driver_log GROUP BY name;


+-------+---------------+
| name | longest trip |
+-------+---------------+
| Ben | 152 |
| Henry | 300 |
| Suzi | 502 |
+-------+---------------+

Но что делать, если вы хотите вывести и дату совершения этой поездки? Можно ли просто добавить столбец trav_date в список вывода? К сожалению, нет:

mysql> SELECT name, trav_date, MAX(miles) AS 'longest trip'

-> FROM driver_log GROUP BY name;


+-------+---------------+--------------+
| name | trav_date | longest trip |
+-------+---------------+--------------+
| Ben | 2001-11-30 | 152 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-11-29 | 502 |
+--------+--------------+---------------+

Запрос возвращает результат, но если вы сравните его с данными таблицы (приведенными ниже), то обнаружите, что даты для имен Ben и Henry корректны, а для Suzi – нет:

+--------+-------+--------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+--------------+-------+
| 1 | Ben | 2001-11-30 | 152 | ← самая длинная поездка Ben'а
| 2 | Suzi | 2001-11-29 | 391 |
| 3 | Henry | 2001-11-29 | 300 | ← самая длинная поездка Henry
| 4 | Henry | 2001-11-27 | 96 |
| 5 | Ben | 2001-11-29 | 131 |
| 6 | Henry | 2001-11-26 | 115 |
| 7 | Suzi | 2001-12-02 | 502 | ← самая длинная поездка Suzi
| 8 | Henry | 2001-12-01 | 197 |
| 9 | Ben | 2001-12-02 | 79 |
| 10 | Henry | 2001-11-30 | 203 |
+-------+---------+--------------+-------+

В чем же причина? Почему запрос выводит неправильный результат? Дело в том, что когда вы включаете в запрос инструкцию GROUP BY, выбирать можно только значения группируемых столбцов или вычисленные для них итоговые значения. Если вы выводите какие-то дополнительные столбцы, они никак не привязаны к группируемым столбцам, и выводимые для них значения никак не задаются. (Похоже, что в только что приведенном запросе СУБД MySQL просто взяла первую дату для каждого водителя, не заботясь о том, является ли она датой самой длинной поездки.)

Общим решением проблем вывода содержимого строк, связанных с минимальным или максимальным значением, является использование соединения (см. главу 12). Если вы не хотите забегать вперед или не хотите использовать другую таблицу, то можете обратиться к описанному ранее приему MAX-CONCAT. Запрос получается не очень красивым, но выводит корректный результат:

mysql> SELECT name,
-> SUBSTRING(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),4) AS date,
-> LEFT(MAX(CONCAT(LPAD(miles,3,' '), trav_date)),3) AS 'longest trip'
-> FROM driver_log GROUP BY name;


+-------+--------------+---------------+
| name | date | longest trip |
+-------+--------------+---------------+
| Ben | 2001-11-30 | 152 |
| Henry | 2001-11-29 | 300 |
| Suzi | 2001-12-02 | 502 |
+-------+--------------+----------------+

Добавлено: 23 Июля 2018 06:32:12 Добавил: Андрей Ковальчук

Управление чувствительностью к регистру функций MIN() и MAX()

Задача
Функции MIN() и MAX() выбирают строки, учитывая их регистр, а вам бы этого не хотелось, или наоборот.

Решение
Измените чувствительность строк к регистру.

Обсуждение
Когда функции MIN() и MAX() применяются к строковым значениям, они выводят результаты, руководствуясь правилами лексического упорядочивания. Чувствительность к регистру влияет на сортировку, а значит, и на функции MIN() и MAX().

mysql> SELECT tstr, bstr FROM textblob_val;


+-----+------+
| tstr | bstr |
+-----+------+
| aaa | aaa |
| AAA | AAA |
| bbb | bbb |
| BBB | BBB |
+-----+------+

Но столбцы только выглядят одинаково, а ведут себя по-разному. Столбец bstr относится к типу BLOB и чувствителен к регистру. Столбец tstr имеет тип TEXT и нечувствителен к регистру. Поэтому функции MIN() и MAX() необязательно получат одинаковые результаты для двух столбцов:

mysql> SELECT MIN(tstr), MIN(bstr) FROM textblob_val;

+-----------+------------+
| MIN(tstr) | MIN(bstr) |
+-----------+------------+
| aaa | AAA |
+-----------+------------+

Чтобы сделать столбец tstr чувствительным к регистру, применим BINARY:

mysql> SELECT MIN(BINARY tstr) FROM textblob_val;


+---------------------+
| MIN(BINARY tstr) |
+---------------------+
| AAA |
+---------------------+

Чтобы сделать столбец bstr не чувствительным к регистру, можно преобразовать все его значения к одному регистру:

mysql> SELECT MIN(LOWER(bstr)) FROM textblob_val;


+-----------------------+
| MIN(LOWER(bstr)) |
+-----------------------+
| aaa |
+-----------------------+

К сожалению, в результате выполнения такой операции изменится и отображаемое значение. Если это важно, используйте вместо только что предложенного такой прием (он может выводить немного другой результат):

mysql> SELECT @min := MIN(LOWER(bstr)) FROM textblob_val;
mysql> SELECT bstr FROM textblob_val WHERE LOWER(bstr) = @min;


+-----+
| bst r |
+-----+
| aaa |
| AAA |
+-----+

Добавлено: 23 Июля 2018 06:30:44 Добавил: Андрей Ковальчук

Поиск значений, связанных с минимальным и максимальным значениями

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

Решение
Используйте два запроса и переменную SQL. Или «прием MAX-CONCAT». Или соединение (join).

Обсуждение
Функции MIN() и MAX() находят границы диапазона значений. Бывают ситуации, когда после нахождения минимального или максимального значения вам нужно получить и другие значения той строки, в которой оно встретилось. Например, наибольшее население штата можно получить так:

mysql> SELECT MAX(pop) FROM states;


+------------+
| MAX(pop) |
+------------+
| 29760021 |
+------------+

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

mysql> SELECT name, MAX(pop) FROM states WHERE pop = MAX(pop);
ERROR 1111 at line 1: Invalid use of group function


Практически каждый рано или поздно пробует сделать нечто подобное, но ничего не получается, поскольку агрегирующие функции, такие как MIN() и MAX(), нельзя использовать в инструкциях WHERE. В приведенном выше предложении мы пытались сначала определить, какая запись содержит макси-мальное значение для численности населения, а затем вывести название соответствующего штата. Проблема в том, что мы-то хорошо понимаем, что имели в виду, а вот для MySQL все это не имеет никакого смысла. Запрос не удается выполнить, так как MySQL использует инструкцию WHERE для того, чтобы определить, какие записи выбрать, но при этом узнаёт значение агрегирующей функции только после того, как выбраны записи, по которым определяется значение функции! То есть предложение является внутренне противоречивым. Можно было бы справиться с этим, используя подзапрос, однако MySQL будет поддерживать их только начиная с версии 4.1. Между тем, можно разбить решение задачи на два этапа: первый запрос будет извлекать максимальное значение в переменную SQL, а второй – ссылаться на эту переменную в своей инструкции WHERE:

mysql> SELECT @max := MAX(pop) FROM states;
mysql> SELECT @max AS 'highest population', name FROM states WHERE pop = @max;


+----------------------+-----------+
| highest population | name |
+----------------------+-----------+
| 29760021 | California |
+----------------------+-----------+

Этот прием работает даже тогда, когда минимальное или максимальное значение не содержится непосредственно в строке, а получается из нее. Если вы хотите узнать длину самого короткого стиха в King James Version, ее легко можно вычислить так:

mysql> SELECT MIN(LENGTH(vtext)) FROM kjv;


+-------------------------+
| MIN(LENGTH(vtext)) |
+-------------------------+
| 11 |
+-------------------------+

Если же вы хотите получить ответ на вопрос: «Что это за стих?», выполните следующий запрос:

mysql> SELECT @min := MIN(LENGTH(vtext)) FROM kjv;
mysql> SELECT bname, cnum, vnum, vtext FROM kjv WHERE LENGTH(vtext) = @min;


+--------+-------+-------+---------------+
| bname | cnum | vnum | vtext |
+--------+-------+-------+---------------+
| John | 11 | 35 | Jesus wept. |
+--------+-------+-------+---------------+

Есть и другой способ, который можно использовать для нахождения значений, связанных с минимумом или максимумом. В справочном руководстве по MySQL он называется «прием MAX-CONCAT» («MAX-CONCAT trick»).

Способ не очень изящен, но может оказаться полезным тем, кто работает с версиями MySQL, не допускающими использования переменных SQL. Технология такова: используя CONCAT(), добавляем столбец к столбцу суммирования, находим максимум получившихся значений при помощи MAX() и из-влекаем несуммируемую часть значения результата. Например, чтобы вывести название штата с наибольшей численностью населения, можно выбрать максимальное составное значение столбцов pop и name, а затем извлечь из него составляющую name. Давайте будем действовать последовательно.

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

mysql> SELECT MAX(pop) FROM states;


+------------+
| MAX(pop) |
+------------+
| 29760021 |
+------------+

Восемь символов. Такая информация необходима для того, чтобы соответствующим образом разместить составляющую названия штата в комбинированном значении население-штат, обеспечив тем самым его корректное извлечение. Составляющая названия должна начинаться с фиксированной позиции. Зная, что максимальное значение численности населения состоит из восьми символов, дополним столбец pop пробелами до восьми символов, тогда значения name всегда будут начинаться с девятой позиции. Но будьте внимательны при дополнении значений численности населения пробелами. Значения, порождаемые функцией CONCAT(), являются строками, поэтому функция MAX() при сортировке будет рассматривать значения население-штат как строки. Если выровнять значения pop по левому краю, дополнив их пробелами справа с помощью RPAD(), то составные значения будут такими:

mysql> SELECT CONCAT(RPAD(pop,8,' '),name) FROM states;


+---------------------------------------+
| CONCAT(RPAD(pop,8,' '),name) |
+---------------------------------------+
| 4040587 Alabama |
| 550043 Alaska |
| 3665228 Arizona |
| 2350725 Arkansas |
...

Эти значения будут упорядочиваться в лексическом порядке, что удобно для нахождения наибольшего строкового значения с помощью функции MAX().

Но значения pop – это числа, и хотелось бы, чтобы значения упорядочивались как числа. Чтобы лексическое упорядочивание соответствовало числовому, необходимо выровнять значения численности населения вправо, дополнив их пробелами слева при помощи LPAD():

mysql> SELECT CONCAT(LPAD(pop,8,' '),name) FROM states;


+--------------------------------------+
| CONCAT(LPAD(pop,8,' '),name) |
+--------------------------------------+
| 4040587Alabama |
| 550043Alaska |
| 3665228Arizona |
| 2350725Arkansas |
...

Теперь используем выражение CONCAT() в сочетании с MAX() для нахождения значения с наибольшей составляющей численности населения:

mysql> SELECT MAX(CONCAT(LPAD(pop,8,' '),name)) FROM states;


+---------------------------------------------+
| MAX(CONCAT(LPAD(pop,8,' '),name)) |
+---------------------------------------------+
| 29760021California |
+---------------------------------------------+

Чтобы получить конечный результат (название штата с наибольшим населением), извлеките из максимального составного значения подстроку, начинающуюся с девятого символа:

mysql> SELECT SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) FROM states;


+----------------------------------------------------------------+
| SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) |
+----------------------------------------------------------------+
| California |
+----------------------------------------------------------------+

Естественно, для хранения промежуточного результата гораздо удобнее использовать переменную SQL. Это и более эффективно, так как не приходится заниматься объединением значений столбцов для сортировки и разбиением результата на составляющие для вывода.

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

mysql> CREATE TEMPORARY TABLE t
-> SELECT MAX(pop) as maxpop FROM states;
mysql> SELECT states.* FROM states, t WHERE states.pop = t.maxpop;


+-----------+---------+--------------+------------+
| name | abbrev | statehood | pop |
+-----------+---------+--------------+------------+
| California | CA | 1850-09-09 | 29760021 |
+-----------+---------+--------------+-------------+

Добавлено: 23 Июля 2018 06:29:10 Добавил: Андрей Ковальчук

Использование ключевого слова DISTINCT для удаления дубликатов

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

Решение
Используйте ключевое слово DISTINCT для выбора уникальных значений и конструкцию COUNT(DISTINCT) – для подсчета их количества.

Обсуждение
Удаление дубликатов для получения информации о том, какие значения или строки входят в набор данных, – это суммарная операция, не требующая использования агрегирующей функции. Используем ключевое слово DISTINCT (или его синоним DISTINCTROW). DISTINCT сокращает результат запроса и часто используется в сочетании с ORDER BY для расположения значений в более удобном порядке. Например, если вы хотите узнать имена водителей из таблицы driver_log, выполните такой запрос:

mysql> SELECT DISTINCT name FROM driver_log ORDER BY name;


+-------+
| name |
+-------+
| Ben |
| Henry |
| Suzi |
+-------+

Запрос без использования ключевого слова DISTINCT выводит те же самые имена, но в менее удобной форме:

mysql> SELECT name FROM driver_log;


+-------+
| name |
+-------+
| Ben |
| Suzi |
| Henry |
| Henry |
| Ben |
| Henry |
| Suzi |
| Henry |
| Ben |
| Henry |
+-------+

Если вы хотите узнать, сколько разных водителей включено в таблицу, используйте конструкцию COUNT(DISTINCT):

mysql> SELECT COUNT(DISTINCT name) FROM driver_log;


+------------------------------+
| COUNT(DISTINCT name) |
+------------------------------+
| 3 |
+------------------------------+

Конструкция COUNT(DISTINCT) игнорирует значения NULL. Если вы хотите сосчитать и значения NULL (если они присутствуют), сделайте следующее:

COUNT(DISTINCT знач) + IF(COUNT(IF(знач IS NULL,1,NULL))=0,0,1)


Того же эффекта можно достичь при помощи таких выражений:

COUNT(DISTINCT знач) + IF(SUM(ISNULL(знач))=0,0,1)
COUNT(DISTINCT знач) + (SUM(ISNULL(знач))!=0)


Конструкция COUNT(DISTINCT) доступна, начиная с MySQL версии 3.23.2. Если вы работаете с более ранней версией, необходимо использовать какой-то обходной маневр для вычисления количества строк запроса SELECT DISTINCT.

Можно, например, выбрать уникальные значения в отдельную таблицу, а затем применить COUNT(*) для подсчета количества строк новой таблицы.

Запросы DISTINCT часто используются в сочетании с агрегирующими функциями для получения более полного описания имеющихся данных. Например, если применить COUNT(*) к таблице customer, вы узнаете количество своих клиентов, применив DISTINCT к значениям таблицы state, вы узнаете,в каких штатах живут ваши клиенты. Если же выполнить COUNT(DISTINCT) к значениям state, то будет выведено количество штатов, в которых есть ваши клиенты.

Если использовать DISTINCT для нескольких столбцов, то будут выведены уникальные комбинации значений этих столбцов, а COUNT(DISTINCT) вычислит количество таких комбинаций. Следующий запрос находит в таблице mail различные пары отправитель-получатель (srcuser-dstuser) и считает иколичество:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> ORDER BY srcuser, dstuser;


+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| barb | tricia |
| gene | barb |
| gene | gene |
| gene | tricia |
| phil | barb |
| phil | phil |
| phil | tricia |
| tricia | gene |
| tricia | phil |
+---------+---------+

mysql> SELECT COUNT(DISTINCT srcuser, dstuser) FROM mail;


+------------------------------------------+
| COUNT(DISTINCT srcuser, dstuser) |
+------------------------------------------+
| 10 |
+------------------------------------------+

Можно применять DISTINCT не только к столбцам, но и к выражениям. Чтобы вычислить количество часов дня, в которые отправлялись сообщения таблицы mail, будем учитывать неповторяющиеся значения HOUR():

mysql> SELECT COUNT(DISTINCT HOUR(t)) FROM mail;


+----------------------------------+
| COUNT(DISTINCT HOUR(t)) |
+----------------------------------+
| 12 |
+----------------------------------+

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

mysql> SELECT DISTINCT HOUR(t) FROM mail ORDER BY 1;


+----------+
| HOUR(t) |
+----------+
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 17 |
| 22 |
| 23 |
+----------+

Обратите внимание на то, что запрос не сообщает нам, сколько сообщений было отправлено в каждый из часов.

Добавлено: 23 Июля 2018 06:27:40 Добавил: Андрей Ковальчук

Суммирование при помощи функций SUM() и AVG()

Задача
Вам нужно найти сумму чисел или вычислить их среднее значение.

Решение
Используйте функцию SUM() или AVG().

Обсуждение
Функции SUM() и AVG() выводят сумму и среднее значение для набора данных. Используйте их, чтобы получить ответы на вопросы:

• Каковы общий объем почтового трафика и средний размер сообщения?

mysql> SELECT SUM(size) AS 'total traffic',
-> AVG(size) AS 'average message size'
-> FROM mail;


+-------------+---------------------------+
| total traffic | average message size |
+-------------+---------------------------+
| 3798185 | 237386.5625 |
+-------------+---------------------------+

• Сколько всего миль проехали водители из таблицы driver_log? Каким был средний дневной пробег?

mysql> SELECT SUM(miles) AS 'total miles',
-> AVG(miles) AS 'average miles/day'
-> FROM driver_log;


+------------+----------------------+
| total miles | average miles/day |
+------------+----------------------+
| 2166 | 216.6000 |
+------------+----------------------+

• Каково общее население США?

mysql> SELECT SUM(pop) FROM states;


+-------------+
| SUM(pop) |
+-------------+
| 248102973 |
+-------------+

(Значение представляет население США на 1 апреля 1990 года. Приведенные цифры отличаются от показателей переписи населения, так как таблица states не содержит данных о Вашингтоне, округ Колумбия.) Функции SUM() и AVG() являются строго числовыми, то есть не могут применяться к строкам или значениям времени. Правда, в некоторых случаях можно преобразовать нечисловые значения в числовые. Предположим, что
таблица хранит значения TIME, представляющие затраченное время:

mysql> SELECT t1 FROM time_val;


+-----------+
| t1 |
+-----------+
| 15:00:00 |
| 05:01:30 |
| 12:30:20 |
+-----------+

Чтобы вычислить общее потраченное время, сначала используем функцию TIME_TO_SEC() для преобразования значений в секунды. Результат также будет выражен в секундах, так что если вы предпочитаете формат TIME, передайте результат в SEC_TO_TIME():

mysql> SELECT SUM(TIME_TO_SEC(t1)) AS 'total seconds',
-> SEC_TO_TIME(SUM(TIME_TO_SEC(t1))) AS 'total time'
-> FROM time_val;


+----------------+------------+
| total seconds | total time |
+----------------+------------+
| 117110 | 32:31:50 |
+----------------+------------+

Добавлено: 23 Июля 2018 06:26:22 Добавил: Андрей Ковальчук

Суммирование при помощи функций MIN() и MAX()

Задача
Вам нужно определить наименьшее или наибольшее значения множества данных.

Решение
Используйте функцию MIN() для нахождения минимального значения, а функцию MAX() – для нахождения максимального значения.

Обсуждение
Нахождение наибольшего и наименьшего значений похоже на сортировку, только вместо того, чтобы выводить все упорядоченное множество, выбираем всего одно значение с одного или другого конца отсортированного списка. Такие операции выполняются для получения ответов на вопросы о наибольшем, наименьшем, самом молодом и старом, наиболее дорогом и дешевом и т. д. Одним из способов нахождения подобных значений является использование функций MIN() и MAX().

Поскольку функции MIN() и MAX() определяют экстремальные значения множества, их можно использовать для определения диапазонов:

• Какой диапазон дат представлен строками таблицы mail? Каковы размеры самого короткого и самого длинного сообщений?

mysql> SELECT
-> MIN(t) AS earliest, MAX(t) AS latest,
-> MIN(size) AS smallest, MAX(size) AS largest
-> FROM mail;


+--------------------------+--------------------------+----------+-----------+
| earliest | latest | smallest | largest |
+--------------------------+--------------------------+----------+-----------+
| 2001-05-11 10:15:08 | 2001-05-19 22:21:51 | 271 | 2394482 |
+--------------------------+--------------------------+----------+-----------+

• Какая поездка из таблицы driver_log была самой короткой, а какая самой длинной?

mysql> SELECT MIN(miles) AS shortest, MAX(miles) AS longest
-> FROM driver_log;

+----------+---------+
| shortest | longest |
+----------+---------+
| 79 | 502 |
+----------+---------+

• Каково наибольшее и наименьшее население штата США?

mysql> SELECT MIN(pop) AS 'fewest people', MAX(pop) AS 'most people'
-> FROM states;


+-----------------+---------------+
| fewest people | most people |
+-----------------+---------------+
| 453588 | 29760021 |
+-----------------+---------------+

• Название какого штата является первым в лексическом порядке, а какого – последним?

mysql> SELECT MIN(name), MAX(name) FROM states;


+-------------+---------------+
| MIN(name) | MAX(name) |
+-------------+---------------+
| Alabama | Wyoming |
+-------------+---------------+

Функции MIN() и MAX() необязательно должны применяться непосредственно к значениям столбцов. Они работают и с выражениями, и со значениями, полученными из значений столбцов. Например, чтобы определить длины самого короткого и самого длинного названий штата, выполните следующий запрос:

mysql> SELECT MIN(LENGTH(name)) AS shortest, MAX(LENGTH(name)) AS longest
-> FROM states;


+----------+---------+
| shortest | longest |
+----------+---------+
| 4 | 14 |
+----------+---------+

Добавлено: 23 Июля 2018 06:15:58 Добавил: Андрей Ковальчук

Суммирование с помощью функции COUNT()

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

Решение
Используйте функцию COUNT().

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

Если в запросе нет инструкции WHERE, то будут сосчитаны все записи таблицы, как в следующем запросе, выводящем количество строк таблицы driver_log:

mysql> SELECT COUNT(*) FROM driver_log;


+------------+
| COUNT(*) |
+------------+
| 10 |
+------------+


Если вы не знаете, сколько штатов в США, вам поможет такой запрос:

mysql> SELECT COUNT(*) FROM states;


+------------+
| COUNT(*) |
+------------+
| 50 |
+------------+

COUNT(*) без инструкции WHERE очень быстро выполняется для таблиц ISAM или MyISAM. А вот для таблиц BDB или InnoDB функцию лучше не использовать; запрос проводит полный просмотр таблиц этих типов, поэтому для больших таблиц операция может быть медленной. Если вас интересует только приблизительное количество строк таблицы, и вы работаете с версией MySQL 3.23 или выше, то можете избежать полного просмотра, используя предложение SHOW TABLE STATUS и исследуя значение Rows вывода. Если бы таблица states относилась к типу InnoDB, результат запроса выглядел бы как-то так:

mysql> SHOW TABLE STATUS FROM cookbook LIKE 'states'\G

*************************** 1. row ***************************
Name: states
Type: InnoDB
Row_format: Dynamic
Rows: 50
Avg_row_length: 327
Data_length: 16384
Max_data_length: NULL
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Create_options:
Comment: InnoDB free: 479232 kB

Чтобы сосчитать количество строк, удовлетворяющих некоторым условиям, укажите эти условия в инструкции WHERE. Условия могут быть любыми, благодаря чему COUNT() умеет отвечать на множество разных вопросов:

• Сколько раз водитель проезжал более 200 миль за день?

mysql> SELECT COUNT(*) FROM driver_log WHERE miles > 200;


+------------+
| COUNT(*) |
+------------+
| 4 |
+------------+

• Сколько дней была за рулем Suzi?

mysql> SELECT COUNT(*) FROM driver_log WHERE name = 'Suzi';


+------------+
| COUNT(*) |
+------------+
| 2 |
+------------+

• Сколько штатов входило в США в начале XX века?

mysql> SELECT COUNT(*) FROM states WHERE statehood < '1900-01-01';


+------------+
| COUNT(*) |
+------------+
| 45 |
+------------+

• Сколько из этих штатов вступило в Союз в XIX веке?

mysql> SELECT COUNT(*) FROM states
-> WHERE statehood BETWEEN '1800-01-01' AND '1899-12-31';


+------------+
| COUNT(*) |
+------------+
| 29 |
+------------+

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

SELECT COUNT(*), COUNT(mycol) FROM mytbl;


Тот факт, что COUNT(выражение) не учитывает значения NULL, можно использовать при получении нескольких счетчиков для одного набора данных. Чтобы в одном запросе вычислить количество поездок в выходные дни (субботу и воскресенье) в таблице driver_log, сделайте следующее:

mysql> SELECT
-> COUNT(IF(DAYOFWEEK(trav_date)=7,1,NULL)) AS 'Saturday trips',
-> COUNT(IF(DAYOFWEEK(trav_date)=1,1,NULL)) AS 'Sunday trips'
-> FROM driver_log;


+-----------------+----------------+
| Saturday trips | Sunday trips |
+-----------------+----------------+
| 1 | 2 |
+-----------------+----------------+

Чтобы отдельно сосчитать количество поездок в рабочие и выходные дни, выполните такой запрос:

mysql> SELECT
-> COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),1,NULL)) AS 'weekend trips',
-> COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),NULL,1)) AS 'weekday trips'
-> FROM driver_log;


+-----------------+-----------------+
| weekend trips | weekday trips |
+-----------------+-----------------+
| 3 | 7 |
+-----------------+-----------------+

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

Добавлено: 23 Июля 2018 06:15:00 Добавил: Андрей Ковальчук

Сортировка значений ENUM

Задача
Значения ENUM не сортируются так, как другие строковые столбцы.

Решение
Поймите, как они работают, и используйте их возможности себе во благо.

Обсуждение
ENUM считается строковым типом, но значения ENUM обладают особым свойством: они хранятся в числовом формате, причем числовые значения отсортированы именно в том порядке, в каком они перечислены в определении таблицы. Эти числовые значения определяют порядок сортировки вывода перечислимых типов, что может быть полезным. Предположим, что у вас есть таблица weekday, содержащая перечислимый столбец day, который включает в себя названия дней недели:

CREATE TABLE weekday
(day ENUM('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
);


Внутренний формат хранения значений таков: MySQL сопоставляет перечисленным значениям от Sunday до Saturday числовые значения от 1 до 7. Чтобы убедиться в этом, создайте таблицу, используя только что рассмотренное определение, и вставьте в нее запись для каждого дня недели. Чтобы увидеть эффект сортировки, сделаем порядок ввода отличным от порядка значений в списке, вводя дни произвольным образом:

mysql> INSERT INTO weekday (day) VALUES('Monday'),('Friday'),
-> ('Tuesday'), ('Sunday'), ('Thursday'), ('Saturday'), ('Wednesday');


Затем выведем значения и как строки, и как внутренние числовые значения (чтобы получить последние, добавьте к строкам 0, инициируя тем самым преобразование строки в число):

mysql> SELECT day, day+0 FROM weekday;


+--------------+---------+
| day | day+0 |
+--------------+---------+
| Monday | 2 |
| Friday | 6 |
| Tuesday | 3 |
| Sunday | 1 |
| Thursday | 5 |
| Saturday | 7 |
| Wednesday | 4 |
+--------------+----------+

Поскольку запрос не содержит инструкцию ORDER BY, записи выводятся неупорядоченными. Если добавить инструкцию ORDER BY day, будет очевидно, что MySQL выполняет сортировку по внутренним числовым значениям:

mysql> SELECT day, day+0 FROM weekday ORDER BY day;


+-------------+---------+
| day | day+0 |
+-------------+---------+
| Sunday | 1 |
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
+--------------+--------+

Что тогда делать, если требуется вывести значения ENUM в лексическом порядке? Используйте функцию CONCAT(), чтобы заставить MySQL воспринимать их как строки. Обычно CONCAT() принимает несколько аргументов и объединяет их в одну строку. Но функцию можно использовать и с одним аргументом, что удобно в тех случаях, когда единственное, что вам нужно от CONCAT(), это ее способность выводить строковый результат:

mysql> SELECT day, day+0 FROM weekday ORDER BY CONCAT(day);


+--------------+--------+
| day | day+0 |
+--------------+--------+
| Friday | 6 |
| Monday | 2 |
| Saturday | 7 |
| Sunday | 1 |
| Thursday | 5 |
| Tuesday | 3 |
| Wednesday | 4 |
+--------------+---------+

Если вы всегда (или почти всегда) сортируете неперечислимый столбец в определенном нелексическом порядке, подумайте о том, чтобы изменить его тип на ENUM, перечислив его значения в соответствующем порядке. Создадим таблицу color, содержащую строковый столбец, и заполним ее тестовыми данными:

mysql> CREATE TABLE color (name CHAR(10));
mysql> INSERT INTO color (name) VALUES ('blue'),('green'),
-> ('indigo'),('orange'),('red'),('violet'),('yellow');


Если сейчас выполнить сортировку по столбцу name, ее порядок будет лексическим, так как столбец содержит значения типа CHAR:

mysql> SELECT name FROM color ORDER BY name;

+---------+
| name |
+---------+
| blue |
| green |
| indigo |
| orange |
| red |
| violet |
| yellow |
+---------+

Теперь предположим, что вы хотите упорядочить столбец по цветам в том порядке, в котором они расположены в радуге. (Этот порядок задается именем «Roy G. Biv», последовательность букв которого совпадает с первыми буквами цветов радуги.) В качестве одного из способов можно предложить использовать функцию FIELD():

mysql> SELECT name FROM color
-> ORDER BY
-> FIELD(name,'red','orange','yellow','green','blue','indigo','violet');


+---------+
| name |
+---------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+---------+

Чтобы сделать то же самое, не прибегая к помощи FIELD(), используйте предложение ALTER TABLE для преобразования столбца name в тип ENUM, в котором цвета перечислены в нужном порядке:

mysql> ALTER TABLE color
-> MODIFY name
-> ENUM('red','orange','yellow','green','blue','indigo','violet');


После преобразования таблицы сортировка по столбцу name приводит к «радужному» упорядочиванию без каких бы то ни было дополнительных действий:

mysql> SELECT name FROM color ORDER BY name;


+--------+
| name |
+--------+
| red |
| orange |
| yellow |
| green |
| blue |
| indigo |
| violet |
+--------+

Добавлено: 23 Июля 2018 06:13:17 Добавил: Андрей Ковальчук