4. BÖLÜM : BASİT SORGULAR YARATMAK

4.1.Giriş

Sorgu (query) belirli bir data seti üzerinde odaklanmayı sağlayan özelliklerin oluşturduğu

kümedir. Bu sorgu özelliklerini oluşturabilmek için filtreleme ve sıralama görevi(filter and

sort task) ile sorgu yapıcı (query builder) kullanılabilir.

Filtreleme ve sıralama görevi satır, sütun seçerek ve sıralama yaparak yeni bir sas çizelgesi

oluşturmanıza izin verir. Sorgu yapıcı ise bu işlemlere ek olarak, yeni sütunlar oluşturmanıza,

tablolar eklemenize, gruplama yapmanıza, özetlemenize ve sütunların niteliklerini

değiştirmenize izin verir.

4.2. Veriyi Filtrelemek ve Sıralamak

Filtreleme ve Sıralama : filtreleme

Basit filtreler değişken isimleri, operator ve veri değerleri için yapılmıştır. Daha karmaşık

filtreleme için “advanced edit” seçeneğini seçiniz.

1

2

Filtreleme ve Sıralama : sıralama

Verileri azalan ya da artan olarak sıralayabilirsiniz.

SÜTUNLARI SEÇMEK VE SATIRLARI FİLTRELEMEK

Orion Stars şirketi 2005 yılından itibaren olan internet satışlarını analiz etmek istiyor. Bunun

için de firma 1 ocak 2005 ve sonrasındaki internet siparişlerini içeren yeni bir veri kaynağı

oluşturacaktır.

Bu işlemler filter and sort task veya query builder kullanılarak yapılabilir.

1. File-New-Project komutlarıyla yeni bir proje oluşturalım. Orders tablosunu eklemek

için File-Open-Data yolunu izleyerek verinin kaynağını bulalım. Orders tablosunu

seçin ve Open komutu seçin.

2. Filter and sort’ u açın.

3. Bütün değişkenleri seçtikten sonra Emloyee_ID=999999999 olduğundan seçilmez.

3

4. Aşağıda gösterilen filtreler tanımlanır.

4

5. Sort kısmını seçin. Order Date değişkenini sıra değişkeni olarak işaretleyin.

Sıralama seçeneğini descending olarak değiştirin.

6. OK tıklayın ve sonuçları kontrol edin.

5

7. Query Builder da benzer sonuçları elde etmek için kullanılabilir. Orders tablosunu

açın ve data kısmından Query Builder ı tıklayın (ya da orders a sağ tıklayıp Query

Builder ı seçin).

8. Query name yerine Internet2005+Orders Query ve output name yerine

internet2005 yazınız. Change i tıklayarak değiştirip Save e tıklayınız.

9. Tüm sütunları seçebilmek için t1(orders) ı seçin, sürükleyip Select Data kısmına atın.

Remove simgesini kullanarak Employee_ID sütununu kaldırın.

Sütunlar çift tıklayarak ya da sürükleyip atarak eklenebilir. Birden fazla sütun seçim

CTRL yi basılı tutup seçim yaparak ve SHIFT ile sütunların aralığını belirleyerek

yapılabilir.

6

10. Sütun özellikleri select data kısmından görülebilir ve değiştirilebilir. Order Date ı

tıklayın ve properties simgesini seçin.

11. Bu sütuna format uygulayabilmek için change i seçin. Açılan pencerede categories

kısmından Date i ve formats kısmından MMDDYYw.d yi seçin. Overall width

seçeneğini 10 olarak değiştirin.

7

12. Verilen tarihten sonraki siparişleri dahil edebilmek için Filter data kısmını seçin.

Order Type değişkenini sürükleyip Filter data nın içine atın.

13. İlk adımda işlemi Equal to olarak değiştirin ve value field kısmına 3 yazın. Next i

tıklayın ve Finish i seçin.

8

14. Filter data kısmına Order Date i atın. İlk adımda işlemi greater than or equal to

olarak değiştirin ve “02APR2005” yazın. Next- Finish komutlarını seçin.

