Miuul Araştırma

T-SQL ile 20 Milyon Satırlık Gerçek Müşteri Verisi Üretme

Bu makalemizde belli bir rastgelelik kuralı ile gerçek insan isimleri ve gerçek adreslerden oluşan 20 milyon satırlık bir müşteri verisetinin T-SQL ile nasıl oluşturulduğunu anlatıyorum.


Görsel: Eugene Pylinsky

Bu makalede gerçek insan isimleri ve gerçek adreslerden oluşan 20 milyon satırlık bir müşteri veriseti oluşturmayı anlatacağım.

Veri bilimini besleyen en önemli şey bildiğiniz gibi verinin kendisi. Tabi gerçek veriden bahsediyorum. Ancak her zaman gerçek veri bulma imkanımız olmuyor. İşte bu yüzden ben gerçek veriye uygun şekilde nasıl veri üretilir onu anlatacağım. Zira bu konu çok önemli. Birçok proje elde veri olmadığı için üzerinde çalışılamıyor. Oysa veri türetmek özünde çok kolay. Ümit ederim bu makale ile sizler ihtiyacınız olan her türlü veriyi üretmek adına aklınızda fikirler oluşturacaktır.

Burada tüm işlemi SQL Server üzerinde TSQL kodları ile gerçekleştireceğiz. Bu vesile ile TSQL’in yeteneklerine de tanık olacağız.

Şimdi gelelim oluşturmak istediğimiz verinin yapısına. Sonuç itibariyle bir müşteri veriseti oluşturacağız. İhtiyaç duyacağımız alanlar aşağıdaki gibi.

Öncelikle bu işlemleri benimle birlikte yapmak için paylaştığım SQL Server backup dosyasını bu linkten indirip kendinize restore etmeniz gerekiyor. Restore işlemi şu şekilde:

Backup dosyası SQL Server 2019 sürümünden alındığı için SQL Server’ınızım 2019 olması gerekiyor. İndirdiğiniz CRM.bak dosyasını kolay bir yere kopyalayın. Örneğin ben c:\material içine kopyaladım. Yedekten dönerken dosyayı bulması kolay oluyor.

Databases üzerinde sağ tık restore database diyoruz.

Device deyip 3 noktaya tıklıyoruz.

Add butonuna tıklıyoruz.

İndirdiğimiz CRM.bak dosyasını seçiyoruz.

OK butonuna basıyoruz ve restore işlemini başlatıyoruz.

Restore işlemi tamamlandı.

Database'imizi başarı ile yedekten döndük ve artı üzerinde çalışabilecek durumdayız.

Şimdi bize bu müşteri verisini oluşturmak için, hem de  20 milyon satırlık oluşturmak için bazı veriler gerekiyor.

Örneğin adsoyad alanını oluşturmak için Türkiye’deki isimlerin bulunduğu bir “Names” tablosu ve Türkiye’deki soyisimlerin bulunduğu bir “Surnames” tablosu. Bu tablolardaki değerleri rastgele birleştirirsek oldukça fazla sanal adsoyad elde edebiliriz. Şanslıyız ki bizim veritabanımızda bu iki tablo da mevcut.

Names tablosunda isimler ve cinsiyet bilgisi var ve satır sayısı 564.

Aynı şekilde Surnames tablosunda da Türkiye’de kullanılan soyisimler var ve onun da sayısı 7.340 satır.

Şimdi belli bir rastgelelik ile bu isim ve soyisimlerden birer tanesini çekersek alın size yeni bir kişi adı ve soyadı.

Bu işlemi TSQL ile aşağıdaki gibi yapabiliyoruz.

[email protected] İSMİNDE BİR DEĞİŞKEN TANIMLIYORUZ.

DECLARE @NAME AS VARCHAR(100)

--RASTGELE OLUŞACAK BİR ID DEĞERİ İÇİN BİR DEĞİŞKEN TANIMLIYORUZ

DECLARE @RANDID AS INT

--RAND() FONKSİYONU İLE 0 İLE 1 ARASINDA ONDALIK BİR RASTGELE DEĞER BULUYORUZ

