Предположим, нам нужно узнать количество таблиц, вью и триггеров у пользователей БД SYS, SYSTEM и SCOTT. Первым приходит в голову такое решение:
SQL> SELECT owner, object_type, count(*)
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER')
AND owner IN ('SYS', 'SYSTEM', 'SCOTT')
GROUP BY owner, object_type
ORDER BY owner, object_type;
OWNER OBJECT_TYPE COUNT(*)
-------------------- -------------------- -----------
SCOTT TABLE 4
SYS TABLE 998
SYS TRIGGER 10
SYS VIEW 3865
SYSTEM TABLE 157
SYSTEM TRIGGER 2
SYSTEM VIEW 12
7 rows selected
Что ж, мы получили ответ на наш вопрос. Но результат не нагляден. Куда нагляднее был бы такой ответ:
OBJECT_TYPE SYS SYSTEM SCOTT
-------------------- ---------- ---------- ----------
TRIGGER 10 2 0
TABLE 998 157 4
VIEW 3865 12 0
Это сводная таблица, знакомая многим по работе в Excel. Достаточно беглого взгляда не нее, чтобы получить ответы на вопросы, которые мы еще не успели себе задать. Например, о том, у какого пользователя из трех больше всего вью, у какого - вовсе нет вью, и объекты какого типа есть у всех трех пользователей. Сводная таблица делает такого рода факты очевидными.
Как видно из примера, в сводной таблице не только столбцы, но и строки имеют (логические) имена. Эти имена не что иное, как значения атрибутов исследуемой сущности: в нашем примере имена столбцов есть значения all_objects.owner, а имена строк - значения all_objects.object_type. А на пересечении строк и столбцов находятся результаты агрегирования данных по соответствующим значениям.
Как же представить данные в виде сводной таблицы при помощи SELECT?
Есть несколько способов:
- Используя подзапросы
- Используя
DECODEилиCASE - Используя опцию
PIVOT
Первый способ использует в качестве подзапросов SELECT'ы с группировкой:
SQL> SELECT
object_type, systable.cnt "SYS", systemtable.cnt "SYSTEM", scotttable.cnt "SCOTT"
FROM
(SELECT object_type, COUNT(*) cnt from all_objects WHERE owner = 'SYS' GROUP BY object_type) systable
FULL OUTER JOIN
(SELECT object_type, COUNT(*) cnt from all_objects WHERE owner = 'SYSTEM' GROUP BY object_type) systemtable USING (object_type)
FULL OUTER JOIN
(SELECT object_type, COUNT(*) cnt from all_objects WHERE owner = 'SCOTT' GROUP BY object_type) scotttable USING (object_type)
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER');
OBJECT_TYPE SYS SYSTEM SCOTT
-------------------- ---------- ---------- ----------
TRIGGER 10 2
TABLE 998 157 4
VIEW 3865 12
Это громоздкий и нерациональный способ. Приведен здесь в качестве экзотики.
Второй способ использует DECODE (или CASE) для формирования значений вычисляемых столбцов. Вариант с DECODE:
SQL> SELECT
object_type,
COUNT(DECODE(owner, 'SYS', 1, NULL)) "SYS",
COUNT(DECODE(owner, 'SYSTEM', 1, NULL)) "SYSTEM",
COUNT(DECODE(owner, 'SCOTT', 1, NULL)) "SCOTT"
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER')
GROUP BY object_type;
OBJECT_TYPE SYS SYSTEM SCOTT
-------------------- ---------- ---------- ----------
TRIGGER 10 2 0
TABLE 998 157 4
VIEW 3865 12 0
Следующий вариант с CASE также демонстрирует возможность поменять местами строки и столбцы сводной таблицы:
SQL> SELECT
owner,
COUNT(CASE object_type WHEN 'TABLE' THEN 1 ELSE NULL END) "TABLE",
COUNT(CASE object_type WHEN 'VIEW' THEN 1 ELSE NULL END) "VIEW",
COUNT(CASE object_type WHEN 'TRIGGER' THEN 1 ELSE NULL END) "TRIGGER"
FROM all_objects
WHERE owner IN ('SYS', 'SYSTEM', 'SCOTT')
GROUP BY owner;
OWNER TABLE VIEW TRIGGER
-------------------- ---------- ---------- ----------
SYSTEM 157 12 2
SCOTT 4 0 0
SYS 998 3865 10
И, наконец, третий вариант использует опцию PIVOT команды SELECT, доступную в БД Oracle 11g.
SQL> SELECT *
FROM (
SELECT owner, object_type
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER'))
PIVOT (COUNT(*) FOR owner IN ('SYS', 'SYSTEM', 'SCOTT'));
OBJECT_TYPE 'SYS' 'SYSTEM' 'SCOTT'
-------------------- ---------- ---------- ----------
TRIGGER 10 2 0
TABLE 998 157 4
VIEW 3865 12 0
Посмотрим внимательно, что делает последняя команда. Подзапрос формирует выборку интересующих нас объектов БД, на которой будет выполнено агрегирование. Подзапрос также задает столбцы, значения которых станут именами строк и столбцов сводной таблицы. Опция PIVOT задает агрегатную функцию, COUNT(*), и условие ее применения, FOR owner IN ('SYS', 'SYSTEM', 'SCOTT'), которое определяет, сколько и каких столбцов будет в сводной таблице.
Заметим, что сгенерированные столбцы получили имена в кавычках. Чтобы избежать этого, явно укажем имена для столбцов:
SQL> SELECT *
FROM (
SELECT owner, object_type
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER'))
PIVOT (COUNT(*) FOR owner IN ('SYS' AS sys, 'SYSTEM' AS system, 'SCOTT' AS scott));
OBJECT_TYPE SYS SYSTEM SCOTT
-------------------- ---------- ---------- ----------
TRIGGER 10 2 0
TABLE 998 157 4
VIEW 3865 12 0
Теперь усложним первоначальную задачу, потребовав, чтобы сводная таблица отображала данные по годам: в каком году сколько объектов БД каждого типа было создано пользователями. На языке анализа данных, это операция детализации (drill down). Итак, до сих пор мы видели данные в разрезе типов объектов и владельцев, а теперь копнем глубже:
SQL> SELECT *
FROM (
SELECT owner, object_type, to_char(created, 'yyyy') yyyy
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER'))
PIVOT (COUNT(*) FOR owner IN ('SYS', 'SYSTEM', 'SCOTT'))
ORDER BY 1,2;
OBJECT_TYPE YYYY 'SYS' 'SYSTEM' 'SCOTT'
-------------------- ---- ---------- ---------- ----------
TABLE 2010 974 157 4
TABLE 2011 3 0 0
TABLE 2012 20 0 0
TABLE 2013 1 0 0
TABLE 2014 0 0 0
TRIGGER 2010 8 2 0
TRIGGER 2011 1 0 0
TRIGGER 2012 1 0 0
TRIGGER 2013 0 0 0
TRIGGER 2014 0 0 0
VIEW 2010 3803 12 0
VIEW 2011 54 0 0
VIEW 2012 5 0 0
VIEW 2013 3 0 0
VIEW 2014 0 0 0
15 rows selected
Операция, обратная детализации, называется свёртка (roll up). Для выполнения свёртки по годам в нашем случае достаточно убрать столбец yyyy из подзапроса, то есть, вернуться к предыдущему запросу, не делающему разбиение по годам. Дальнейшая свёртка возможна по типам объектов, что даст нам общее количество таблиц, вью и триггеров, принадлежащих владельцам:
SQL> SELECT *
FROM (
SELECT owner
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER'))
PIVOT (COUNT(*) FOR owner IN ('SYS', 'SYSTEM', 'SCOTT'));
'SYS' 'SYSTEM' 'SCOTT'
---------- ---------- ----------
4873 171 4
Чтобы получить свёртку по владельцам, поменяем местами строки и столбцы сводной таблицы:
SQL> SELECT *
FROM (
SELECT object_type
FROM all_objects
WHERE owner IN ('SYS', 'SYSTEM', 'SCOTT'))
PIVOT (COUNT(*) FOR object_type IN ('TABLE', 'VIEW', 'TRIGGER'));
'TABLE' 'VIEW' 'TRIGGER'
---------- ---------- ----------
1159 3877 12
Два последние примера - вырожденные случаи сводных таблиц, когда в них остается по одной строке.
Помимо операции PIVOT, в Oracle 11g имеется также обратная ей операция UNPIVOT, способная трансформировать сводную таблицу в традиционное реляционное представление:
SQL> WITH pivottable AS (
SELECT *
FROM (
SELECT owner, object_type
FROM all_objects
WHERE object_type IN ('TABLE', 'VIEW', 'TRIGGER'))
PIVOT (COUNT(*) FOR owner IN ('SYS' AS SYS, 'SYSTEM' AS SYSTEM, 'SCOTT' AS scott)))
SELECT *
FROM pivottable
UNPIVOT (cnt FOR owner IN (SYS, SYSTEM, scott));
OBJECT_TYPE OWNER CNT
-------------------- ------------------------ ----------
TRIGGER SYS 10
TRIGGER SYSTEM 2
TRIGGER SCOTT 0
TABLE SYS 998
TABLE SYSTEM 157
TABLE SCOTT 4
VIEW SYS 3865
VIEW SYSTEM 12
VIEW SCOTT 0
9 rows selected
Это практически тот же результат, что мы получили в начале статьи.
Лучше разобраться с возможностями PIVOT и UNPIVOT мне помогла хорошая статья pivot and unpivot queries in 11g by Adrian Billington.
Комментариев нет:
Отправить комментарий