Запрос: Как перечислить строки на основе условия?

avatar
Erick Asto Oblitas
1 июля 2021 в 17:14
94
2
0

Имея следующую таблицу:

DROP TABLE IF EXISTS #Data
CREATE TABLE #Data
(
    Code VARCHAR(10),
    Fee VARCHAR(3),
    AValue DECIMAL(10, 4)
)
-- DELETE FROM #Data
INSERT INTO #Data
VALUES
('A001', '001', 100), ('A001', '002', 200), ('A001', '003', -50), ('A001', '004', -250), ('A001', '005', 340), ('A001', '006', 500), ('A001', '007', 600)

Мне нужно получить следующий результат (упорядоченная последовательность, основанная на положительном или отрицательном значении Value):

Code    Fee Value      Row
A001    001 100.0000   P1
A001    002 200.0000   P1
A001    003 -50.0000   N1
A001    004 -250.0000  N1
A001    005 340.0000   P2
A001    006 500.0000   P2
A001    007 600.0000   P2

Я пробовал это:

SELECT Code, Fee, AValue, ROW_NUMBER() OVER(PARTITION BY Code, (CASE WHEN AValue > 0 THEN 1 ELSE 2 END) ORDER BY Fee) 'nRow',
FORMAT(ROW_NUMBER() OVER(PARTITION BY Code, Fee, CASE WHEN AValue > 0 THEN 1 ELSE 2 END ORDER BY Fee), CASE WHEN AValue > 0 THEN 'POS00' ELSE 'NEG00' END)
FROM #Data

Но он возвращает:

Code    Fee Value      Row
A001    001 100.0000   P1
A001    002 200.0000   P1
A001    003 -50.0000   N1
A001    004 -250.0000  N1
A001    005 340.0000   P1
Источник
Larnu
1 июля 2021 в 17:15
1

Какая здесь логика? Вы не объясняете это. Если я правильно догадываюсь, что происходит, когда value имеет значение 0?

Erick Asto Oblitas
1 июля 2021 в 17:27
0

@Larnu Мне нужно создать группы, отсортированные в зависимости от того, является ли значение положительным или отрицательным.

Ответы (2)

avatar
Larnu
1 июля 2021 в 17:22
0

Это своего рода слепое предположение, но, возможно, это то, что вам нужно:

WITH Grps AS(
    SELECT Code,
           Fee,
           AValue,
           ROW_NUMBER() OVER (ORDER BY Fee) - 
           ROW_NUMBER() OVER (PARTITION BY CASE WHEN Avalue > 0 THEN 1 WHEN Avalue < 0 THEN -1 END ORDER BY Fee) AS Grp
    FROM #Data)
SELECT Code,
       Fee,
       AValue,
       CONCAT(CASE WHEN Avalue > 0 THEN 'P' WHEN Avalue < 0 THEN 'N' ELSE 'Z' END, 
              DENSE_RANK() OVER (PARTITION BY CASE WHEN Avalue > 0 THEN 1 WHEN Avalue < 0 THEN -1 END ORDER BY Grp)) AS Row
FROM Grps
ORDER BY Fee;```
avatar
Gordon Linoff
1 июля 2021 в 17:21
1

Это своего рода проблема с пробелами и островками, но она достаточно сложная. Предполагая, что у вас нет значений 0, тогда sign() — ваш друг.

Вот подход, который использует тот факт, что разница номеров строк постоянна, когда значения в соседних строках должны быть объединены:

SELECT Code, Fee, AValue, 
       DENSE_RANK() OVER (PARTITION BY sign(avalue) ORDER BY seqnum - seqnum_2) as num
FROM (SELECT d.*,
             ROW_NUMBER() OVER (PARTITION BY code, sign(avalue) ORDER BY fee) as seqnum_2,
             ROW_NUMBER() OVER (PARTITION BY code ORDER BY fee) as seqnum
      FROM Data d
     ) d
ORDER BY Code, Fee;

Вы можете включить это в свою строку, используя CONCAT() или что-то подобное.

Здесь это db<>скрипка.

Larnu
1 июля 2021 в 17:26
0

Я совсем забыл, что есть функция SIGN