15. Sort data kısmını seçin ve Order Date atın. Sıralama seçeneğini descending olarak

değiştirin.

9

16. Run komutunu seçin.

17. Projeyi Demo C4 olarak kaydedin.

4.3 Bir Tanımlamayla Yeni Sütunlar Oluşturmak

Orion Star yükleme metotlarını her sipariş ve ulaşım günü arasında kaç gün geçtiğini

saptayarak analiz etmek istiyor. Ayrıca firma mağaza fiyatıyla, yükleme giderlerinin toplam

fatura miktarlarını hesaplamak istiyor.

1. Demo C4 projesini açın, Query Builder ı seçin ( Demo C4 ü kaydetmediyseniz yeni

bir proje açıp orders tablosunu ekleyin.)

2. Query name alanına Shipping Detail Query yazın. Change i seçin ve Output name

alanına shipping yazın.

10

3. Select data kısmının aktif olduğundan emin olun. Bu sütunları ekleyin: Order_ID,

Order_Date, Delivery_Date, Product_ID, Total, Retail_Price, Shipping ve Profit.

4. Yeni bir sütun ekleyebilmek için Add a new computed coloumn simgesini seçin ya

da Computed Columns-New komutlarını izleyin.

5. İlk adımda Advanced expression-next seçin.

6. Delivery Date i tanımlamaya eklemek için çift tıklayın.Eksi işareti girin ve Order

Date e çift tıklayarak tanımlamayı bitirin.Next seçin.

11

7. Column Name alanına Days to Deliver yazın.

8. 4. Adımda yeni sütunun özelliklerine bakın ve Finish seçin.

Yapılan satışların toplam fatura bilgilerini bulmak için başka bir sütun oluşturalım.

12

9. Invoice_Amount sütunu için bu süreci tekrar edin.

10. Birinci adımda Advanced expression-Next seçin.

11. Functions sekmesinden SUM ı bulun ve çift tıklayarak tanımlama olarak ekleyin.

12. SUM fonksiyonunun içine Total Retail Price ı ve Shipping i aralarına virgül koyarak

ekleyin.

13. Next seçin. Sütun adı alanına invoice_amount yazın, Change i seçin. Format

penceresinde categories kısmından Currency i ve formats kısmından DOLLARw.d

yi seçin. Aralığı 8 ve virgülden sonraki basamak sayısını 2 yapın. OK-Next seçin.

13

14. Özellikleri doğrulayın ve Finish seçin.

15. Sort data sekmesine Days to Deliver ekleyip descending seçin.

16. Run ı tıklayın.

14

4.4. Bir Sorguda Veri Gruplama ve Özetleme Amaçlar:

Sorguda gruplamaya değişken atamak Analizi değişken ve özet istatistik seçmek Gruplandırılmış veriyi filtrelemek

Orion Star, en yüksek fiyatlı ürünü öne çıkaran bir satış promosyon teklifi yapmak istiyor.

Şirket toplam karı 500 doları aşan malların listesini de elde etmek istiyor.

1. Demo c4 projesinden sağ tıklayarak orders ı ve Query Builder ı seçin.

2. Query name yerine Top Products Query ve Output name yerine topproducts

yazınız.

3. Select Data sekmesinden Product ID ve Profit i çift tıklayarak aynı sütuna ekleyin.

4. Summary sütununu tıklayın ve SUM ı seçin.

15

5. Computed columns’da _Calculation üzeirnde sağ klikleyerek Filter Summarized

Data’yı seçin.

6. Column Name yerine Sum_of_Profit yazın. Operator bölümünden Greater than i

seçip value kısmına 500 yazınız. Finish i tıklayın.

16

7. Sort Data sekmesini seçip Calculation ı Descending olarak ayarlayın.

8. Run a tıklayın ve Demo c4 e kaydedin.

17

4.5 Tabloları Birleştirme

Bir önceki bölümde $500 aşan ürünlerde toplam kar belirlendi. Analistler ürün, tedarikçi ve

