Planowanie zapytania

Raportowanie w SQL

Tyler Pernes

Learning & Development Consultant

Cel rozdziału

Pytania pomocnicze

Z jakich tabel pobieramy dane?
Jak połączyć tabele?
Jakie pola należy utworzyć?
Jakie filtry zastosować?
Czy potrzebne jest sortowanie lub ograniczenie?

Scenariusz

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+

1 - Z jakich tabel pobieramy dane?

2 - Jak połączyć tabele?

3 - Jakie pola należy utworzyć?

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+

season - static string
demographic_group - conditional
golds - SUM()

4 - Jakie filtry należy zastosować?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+

WHERE czy HAVING?
Filtr na wymiarze = WHERE

5 - Czy potrzebne jest sortowanie lub ograniczenie wyników?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+

LIMIT nie jest potrzebny
Sortowanie według golds malejąco

Czas na ćwiczenia!

Raportowanie w SQL