--BULDUĞUMUZ RASTGELE DEĞER İLE 564 Ü ÇARPTIRARAK RANDID DEĞERİNİ BELİRLİYORUZ

SET @RANDID=RAND()*564

--ELDE ETTİĞİMİZ @RANDID DEĞERİNİ SORGUYA PARAMETRE OLARAK GÖNDERİYORUZ VE RASTGELE BİR KAYIT ÇEKİYORUZ

SELECT TOP 1 @NAME=NAME_ FROM NAMES WHERE [email protected]

--RASTGELE ÇEKTİĞİMİZ BİR SATIRLIK KAYITTAN DÖNEN @NAME DEĞERİNİ VE BULDUĞUMUZ @RANDID DEĞERİNİ ÇEKİYORUZ

SELECT @RANDID AS RANID, @NAME AS NAME_

 

Görüldüğü gibi sorgu sonucunda karşımıza rastgele çıkan ID 306 ve tabloda bu ID ye karşılık gelen isim Erol.

Aynı işlemi “Surnames” tablosu için de yaparak rastgele bir soyisim elde edelim.

[email protected] İSMİNDE BİR DEĞİŞKEN TANIMLIYORUZ.
DECLARE @SURNAME AS VARCHAR(100)

--RASTGELE OLUŞACAK BİR ID DEĞERİ İÇİN BİR DEĞİŞKEN TANIMLIYORUZ
DECLARE @RANDID AS INT

--RAND() FONKSİYONU İLE 0 İLE 1 ARASINDA ONDALIK BİR RASTGELE DEĞER BULUYORUZ

--BULDUĞUMUZ RASTGELE DEĞER İLE 564 Ü ÇARPTIRARAK RANDID DEĞERİNİ BELİRLİYORUZ
SET @RANDID=RAND()*7340

--ELDE ETTİĞİMİZ @RANDID DEĞERİNİ SORGUYA PARAMETRE OLARAK GÖNDERİYORUZ VE RASTGELE BİR KAYIT ÇEKİYORUZ
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]

--RASTGELE ÇEKTİĞİMİZ BİR SATIRLIK KAYITTAN DÖNEN @SURNAME DEĞERİNİ VE BULDUĞUMUZ @RANDID DEĞERİNİ ÇEKİYORUZ
SELECT @RANDID AS RANID, @SURNAME AS SURNAME

Rastgele veri çekmek için bir başka yöntemimiz daha var. O da aşağıdaki gibi Order By NEWID() komutu. Ancak bu komut bizim yöntemimize göre özellikle büyük tablolarda göre daha yavaş çalıştığı için tercih etmiyoruz.

Bu aşamada cinsiyet yani “Gender” alanı da “Names” tablosunda olduğu için onu da aynı şekilde çekebiliriz. “NameSurname” alanı ise Names+’ ‘+Surnames alanlarının birleşimi. Şimdi bu üç bilgiyi çekecek sorguyu yazalım.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
SET @RANDID=RAND()*564
SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]
SET @RANDID=RAND()*7340
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]
SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,@NAME+' '[email protected] AS NAMESURNAME

Görüldüğü gibi rastgele Ad, Soyad, Cinsiyet, AdSoyad alanlarından Eda ÇAĞLIKÇÖSE isimli bir kadın müşteri bilgisi oluştu.

Devam edelim. Sırada doğum tarihi var.  Onun için de bir değişken tanımlayacağız. Doğum tarihi 01.01.1950 ile 01.01.2000 yılları arasında rastgele bir tarih olsun. Bunun için DateAdd fonksiyonunu kullanacağız. DateAdd fonksiyonu bir tarihe gün, ay,yıl gibi değerlerden bir değer ekleyerek yeni bir değer elde etmemizi sağlar.

Örneğin aşağıdaki gibi 01.01.1950 ye 5 yıl ekler isek 01.01.1955 i buluruz. 300 gün ekler isek 28.10.1950 tarihini buluruz.

SELECT DATEADD(YEAR,5,'1950-01-01')