ülke adı da dahil olmak üzere bu üst ürünler hakkında daha fazla bilgi istedi.

1.Demo c4 projesini açın ve topproducts tablosunu ekleyin.

2.Products ı ekleyin.

18

3. country lookup.xlsx dosyasını ekleyin. First row of range contains field names

kutucuğunu seçin.

4. topproducts ın sağına tıklayarak Query Builder i seçin.

5.Query name yerine Top Products Info Query ve output name e topproductsinfo yazın.

6.Add Tables a basıp, ctrl ye basılı tutarak, products ve countrylookup ı seçin.

7. Böylece topproducts datasına eklenmiş olur. OK a tıklayın .

8.The Tables and Joins penceresi otomatik olarak açılır. Tabloları eklemek için ilk olarak

Supplier Country i products table dan seçin. İkinci kez Supplier Country i sürükleyip

Country_Key e atalım.

19

9.Join Tables sekmesinden Matching rows only given a condition (Inner Join) seçin ve

t2.Supplier_Country=t3.Country_Key ve OK a basın.

20

10.Select Data sekmesine geri dön ProductID , Sum of Profit, Product Category, Product

Name, Supplier Name, Country Name çift tıklayarak atın.

11. Run a basın ve Demo c4 e kaydedin.

4.6. Eşleşmeyen Satırları İçeren Tabloları Birleştirme

Orion Yıldız da Pazarlama Bölümü son zamanlarda sipariş vermeyen müşterilerin

belirlenmesini istiyor.

21

1.Demo c4 projesini açın, customers ve orders datalarını ekleyin.

2.Project Tree ya da Process Flow dan costumers seçip sağ tıklayıp Query Builder ı seçin.

Query name i Inactive Costumers Query ve tabloyu inactive_costumers olarak

isimlendirin.

3.Add Tables tan orders ı seçerek ekleyin.

4.Join Tables ı seçin.

5.Costumers ve orders ın arasına sağ tıklayıp properties i seçin.

6. costumer ID ve orders arasında All rows from the left table given a condition (left join)

seçip OK u tıklayın.

22

7.Select data dan çift tıklayarak Costumer ID (costumerstan), Costumer Country,

Costumer Gender, Costumer Name, Costumer Age Group, Costumer Type ve Costumer

ID(orderstan) ekleyin .

23

8. Run ı tıklayın. Costumer ID1 de kayıp değerler olduğunu görüyoruz.

24

9.Modify Task ı seçip Filter Data sekmesinden Costumer ID(orderstaki) i sürükleyerek

atıyoruz. 10.Operator den Is missing i seçip Finish i tıklayın

11. Run a basın ve açılan pencerede yes e tıklayın.

12.Demo c4 e kaydedin.

4.7.Yeniden Kodlanmış Değerlerden Yeni Sütunlar Oluşturma

Sipariş başına daha fazla kar elde etmek için, yönetim aşağıdaki gibi, her sipariş için kar

değerini kategorize etmek istiyor:

$0 to $100

25

$100 to $500

$500 and above

1.Demo c4 projesini açın ve orders tablosunu ekleyin.

2.Orders a sağ tıklayarak Query Builder ı açın .

3. Query name e Profits Categories Query ve output name e profit_cat yazın .

4. Select data dan Costumer ID, Order ID, Profit seçin.

5. Yeni sütun oluşturmak için Computed Coloumns ı seçip New seçeneğini tıklayın.

6.İlk Adımda Recoded Column ı seçip Next e basın.

26

7.İkinci adımda profit i seçip Next i tıklayın.

8.Üçüncü adımda Replace values sekmesinden Category i seçin ve a missing value

işaretleyin.

27

9. Add-Replace range sekmesinde set a lower limit için 0 değerini verin. Set an upper limit

için 100 verin.

10.Önceki adımı tekrarlayın.

11.With bölümüne 0 to 100 yazın. Bu adımı 100-500 ve 500’den büyük için tekrarlayın.

Aşağıdaki tabloyu elde edin.

28

Column type Character olarak seçilmeli.

