分组方式-无聚合

Question

我有一个oracle表，其中ref_id是标志字段，是数据的类型，ORN是每个ref_id:中数据的顺序

ref_id   data    ORN   flag
1       100     0     0
1       200     1     0
1       300     2     0
1       400     3     0
1       110     0     1
1       210     1     1
1       150     0     2
1       250     1     2
1       350     2     2
1       450     3     2
2       500     0     0
2       600     1     0
2       700     2     0
2       800     3     0
2       120     0     1
2       220     1     1
2       320     1     1
2       420     1     1
2       170     0     2
2       270     1     2
2       370     2     2
2       470     3     2

我需要对数据进行分组，以获得每个ref_id 的标志0中的最后数据和标志2中的最后数据

所以新的表格是这样的：

ref_id    data_1    data_2
1        400       450
2        800       470

有什么提示可以在不使用循环的情况下实现这一点吗？

Answer 1

您可以使用分析函数并按如下方式分组：

SELECT REF_ID, 
MAX(CASE WHEN FLAG = 0 THEN DATA END) AS DATA_0,
MAX(CASE WHEN FLAG = 2 THEN DATA END) AS DATA_2
FROM
(
SELECT REF_ID, DATA, ORN, FLAG,
ROW_NUMBER() OVER (PARTITION BY REF_ID, FLAG ORDER BY ORN DESC) AS RN
FROM YOUR_TABLE 
WHERE FLAG IN (0,2)
)
WHERE RN = 1
GROUP BY REF_ID

Answer 2

或者使用两步方法，首先(在CTE中(只选择与REF_ID中最后一个ORN相对应的DATA列的值

请注意，如果ORN不是唯一的，您可能会得到多个可能具有不同值的行。

在REF_ID的下一步简单聚合中，我使用max函数，即在平局的情况下，这将获得DATA的最高值

如果REF_ID和ORN的组合是唯一的(主键(，您可以使用可互换的MIN和MAX，但最好知道，如果允许重复，它们将提供差异结果。

with agg as (
select 
REF_ID,FLAG, DATA, ORN,
case when flag = 0  and ORN = max(ORN) over (partition by REF_ID, FLAG) then data end as data_0,
case when flag = 2  and ORN = max(ORN) over (partition by REF_ID, FLAG) then data end as data_2
from tab
)
select  REF_ID, 
max(data_0) as data_0,
max(data_2) as data_2
from agg
group by REF_ID
order by 1;

这里是CTE 的结果

REF_ID       FLAG       DATA        ORN     DATA_0     DATA_2
---------- ---------- ---------- ---------- ---------- ----------
1          0        100          0                      
1          0        200          1                      
1          0        300          2                      
1          0        400          3        400           
1          1        110          0                      
1          1        210          1                      
1          2        150          0                      
1          2        250          1                      
1          2        350          2                      
1          2        450          3                   450
...

以及最终查询的结果

REF_ID     DATA_0     DATA_2
---------- ---------- ----------
1        400        450
2        800        470   

Answer 3

您可以为此目的使用聚合函数(FIRST/LAST.(。

https://docs.oracle.com/database/121/SQLRF/functions074.htm#SQLRF00641

https://docs.oracle.com/database/121/SQLRF/functions095.htm#SQLRF00653。

with t (ref_id,data,ORN,flag) as (
select   1,       100,     0,     0 from dual union all
select   1,       200,     1,     0 from dual union all
select   1,       300,     2,     0 from dual union all
select   1,       400,     3,     0 from dual union all
select   1,       110,     0,     1 from dual union all
select   1,       210,     1,     1 from dual union all
select   1,       150,     0,     2 from dual union all
select   1,       250,     1,     2 from dual union all
select   1,       350,     2,     2 from dual union all
select   1,       450,     3,     2 from dual union all
select   2,       500,     0,     0 from dual union all
select   2,       600,     1,     0 from dual union all
select   2,       700,     2,     0 from dual union all
select   2,       800,     3,     0 from dual union all
select   2,       120,     0,     1 from dual union all
select   2,       220,     1,     1 from dual union all
select   2,       320,     1,     1 from dual union all
select   2,       420,     1,     1 from dual union all
select   2,       170,     0,     2 from dual union all
select   2,       270,     1,     2 from dual union all
select   2,       370,     2,     2 from dual union all
select   2,       470,     3,     2 from dual 
)
select 
ref_id
, max(decode(flag, 0, data)) keep (dense_rank last order by decode(flag, 0, 100, 50), orn ) x
, max(decode(flag, 2, data)) keep (dense_rank last order by decode(flag, 2, 100, 50), orn ) y 
-- or
, min(decode(flag, 0, data)) keep (dense_rank first order by decode(flag, 0, 50, 100), orn desc) xx
, min(decode(flag, 2, data)) keep (dense_rank first order by decode(flag, 2, 50, 100), orn desc) yy
from t
group by ref_id
REF_ID          X          Y         XX         YY
---------- ---------- ---------- ---------- ----------
1        400        450        400        450
2        800        470        800        470