如何用分区重写脚本

Question

我有一个包含两组列的表。第一组用于获得row_num(row_num() partition by ...)，第二组用于获得重复数CCD_；1.

create table source
(
id      int primary key,
name    varchar(10),
surname varchar(10),
day_number     int,
height int
);

insert into source(
id,
name,
surname,
day_number,
height
)
values
(1,'Mike', 'Wilson', 1, 160),
(2,'Mike', 'Wilson', 1, 165),
(3,'Mike', 'Wilson', 2, 170),
(4,'Mike', 'Wilson', 3, 175),
(5,'Mike', 'Wilson', 3, 180),

(6,'John', 'Conor', 1, 160),
(7,'John', 'Conor', 1, 165),
(8,'John', 'Conor', 2, 170),
(9,'John', 'Conor', 3, 175);

预期结果为

(1,'Mike', 'Wilson', 1, 160),
(2,'Mike', 'Wilson', 1, 165),
(4,'Mike', 'Wilson', 3, 175),
(5,'Mike', 'Wilson', 3, 180),
(6,'John', 'Conor', 1, 160),
(7,'John', 'Conor', 1, 165),
(9,'John', 'Conor', 3, 175)

你能帮我改进一下选择吗？具有分析函数？

with t1 as (
select row_number() over (partition by name, surname order by id, height) as row_num as row_num,
count(*) over (partition by name, surname, day_number)             as amount,
*
from source
),
t2 as (
select *
from t1
where row_num = 1
)
select *
from t2
union
select t1.*
from t1
join t2 on t1.name = t2.name
and t1.surname = t2.surname
where t1.amount > 1;

Answer 1

您只能使用CTE t1:

select Y.id, Y.Name,Y.surname,Y.day_number,Y.height from  t1 X left join t1 Y
on X.name = Y.name
and X.surname = Y.surname
where X.row_num=1 AND Y.amount > 1 
order by Y.id

由于窗口函数为每一行计算的值(所有行的amount将是相同的within partition by name, surname, day_number(，因此row_num=1也将包含在其中，因此您可以尝试更简单的查询：

select id,name,surname,day_number from 
(
select count(*) over (partition by name, surname, day_number) as amount,
* from  source
)X
where amount>1
order by id

在你想要的输出中有一行：

(9,'John', 'Conor', 3, 175)

但这张唱片(约翰·康纳(没有重复的，天数=3