Sql ile Turkcell Süperlig simülasyonu

Sql ile yapabileceklerinizin sınırı sizinle başlıyor sizinle bitiyor. Buradaki örneğin amacı Süperlig puan durumu tablosundan yola çıkarak maçları, fikstürü oluşturmak, maçları rasgele skorlarla oynatmak, rastgele skorlara uygun şekilde puan cetvelini güncellemek.

Konu: herhangi bir spor ligindeki takımların fikstürünü oluşturmak, maçları oynatmak, oluşan sonuçlara göre puan cetvelini güncellemek.
Amaç: Mümkün olan en az sayıda komutla ve en hızlı sürede işi bitirmek.
Verilenler: Sql, Puan Cetveli, Geçici Tablolar, Tetikleyiciler, Cursor

Adım 1: Tabloları oluşturmak

— Puan cetveli
— Maçları ve sonuçları bu tabloda tutacağız. Sql ile Takim tablosu içeriğine göre buraya dolduracağız. Maçlara rastgele sonuçları işleyeceğiz.

CREATE TABLE [dbo].[Takim](
[TakimID] [tinyint] IDENTITY(1,1) NOT NULL,
[TakimAdi] [nvarchar](50) NULL,
[OynadigiMac] [tinyint] NULL,
[Galibiyet] [tinyint] NULL,
[Malubiyet] [tinyint] NULL,
[Beraberlik] [tinyint] NULL,
[Avaraj] [int] NULL,
[AttigiGol] [int] NULL,
[YedigiGol] [int] NULL,
[PuanDurumu] [int] NULL,
CONSTRAINT [PK_PuanDurumu] PRIMARY KEY CLUSTERED
(
[TakimID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Maclar](
[MacID] [int] IDENTITY(1,1) NOT NULL,
[EvSahibiTakim] [tinyint] NULL,
[RakipTakim] [tinyint] NULL,
[EAttigiGol] [tinyint] NULL,
[RAttigiGol] [tinyint] NULL,
[Hafta] [tinyint] NULL,
CONSTRAINT [PK_Maclar] PRIMARY KEY CLUSTERED ([MacID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]TABLE [dbo].[Takim](
[TakimID] [tinyint] IDENTITY(1,1) NOT NULL,
[TakimAdi] [nvarchar](50) NULL,
[OynadigiMac] [tinyint] NULL,
[Galibiyet] [tinyint] NULL,
[Malubiyet] [tinyint] NULL,
[Beraberlik] [tinyint] NULL,
[Avaraj] [int] NULL,
[AttigiGol] [int] NULL,
[YedigiGol] [int] NULL,
[PuanDurumu] [int] NULL, CONSTRAINT [PK_PuanDurumu] PRIMARY KEY CLUSTERED ([TakimID]ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Tabloları oluşturduktan sonra Takim tablosunun içini doldurabiliriz.

INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (1, N’GALATASARAY’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (2, N’FENERBAHCE’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (3, N’BURSASPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (4, N’KAYSERISPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (5, N’BESIKTAS’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (6, N’TRABZONSPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (7, N’GENCLERBIRLIGI’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (8, N’ISTANBULSPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (9, N’ESKISEHIRSPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (10, N’ANTALYASPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (11, N’GAZIANTEPSPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (12, N’KASIMPASASPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (13, N’MANISASPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (14, N’DIYARBAKIR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (15, N’ANKARAGUCU’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (16, N’SIVASSPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (17, N’DENIZLISPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
INSERT [dbo].[Takim] ([TakimID], [TakimAdi], [OynadigiMac], [Galibiyet], [Malubiyet], [Beraberlik], [Avaraj], [AttigiGol], [YedigiGol], [PuanDurumu]) VALUES (18, N’ANKARASPOR’, 0, 0, 0, 0, 0, 0, 0, 0)
SET IDENTITY_INSERT [dbo].[Takim] OFF

Ligi yeniden simüle etmeden önce mevcut sonuçları temizlemek için kullanılacak yordam:

CREATE PROCEDURE [dbo].[sp_PuanlariSifirla]
— Add the parameters for the stored procedure here
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
truncate table maclar
update Takim set OynadigiMac=0, Galibiyet=0, Malubiyet=0,Beraberlik=0,Avaraj=0,AttigiGol=0,YedigiGol=0,PuanDurumu=0;
END

Olası bütün maçları gösteren sql görünümü için sql sorgusu veya görünüm kullanılabilir.

create view [dbo].[v_OlasiMaclar] as
select evsahibi.TakimID as evsahibid, evsahibi.TakimAdi as evsahibiadi, konuk.TakimID as konukid, konuk.TakimAdi as konukadi, evsahibi.TakimID*100+konuk.TakimID as mackodu from Takim evsahibi cross join Takim konuk where evsahibi.TakimID<>konuk.TakimID

Olası maçları takvime yaymak lazım. Bazı şartları sağlayıp sağlamadığı kontrol edilmemiş ama maçları haftalara yayma işlemini yapan aşağıdaki yordam fikstürü oluşturmak için kullanılıyor.

CREATE PROCEDURE [dbo].[sp_FiksturOlustur]
AS
BEGIN
SET NOCOUNT ON;
truncate table maclar;
select *, 0 as Oynandi, 0 as Secilemez into #tmpMaclar from v_OlasiMaclar
select TakimID, TakimAdi, 0 as Oynadi, 0 as OynadigiMacSayisi into #tmpTakimlar from Takim order by TakimID asc
declare @takimsayisi int,
@macsayisi int,
@evsahibi tinyint,
@misafir tinyint,
@hafta int,
@haftasayac int,
@macid int,
@toplammacsayisi int

set @takimsayisi = (select COUNT(*) from #tmpTakimlar)
set @macsayisi = @takimsayisi / 2
set @hafta = @takimsayisi*2-2
set @haftasayac = 1
set @toplammacsayisi=0
while (((select COUNT(*) from #tmpMaclar)>0) and (@haftasayac <= @hafta))
begin
while ((select COUNT(*) from #tmpTakimlar where (Oynadi = 0) and (OynadigiMacSayisi=(@haftasayac-1))) > 0)
begin
set @evsahibi = (select top 1 TakimID from #tmpTakimlar where oynadi=0 order by NEWID())
set @misafir = (select top 1 TakimID from #tmpTakimlar where TakimID <> @evsahibi and Oynadi=0 order by NEWID())
set @macid = (select mackodu from #tmpMaclar where Evsahibid=@evsahibi and konukid=@misafir)
update #tmpTakimlar set Oynadi = 1, OynadigiMacSayisi=(OynadigiMacSayisi+1) where TakimID=@evsahibi
update #tmpTakimlar set Oynadi = 1, OynadigiMacSayisi=(OynadigiMacSayisi+1) where TakimID=@misafir
insert into Maclar (EvSahibiTakim,RakipTakim,Hafta) values (@evsahibi, @misafir, @haftasayac)
delete from #tmpMaclar where mackodu=@macid
update #tmpMaclar set Secilemez=1 where evsahibid=@evsahibi
update #tmpMaclar set Secilemez=1 where evsahibid=@misafir
update #tmpMaclar set Secilemez=1 where konukid=@evsahibi
update #tmpMaclar set Secilemez=1 where konukid=@misafir
end
update #tmpTakimlar set Oynadi=0
update #tmpMaclar set Secilemez = 0
set @haftasayac=@haftasayac+1
end
drop table #tmpTakimlar
drop table #tmpMaclar
END

Maçları rastgele sırada oynatmak için aşağıdaki saklı yordam kullanılabilir. Bu saklı yordam oluşturulan fikstür üzerinde gezerek maç skorlarını fikstür tablosunda günceller. Maçlar tablosu üzerindeki update trigger’ı vasıtası ile puan cetvelinde gerekli işlemler yapılır.

CREATE PROCEDURE [dbo].[sp_MaclariOynat_v2]
— Add the parameters for the stored procedure here
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
declare @evsahibi tinyint,
@misafir tinyint,
@evsahibigol tinyint,
@misafirgol tinyint,
@oynatalimugurcum cursor
set @oynatalimugurcum = CURSOR FOR select EAttigiGol,RAttigiGol from maclar
open @oynatalimugurcum
fetch next from @oynatalimugurcum into @evsahibigol, @misafirgol
while (@@fetch_status=0)
begin
set @evsahibigol=rand()*10
set @misafirgol=rand()*10
UPDATE Maclar
SET Eattigigol = @evsahibigol,
Rattigigol = @misafirgol
WHERE CURRENT OF @oynatalimugurcum
fetch next from @oynatalimugurcum into @evsahibigol, @misafirgol
end
close @oynatalimugurcum
deallocate @oynatalimugurcum
END

Ve işte malum trigger:

create trigger [dbo].[trig_puantablosunuisle]
on [dbo].[Maclar]
after update
as
begin
— ne yapacak
— kazanan takimin puanini 3 arttiracak, attığı gol varsa yediği gol, averaj değiştirilecek
— beraberlikse her iki takımın puanını 3 arttıracak
— yenilen takımın puanı değişmeyecek
if (select count(*) from inserted) > 1 — eğer birden fazla maçı toplu güncellemek isterse engelle
rollback tran
else
begin
declare @evsahibi tinyint,
@misafir tinyint,
@evsahibigol tinyint,
@misafirgol tinyint
set @evsahibi = (select evsahibitakim from inserted)
set @evsahibigol = (select eattigigol from inserted)
set @misafir = (select rakiptakim from inserted)
set @misafirgol = (select rattigigol from inserted)
if (@evsahibigol =@misafirgol) –her iki takımda bir puan aldı
begin
update takim set puandurumu=puandurumu+1, oynadigimac=oynadigimac+1,beraberlik=beraberlik+1, attigigol=attigigol+@evsahibigol, yedigigol=yedigigol+@evsahibigol where TakimID= @evsahibi
update takim set puandurumu=puandurumu+1, oynadigimac=oynadigimac+1,beraberlik=beraberlik+1, attigigol=attigigol+@evsahibigol, yedigigol=yedigigol+@evsahibigol where TakimID= @misafir
end
else if (@evsahibigol >@misafirgol)
begin
update takim set puandurumu=puandurumu+3, oynadigimac=oynadigimac+1,galibiyet=galibiyet+1, attigigol=attigigol+@evsahibigol, yedigigol=yedigigol+@misafirgol, avaraj=avaraj+@evsahibigol-@misafirgol where TakimID= @evsahibi
update takim set oynadigimac=oynadigimac+1,malubiyet=malubiyet+1, attigigol=attigigol+@misafirgol, yedigigol=yedigigol+@evsahibigol, avaraj=avaraj-@evsahibigol+@misafirgol where TakimID= @misafir
end
else
begin
update takim set puandurumu=puandurumu+3, oynadigimac=oynadigimac+1,galibiyet=galibiyet+1, attigigol=attigigol+@misafirgol, yedigigol=yedigigol+@evsahibigol, avaraj=avaraj-@evsahibigol+@misafirgol where TakimID= @misafir
update takim set oynadigimac=oynadigimac+1,malubiyet=malubiyet+1, attigigol=attigigol+@evsahibigol, yedigigol=yedigigol+@misafirgol, avaraj=avaraj+@evsahibigol-@misafirgol where TakimID= @evsahibi
end
end
end

Aynı lig simülasyonunu yeniden gerçekleştiren son yordamımız:

CREATE PROCEDURE [dbo].[sp_LigiOynat]
— Add the parameters for the stored procedure here
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
exec sp_PuanlariSifirla
exec sp_FiksturOlustur
exec sp_MaclariOynat_v2
END

Ve sonuç:

Devamını bu sql dosyasında bulabilirsiniz.