--1955-01-01 00:00:00.000

SELECT DATEADD(DAY,300,'1950-01-01')

--1950-10-28 00:00:00.000

Şimdi bu yöntemi kullanarak rastgele bir doğum tarihi elde edelim. Bunun için 01.01.1950 yılına 50 2000 yılına kadar rastgele bir gün sayısı eklememiz gerekiyor. Aradaki yıl sayısı 50. Gün sayısı ise 365x50. Bunu rastgele getirecek değer ise RAND()*365x50.

Şimdi kodumuzu yazalım.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
DECLARE @BIRTHDATE AS DATE
SET @RANDID=RAND()*564
SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]
SET @RANDID=RAND()*7340
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]
SET @BIRTHDATE = DATEADD(DAY,RAND()*365*50, '1990-01-01')
SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,
@NAME+' '[email protected] AS NAMESURNAME, @BIRTHDATE AS BIRTHDATE

Artık rastgele doğum tarihimiz de oluştu.

Sırada email adresi var. Email adresini şöyle düşünebiliriz. Adın ilk üç harfi,”_” ve soyisim. Tabi burada Türkçe karakterler İngilizceye dönüşecek ve küçük harf yazılacak. Bir de Hotmail, gmail gibi mail sağlayıcılardan herhangi birini rastgele seçmesi gerekiyor.

Öncelikle Türkçe karakter sorununu çözelim. Bunun için bir fonksiyon yazıyoruz.

create FUNCTION [dbo].[TurkishCharacter](@STR AS VARCHAR(1000)) RETURNS VARCHAR(1000)

AS
BEGIN
DECLARE @RESULT AS VARCHAR(1000)
SET @[email protected]
SET @RESULT =REPLACE(@RESULT,'İ','I')
SET @RESULT =REPLACE(@RESULT,'Ç','C')
SET @RESULT =REPLACE(@RESULT,'Ş','S')
SET @RESULT =REPLACE(@RESULT,'Ö','O')
SET @RESULT =REPLACE(@RESULT,'Ğ','G')
SET @RESULT =REPLACE(@RESULT,'Ü','U')
RETURN @RESULT
END

Şimdi fonksiyonumuzu deneyelim.

SELECT DBO.TurkishCharacter('Ömer ÇOLAKOĞLU')

Artık bize ismin ilk üç harfi ve soyadın tamamı gerekiyor. Bunun için Left fonksiyonunu kullanıyoruz.

SELECT Left(DBO.TurkishCharacter('Ömer’),3)

Şimdi artık Soyad ile birleştirerek bir email oluşturabiliriz. Ancak hepsini küçük harfe çevirmemiz gerekiyor. Bunun için kullanacağımız komut ise “Lower” komutu.

SELECT Lower( Left(DBO.TurkishCharacter('Ömer'),3) + '_' + DBO.TurkishCharacter('ÇOLAKOĞLU'))

Email in @’ ten önceki kısmı tamam. Şimdi @’ten sonraki kısmına bakalım. Yani belli bir rastgelelik ile Hotmail,Gmail,Yahoo,Live,Outlook gibi değerler alsın.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
DECLARE @BIRTHDATE AS DATE
DECLARE @EMAIL AS VARCHAR(100)

----NAME----

SET @RANDID=RAND()*564

SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]

---SURNAME--

SET @RANDID=RAND()*7340

SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]

---BIRTHDATE---

SET @BIRTHDATE= DATEADD(DAY,RAND()*365*50, '1990-01-01')

---EMAIL----

DECLARE @RAND AS FLOAT
SET @RAND=RAND()*100
IF @RAND BETWEEN 0 AND 19
SET @EMAIL='@fakehotmail.com'
IF @RAND BETWEEN 20 AND 39
SET @EMAIL='@fakegmail.com'
IF @RAND BETWEEN 40 AND 59
SET @EMAIL='@fakeyahoo.com'
IF @RAND BETWEEN 60 AND 79
SET @EMAIL='@fakeoutlook.com'
IF @RAND BETWEEN 80 AND 89
SET @EMAIL='@fakelive.com'
IF @RAND BETWEEN 90 AND 100
SET @EMAIL='@fakeyahoo.com'

