Ключевое слово Oracle «Partition By»

195

Может кто-нибудь объяснить, что делает ключевое слово partition by, и дать простой пример этого в действии, а также почему он хотел бы использовать его? У меня есть SQL-запрос, написанный кем-то другим, и я пытаюсь выяснить, что он делает.

Пример раздела:

SELECT empno, deptno, COUNT(*) 
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp

Примеры, которые я видел в Интернете, выглядят слишком глубоко.

Теги:
window-functions

5 ответов

207
Лучший ответ

Предложение PARTITION BY задает диапазон записей, которые будут использоваться для каждой "ГРУППЫ" в предложении OVER.

В вашем примере SQL, DEPT_COUNT вернет число сотрудников в этом отделе для каждой записи сотрудника. (Это как если бы вы депонизовали таблицу emp, вы все равно возвращаете каждую запись в таблице emp.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

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

Это похоже на получение результатов GROUP BY (SUM, AVG и т.д.) без агрегации набора результатов.

Полезно, если вы используете функции LAST OVER или MIN OVER, чтобы получить, например, самую низкую и самую высокую зарплату в отделе, а затем использовать это при расчете зарплаты за отчетный период без выбора подбора, что гораздо быстрее.

Прочтите дополнительную статью статью AskTom.

Поделиться
  • 1
    Камило, я исправил пример. Благодарю.
  • 5
    LAST_VALUE - возвращает последнюю зарплату, MAX возвращает самую высокую зарплату
62

Концепция очень хорошо объясняется принятым ответом, но я обнаружил, что чем больше примеров видно, тем лучше он погружается. Здесь приведен пример:

1) Boss говорит "получите мне количество предметов, которые мы имеем в наличии, сгруппированных по бренду"

Вы говорите: "нет проблем" 

SELECT 
      BRAND
      ,COUNT(ITEM_ID) 
FROM 
      ITEMS
GROUP BY 
      BRAND;

Результат:

+--------------+---------------+
|  Brand       |   Count       | 
+--------------+---------------+
| H&M          |     50        |
+--------------+---------------+
| Hugo Boss    |     100       |
+--------------+---------------+
| No brand     |     22        |
+--------------+---------------+

2) Босс говорит "Теперь принесите мне список всех предметов с их брендом и количеством предметов, которые имеют этот бренд"

Вы можете попробовать:

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) 
 FROM 
      ITEMS
 GROUP BY 
      BRAND;

Но вы получаете:

ORA-00979: not a GROUP BY expression

Здесь находится OVER (PARTITION BY BRAND):

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND) 
 FROM 
      ITEMS;

Что означает:

  • COUNT(ITEM_ID) - получить количество элементов
  • OVER - над набором строк
  • (PARTITION BY BRAND) - имеющие один и тот же бренд

И результат:

+--------------+---------------+----------+
|  Items       |  Brand        | Count()  |
+--------------+---------------+----------+
|  Item 1      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 2      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 3      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 4      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 5      |  H&M          |   50     | 
+--------------+---------------+----------+

и т.д...

Поделиться
  • 2
    Если я хочу получить один результат для каждой группы .. Как я получу его?
  • 0
    Знаете ли вы, можно ли использовать OVER PARTITION BY в предложении WHERE?
Показать ещё 1 комментарий
23

Это расширение SQL, называемое аналитикой. "Over" в выражении select говорит оракулу, что функция является аналитической функцией, а не группой по функциям. Преимущество использования аналитики заключается в том, что вы можете собирать суммы, счета и многое другое только за один проход данных, а не зацикливать данные с помощью подборов или хуже, PL/SQL.

Сначала это выглядит запутанным, но это будет второй характер быстро. Никто не объясняет это лучше, чем Том Ките. Таким образом, ссылка выше отлично.

Конечно, чтение документации является обязательным.

Поделиться
9 
EMPNO     DEPTNO DEPT_COUNT

 7839         10          4
 5555         10          4
 7934         10          4
 7782         10          4 --- 4 records in table for dept 10
 7902         20          4
 7566         20          4
 7876         20          4
 7369         20          4 --- 4 records in table for dept 20
 7900         30          6
 7844         30          6
 7654         30          6
 7521         30          6
 7499         30          6
 7698         30          6 --- 6 records in table for dept 30

Здесь мы получаем счетчик для соответствующего дептно. Что касается deptno 10, то у нас есть 4 записи в таблице emp аналогичные результаты для deptno 20 и 30.

Поделиться
  • 11
    Нет возражений на вопрос, как работает PARTITION. Только пример выходных данных не полностью отвечает на вопрос.
1

ключевое слово over partition похоже на то, что мы разбиваем данные на client_id создание подмножества каждого идентификатора клиента

select client_id, operation_date,
       row_number() count(*) over (partition by client_id order by client_id ) as operationctrbyclient
from client_operations e
order by e.client_id;

этот запрос вернет число операций, выполняемых client_id

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню