вторник, 28 января 2014 г.

Сводные таблицы в Oracle 11g

Предположим, нам нужно узнать количество таблиц, вью и триггеров у пользователей БД 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?

Есть несколько способов:

  1. Используя подзапросы
  2. Используя DECODE или CASE
  3. Используя опцию 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.

Комментариев нет:

Отправить комментарий