Работа с запросами Approximate Top-N (APPROX_RANK, APPROX_SUM, APPROX_COUNT) в Oracle 18c

Работа с запросами Approximate Top-N в Oracle 18cФункции APPROX_RANK, APPROX_SUM и APPROX_COUNT были введены в Oracle 18c, чтобы обеспечить приблизительную обработку Top-N запросов (Approximate Top-N Query Processing). Это расширяет приблизительную обработку запросов, которая была введена в предыдущих двух выпусках (Oracle 12cR1 и 12c R2).



 

Подготовка

Примеры в этой статье требуют следующей таблицы. Таблица заполнена случайными данными, поэтому ваши результаты могут немного отличаться от представленных здесь.


DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT level AS id,
       TRUNC(DBMS_RANDOM.value(1, 5))*10 department,
       UPPER(TO_CHAR(TO_DATE(TRUNC(DBMS_RANDOM.value(1, 6)),'j'), 'jsp')) AS record_type,
       ROUND(DBMS_RANDOM.value(1,51),2) AS record_value
FROM   dual
CONNECT BY level <= 100000;

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


COLUMN record_type FORMAT A12

SELECT department,
       record_type,
       COUNT(*) AS record_count,
       SUM(record_value) AS sum_record_val
FROM   t1
GROUP BY department, record_type
ORDER BY department, record_type;

DEPARTMENT RECORD_TYPE  RECORD_COUNT SUM_RECORD_VAL
---------- ------------ ------------ --------------
        10 FIVE                 5012       130044.4
        10 FOUR                 5042      131431.59
        10 ONE                  4912      128534.48
        10 THREE                4922      126185.08
        10 TWO                  5160      133586.67
        20 FIVE                 4816      124278.87
        20 FOUR                 4983      130158.06
        20 ONE                  4961      128156.44
        20 THREE                5049      131219.07
        20 TWO                  5018       129587.6
        30 FIVE                 5059      131168.33
        30 FOUR                 4969      128337.92
        30 ONE                  5042      131607.88
        30 THREE                5115      131455.11
        30 TWO                  4918      127673.23
        40 FIVE                 4965      128567.84
        40 FOUR                 5007      128968.23
        40 ONE                  5017      130735.96
        40 THREE                4944      130084.71
        40 TWO                  5089      131310.33

SQL>

 

Вступление

В этом разделе я буду показывать один и тот же запрос, выделяя различные части выражения, чтобы проиллюстрировать сделанный акцент. Не важно, что на самом деле делает запрос на данный момент. Просто сосредоточьтесь на синтаксисе. Все примеры основаны на функции APPROX_SUM, но точки применяются в равной степени к функции APPROX_COUNT.

Приблизительная обработка запросов верхнего уровня (approximate top-n query processing) в настоящее время сосредоточена на сценариях, в которых вы оцениваете группы данных на основе SUM значений или COUNT из числа строк в группе, следовательно, функции APPROX_SUM и APPROX_COUNT. Эти функции могут использоваться только в сочетании с функцией APPROX_RANK, которая должна присутствовать в предложении HAVING запроса, но также может отображаться в списке SELECT, если вы хотите отобразить ранг данных.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

Такая же приближенная функция, используемая в списке SELECT, должна также отображаться в предложении ORDER BY функции APPROX_RANK.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

Предложение ORDER BY является обязательным и всегда должно включать ключевое слово DESC, поэтому его нельзя использовать для выполнения bottom-n запросов, таких как большинство других методов.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

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


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

Предложение HAVING ограничивает ранги, возвращаемые запросом. Это используется для top-n запросов.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

Функции APPROX_SUM и APPROX_COUNT могут принимать второй параметр «MAX_ERROR», который заставляет их отображать максимальную ошибку между фактической и приблизительной суммой или счетчиком соответственно. Я не видел в возврате этой функции ничего, кроме «0» в наборах данных, которые я пробовал.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_SUM(record_value, 'MAX_ERROR') AS approx_sum_val_me,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 5;