12. 4.adımda sütun alanına profit category yazın. Change – None – OK komutlarını izleyin

ve Next i seçin.

29

13. Finish i tıklayın

14. Run ı tıklayın ve projeyi kaydedin.

30

EXERCISES

1.FILTER AND SORT U KULLANARAK YENİ TABLO OLUŞTURMA

1. File -> open -> data-> employee adressess –> open

Filter sekmesinden city i ve equal to seçin.

31

Bir sonraki adım da sort data dan postal code u ascending olarak sıralayın.

OK u tıklayın .

32

Projeyi employees c4 olarak kaydedin.

2. QUERY BUILDER İLE YENİ TABLO OLUŞTURMA

2. file-> open –> data –> employee organization

Query Builder ı açın ve Query name e Sales Employees Query output a da sales_emps

yazın.

Filter data sekmesinden job title ı sürükleyin.

Açılan pencerede contains i seçip value bölümüne sales yazın. Finish i tıklayın.

33

Select data sekmesinden department ı sürükleyin. Run ı tıklayın ve projeyi employees c4

olarak kaydedin.

34

3. FILTER AND SORT TAKİ ADVANCED FILTER I KULLANARAK YENİ TABLO

OLUŞTURMA

3. file –> open – >data –> employees adressess

Filter and sort a tıklayıp gerekli değişkenleri attık.

35

Filter tab – >advanced edit –>functions –> SUBSTR

Ve t1 den postal code u fonksiyonun içine yazın. Virgül koyup 3 değerini yazın ve 920 ye

eşitleyin. OK u tıklayın.

Sort sekmesinden postal code u ascending olarak sıralayalım.

36

OK u tıklayın. Employees C4 olarak kaydedin.

4. QUERY BUILDER DAN COMPUTED COLOUMN İLE YENİ TABLO

OLUŞTURMA

4. file –> open –> data –> employees payroll

37

Query Builder dan query name e New Salary Query ve output a New_Salary yazın. Ve

gerekli değişkenleri CTRL ye basılı tutarak atın.

Salary i seç ve properties simgesini tıkla. Old_salary ismini ver .

38

Filter data sekmesinden Employee Term Date i sürükleyin ve Operator den is missing i

seçin. Next – finish.

Select data sekmesinden New Computed Columns simgesini seçin ve advanced expression

ı seçtikten sonra next i tıklayın. Selected columns tan Old_Salary e çift tıklayıp 1.02 ile

çarpın.

39

Next e tıklayın ve 3.adımda New Salary ismini verin.

40

Change i tıklayarak currency kategorisinden DOLLARw.d yi seçip, overall width 12

decimal places 2 yazın.

OK – Next – Finish sonuçları kontrol edip Run ı tıklayın.

Employees C4 olarak kaydedin.

41

5. CALCULATED COLUMN KULLANARAK FONKSİYON İLE QUERY

YARATMA

5. File –> open –> data – >employee donations

Query Builder dan Query name e Donations Query ve Total_Donations isimlerini veriniz.

Gerekli değişkenlere atınız.

Computed columns –> new –> advanced expression – >next. Sum fonksiyonun içine

gerekli değişkenleri yazın.

42

Next i tıklayın ve Total Donations ismini verin. Change e tıklayıp currency kategorisinden

DOLLARw.d yi seçin ve overall width e 10 decimal places e 2 verin. OK – Next.

43

Finish –> Close. Sonuçları kontrol edin. Projeyi Employees C4 olarak kaydedin ve Run ı

tıklayın.

6. İŞ GÜNLERİNİ HESAPLAMAK İÇİN SORGU(QUERY) YARATMA

6. File –> open –> data-> employee payroll –> query builder

Query name e Years Employed Query yazın.

44

Gerekli değişkenleri çift tıklayarak seçin.

Filter data sekmesinden Employee Hire Date ü sürükleyin ve operator den is missing i

seçin. Finish.

45

Select data – >computed columns –> new –> advanced expression –> next ve

