Первый порядок SQL, затем раздел в предложении over

У меня проблема, я хочу разбить отсортированную таблицу. Как я могу это сделать?

Я использую SQL Server 2016.

Таблица ввода:

|---------|-----------------|-----------|------------|
|  prod   |   sortcolumn    |    type   |    value   |
|---------|-----------------|-----------|------------|
|    X    |        1        |     P     |     12     |
|    X    |        2        |     P     |     23     |
|    X    |        3        |     E     |     34     |
|    X    |        4        |     P     |     45     |
|    X    |        5        |     E     |     56     |
|    X    |        6        |     E     |     67     |
|    Y    |        1        |     P     |     78     |
|---------|-----------------|-----------|------------|

Желаемый результат

|---------|-----------------|-----------|------------|------------|
|  prod   |   sortcolumn    |    type   |    value   |    rowNr   |
|---------|-----------------|-----------|------------|------------|
|    X    |        1        |     P     |     12     |      1     |
|    X    |        2        |     P     |     23     |      2     |
|    X    |        3        |     E     |     34     |      1     |
|    X    |        4        |     P     |     45     |      1     |
|    X    |        5        |     E     |     56     |      1     |
|    X    |        6        |     E     |     67     |      2     |
|    Y    |        1        |     P     |     78     |      1     |
|---------|-----------------|-----------|------------|------------|

Я так далеко:

SELECT
  table.*,
  ROW_NUMBER() OVER(PARTITION BY table.prod, table.type ORDER BY table.sortColumn) rowNr
FROM table

Но это не перезапускает номер строки в 4-й строке, поскольку это тот же продукт и тип. Как я могу перезапускать каждый продукт, а также при каждом изменении типа на основе критериев сортировки, даже если тип возвращается к тому, что уже было раньше? Возможно ли это даже с функцией ROW_NUMBER или мне нужно работать с LEAD, LAG и CASES (что, вероятно, сделает это очень медленно, не так ли?)

Спасибо!


sql sql-server tsql gaps-and-islands row-number
person Letimogo    schedule 28.06.2018    source источник
comment
Я не понимаю, как можно избежать отставания / опережения, если вам действительно НУЖНА информация из предыдущей строки для вашей логики.   -  person George Menoutis    schedule 28.06.2018
comment
Как вы думаете, почему LEAD и / или LAG замедляют выполнение запроса?   -  person Larnu    schedule 28.06.2018
comment
Это мой опыт до сих пор ... Но это из старых версий оракула, лучше на новом сервере sql? Учтите, что таблица среднего размера (около 2 млн строк).   -  person Letimogo    schedule 28.06.2018
comment
Oracle и SQl Server - очень разные продукты. LAG и LEAD очень эффективны, судя по моему опыту, и намного лучше, чем выполнение JOIN в соответствии с FROM MyTable MT LEFT JOIN MyTable MTl ON MT.ID +1 = MTl.ID   -  person Larnu    schedule 28.06.2018

Ответы (3)


arrow_upward
6
arrow_downward

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

SELECT t.*, 
       ROW_NUMBER() OVER (PARTITION BY prod ORDER BY sortcolumn)
       -
       ROW_NUMBER() OVER (PARTITION BY prod, type ORDER BY sortcolumn) AS grp
FROM mytable t

получить:

prod    sortcolumn  type    value   grp
----------------------------------------
X       1           P       12      0
X       2           P       23      0
X       3           E       34      2
X       4           P       45      1
X       5           E       56      3
X       6           E       67      3
Y       1           P       78      0

Теперь поле grp можно использовать для разбиения:

;WITH IslandsCTE AS (
    SELECT t.*, 
           ROW_NUMBER() OVER (PARTITION BY prod ORDER BY sortcolumn)
           -
           ROW_NUMBER() OVER (PARTITION BY prod, type ORDER BY sortcolumn) AS grp
    FROM mytable t  
)
SELECT prod, sortcolumn, type, value,
       ROW_NUMBER() OVER (PARTITION BY prod, type, grp ORDER BY sortcolumn) AS rowNr
FROM IslandsCTE
ORDER BY prod, sortcolumn

Демо здесь

person Giorgos Betsos    schedule 28.06.2018
comment
Да это именно то, что мне нужно! Я попробовал, и он работает, как ожидалось, и очень быстро. Спасибо! - person Letimogo; 28.06.2018

arrow_upward
4
arrow_downward

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

Вот один из способов решения этой проблемы. Настраивать:

DECLARE @t TABLE (
    prod varchar(1),
    sortcolumn int,
    type varchar(1),
    value int
);

INSERT @t VALUES
('X', 1, 'P', 12),
('X', 2, 'P', 23),
('X', 3, 'E', 34),
('X', 4, 'P', 45),
('X', 5, 'E', 56),
('X', 6, 'E', 67),
('Y', 1, 'P', 78)
;

Получите несколько номеров строк:

;WITH numbered AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY prod, type ORDER BY sortcolumn) as rnX,
        ROW_NUMBER() OVER (PARTITION BY prod ORDER BY sortcolumn) as rn
    FROM
        @t
)

numbered теперь выглядит так:

prod sortcolumn  type value       rnX                  rn
---- ----------- ---- ----------- -------------------- --------------------
X    1           P    12          1                    1
X    2           P    23          2                    2
X    3           E    34          1                    3
X    4           P    45          3                    4
X    5           E    56          2                    5
X    6           E    67          3                    6
Y    1           P    78          1                    1

Почему это полезно? Посмотрите на разницу между rnX и rn:

prod sortcolumn  type value       rnX                  rn                   rn - rnX
---- ----------- ---- ----------- -------------------- -------------------- --------------------
X    1           P    12          1                    1                    0
X    2           P    23          2                    2                    0
X    3           E    34          1                    3                    2
X    4           P    45          3                    4                    1
X    5           E    56          2                    5                    3
X    6           E    67          3                    6                    3
Y    1           P    78          1                    1                    0

Как видите, каждая «группа» имеет общее значение rn - rnX, и оно меняется от одной группы к другой.

Итак, теперь, если мы разделим на prod, type, и номер группы, тогда число внутри этого:

SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY prod, type, rn - rnX ORDER BY sortcolumn) rowNr
FROM
    numbered
ORDER BY 
    prod, sortcolumn

были сделаны:

prod sortcolumn  type value       rnX                  rn                   rowNr
---- ----------- ---- ----------- -------------------- -------------------- --------------------
X    1           P    12          1                    1                    1
X    2           P    23          2                    2                    2
X    3           E    34          1                    3                    1
X    4           P    45          3                    4                    1
X    5           E    56          2                    5                    1
X    6           E    67          3                    6                    2
Y    1           P    78          1                    1                    1

Ссылки по теме: Что нужно SQL: SERIES()

person AakashM    schedule 28.06.2018
comment
Красиво объяснено! - person Alex; 29.06.2018

arrow_upward
-2
arrow_downward

Попробуй это

select prod, sortcolumn, type, value, row_number() over (partition by prod, sortcolumn, type order by value) rowNr    
from table_name
person Gaj    schedule 28.06.2018