SET @EMAIL=Lower( Left(DBO.TurkishCharacter(@NAME),3) + '_' + DBO.TurkishCharacter(@SURNAME))[email protected]

SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,

@NAME+' '[email protected] AS NAMESURNAME, @BIRTHDATE AS BIRTHDATE,@EMAIL AS EMAIL

Email işini de hallettikten sonra sırada TC Kimlik numarası var. Bu işlem için içinde 30 milyon satır TC Kimlik numarası algoritmasına göre oluşturulan rastgele kimlik numaralarının olduğu bir tablo oluşturdum. Müşteri oluştururken bu tablodan kayıt çekeceğiz.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
DECLARE @BIRTHDATE AS DATE
DECLARE @EMAIL AS VARCHAR(100)
DECLARE @TCNUMBER AS VARCHAR(11)
----NAME----
SET @RANDID=RAND()*564
SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]
---SURNAME--
SET @RANDID=RAND()*7340
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]
---BIRTHDATE---
SET @BIRTHDATE=DATEADD(DAY,RAND()*365*50,'1990-01-01')
---EMAIL----
DECLARE @RAND AS FLOAT
SET @RAND=RAND()*100
IF @RAND BETWEEN 0 AND 19
SET @EMAIL='@fakehotmail.com'
IF @RAND BETWEEN 20 AND 39
SET @EMAIL='@fakegmail.com'
IF @RAND BETWEEN 40 AND 59
SET @EMAIL='@fakeyahoo.com'
IF @RAND BETWEEN 60 AND 79
SET @EMAIL='@fakeoutlook.com'
IF @RAND BETWEEN 80 AND 89
SET @EMAIL='@fakelive.com'
IF @RAND BETWEEN 90 AND 100
SET @EMAIL='@fakeyahoo.com'

SET @EMAIL=Lower( Left(DBO.TurkishCharacter(@NAME),3) + '_' + DBO.TurkishCharacter(@SURNAME))[email protected]
--TCNUMBER---
SELECT @TCNUMBER=TCNUMBER FROM TCNUMBERS WHERE ID=1

SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,
@NAME+' '[email protected] AS NAMESURNAME, @BIRTHDATE AS BIRTHDATE,@EMAIL AS EMAIL,
@TCNUMBER AS TCNUMBER

TC Kimlik numaralarının bulunduğu tablo zaten rastgele bir sırada olduğu için döngü ile ID değeri sıradaki değeri alacağız. Yani tekrardan rastgele bir kayıt çekip 30 milyon satırda işi uzatmaya gerek yok. Ben burada örnek olması açısından ID=1 dedim.

Bu arada bu TCKimlik numaraları da bir algoritma ile rastgele oluşturuldu. Yani herhangi bir yerden alınma değil. Olur da kendi numaranıza denk gelirseniz şaşırmayın. ☺

Bir sonraki adım telefon numarası. Şanslısınız çünkü bunun için de bir tablomuz var ve içinde farklı operatörler için oluşturulmuş 30 milyon telefon numarası var. Bu tabloda Guid alanını rastgelelik sağlaması için ekledim. Şu aşamada bizim için çok önemli değil. Yine hatırlatayım bu numaralar tamamen rastgele olarak oluşturuldu. Bir yerden alınma değil. Aman ha KVKK falan başımız derde girmesin. ☺

Şimdi TC Kimlik numarası ile aynı mantıkta telefon numarası seçelim.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
DECLARE @BIRTHDATE AS DATE
DECLARE @EMAIL AS VARCHAR(100)
DECLARE @TCNUMBER AS VARCHAR(11)
DECLARE @TELNR AS VARCHAR(15)

----NAME----
SET @RANDID=RAND()*564
SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]
---SURNAME--
SET @RANDID=RAND()*7340
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]
---BIRTHDATE---
SET @BIRTHDATE=DATEADD(DAY,RAND()*365*50,'1990-01-01')
---EMAIL----
DECLARE @RAND AS FLOAT

