Hurtownie danych - Spr.3.

PWr. WIZ, Data: 09.04.2015

Student		Ocena
Indeks
Imię
Nazwisko

Zestaw składa się z 7 zadań. Jeżeli nie potrafisz rozwiązać zadania, to próbuj podać chociaż częściowe rozwiązanie lub uzasadnienie przyczyny braku rozwiązania. Pamiętaj o podaniu nr. indeksu oraz imienia i nazwiska.

Baza danych: AdventureWorks

Zad. 1.

Zdefiniować zapytanie (na podstawie tabeli zamówień z bazy AdventureWorks), które zwróci liczbę zamówień obsługiwanych przez pracowników:

• w poszczególnych latach

• (pracID, rok, Liczba zamówień)

• dla klientów w kolejnych latach

• (pracID, klientID, rok, Liczba zamówień)

Rozwiązanie:

1a

Select SalesPersonID, YEAR(OrderDate) as rok , COUNT(*) as liczba_zamowien

From Sales.SalesOrderHeader

Where SalesPersonID IS NOT NULL

Group by SalesPersonID, YEAR(OrderDate)

Order by Year(OrderDate)

1b

Select SalesPersonID, CustomerID, Year(OrderDate) as rok, Count(*) as liczba_zamowien

From Sales.SalesOrderHeader

Where SalesPersonID IS NOT NULL

Group by SalesPersonID, CustomerID, Year(OrderDate)

Order By Year(OrderDate)

Zad 2.

Utworzyć tabelę Zamówienia wykorzystując wynik zapytania utworzonego w poprzednim zadaniu

(pracID, klientID, rok, Liczba zamówień)

Rozwiązanie:

Select SalesPersonID, CustomerID, Year(OrderDate) as rok, Count(*) as liczba_zamowien

Into Zamowienia

From Sales.SalesOrderHeader

Where SalesPersonID IS NOT NULL

Group by SalesPersonID, CustomerID, Year(OrderDate)

Order By Year(OrderDate)

Zad 3.

Zdefiniować zapytanie z wykorzystaniem wyrażenia CASE, które zwróci liczbę obsługiwanych zamówień przez pracowników w poszczególnych latach. Wynik należy przedstawić zgodnie z podaną niżej definicją:

• T1(idPrac, rok1, rok2, rok3, …) - liczba zamówień wyznaczona dla każdego pracownika w poszczególnych latach

Rozwiązanie:

WITH pomocnicza

AS

(

SELECT SalesPersonID,

CASE WHEN (rok = '2011') THEN SUM(liczba_zamowien) END AS r11,

CASE WHEN (rok = '2012') THEN SUM(liczba_zamowien) END AS r12,

CASE WHEN (rok = '2013') THEN SUM(liczba_zamowien) END AS r13,

CASE WHEN (rok = '2014') THEN SUM(liczba_zamowien) END AS r14

FROM dbo.Zamowienia

GROUP BY SalesPersonID, rok

)

Select SalesPersonID, sum(r11) as '2011', sum(r12) as '2012', sum(r13) as '2013', sum(r14) as '2014'

From pomocnicza

Group by SalesPersonID

Zad 4.

Zdefiniować zapytanie tworzące ten sam rezultat jak w zad. 3., wykorzystując w tym celu operator PIVOT.

Rozwiązanie:

Select SalesPersonID,

ISNULL([2011], 0) as '2011',

ISNULL([2012], 0) as '2012',

ISNULL([2013], 0) as '2013',

ISNULL([2014], 0) as '2014'

From (Select SalesPersonID, rok, liczba_zamowien

From Zamowienia) tabela1

Pivot

(

SUM(liczba_zamowien)

For rok IN ([2011], [2012], [2013], [2014])

) as tabela2

Order by SalesPersonID

Zad. 5.

Zdefiniować zapytanie z wykorzystaniem operatora PIVOT wyznaczające średnią kwotę sprzedaży towarów poszczególnym klientom w kolejnych latach

Rozwiązanie:

SELECT CustomerID,

ISNULL([2011], 0) AS '2011',

ISNULL([2012], 0) AS '2012',

ISNULL([2013], 0) AS '2013',

