SQL ile Covid-19 Aşılama Verilerinin Analizi

Çalışmaya başlarken T.C. Sağlık Bakanlığı'nın Covid-19 aşılanma verilerini paylaştığı sayfanın HTML bilgilerini parse ederek SQL databaseine aktaralım.

Uygulama

Hazırladığımız uygulama yaklaşık üç dakikada bir sürekli çalıştı ve sayfadaki verileri parçalayıp tarih/saat bilgisi ile birlikte veri tabanına aktardı. 

Her üç dakikada 81 şehre ait kayıt geldiğini düşünürsek:

• 1 saatte 81x20 = 1620
• 1 günde 81x20x24 = 38 880 
• 1 haftada 81x20x24x7 = 272 160 
• 1 ayda 81x20x24x30 = 1 166 400  

satırlık bir veri toplama durumundan bahsediyoruz. 

Uygulamanın çalışmaya başlama tarihi 17.06.2021 ve son çalışma tarihi ise 25.08.2021. Yani toplam 69 gün çalışmış ve yaklaşık üç milyon satır veri kaydetmiş. Bu veri tabanını SQL Server yedek dosyası olarak bağlantıdan indirebilirsiniz. 

Peki bu verilerde neler yer alıyor?

• Tarih (DATE_)
• Şehir (CITY)
• Anlık toplam yapılan 1. doz sayısı (_1DOSE)
• Anlık toplam yapılan 2. doz sayısı (_2DOSE)
• Anlık toplam yapılan toplam doz sayısı (_TOTAL) 
• İlgili şehrin nüfusu (POPULATION)

Uygulama bu verileri kayıt altına alıyor. Ayrıca 81 il için bu şekilde her toplu çalışmaya da bir numara veriliyor. (SEQID)

Örneğin 5412 numaralı aktarım işlemi 26.06.2021 tarihinde saat 22:41:31'de gerçekleşmiş.

Burada sistemi sürekli olarak internet sitesinden takip etmiş olsak sadece son durumu görebilecektik. Oysa şimdi üç dakikada bir aradaki farkı görebilir durumdayız. Şimdi bir sonraki SEQID değeri olan 5413'e bakalım. 

Görüldüğü gibi tam üç dakika sonra gerçekleşen bir aktarım.

Örneğin burada Ankara'ya baktığımızda 1. doz sayısının 2 652 259 olduğunu görüyoruz. Bir önceki veriye baktığımızda ise 1. doz sayısının 2 652 085 olduğu anlaşılıyor.

Kısaca üç dakika içerisinde Ankara'da yapılan 1. doz aşı sayısının 2 652 259 - 2 652 085 = 174 olduğu anlaşılıyor. Aynı hesap 2. doz için de yapılır ise 1 241 916 - 1 241 906 = 10 sayısına ulaşıyoruz. Toplam için yaparsak üç dakikada 3 894 175 - 3 893 991 = 184 aşı yapıldığı görülüyor.

Bu noktada bu farkları alabilir hale gelirsek saat bazlı, gün bazlı, il bazlı aşı sayılarını görebiliriz. Bunun içinse her bir satırın kendisinden önceki satırı bulmamız gerekir. Bu yüzden tabloya bir PREVID alanı ekliyoruz ve bu alanı update ediyoruz.

Bu komutu çalıştırdıktan sonra artık her bir şehrin kayıt bilgisinin bir önceki kayıt bilgisinin ID’si PREVID olarak update edilmiş durumda.

Şimdi aradaki farkı buldurup bu üç dakikalık zaman diliminde ne kadarlık aşı yapıldığı bilgisini hesaplamamız gerekiyor. 

Bunun için 1. doz, 2. doz ve toplam için DIFF_1DOSE, DIFF_2DOSE, DIFF_TOTAL alanlarını tablomuza ekliyoruz. 

Sırada bu alanları güncelleme işlemi var. Bunun için ise aşağıdaki komutu kullanacağız. Her doz için kendisinden önceki satırı bulup aradaki farkı DIFF alanlarına yazdıracağız. 

Nihayetinde baktığımızda bu değerleri dolu şekilde görebileceğiz. 

Şimdi Ankara’da 1 günde yapılan aşı sayısına bakalım. Örneğin seçtiğimiz gün 12.07.2021 olsun. Seçtiğimiz günde Ankara’da ne kadar aşı yapıldığına şu şekilde ulaşabiliriz:

Bir de diğer şehirlerde seçilen bu günde ne kadar aşılama yapılmış bakalım. CITY alanının filtresini kaldırdığımızda görebiliyoruz. 

Bir başka örnek kapsamında Ankara'da incelemeye alınan zaman dilimi içerisinde ne kadar aşılama yapıldığına bakalım. 

Bu sırada ilk kayıtlara baktığımızda kendilerinden önce kayıt olmadığı için fark alanlarının null olduğunu görebiliyoruz. 

Bu kayıtların DIFF değerlerini mevcut değerler ile güncelliyoruz. 

Null değerlerden kurtulduk.

Şimdi ise başka bir sorguya dönelim ve ilgili zaman diliminde tüm şehirlerde toplamda ne kadar aşılama yapıldığına bakalım. 

Elimizde şehirlerin nüfus bilgisini tuttuğumuz POPULATION alanı var. Buna göre nüfusun ne kadarının aşılanmış olduğunu da inceleyebiliriz. Burada dikkat edilmesi gereken husus ise şu: POPULATION alanı şehrin tüm nüfusunu gösteriyor. Sağlık Bakanlığı verilerinde kullanılan nüfus alanı ise 18 yaş üstü olduğu için burası ile sitedeki oranların tam olarak yüzdesel tutmasını beklememek gerekir. 

Bir başka sorgu kapsamında hangi saatlerde daha çok aşılama yapıldığını inceleyelim.

Sorgu sonucunda 10:00-16:00 arasının yoğun aşılama saatleri olduğunu görüyoruz. 

Peki haftanın hangi günü daha yoğun? Burada ise görüldüğü gibi pazartesi ve cuma günlerinde ciddi bir yoğunluk var. 

Bu veriler tabii ki kusursuz değil. Zira uygulamayı çalıştırdığım bilgisayar bir bayram tatilinde update alıp kendini restart etmiş. Bu nedenle 4-5 günlük bir veri kaybı söz konusu Onunla alakalı da yakın bir zamanda belli bir örnekleme üzerinden o boşluğu doldurma çalışması yapmayı düşünüyorum.

Peki bu verileri görselleştirsek nasıl durur? 

DevExpress Dashboard yardımıyla örnek bazı raporlar hazırladım. Siz de gerek DevExpress Dashboard üzerinde gerekse PowerBI gibi araçlar ile kendi görsellerinizi oluşturabilirsiniz. 

SQL hakkında daha geniş kapsamlı bilgiye erişmek ve kariyerinizde SQL bilginizle fark yaratmak isterseniz Miuul'un sunduğu MS SQL Developer eğitimine göz atabilirsiniz.