Oracle SQL Pattern Matching 3 | جدا مسلي وواقعي

Опубликовано: 23 Март 2026
на канале: khaled alkhudari

734

drop table Skeet_shooting;

create table Skeet_shooting
( player_name varchar2(100),
shoot_number number ,
score number,
constraint Skeet_shooting_pk primary key (player_name,shoot_number)
);

select DBMS_RANDOM.VALUE(0,1) from dual;
select round(DBMS_RANDOM.VALUE(0,1)) from dual;

begin
for i in 1..5
loop
for j in 1..10
loop
insert into Skeet_shooting
values ('player '||i, j,round(DBMS_RANDOM.VALUE(0,1)));
end loop;

end loop;
end;

select * from Skeet_shooting
order by player_name, shoot_number

select PLAYER_NAME, sum(score)
from Skeet_shooting
group by PLAYER_NAME
order by sum(score) desc

select PLAYER_NAME, sum(score)
from Skeet_shooting
group by PLAYER_NAME
order by sum(score) desc
fetch first 1 row with ties

select *
from Skeet_shooting s
MATCH_RECOGNIZE
(
partition by PLAYER_NAME
order by shoot_number
measures
match_number() matchv,
final count(score) c_p_u
all ROWs PER MATCH
PATTERN (x{2,} )
define x as score=1
) c

select PLAYER_NAME, max(c_p_u)
from
(
select *
from Skeet_shooting s
MATCH_RECOGNIZE
(
partition by PLAYER_NAME
order by shoot_number
measures
match_number() matchv,
final count(score) c_p_u
all ROWs PER MATCH
PATTERN (x{2,} )
define x as score=1
) c
)
group by PLAYER_NAME
order by max(c_p_u) desc
fetch first 1 row with ties