考虑优先级的分组，按类型分组

Question

我需要根据数据的类型对它们进行分组，并考虑优先级。

以下面的CTE为例。

WITH classif AS
(
select 1 as id, 'account' as type, 'high' as priority from dual union all
select 2 as id, 'account' as type, 'none' as priority from dual union all
select 3 as id, 'account' as type, 'medium' as priority from dual union all
select 4 as id, 'security' as type, 'high' as priority from dual union all
select 5 as id, 'security' as type, 'medium' as priority from dual union all
select 6 as id, 'security' as type, 'low' as priority from dual union all
select 7 as id, 'security' as type, 'none' as priority from dual union all
select 8 as id, 'transform' as type, 'none' as priority from dual union all
select 9 as id, 'transform' as type, 'none' as priority from dual union all
select 10 as id, 'transform' as type, 'none' as priority from dual union all
select 11 as id, 'transform' as type, 'none' as priority from dual union all
select 12 as id, 'enrollment' as type, 'medium' as priority from dual union all
select 13 as id, 'enrollment' as type, 'low' as priority from dual union all
select 14 as id, 'enrollment' as type, 'low' as priority from dual union all
select 15 as id, 'enrollment' as type, 'low' as priority from dual;
select 15 as id, 'process' as type, 'low' as priority from dual;
select 15 as id, 'process' as type, 'none' as priority from dual;
select 15 as id, 'process' as type, 'none' as priority from dual;
)

对于这个数据集，我的输出必须是这样的

------------+-------------
type        |  priority
------------+-------------
account     |  high
security    |  high
transform   |  none
enrollment  |  medium
process     |  low
---------------------------

优先级从"高"开始。";none"

输出规则必须像这样

• 当一个类型有优先级为";high&;"的行时，该类型的输出必须为";high&;">
• 当一个类型有优先级为"中"的行时;不要有别的有"high"，输出必须"medium"
• 当一个类型有优先级为"low"的行时;不要有别的与"引起;或"中"，输出必须为"低"。
• 当一个类型有一行优先级为"none"不要有任何东西否则，输出必须为"none">

我正在尝试做下面这样的查询，但这将返回所有行，而不是根据优先级分组

select 
type,
case 
when priority = 'high' then 'high'
when priority = 'medium' and priority <> 'high' then 'medium'
when priority = 'medium' and priority <> 'high' then 'medium'
when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
end as priority        
from classif
group by type,
case
when priority = 'high' then 'high'
when priority = 'medium' and priority <> 'high' then 'medium'
when priority = 'medium' and priority <> 'high' then 'medium'
when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
end;

你能帮我解决这个问题吗?

Answer 1

您可以使用ROW_NUMBER并通过CASE语句排序来将优先级转换为数值:

SELECT id, type, priority
FROM   (
SELECT c.*,
ROW_NUMBER() OVER (
PARTITION BY type
ORDER BY CASE priority
WHEN 'high'   THEN 1
WHEN 'medium' THEN 2
WHEN 'low'    THEN 3
ELSE 4
END
) AS rn
FROM   classif c
)
WHERE rn = 1;

或者，您可以使用MAX(...) KEEP (DENSE RANK FIRST ...)并类似地使用CASE语句进行排序:

SELECT MAX(id) KEEP (
DENSE_RANK FIRST
ORDER BY CASE priority
WHEN 'high'   THEN 1
WHEN 'medium' THEN 2
WHEN 'low'    THEN 3
ELSE 4
END
) AS id,
type,
MAX(priority) KEEP (
DENSE_RANK FIRST
ORDER BY CASE priority
WHEN 'high'   THEN 1
WHEN 'medium' THEN 2
WHEN 'low'    THEN 3
ELSE 4
END
) AS priority
FROM   classif
GROUP BY type

或者，您可以使用子查询来存储相对优先级级别，并使用连接:

SELECT MAX(c.id) KEEP (DENSE_RANK LAST ORDER BY p.id) AS id,
type,
MAX(c.priority) KEEP (DENSE_RANK LAST ORDER BY p.id) AS priority
FROM   classif c
LEFT OUTER JOIN (
SELECT 'high' AS priority, 3 As id FROM DUAL UNION ALL
SELECT 'medium', 2 FROM DUAL UNION ALL
SELECT 'low',    1 FROM DUAL UNION ALL
SELECT 'none',   0 FROM DUAL
) p
ON c.priority = p.priority
GROUP BY c.type

对于您的样本数据，所有输出:

<表类>ID类型优先级tbody><<tr>1账户高12招生中的15过程低4安全高8变换没有