Как выбрать из каждой группы записей первые N записей?

dbstalker, 23 мая

Постановка задачи: Нужно из каждой группы записей (записи у которых совпадают ключи) необходимо выбрать только наперед заданное количество записей.

Способ решения. Я хочу предложить такой способ с использованием аналитических функций DENSE_RANK, RANK, NTILE, ROW_NUMBER


CREATE TABLE DBST_TABL
 (CITY    VARCHAR2(20 BYTE),
  NAME    VARCHAR2(30 BYTE),
  PERIOD  VARCHAR2(6 BYTE),
  GOODS   VARCHAR2(6 BYTE));

insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200602','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200602','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n2','200601','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n4','200604','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n2','200601','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200601','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200602','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n3','200602','t2');
insert into  dbst_tabl (city,name,period,goods) values ('c2','n4','200603','t3');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200603','t4');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200603','t5');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200604','t1');
insert into  dbst_tabl (city,name,period,goods) values ('c3','n4','200604','t2');

Получаем следующую таблицу:


CITY|NAME|PERIOD|GOODS
c1|n1|200601|t0|
c1|n1|200601|t1|
c1|n1|200601|t2|
c1|n1|200601|t3|
c1|n1|200602|t1|
c1|n1|200602|t2|
c1|n1|200603|t3|
c1|n1|200603|t4|
c1|n1|200603|t5|
c1|n2|200601|t0|
c1|n2|200601|t2|
c1|n2|200601|t3|
c1|n2|200601|t4|
c1|n4|200603|t3|
c1|n4|200603|t4|
c1|n4|200603|t5|
c1|n4|200604|t1|
c1|n4|200604|t2|
c1|n4|200604|t3|
c2|n2|200601|t0|
c2|n2|200601|t2|
c2|n2|200601|t3|
c2|n2|200601|t4|
c2|n3|200601|t5|
c2|n3|200602|t1|
c2|n3|200602|t2|
c2|n4|200603|t3|
c3|n4|200603|t4|
c3|n4|200603|t5|
c3|n4|200604|t1|
c3|n4|200604|t2|

select CITY, NAME, PERIOD,goods,toprank from
(SELECT CITY, NAME, PERIOD,goods, DENSE_RANK() OVER(partition by city,name,period ORDER BY goods desc) toprank FROM DBST_TABL) where toprank<=2

CITY|NAME|PERIOD|GOODS|TOPRANK
c1|n1|200601|t3|1|
c1|n1|200601|t2|2|
c1|n1|200602|t2|1|
c1|n1|200602|t1|2|
c1|n1|200603|t5|1|
c1|n1|200603|t4|2|
c1|n2|200601|t4|1|
c1|n2|200601|t3|2|
c1|n4|200603|t5|1|
c1|n4|200603|t4|2|
c1|n4|200604|t3|1|
c1|n4|200604|t2|2|
c2|n2|200601|t4|1|
c2|n2|200601|t3|2|
c2|n3|200601|t5|1|
c2|n3|200602|t2|1|
c2|n3|200602|t1|2|
c2|n4|200603|t3|1|
c3|n4|200603|t5|1|
c3|n4|200603|t4|2|
c3|n4|200604|t2|1|
c3|n4|200604|t1|2|

Просто, но со вкусом.

Теперь немного теории.

Рассмотрим две функции rank и dense_rank, которые выполняют ранжирование записей .

Синтаксис:
RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

< partition_by_clause >
Разделяет результирующий набор, производимый предложением FROM, на секции (группы), к которым применяется функция RANK.

< order_by_clause >
Определяет порядок, в котором значения RANK применяются к строкам в секции.

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

Замечание:
Все строки с одним и тем же значением сортировки получают ранг, совпадающий с порядковым номером первой из таких строк. Следующий ранг также совпадает с порядковым номером. Другими словами, если есть две строки с одинаковым значением сортировки, которое идет первым, то они получат RANK=1, а третья строка будет иметь RANK=3. Строк с RANK=2 не будет.

Синтаксис
DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

