Фильтрация строк по номеру строки и разделению по столбцам и бизнес-логике

Предположим, давайте рассмотрим приведенный ниже набор таблиц.

Сорт Сорт правило версия ПравилоДоступно Класс 1 Класс1-Csx -02.01 Csx 02.01 Да Класс1 Класс 1- Csx-01.01 Csx 01.01 Да Класс1 Класс1-CsxBusrule -02.01 CsxBusrule 02.01 снято Класс1 Класс1-CsxBusrule -01.01 CsxBusrule 01.01 Да Класс1 Класс 1 – Csxfw –01.01. Csxfw 01.01 Да Класс2 Класс 2 — Csx — 02.01 Csx 02.01 Да Класс2 Класс 2 — Csxbusrule -02.01 Csxbusrule 02.01 Да Класс3 Класс 3 – Csxfw –02.01. Csxfw 02.01 Нет в наличии Класс3 Класс 3 – Csxfw –01.01. Csxfw 01.01 Да

Здесь сначала нам нужно разделить класс, затем правило, а затем по версии.

Это порядок наборов правил

CSXBusrule>> CSX >> CSXFW

Если верхняя версия правила отозвана, мы должны игнорировать это правило и перейти к следующему доступному порядку.

Если верхняя версия правила «Недоступна», мы должны игнорировать эту версию и перейти к следующей версии в следующем доступном порядке.

Для каждого уникального класса, если у нас есть три правила Csx, CsxBusrule, Csxfw, мы должны принять во внимание Csxbusrule, а затем вернуть его более высокую версию. и если более высокая версия Csxbusrule будет отозвана, мы не должны брать какие-либо строки в csxbusrule, мы должны игнорировать csxbusrule, а затем использовать csx в соответствии с порядком

Если для класса существует только правило Csxfw, должна быть возвращена более высокая версия этой строки.

Ниже ожидаемый результат

Сорт Ключ ПРАВИЛО Версия правило доступно Класс 1 Класс1-Csx-02.01 Csx 02.01 Да Класс2 Класс 2 — Csxbusrule -02.01 Csxbusrule 02.01 Да Класс3 Класс 3 — Csxfw-01.01 Csxfw 01.01 Да

Select 
  Key ,
  Class, 
  Rule , 
  Version 
FROM 
(
  SELECT 
    t.*,
    ROW_NUMBER() 
      OVER (PARTITION BY class ORDER BY 
                    CASE rule WHEN 'CSXBusRule' THEN 1 
                              WHEN 'CSX' THEN 2 
                              WHEN 'CSXFW' THEN 3 END, version DESC) AS rn 
  FROM table t 
  where 
    pyRuleAvailable='Yes'
) 

WHERE rn = 1,

попробовал это, но это не включает отозванную логику, мы должны игнорировать правило и перейти к следующему порядку правил, если более высокая версия этого правила отозвана, как это сделать

🤔 А знаете ли вы, что...
SQL позволяет создавать индексы на нескольких столбцах для оптимизации поиска данных.

[РЕШЕНО]Перейти к решению

Sql74185 вопросов Oracle17706 вопросов

Ответы:

with X as (
    select *, row_number() over (partition by class, rule order by version desc) as rnx
    from T
), Y as (
    select *, row_number() over (partition by class order by case rule when 'CSXBusRule' then 1 when 'CSX' then 2 when 'CSXFW' then 3 end) as rny 
    from X
    where rnx = 1 and RuleActive = 'Yes'
)
select * from Y where rny = 1;

Решено

В Oracle 12 вы можете установить приоритет правил, а затем использовать MATCH_RECOGNIZE для сопоставления шаблонов построчно:

SELECT Key,
       Class, 
       Rule, 
       Version,
       RuleAvailable
FROM   (
         SELECT Key,
                Class, 
                Rule, 
                Version,
                RuleAvailable,
                DECODE(rule, 'CsxBusrule', 1, 'Csx', 2, 'Csxfw', 3) AS rule_priority,
                DECODE(ruleavailable, 'Withdrawn', 1, 'Yes', 2) AS available_priority
         FROM   table_name
       )
       MATCH_RECOGNIZE(
         PARTITION BY key
         ORDER BY rule_priority, available_priority, version DESC
         ALL ROWS PER MATCH
         PATTERN (^ {- ( withdrawn+ same_rule* )* -} not_withdrawn )
         DEFINE
           withdrawn AS RuleAvailable = 'Withdrawn',
           same_rule AS Rule = PREV(rule)
       )

