Фильтрация строк по номеру строки и разделению по столбцам и бизнес-логике
Предположим, давайте рассмотрим приведенный ниже набор таблиц.
Здесь сначала нам нужно разделить класс, затем правило, а затем по версии.
Это порядок наборов правил
CSXBusrule>> CSX >> CSXFW
Если верхняя версия правила отозвана, мы должны игнорировать это правило и перейти к следующему доступному порядку.
Если верхняя версия правила «Недоступна», мы должны игнорировать эту версию и перейти к следующей версии в следующем доступном порядке.
Для каждого уникального класса, если у нас есть три правила Csx, CsxBusrule, Csxfw, мы должны принять во внимание Csxbusrule, а затем вернуть его более высокую версию. и если более высокая версия Csxbusrule будет отозвана, мы не должны брать какие-либо строки в csxbusrule, мы должны игнорировать csxbusrule, а затем использовать csx в соответствии с порядком
Если для класса существует только правило Csxfw, должна быть возвращена более высокая версия этой строки.
Ниже ожидаемый результат
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-сервера? Вам нужен ответ для обоих? Или вам нужно удалить этот вопрос? stackoverflow.com/questions/78941862/…
@DaleK удалил то же самое
Не могли бы вы также принять во внимание логику класса 3: если верхняя версия правила недоступна, мы должны использовать следующую доступную версию @shawnt00
Ответы 3
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;
Не могли бы вы также принять во внимание логику класса 3: если верхняя версия правила недоступна, мы должны использовать следующую доступную версию @shawnt00
В 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
)
Оба вывода:
Займите первый ряд с заказом (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
Для упорядочивания строк используйте таблицу (подзапрос)
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. условие соединения на ... чувствительно к регистру.
Вычислить 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'
Там мы используем
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
Я думаю, что это единственное совпадение, что это возвращает ожидаемый ответ для данных ОП. Если вы добавите дополнительные данные fiddle, то возвращаемая строка Class4 будет относиться к строке с более низким приоритетом Csxfw и более высоким номером версии, а не к ожидаемой строке с более высоким приоритетом Csx с более низким номером версии.
Спасибо за тестирование и комментарий. Я прочитал вопрос еще раз. Порядок должен быть ord, version desc. Обновлено.(dbfiddle.uk/ONPUw4-h)
с X as (select *, row_number() поверх (разделение по классу, порядок правил по описанию версии) как rnx из T), Y as (select *, row_number() поверх (разделение по порядку классов по правилу регистра, когда 'CSXBusRule') затем 1, когда «CSX», затем 2, когда «CSXFW», затем 3, конец) как rny из X, где RuleActive = «Да») выберите * из Y, где rny = 1 @shawnt00, где именно