ISNULL([2014], 0) AS '2014'

FROM

(

SELECT CustomerID, YEAR(OrderDate) AS order_year, TotalDue

FROM Sales.SalesOrderHeader

)

AS SourceTable

PIVOT

(

AVG(TotalDue)

FOR order_year IN ([2011], [2012], [2013], [2014])

)

AS PivotTable

ORDER BY CustomerID

Zad. 6.

Zdefiniować zapytanie wyznaczające sumę sprzedaży /zakupów dla poszczególnych pracowników, klientów, pracowników i klientów oraz globalną sumę sprzedaży, zgodnie ze schematem: (pracId, klientId, Suma)

pracID	klientID	Suma
NULL	NULL	127337180.11
NULL	1	102351.80
NULL	2	29623.50
NULL	3	433942.38
NULL	29483	2049.10
268	NULL	1369624.65
268	7	3569.43
268	38	2785.51

Rozwiązanie:

SELECT NULL AS pracID, NULL AS klientID, SUM(TotalDue) AS Suma

FROM Sales.SalesOrderHeader

UNION ALL

SELECT SalesPersonID, NULL, SUM(TotalDue)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID

UNION ALL

SELECT NULL, CustomerID, SUM(TotalDue)

FROM Sales.SalesOrderHeader

GROUP BY CustomerID

UNION ALL

SELECT SalesPersonID, CustomerID, SUM(TotalDue)

FROM Sales.SalesOrderHeader

GROUP BY SalesPersonID, CustomerID;

Zad. 7. Projekt HD

Proszę przygotować zakres realizacji projektu zgodnie z poniższą specyfikacją oraz przedyskutować propozycję projektu z osobą prowadzącą zajęcia. Poczynione uzgodnienia zarejestrować w formie wniosków

Zakresu opracowania projektu HD - etap 1.

1. Tytuł projektu

Zmiany klimatu

2. Charakterystyka dziedziny problemowej

Krótki opis obszaru analizy

Analiza zmian klimatu w różnych państwach na świecie na przestrzeni lat. Dane o najważniejszych wydarzeniach klimatycznych.

3. Problemy i potrzeby

Potrzebna będzie analiza wielu państw w różnych latach.

4. Cel przedsięwzięcia

1. Oczekiwania

Analiza tak zgromadzonych danych, aby wyciągać z nich wnioski.

2. Zakres analizy - badane aspekty

Porównywanie klimatu i najważniejszych zmian na przestrzeni lat w państwach świata.

5. Źródła danych (lokalizacja, format, dostępność)

Dane pobrane ze strony:

http://data.worldbank.org/data-catalog/climate-change

Format pliku: xsl

Dostępność: baza ogólnie dostępna pod podanym linkiem

Wnioski

Bardzo ciekawym operatorem jest operator Pivot, którego robiąc tę listę poznałem, pozwala on na stworzenie bardziej przejrzystego kodu.

W niektórych zadaniach nie byłem do końca pewny, czego dokładnie się wymaga, co zapytanie ma zwrócić (szczególnie zadanie 5 i 6), więc zrobiłem je tak jak mi się zdawało że powinny wyglądać.

Znalezienie strony z dostępnymi ogólnie bazami danych zajęło mi trochę czasu, na początku starałem się znaleźć polską stronę, ale w końcu przerzuciłem się na angielskie strony i to pozwoliło mi znaleźć bazę, którą jak mi się zdaje będzie właściwa na projekt.

-----------------------------------------------------------------------------------------------------------------------------------

Zakresu opracowania projektu HD - etap 2. (Spr. 4. - kolejne zajęcia lab. 25.03.2015 r.)

6. Wstępna analiza źródeł danych

Lp.	Plik	Typ	Liczba rek.	Rozmiar[MB]	Opis
	climate_change_download_0	xls	~14000	4,63	Baza zawiera dane dotyczące klimatu
…

7. Ocena możliwości realizacji celu w kontekście dostępnych źródeł danych

Baza jest wystarczająca do zrealizowania projektu.

8. Model konceptualny danych - wynik analizy źródeł danych

1. Definicja typów encji/klas (wraz z własnościami) oraz związków pomiędzy nimi

2. Diagram klas




---