SET @RAND=RAND()*100
IF @RAND BETWEEN 0 AND 19
SET @EMAIL='@fakehotmail.com'
IF @RAND BETWEEN 20 AND 39
SET @EMAIL='@fakegmail.com'
IF @RAND BETWEEN 40 AND 59
SET @EMAIL='@fakeyahoo.com'
IF @RAND BETWEEN 60 AND 79
SET @EMAIL='@fakeoutlook.com'
IF @RAND BETWEEN 80 AND 89
SET @EMAIL='@fakelive.com'
IF @RAND BETWEEN 90 AND 100
SET @EMAIL='@fakeyahoo.com'

SET @EMAIL=Lower (Left(DBO.TurkishCharacter(@NAME),3) + '_' + DBO.TurkishCharacter(@SURNAME)) + @EMAIL
--TCNUMBER---
SELECT @TCNUMBER=TCNUMBER FROM TCNUMBERS WHERE ID=1
--TELNR---
SELECT @TELNR=TELNR FROM TELNUMBERS WHERE ID=1

SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,
@NAME+' '[email protected] AS NAMESURNAME, @BIRTHDATE AS BIRTHDATE,@EMAIL AS EMAIL,
@TCNUMBER AS TCNUMBER,@TELNR AS TELNR

Sırada adres bilgilerimiz var. İl, ilçe, mahalle, sokak, posta kodu ve açık adres. Yine çok şanslısınız ki, sizin için bunun verisini de hazırladım. Sokak detayına kadar içinde bu bilgilerin yer aldığı bir tablom var ve 856.682 satır.

Bazı mahallelerin posta kodunu eşleştiremedim ama onun dışında gayet güzel bir veri seti.

Yeni değişkenlerimizi tanımlıyoruz.

DECLARE @CITY AS varchar(50)
DECLARE @TOWN AS varchar(50)
DECLARE @DISTRICT AS varchar(50)
DECLARE @STREET AS varchar(50)
DECLARE @POSTALCODE AS varchar(50)
DECLARE @ADDRESSTEXT AS varchar(500)

Şimdi bunlara değer atayalım.

DECLARE @GENDER AS VARCHAR(1)
DECLARE @NAME AS VARCHAR(100)
DECLARE @SURNAME AS VARCHAR(100)
DECLARE @RANDID AS INT
DECLARE @BIRTHDATE AS DATE
DECLARE @EMAIL AS VARCHAR(100)
DECLARE @TCNUMBER AS VARCHAR(11)
DECLARE @TELNR AS VARCHAR(15)
DECLARE @CITY AS varchar(50)
DECLARE @TOWN AS varchar(50)
DECLARE @DISTRICT AS varchar(50)
DECLARE @STREET AS varchar(50)
DECLARE @POSTALCODE AS varchar(50)
DECLARE @ADDRESSTEXT AS varchar(500)

----NAME----
SET @RANDID=RAND()*564
SELECT TOP 1 @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]

---SURNAME--
SET @RANDID=RAND()*7340
SELECT TOP 1 @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]

---BIRTHDATE---
SET @BIRTHDATE=DATEADD(DAY,RAND()*365*50,'1990-01-01')

---EMAIL----
DECLARE @RAND AS FLOAT
SET @RAND=RAND()*100
IF @RAND BETWEEN 0 AND 19
SET @EMAIL='@fakehotmail.com'
IF @RAND BETWEEN 20 AND 39
SET @EMAIL='@fakegmail.com'
IF @RAND BETWEEN 40 AND 59
SET @EMAIL='@fakeyahoo.com'
IF @RAND BETWEEN 60 AND 79
SET @EMAIL='@fakeoutlook.com'
IF @RAND BETWEEN 80 AND 89
SET @EMAIL='@fakelive.com'
IF @RAND BETWEEN 90 AND 100
SET @EMAIL='@fakeyahoo.com'