yrdif(Employee_Hire_Date, today( ), ‘actual’

Next i tıklayın ve Years Employed ismini verin. Change i tıklayarak numeric kategorisini

seçin ve overall width e 4 decimal places 1 yazın.

46

OK –> Next –> Finish –> Close –> Run

47

7. VERİLERİ GRUPLAMA VE ÖZETLEME İÇİN QUERY BUILDER KULLANMA

7. file – >open –> data –> employee master-> Query Builder

Query name e Average Salary per City Query ve output a AvgSal_City isimlerini yazın.

Select data dan city ve salary i sürükleyin. Salary e tıklayın ve summary’den AVG yi seçin

Sort data sekmesinden calculation ı seçip descending olarak sıralayın.

48

Run ->Employees C4 olarak kaydedin.

8.VERİLERİ FİLTRELEME, ÖZETLEME VE GRUPLAMA İÇİN QUERY BUILDER

KULLANMA

8. File –> open –> data –> employee master –> Query Builder

49

Query name e Salary Summary by Dept Query ve output a salary_summary yazın. Gerekli

değişkenleri atın ve salary i 2 kez attığınızdan emin olun. Employee ID nin yanında Count u

seçin. İlk salary nin yanını AVG, ikinci salary nin yanını SUM olarak değiştirin.

Avg of salary seçili olduğunda properties simgesini tıklayın. Change – decimal places 0.

50

Sum_of_salary için bu 3 adımı tekrarlayın.

Filter data sekmesinden calculation ı filter the summarized data pane e sürükleyin ve

Count_of_Employee_ID ismini verin. Greater than i seçip value kısmına 10 değerini verin.

Finish i tıklayın.

Sort data sekmesinden calculation ı sürükleyio descending i sıralayın.

51

Run ı tıklayın ve Employees C4 olarak kaydedin.

9. EN ÇOK KAZANAN 10 MESLEK RAPORU ÜRETMEK İÇİN QUERY BUILDER

KULLANMA

file –> open – >data –> employee master –> Query Builder

52

Query name e Top 10 Paid Jop Titles yazın. Gerekli değişkenleri çift tıklayarak seçin ve

Salary nin yanındaki Summary field dan AVG yi seçin. AVG_of_Salary seçili olduğunu

görüp properties simgesini tıklayın.

Label a avarage salary yazın ve OK a basın.

53

Filter data sekmesinden Job Title ı filter the row data ya sürükleyin ve Does not contain i

seçin. Value yerine Chief yazın ve Finish i tıklayın.

Sort data sekmesinden calculation ı sürükleyin ve descending olarak sıralayın.

54

Options ı seçin result format ın altındaki kutucuğu ( override the corresponding default

setting in tools -> options) seçin ve report u tıklayın. Limit number of rows to save in

output değerini 10 yapın.

55

Aynı penceredeki titles sekmesine gelip kutucuğu işaretleyin ve Top 10 Average Salaries by

Job Title yazın. OK u tıklayın.

Run ı tıklayın ve Employees C4 olarak kaydedin.

10. 2 TABLODAN ALINAN VERİLERİ BİRLEŞTİRME

10. employee payroll ve employee addressess ı açın.

Employee payroll a sağ tıklayıp Query Builder ı seçin. Payroll Location Join Query

ismini verin. Output a da employee_payroll_location ismini verin.

56

Add tables a tıklayıp employee addressess ı açın ve join tables ı tıklayın. Otomatik olarak

employee_ID açılacak.

57

Select data dan gerekli verileri seçin.

Salary seçili iken properties simgesini tıklayın ve change den currency kategorisini seçip

DOLLARw.d nin overall width 12, decimal places 2 yazın.

58

OK, aynı işlemi birth date için yapıp change den date kategorisini seçip DATEw.d nın

overall width 9 yapın ve OK-OK.

Select data sekmesinden add a new computed column simgesini tıklayın. İlk adımda

advanced expression – next i tıklayın. İkinci adımda salary*.015 yazın .

59

Üçüncü adımda Bonus ismini verin ve change i tıklayıp currency kategorisinden

DOLLARw.d nin overall width 10 decimal places 2 yapın.

60

Ok –> next –> finish .

Filter data sekmesinden employee term date i sürükleyin ve is missing i seçin . Finish i

tıklayın.

Run-> employees C4 olarak kaydet.

11. 3 TABLODAN ALINAN VERİLERİ BİRLEŞTİRME

61

11. önceki sorunun devamı olarak file – >import data –> country lookup.xlxs-> next –>

first row of range contains field names –> next –>finish.

Payroll location join query e sağ tıklayıp modify ı seç.

Add tables a tıkla ve country lookup ı seç. OK. Açılan pencerede employee addressess ın

altındaki country i country key e sürükleyin.

62

OK. Country i seçip remove simgesini tıklayın. Country name e çift tıklayıp ekleyin.

Run ı tıklayın ve Employees C4 olarak kaydedin.

63

12. EQUIJOIN OLMAYAN KOŞULLARDA VERİLERİ BİRLEŞTİRME

12. file –> open –> data –> bonus Schedule. Xlxs –> open –> next –> first row of range

contains field names – >next –> finish

Process flow dan employee payroll a sağ tıkla ve Query Builder ı seç. Add tables-> bonus

Schedule-> open .

64

Employee hire date i 2 kez tıklayarak employed after sürükleyin ve koşulu >= yapın.

65

Daha sonra employee hire date i employee before a sürükleyin ve koşulu <= yapın.

66

Select data dan gerekli veriler seçilir.

Select data dan add a computed new column simgesi seçilir ve ilk adımda advanced

expression – next ve ikici adımda t1.salary * t2.bonus_percent yazılır.

67

İkinci adımda bonus_amount ismi verilir. Change e tıklayıp currency kategorisinden

DOLLARw.d nin overall width 10 ve decimal places 2 yazılır.

68

Run- >employees c4 olarak kaydedin.

13. 2 TABLOYU OUTER JOIN KULLANARAK BİRLEŞTİRME

13. file –> open –> data –> employee donations –> open

69

Sağ tıklayarak Query Builder ı açalım. Query name e Employees Without Donations

Query ve output a no_donations yazın.

Add tables a tıklayıp employee addressess ı ekleyin. Ve join tables ı tıklayıp venn

şemasına sağ tıklayıp properties i seçin.

70

Ok- >close . select data dan t2 yi sürükleyin. Filter data sekmesinden employee_ID yi

employee_donationstan seçin. İs missing i seçip finish e tıklayın.

Run ı tıklayın ve employees C4 olarak kaydedin.

14. AYRI DEĞER KODLAYARAK YENİ SÜTUN OLUŞTURMA

14. file –> open –>data –> employee adressess

Query Builder dan query name e Country Name Values Query ve output a country_name

yazın. Select data dan t1 i sürükleyin.

71

Country i remove simgesiyle silin. Select data dan add a new computed column ı tıklayın.

1. Adımda recoded column –> next. İkinci adımda ise Country i seçip next i tıklayın. 3.

Adımda character ve the current value seçili olsun.

72

Add i tıklayıp yine add butonundaki sekmeden CTRL ye basılı tutarak AU ve au’yu seçin .

with this value yerine Australia yazın.

Add i tekrar seçin ve CTRL ye basılı tutarak US ve us yi seçin . with this value ya United

states yazınız.

73

74

Next –> country ismini verin. Finish ve run ı tıklayın.

15. DEĞER ARALIKLARI KODLAYARAK YENİ SÜTUN OLUŞTURMA

15. file – >open –> data –> employee payroll –> open query builder açıp query name e

Salary Range Query ve output a Salary_Range. Gerekli veriler seçilir.

Employee hire date i tıklayıp properties simgesini seçin. Change –> date –> DDMMYDw.

Ve overall width 10. OK – OK.

75

Select data dan add a new computed column simgesini tıklayıp ilk adımda recoded column

ikinci adımda salary i seçin ve üçüncü adımda character i seçip add den replace range

sekmesine gelin.

76

Run ve employees C4 olarak kaydedin.

77