В этой статье я собрал запросы, иллюстрирующие типичное использование агрегатных функций. Я начинаю с рассмотрения общих агрегатов (totals), когда агрегатные функции применяются ко всей выборке. Затем рассматриваются частные агрегаты, или субагрегаты (subtotals), получаемые при помощи GROUP BY, и совместное использование общих и частных агрегатов в запросах. Приводятся примеры запросов для получения доли или процента частных агрегатов в общих. В заключение, даны примеры использования HAVING.
Итак, начнем. Простейший случай - применение агрегатной функции ко всей выборке, в результате чего мы получаем общее (total) агрегатное значение:
SQL> -- сколько всего пользователей в БД?
SQL> SELECT COUNT(*) FROM all_users;
COUNT(*)
----------
14
SQL> -- сколько объектов БД доступно пользователю?
SQL> SELECT COUNT(ALL *) FROM all_objects;
COUNT(ALL*)
-----------
20850
Здесь COUNT(ALL *) равнозначно COUNT(*).
SQL> -- объекты скольких различных типов доступны пользователю?
SQL> SELECT COUNT(DISTINCT object_type) FROM all_objects;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
34
Чтобы сравнить агрегатные данные по двум конкретным пользователям, воспользуемся UNION ALL:
SQL> SELECT 'SYS', COUNT(*), COUNT(UNIQUE object_type)
FROM all_objects WHERE owner='SYS'
UNION ALL
SELECT 'SYSTEM', COUNT(*), COUNT(UNIQUE object_type)
FROM all_objects WHERE owner='SYSTEM'
;
'SYS' COUNT(*) COUNT(UNIQUEOBJECT_TYPE)
------ ---------- ------------------------
SYS 8984 32
SYSTEM 502 12
Выражения COUNT(DISTINCT object_type) и COUNT(UNIQUE object_type) равнозначны.
Что, если нас интересует подсчет только определенных значений? Например, сколько таблиц и вью каждого владельца доступно текущему пользователю? В этом случае нам поможет вложение скалярной функции DECODE в агрегатную:
SQL> SELECT COUNT(DECODE(object_type, 'TABLE', 1, 'VIEW', 1, NULL)) FROM all_objects;
COUNT(DECODE(OBJECT_TYPE,'TABL
------------------------------
6496
SQL> -- доля таблиц и вью в общем количестве доступных объектов:
SQL> SELECT COUNT(DECODE(object_type, 'TABLE', 1, 'VIEW', 1, NULL))/COUNT(*) FROM all_objects;
COUNT(DECODE(OBJECT_TYPE,'TABL
------------------------------
0.311558752997602
SQL> -- пользователи-владельцы более 5000 объектов
SQL> SELECT username
FROM all_users au
WHERE 5000 < (SELECT count(*) FROM dba_objects WHERE owner=au.username)
ORDER BY 1;
USERNAME
------------------------------
SYS
Перейдем к частным агрегатам, или субагрегатам (subtotals). Использование агрегатной функции с GROUP BY позволяет получить агрегатные данные в выбранном разрезе:
SQL> -- сколько объектов каждого типа доступно пользователю?
SQL> SELECT object_type, COUNT(*)
FROM all_objects
GROUP BY object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
INDEX PARTITION 126
TABLE SUBPARTITION 32
CONSUMER GROUP 2
SEQUENCE 157
...
INDEXTYPE 8
CLUSTER 10
TYPE 1608
JOB 14
EVALUATION CONTEXT 9
34 rows selected
SQL> -- сколько разных типов объектов у каждого пользователя?
SQL> SELECT owner, COUNT(*), COUNT(DISTINCT object_type)
FROM all_objects
GROUP BY owner;
OWNER COUNT(*) COUNT(DISTINCTOBJECT_TYPE)
------------------------------ ---------- --------------------------
APEX_040000 2770 11
APEX_040200 3005 12
AY 22 7
CTXSYS 380 13
FLOWS_FILES 11 4
HR 34 6
MDSYS 659 14
OUTLN 8 3
PUBLIC 4013 1
SYS 8984 32
SYSTEM 502 12
XDB 462 15
12 rows selected
Поскольку запросы с GROUP BY возвращают табличный результат, то такой результат можно агрегировать еще раз:
SQL> -- среднее количество доступных объектов каждого владельца
SQL> SELECT AVG(cnt)
FROM (
SELECT object_type, COUNT(*) cnt
FROM all_objects
GROUP BY object_type);
AVG(CNT)
-------------
613.235294117
И даже так:
SQL> SELECT AVG(COUNT(*))
FROM all_objects
GROUP BY object_type;
AVG(COUNT(*))
-------------
613.235294117
Здесь вложенная агрегатная функция COUNT(*) работает с GROUP BY, чтобы произвести частные агрегатные значения в разрезе object_type, а внешняя AVG() - агрегирует частные значения в общее. Вложить агрегатные функции друг в друга глубже невозможно.
Это ограничение, конечно, не распространяется на вложение подзапросов с агрегатами:
SQL> -- общее количество доступных объектов
SQL> SELECT SUM(s)
FROM (
-- кол-во объектов в разрезе владельца
SELECT owner, SUM(cnt) s
FROM (
-- кол-во объектов в разрезе владельца и типа
SELECT owner, object_type, COUNT(*) cnt
FROM all_objects
GROUP BY owner, object_type)
GROUP BY owner);
SUM(S)
----------
20850
Используя субагрегирование с GROUP BY, получим доли таблиц в объектах каждого владельца:
SQL> SELECT owner, COUNT(DECODE(object_type,'TABLE', 1, NULL))/COUNT(*) ratio
FROM all_objects
GROUP BY owner;
OWNER RATIO
------------------------------ ----------
APEX_040200 0.15041597
MDSYS 0.11532625
PUBLIC 0
OUTLN 0.375
CTXSYS 0.12894736
HR 0.20588235
FLOWS_FILES 0.09090909
SYSTEM 0.30677290
APEX_040000 0.15379061
XDB 0.17099567
SYS 0.10574354
AY 0.5
12 rows selected
Что, если нужно получить долю объектов каждого владельца в общем количестве объектов? В таком случае, помимо субагрегата в разрезе владельцев, нам нужен общий агрегат (по всем строкам):
SQL> SELECT owner, COUNT(*)/(SELECT COUNT(*) FROM all_objects) ratio
FROM all_objects
GROUP BY owner;
OWNER RATIO
------------------------------ ----------
APEX_040200 0.14412470
MDSYS 0.03160671
PUBLIC 0.19247002
OUTLN 0.00038369
CTXSYS 0.01822541
HR 0.00163069
FLOWS_FILES 0.00052757
SYSTEM 0.02407673
APEX_040000 0.13285371
XDB 0.02215827
SYS 0.43088729
AY 0.00105515
12 rows selected
Более эффективный и удобочитаемый вариант, представляющий одновременно общее и частные агрегатные значения, а также долю частных агрегатов в общем:
SQL> SELECT owner, cnt.total, COUNT(*) subtotal, ROUND(COUNT(*)/cnt.total, 4) ratio
FROM all_objects,
(SELECT COUNT(*) total FROM all_objects) cnt
GROUP BY owner, cnt.total
ORDER BY 2 DESC;
OWNER TOTAL SUBTOTAL RATIO
------------------------------ ---------- ---------- ----------
APEX_040000 20850 2770 0.1329
APEX_040200 20850 3005 0.1441
AY 20850 22 0.0011
CTXSYS 20850 380 0.0182
FLOWS_FILES 20850 11 0.0005
HR 20850 34 0.0016
MDSYS 20850 659 0.0316
OUTLN 20850 8 0.0004
PUBLIC 20850 4013 0.1925
SYS 20850 8984 0.4309
SYSTEM 20850 502 0.0241
XDB 20850 462 0.0222
12 rows selected
И еще вариация на ту же тему:
SQL> WITH
t AS (
SELECT COUNT(*) total
FROM all_objects),
s AS (
SELECT object_type, COUNT(*) subtotal
FROM all_objects
GROUP BY object_type)
SELECT
s.object_type, ROUND(s.subtotal/t.total*100, 2) "Percent"
FROM
t CROSS JOIN s
ORDER BY
2 DESC;
OBJECT_TYPE Percent
------------------- ----------
VIEW 20.57
SYNONYM 19.66
INDEX 19.13
TABLE 10.59
TYPE 7.71
...
SCHEDULE 0.01
DESTINATION 0.01
CONSUMER GROUP 0.01
RULE 0
EDITION 0
34 rows selected
Чтобы ограничить результат агрегирующего запроса только строками, которые нас интересуют, в нашем распоряжении имеется опция HAVING:
SQL> -- имеются ли в БД 5 или более пользователей, созданных в один день?
SQL> SELECT TRUNC(created), COUNT(*)
FROM all_users
GROUP BY TRUNC(created)
HAVING COUNT(*) > 5;
TRUNC(CREATED) COUNT(*)
-------------- ----------
27.08.2011 12
SQL> -- что это за пользователи?
SQL> SELECT *
FROM all_users
WHERE TRUNC(created) IN (
SELECT TRUNC(created)
FROM all_users
GROUP BY TRUNC(created)
HAVING COUNT(*) > 5)
ORDER BY TRUNC(created);
USERNAME USER_ID CREATED
------------------------------ ---------- -----------
SYS 0 27.08.2011
MDSYS 42 27.08.2011
ANONYMOUS 35 27.08.2011
XDB 34 27.08.2011
HR 43 27.08.2011
APEX_040000 47 27.08.2011
APEX_PUBLIC_USER 45 27.08.2011
FLOWS_FILES 44 27.08.2011
XS$NULL 2147483638 27.08.2011
SYSTEM 5 27.08.2011
CTXSYS 32 27.08.2011
OUTLN 9 27.08.2011
12 rows selected
Как известно, именно HAVING помогает отыскать строки с дублирующимися значениями столбцов, или сочетаниями значений. Так, проверить на дублирование сочетание значений столбцов A, B и C таблицы T можно следующим запросом:
SELECT a, b, c, COUNT(*)
FROM t
GROUP BY a, b, c
HAVING COUNT(*) > 1;
Если нам необходимы только строки с агрегатными значениями, большими (или меньшими), чем другое агрегатное значение, на помощь вновь приходит HAVING и подзапрос с агрегатной функцией:
SQL> -- владельцы объектов, у которых кол-во объектов больше среднего
SQL> SELECT owner, COUNT(*)
FROM dba_objects
GROUP BY owner
HAVING COUNT(*) > (SELECT AVG(COUNT(*)) FROM dba_objects GROUP BY owner);
OWNER COUNT(*)
------------------------------ ----------
APEX_040200 3203
PUBLIC 4014
APEX_040000 2955
SYS 9202
Для получения более широкой картины работы с агрегатами в СУБД Oracle см. мои статьи ROLLUP, CUBE и GROUPING SETS в Oracle 11gR2, Пользовательские агрегатные функции и Сводные таблицы в Oracle 11g.
Комментариев нет:
Отправить комментарий