SET @EMAIL=Lower( Left(DBO.TurkishCharacter(@NAME),3) + '_' + DBO.TurkishCharacter(@SURNAME))[email protected]
--TCNUMBER---
SELECT @TCNUMBER=TCNUMBER FROM TCNUMBERS WHERE ID=1
--TELNR---
SELECT @TELNR=TELNR FROM TELNUMBERS WHERE ID=1
--ADDRESS--
SET @RANDID=RAND()*856682

SELECT  TOP 1   @TOWN=TOWN,@DISTRICT=DISTRICT,@STREET= STREETNAME, @POSTALCODE=POSTALCODE, @ADDRESSTEXT= ADDRESSTEXT
FROM STREETS
WHERE ID= @RANDID

SELECT @NAME AS NAME_,@SURNAME AS SURNAME,@GENDER AS GENDER,
@NAME+' '[email protected] AS NAMESURNAME, @BIRTHDATE AS BIRTHDATE,@EMAIL AS EMAIL,
@TCNUMBER AS TCNUMBER,@TELNR AS TELNR,
@TOWN AS TOWN,@DISTRICT AS DISTRICT,@STREET AS STREETNAME,@POSTALCODE AS POSTALCODE,@ADDRESSTEXT AS ADDRESSTEXT

Adres bilgisini de bu şekilde alabildik. Ancak olayın gerçek olması adına buradaki rastgeleliğe müdahele etmemiz gerekiyor. Zira gerçek bir müşteri verisi oluşturacaksak illerdeki nüfus oranına göre bir dağılım yapmamız gerekiyor. Bunun için bir tablo oluşturdum ve nüfus oranına göre bir dağılım yaptım. Ayrıca her bir şehirdeki sokakların başlangıç ID sini ve bitiş ID sini de bir tabloda tuttum ki bu sayede çok performanslı şekilde veri çekebildim ve rastgele adres çekme işini aşağıdaki gibi gerçekleştirdim.

0 ile 100 arasında çektiğim rastgele bir değer
0-18,49 arasında ise şehir İstanbul
18,49-25,26 arasında ise Ankara
25,26 -30,52 arasında ise İzmir

….

45,2 -47,71 arasında ise Gaziantep olarak belirlendi.

Böylece İstanbul’dan bir müşterinin oluşma olasılığı Gaziantep’te oluşmasından neredeyse 9 kat daha fazla.

Ayrıca Street tablosu default olarak İl,İlçe,Mahalle sırasına göre kayıtlı. Yani otomatik artan ID alanım da bu sıraya göre yapılmış durumda.

O yüzden her bir şehir için başlangıç ve bitiş ID lerini de bir tabloda tuttum. Bu yönteme, detaya ne gerek var diyebilirsiniz belki. Ama inanın standart order by NEWID() ile çekilen random işleme göre yaklaşık 1.000 kat daha hızlı çalışıyor. Böylece hızlı bir şekilde 20 milyon satır oluşturabiliyoruz.

SELECT  @CITY=CITYNAME
FROM CITYPOPULATION WHERE @RAND BETWEEN MINLIMIT AND MAXLIMIT
DECLARE @MINID INT
DECLARE @MAXID INT
SELECT @MINID=MINID,@MAXID=MAXID FROM STREETCOUNT WHERE [email protected]

SET @RANDID=RAND()*(@[email protected])

SELECT  TOP 1   @TOWN=TOWN,@DISTRICT= DISTRICT,@STREET=STREETNAME,@POSTALCODE= POSTALCODE,@ADDRESSTEXT= ADDRESSTEXT
FROM STREETS
WHERE ID= @[email protected]

Artık tüm değişkenlerimiz oluştuğuna göre içeriye insert etme işlemi kaldı. Onun da kodu aşağıdaki gibi.

INSERT INTO CUSTOMERS ( [NAME_], [SURNAME], [NAMESURNAME], [GENDER], [BIRTHDATE], [EMAIL], [TCNUMBER], [TELNR], [CITY], [TOWN], [DISTRICT], [STREET], [POSTALCODE], [ADDRESSTEXT])
VALUES
( @NAME, @SURNAME, @NAMESURNAME, @GENDER, @BIRTHDATE, @EMAIL, @TCNUMBER, @TELNR, @CITY, @TOWN, @DISTRICT, @STREET, @POSTALCODE, @ADDRESSTEXT)