В более ранних версиях вы можете использовать:

SELECT Key,
       Class, 
       Rule, 
       Version,
       RuleAvailable
FROM   (
  SELECT Key,
         Class, 
         Rule, 
         Version,
         RuleAvailable,
         ROW_NUMBER() OVER (
           PARTITION BY key
           ORDER BY rule_priority, version DESC
         ) AS rn
  FROM   (
    SELECT Key,
           Class, 
           Rule, 
           Version,
           RuleAvailable,
           COUNT(CASE RuleAvailable WHEN 'Withdrawn' THEN 1 END) OVER (
             PARTITION BY key, rule
           ) AS num_withdrawn,
           DECODE(rule, 'CsxBusrule', 1, 'Csx', 2, 'Csxfw', 3) AS rule_priority
    FROM   table_name
  )
  WHERE  num_withdrawn = 0
)
WHERE  rn = 1;

Что для примера данных:

CREATE TABLE table_name (Key, rule, version, RuleAvailable) AS
SELECT 'Class1', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csx',        '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '02.01', 'Withdrawn' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csxfw',      '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'CsxBusrule', '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class3', 'Csxfw',      '01.01', 'Yes' FROM DUAL

ALTER TABLE table_name ADD class VARCHAR2(50) GENERATED ALWAYS AS (
  key || '-' || rule || '-' || version
)

Оба вывода:

КЛЮЧ СОРТ ПРАВИЛО ВЕРСИЯ ПРАВИЛО ДОСТУПНО Класс1 Класс1-Csx-02.01 Csx 02.01 Да Класс2 Класс2-CsxBusrule-02.01 CsxBusrule 02.01 Да Класс3 Класс3-Csxfw-01.01 Csxfw 01.01 Да

рабочий пример

Займите первый ряд с заказом (rule priority, version).

Мы используем join ord — приоритет правила.

Полный запрос

select *
from (
select t.* 
  ,row_number()over(partition by class order by ord,version desc) as rn
from test t
left join rulepriority p on t.rulename=p.rulename
where RuleAvailable='Yes'
)t
where rn=1

СОРТ ИМЯ ПРАВИЛА ВЕРСИЯ ПРАВИЛО ДОСТУПНО РН Класс1 Csx 02.01 Да 1 Класс2 CsxBusrule 02.01 Да 1 Класс3 Csxfw 01.01 Да 1

Для упорядочивания строк используйте таблицу (подзапрос)

create table rulepriority (rulename,ord) as
SELECT 'CsxBusrule',1 FROM DUAL union all
SELECT 'Csx'       ,2 FROM DUAL union all
SELECT 'Csxfw'     ,3  FROM DUAL

Обновление 1. условие соединения на ... чувствительно к регистру.

ИМЯ ПРАВИЛА ОРД CsxBusrule 1 Csx 2 Csxfw 3

Вычислить row_number (раздел по порядку классов по описанию версии, ord) для отфильтрованных строк (RuleAvailable='Да').
Выход

select t.* 
  ,row_number()over(partition by class order by ord,version desc) as rn
from test t
left join rulepriority p on t.rulename=p.rulename
where RuleAvailable='Yes'

СОРТ ИМЯ ПРАВИЛА ВЕРСИЯ ПРАВИЛО ДОСТУПНО РН Класс1 Csx 02.01 Да 1 Класс1 CsxBusrule 01.01 Да 2 Класс1 Csxfw 01.01 Да 3 Класс1 Csx 01.01 Да 4 Класс2 CsxBusrule 02.01 Да 1 Класс2 Csx 02.01 Да 2 Класс3 Csxfw 01.01 Да 1

новая рабочий пример.

старая рабочий пример

Там мы используем

CREATE TABLE test (class, rulename, version, RuleAvailable) AS
SELECT 'Class1', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csx',        '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '02.01', 'Withdrawn' FROM DUAL UNION ALL
SELECT 'Class1', 'CsxBusrule', '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class1', 'Csxfw',      '01.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'Csx',        '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class2', 'CsxBusrule', '02.01', 'Yes' FROM DUAL UNION ALL
SELECT 'Class3', 'Csxfw',      '01.01', 'Yes' FROM DUAL

Фильтрация строк по номеру строки и разделению по столбцам и бизнес-логике

Ответы:

Интересные вопросы для изучения