В следующих разделах приведены некоторые примеры использования этих функций.

 

Функции APPROX_SUM и APPROX_RANK

В этом разделе мы покажем несколько примеров использования функций APPROX_SUM и APPROX_RANK.

В следующем примере отображаются top-2 типов записей (record_type), основанные на приблизительной сумме значений для каждого типа записи во всех отделах (department).


SELECT record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_SUM(record_value, 'MAX_ERROR') AS approx_sum_val_me,
       APPROX_RANK(ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY record_type
HAVING APPROX_RANK(ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 4;

RECORD_TYPE  APPROX_SUM_VAL APPROX_SUM_VAL_ME APPROX_RANK_VAL
------------ -------------- ----------------- ---------------
ONE               519034.76                 0               2
TWO               522157.83                 0               1

SQL>

В следующем примере отображаются верхние 2 (top-2 ) типа записей (record_type) в каждом отделе (department) на основе приблизительной суммы значений для каждого типа записи для каждого отдела.


SELECT department,
       record_type,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_SUM(record_value, 'MAX_ERROR') AS approx_sum_val_me,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_SUM(record_value) DESC) <= 2
ORDER BY 1, 5;

DEPARTMENT RECORD_TYPE  APPROX_SUM_VAL APPROX_SUM_VAL_ME APPROX_RANK_VAL
---------- ------------ -------------- ----------------- ---------------
        10 TWO                  133631                 0               1
        10 FOUR                 131452                 0               2
        20 THREE                131247                 0               1
        20 FOUR                 130162                 0               2
        30 ONE                  131646                 0               1
        30 THREE                131482                 0               2
        40 TWO                  131315                 0               1
        40 ONE                  130750                 0               2

SQL>

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


SELECT department,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_SUM(record_value, 'MAX_ERROR') AS approx_sum_val_me,
       APPROX_RANK(ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY department
HAVING APPROX_RANK(ORDER BY APPROX_SUM(record_value) DESC) <= 1
ORDER BY 1, 4;

DEPARTMENT APPROX_SUM_VAL APPROX_SUM_VAL_ME APPROX_RANK_VAL
---------- -------------- ----------------- ---------------
        30      650242.47                 0               1

SQL>

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


SELECT record_type,
       department,
       APPROX_SUM(record_value) AS approx_sum_val,
       APPROX_SUM(record_value, 'MAX_ERROR') AS approx_sum_val_me,
       APPROX_RANK(PARTITION BY record_type ORDER BY APPROX_SUM(record_value) DESC) AS approx_rank_val
FROM   t1
GROUP BY record_type, department
HAVING APPROX_RANK(PARTITION BY record_type ORDER BY APPROX_SUM(record_value) DESC) <= 1
ORDER BY 1, 5;

RECORD_TYPE  DEPARTMENT APPROX_SUM_VAL APPROX_SUM_VAL_ME APPROX_RANK_VAL
------------ ---------- -------------- ----------------- ---------------
FIVE                 30         131171                 0               1
FOUR                 10         131452                 0               1
ONE                  30         131646                 0               1
THREE                30         131482                 0               1
TWO                  10         133631                 0               1

SQL>

 

APPROX_COUNT and APPROX_RANK

В этом разделе мы покажем несколько примеров использования функций APPROX_COUNT и APPROX_RANK.

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


SELECT record_type,
       APPROX_COUNT(*) AS approx_count_val,
       APPROX_COUNT(*, 'MAX_ERROR') AS approx_count_val_me,
       APPROX_RANK(ORDER BY APPROX_COUNT(*) DESC) AS approx_rank_val
FROM   t1
GROUP BY record_type
HAVING APPROX_RANK(ORDER BY APPROX_COUNT(*) DESC) <= 2
ORDER BY 1, 4;

RECORD_TYPE  APPROX_COUNT_VAL APPROX_COUNT_VAL_ME APPROX_RANK_VAL
------------ ---------------- ------------------- ---------------
THREE                   20030                   0               2
TWO                     20185                   0               1

SQL>

В следующем примере отображаются верхние 2 (top-2) типа записей в каждом отделе на основе приблизительного количества строк в типе записи в каждом отделе.


SELECT department,
       record_type,
       APPROX_COUNT(*) AS approx_count_val,
       APPROX_COUNT(*, 'MAX_ERROR') AS approx_count_val_me,
       APPROX_RANK(PARTITION BY department ORDER BY APPROX_COUNT(*) DESC) AS approx_rank_val
FROM   t1
GROUP BY department, record_type
HAVING APPROX_RANK(PARTITION BY department ORDER BY APPROX_COUNT(*) DESC) <= 2
ORDER BY 1, 5;

DEPARTMENT RECORD_TYPE  APPROX_COUNT_VAL APPROX_COUNT_VAL_ME APPROX_RANK_VAL
---------- ------------ ---------------- ------------------- ---------------
        10 TWO                      5160                   0               1
        10 FOUR                     5042                   0               2
        20 THREE                    5049                   0               1
        20 TWO                      5018                   0               2
        30 THREE                    5115                   0               1
        30 FIVE                     5059                   0               2
        40 TWO                      5089                   0               1
        40 ONE                      5017                   0               2

SQL>

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


SELECT department,
       APPROX_COUNT(*) AS approx_count_val,
       APPROX_COUNT(*, 'MAX_ERROR') AS approx_count_val_me,
       APPROX_RANK(ORDER BY APPROX_COUNT(*) DESC) AS approx_rank_val
FROM   t1
GROUP BY department
HAVING APPROX_RANK(ORDER BY APPROX_COUNT(*) DESC) <= 1
ORDER BY 1, 4;

DEPARTMENT APPROX_COUNT_VAL APPROX_COUNT_VAL_ME APPROX_RANK_VAL
---------- ---------------- ------------------- ---------------
        30            25103                   0               1

SQL>

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


SELECT record_type,
       department,
       APPROX_COUNT(*) AS approx_count_val,
       APPROX_COUNT(*, 'MAX_ERROR') AS approx_count_val_me,
       APPROX_RANK(PARTITION BY record_type ORDER BY APPROX_COUNT(*) DESC) AS approx_rank_val
FROM   t1
GROUP BY record_type, department
HAVING APPROX_RANK(PARTITION BY record_type ORDER BY APPROX_COUNT(*) DESC) <= 1
ORDER BY 1, 5;

RECORD_TYPE  DEPARTMENT APPROX_COUNT_VAL APPROX_COUNT_VAL_ME APPROX_RANK_VAL
------------ ---------- ---------------- ------------------- ---------------
FIVE                 30             5059                   0               1
FOUR                 10             5042                   0               1
ONE                  30             5042                   0               1
THREE                30             5115                   0               1
TWO                  10             5160                   0               1

SQL>

Надеюсь, примеры оказались для Вас наглядными и понятными, и работа с функциями Approximate Top-N в Oracle 18c станет для Вас обычным делом! ;-)

Вас заинтересует / Intresting for you:

Oracle и непроцедурный доступ ...
Oracle и непроцедурный доступ ... 6659 просмотров Antoni Tue, 21 Nov 2017, 13:32:50
Отмена сессий в Oracle (ALTER ...
Отмена сессий в Oracle (ALTER ... 5052 просмотров Stepan Ushakov Thu, 01 Nov 2018, 18:04:59
Видеокурс по администрированию...
Видеокурс по администрированию... 5773 просмотров Илья Дергунов Mon, 14 May 2018, 05:08:47
Listener Oracle
Listener Oracle 8758 просмотров Rasen Fasenger Tue, 21 Nov 2017, 13:18:05

Войдите чтобы комментировать