SET @[email protected]+1
END

 

Tüm adımları anlattık. Sırada bu işlemleri tek parçada yazmak  kaldı. Buyrunuz. O da burada.

--TABLOYU TEMİZLİYORUZ
TRUNCATE TABLE CUSTOMERS
DECLARE @I AS INT=1
--1 MİLYON SATIR İÇİN ÇALIŞTIRIYORUZ.
WHILE @I<=1000000
BEGIN
--DEĞİŞKEN TANIMLARI
DECLARE @NAME AS varchar(50)
DECLARE @SURNAME AS varchar(50)
DECLARE @NAMESURNAME AS varchar(100)
DECLARE @GENDER AS char(1)
DECLARE @BIRTHDATE AS DATE
DECLARE @EMAIL AS varchar(50)
DECLARE @TCNUMBER AS char(11)
DECLARE @TELNR AS varchar(15)
DECLARE @CITY AS varchar(50)
DECLARE @TOWN AS varchar(50)
DECLARE @DISTRICT AS varchar(50)
DECLARE @STREET AS varchar(50)
DECLARE @POSTALCODE AS varchar(50)
DECLARE @ADDRESSTEXT AS varchar(500)
DECLARE @RANDID AS INT

--NAME ALANI İÇİN RASTGELE ID OLUŞTURUYORUZ.
SET @RANDID=RAND()*564
SELECT @NAME=NAME_,@GENDER=GENDER FROM NAMES WHERE [email protected]

20% İHTİMALLE İKİNCİ İSMİ OLMA OLASILIĞINA GÖRE İKİNCİ İSİM OLUŞTURUYORUZ
SET @RANDID=RAND()*10
IF @RANDID<3>
BEGIN
SELECT TOP 1 @[email protected]+' '+NAME_ FROM NAMES WHERE [email protected] ORDER BY NEWID()
END

--SURNAME ALANI İÇİN RASTGELE ID OLUŞTURUYORUZ
SET @RANDID=RAND()*7340
SELECT @SURNAME=SURNAME FROM SURNAMES WHERE [email protected]

--NAMESURNAME ALANINI NAME VE SURNAME ALANLARINI BİRLEŞTİREREK ÇEKİYORUZ
SET @[email protected]+' '[email protected]

--EMAIL İÇİN İSİMİN İLK 3 HARFİ VE SOYİSİM BİLGİSİNİ ALIYORUZ.
set @EMAIL=LOWER (dbo.TurkishCharacter(Left(@NAME,3) + '_' + @SURNAME)[email protected])

--RASTGELE BİR MAIL SAĞLAYICI SEÇİYORUZ
DECLARE @RAND AS FLOAT
SET @RAND=RAND()*100
IF @RAND BETWEEN 0 AND 19
SET @[email protected]+'@fakehotmail.com'
IF @RAND BETWEEN 20 AND 39
SET @[email protected]+'@fakegmail.com'
IF @RAND BETWEEN 40 AND 59
SET @[email protected]+'@fakeyahoo.com'
IF @RAND BETWEEN 60 AND 79
SET @[email protected]+'@fakeoutlook.com'
IF @RAND BETWEEN 80 AND 89
SET @[email protected]+'@fakelive.com'
IF @RAND BETWEEN 90 AND 100
SET @[email protected]+'@fakeyahoo.com'

--DOĞUM GÜNÜ İÇİN 01.01.1950 VE 01.01.2000 YILLARI ARASINDA RASTGELE BİR GÜN SEÇİYORUZ.
SET @BIRTHDATE=DATEADD(DAY,(365*50*RAND()),'19500101')
SELECT @TELNR=TELNR FROM TELNUMBERS  WHERE [email protected]
SELECT @TCNUMBER=TCNUMBER FROM TCNUMBERS WHERE [email protected]

