23май
Как выбрать из каждой группы записей первые N записей?
Постановка задачи: Нужно из каждой группы записей (записи у которых совпадают ключи) необходимо выбрать только наперед заданное количество записей.
Способ решения. Я хочу предложить такой способ с использованием аналитических функций 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|
Вот и все. Если хотите больше прочитать про аналитические функции, могу предложить мою статейку. Благодарю за внимание. Жду замечаний и предложений. Успехов Вам!