< partition_by_clause >
Делит результирующий набор, возвращенный предложением FROM, на секции, к которым применяется функция DENSE_RANK.

< order_by_clause >
Определяет порядок, в котором значения DENSE_RANK применяются к строкам секции.

Если две или более строк одной секции равны при ранжировании, каждой такой строке присваивается один и тот же ранг. Но в отличие от rank между номерами, возвращаемыми функцией DENSE_RANK, нет промежутков, и они всегда имеют последовательные значения ранга. Другими словами, если есть две строки с одинаковым значением сортировки, которое идет первым, то они получат dense_RANK=1, а третья строка будет иметь dense_RANK=2.

А теперь на нашей таблице покажу, чем отличаются эти две функции. Сначала добавим три строки.


insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t0');
insert into  dbst_tabl (city,name,period,goods) values ('c1','n1','200601','t0');

Затем выполним запрос:


SELECT  CITY, NAME, PERIOD,GOODS,rank() over (partition by CITY, NAME, PERIOD order by GOODS) rank,
dense_rank() over (partition by CITY, NAME, PERIOD order by GOODS) dense FROM DBST_TABL

Выборка будет иметь следующий вид:


CITY|NAME|PERIOD|GOODS|RANK|DENSE
c1|n1|200601|t0|1|1|
c1|n1|200601|t0|1|1|
c1|n1|200601|t0|1|1|
c1|n1|200601|t0|1|1|
c1|n1|200601|t1|5|2|
c1|n1|200601|t2|6|3|
c1|n1|200601|t3|7|4|
c1|n1|200602|t1|1|1|
c1|n1|200602|t2|2|2|
c1|n1|200603|t3|1|1|
c1|n1|200603|t4|2|2|
c1|n1|200603|t5|3|3|
c1|n2|200601|t0|1|1|
c1|n2|200601|t2|2|2|
c1|n2|200601|t3|3|3|
c1|n2|200601|t4|4|4|
c1|n4|200603|t3|1|1|
c1|n4|200603|t4|2|2|
c1|n4|200603|t5|3|3|
c1|n4|200604|t1|1|1|
c1|n4|200604|t2|2|2|
c1|n4|200604|t3|3|3|
c2|n2|200601|t0|1|1|
c2|n2|200601|t2|2|2|
c2|n2|200601|t3|3|3|
c2|n2|200601|t4|4|4|
c2|n3|200601|t5|1|1|
c2|n3|200602|t1|1|1|
c2|n3|200602|t2|2|2|
c2|n4|200603|t3|1|1|
c3|n4|200603|t4|1|1|
c3|n4|200603|t5|2|2|
c3|n4|200604|t1|1|1|
c3|n4|200604|t2|2|2|

Есть еще одна интересная функция NTILE. Делит отсортированный результирующий набор данных на группы строк приблизительно одинакового размера.

Синтаксис:
NTILE (integer_expression) OVER ( [ ] < order_by_clause > )

integer_expression - Положительная целая константа, указывающая число сегментов, на которые необходимо разделить каждую секцию.

<partition_by_clause> Делит результирующий набор, сформированный предложением FROM, на секции, к которым применяется функция RANK.

< order_by_clause > Определяет порядок назначения значений функции NTILE строкам секции.

Замечания Если количество строк в секции не делится на expression, формируются группы двух размеров, отличающихся на единицу. В порядке, заданном предложением OVER, группы большего размера следуют перед группами меньшего размера.

Наш пример


SELECT  CITY, NAME, PERIOD,GOODS,rank() over (partition by CITY, NAME, PERIOD order by GOODS) rank,
dense_rank() over (partition by CITY, NAME, PERIOD order by GOODS) dense,
ntile(3) over (partition by CITY, NAME, PERIOD order by GOODS) nt 
FROM DBST_TABL