--ADRES İÇİN HANGİ ŞEHİRDEN OLACAĞINI NÜFUS İLE DOĞRU ORANTILI OLARAK RASTGELE ÇEKİYORUZ
SELECT  @CITY=CITYNAME
FROM CITYPOPULATION WHERE @RAND BETWEEN MINLIMIT AND MAXLIMIT

--BULUNAN ŞEHRİN STREETS TABLOSUNDA BAŞLANGIÇ VE BİTİŞ ID DEĞERLERİNİ BULUYORUZ.
DECLARE @MINID INT
DECLARE @MAXID INT
SELECT @MINID=MINID,@MAXID=MAXID FROM STREETCOUNT WHERE [email protected]

--BULDUĞUMUZ BAĞLANGIÇ VE BİTİŞ ID LERİ ARASINDA RASTGELE BİR ID SEÇİYORUZ.
SET @RANDID=RAND()*(@[email protected])

--RASTGELE BULDUĞUMUZ ID DEN İLÇE,MAHALLE,SOKAK,POSTA KODU VE AÇIK ADRES ALANLARINI ALIYORUZ.
SELECT  TOP 1   @TOWN=TOWN, @DISTRICT=DISTRICT, @STREET= STREETNAME, @POSTALCODE = POSTALCODE, @ADDRESSTEXT=ADDRESSTEXT
FROM STREETS
WHERE ID= @[email protected]

--

INSERT INTO CUSTOMERS
( NAME_, SURNAME, NAMESURNAME, GENDER, BIRTHDATE, EMAIL, TCNUMBER, TELNR, CITY, TOWN, DISTRICT, STREET, POSTALCODE, ADDRESSTEXT)
VALUES
(@NAME, @SURNAME, @NAMESURNAME, @GENDER, @BIRTHDATE, @EMAIL, @TCNUMBER, @TELNR, @CITY, @TOWN, @DISTRICT, @STREET, @POSTALCODE, @ADDRESSTEXT)
SET @[email protected]+1
END

Buradaki kod bloğunda 1 milyon satır için müşteri oluşturuyor. Bu işlem bende 4:39 dakika  sürdü ve gördüğünüz gibi 1 milyon satırlık müşteri listemiz oluştu.

Bu hesapla 20 milyon satırlık bir tablonun da yaklaşık 80 dakika süreceğini varsayabilirsiniz. Ancak ben bunun için bir stored procedure oluşturdum ve içine aldığı parametreler ile eş zamanlı 10 session da 2 şer milyon kayıt oluşturacağım.

Şimdi bu procedure'ümüzü çalıştıralım. Gördüğümüz gibi 10 farklı session da eş zamanlı 2 milyon kayıt oluşturuyoruz.

Veee sonunda 20 milyon müşteri kaydımız oluştu. Bu işlem yaklaşık 19 dakika gibi bir sürede tamamlandı. Paralel olarak 10 oturumda gerçekleştirmiştik. Bu kadar rastgeleliği sağlayan bir yapıda 20 milyon tekil müşteri için oldukça güzel bir süre.

Şimdi de birkaç istatistik değerine bakalım.

Şehirlere göre müşteri sayısı. Rakamlar tutarlı.

Cinsiyete göre müşteri sayısı. Kadınlar az farkla önde görünüyor.

Telefon operatörüne göre müşteri sayısı.

Yaşa göre müşteri sayısı.

Rakamlar gayet tutarlı görünüyor.

Umarım beğendiğiniz bir anlatım olmuştur. Bir sonraki makalede görüşmek dileğiyle.

Sağlıcakla kalın.

BigQuery ML ile Çok Terimli Regresyon

Cem İstanbullu

Power BI ile CRM Analitiği - IV: Müşteri Yaşam Boyu Değeri (CLTV) Tahmini

Melda Özdin

R ile Yüksek Performanslı Programlama - I

Onur Boyar

SON ARAŞTIRMALAR

GİRİŞ

Aşağıya kaydolduğunuz e-postayı girin. Şifrenizi sıfırlamanız için size bir bağlantı göndereceğiz.

Giriş Ekranına Dön
Şifre sıfırlama talimatları
adresine gönderildi.