CITY|NAME|PERIOD|GOODS|RANK|DENSE|NT
c1|n1|200601|t0|1|1|1|
c1|n1|200601|t0|1|1|1|
c1|n1|200601|t0|1|1|1|
c1|n1|200601|t0|1|1|2|
c1|n1|200601|t1|5|2|2|
c1|n1|200601|t2|6|3|3|
c1|n1|200601|t3|7|4|3|
c1|n1|200602|t1|1|1|1|
c1|n1|200602|t2|2|2|2|
c1|n1|200603|t3|1|1|1|
c1|n1|200603|t4|2|2|2|
c1|n1|200603|t5|3|3|3|
c1|n2|200601|t0|1|1|1|
c1|n2|200601|t2|2|2|1|
c1|n2|200601|t3|3|3|2|
c1|n2|200601|t4|4|4|3|
c1|n4|200603|t3|1|1|1|
c1|n4|200603|t4|2|2|2|
c1|n4|200603|t5|3|3|3|
c1|n4|200604|t1|1|1|1|
c1|n4|200604|t2|2|2|2|
c1|n4|200604|t3|3|3|3|
c2|n2|200601|t0|1|1|1|
c2|n2|200601|t2|2|2|1|
c2|n2|200601|t3|3|3|2|
c2|n2|200601|t4|4|4|3|
c2|n3|200601|t5|1|1|1|
c2|n3|200602|t1|1|1|1|
c2|n3|200602|t2|2|2|2|
c2|n4|200603|t3|1|1|1|
c3|n4|200603|t4|1|1|1|
c3|n4|200603|t5|2|2|2|
c3|n4|200604|t1|1|1|1|
c3|n4|200604|t2|2|2|2|

Еще предлагаю посмотреть на функцию ROW_NUMBER(). Это просто нумерация упорядоченного набора данных.


SELECT  CITY, NAME, PERIOD,GOODS,ROW_NUMBER() over (order by CITY, NAME, PERIOD,GOODS)  FROM DBST_TABL

CITY|NAME|PERIOD|GOODS|ROW_NUMBER()OVER(ORDERBYCITY,NAME,PERIOD,GOODS)
c1|n1|200601|t0|1|
c1|n1|200601|t0|2|
c1|n1|200601|t0|3|
c1|n1|200601|t0|4|
c1|n1|200601|t1|5|
c1|n1|200601|t2|6|
c1|n1|200601|t3|7|
c1|n1|200602|t1|8|
c1|n1|200602|t2|9|
c1|n1|200603|t3|10|
c1|n1|200603|t4|11|
c1|n1|200603|t5|12|
c1|n2|200601|t0|13|
c1|n2|200601|t2|14|
c1|n2|200601|t3|15|
c1|n2|200601|t4|16|
c1|n4|200603|t3|17|
c1|n4|200603|t4|18|
c1|n4|200603|t5|19|
c1|n4|200604|t1|20|
c1|n4|200604|t2|21|
c1|n4|200604|t3|22|
c2|n2|200601|t0|23|
c2|n2|200601|t2|24|
c2|n2|200601|t3|25|
c2|n2|200601|t4|26|
c2|n3|200601|t5|27|
c2|n3|200602|t1|28|
c2|n3|200602|t2|29|
c2|n4|200603|t3|30|
c3|n4|200603|t4|31|
c3|n4|200603|t5|32|
c3|n4|200604|t1|33|
c3|n4|200604|t2|34|

Вот и все. Если хотите больше прочитать про аналитические функции, могу предложить мою статейку. Благодарю за внимание. Жду замечаний и предложений. Успехов Вам!

3 комментария

Прокоментировать

xvv
29 мая 2008 г. в 14:45

Интересно, а как выбрать из каждой группы N первых записей?

dbstalker
29 мая 2008 г. в 17:02

А если попробовать так:
select CITY, NAME, PERIOD,goods from
(SELECT CITY, NAME, PERIOD,goods, row_number()
OVER(partition by city,name,period ORDER BY goods desc) toprank FROM DBST_TABL)
where toprank<=[указанное Вами число N]?

Ruslan
1 ноября 2010 г. в 13:40

Спасибо за статью. Очень вовремя помогла.

 

Новый комментарий

Я не спамер: введите суму 2+8



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

средства для рук
17 августа, 3 ответа
(Без темы)Шкаф купе
17 августа, 2 ответа
Страховая компания
17 августа, 1 ответа