Transpozycja SQL i funkcje analityczne.



Często zdarza się że trzeba zmienić jakiś typ danych np. funkcja getdate() zwraca date z aktualną godziną, a chcemy mieć czystą datę w SQL(niestety nie ma takiego typu danych ale możemy mieć 'rrrr-mm-dd 00:00:00') spójrzmy na przykład

SELECT convert(smalldatetime,convert(nvarchar(10),
left(getdate(),10),120),120)

Funkcja CONVERT pozwala na określenie typu danych jaki nas interesuje składnia to CONVERT(typ danych na jaki chcemy zmienić, wyrażenie, sposób kodowania) odnośnie sposobu kodowania polski polecam 120.

SELECT cast('20009-10-10' as smalldatetime)

Zmiany typu możemy dokonać również przy użyciu funkcji CAST(wyrażenie AS typ danych)

Poniżej tabela przedstawiająca styl daty i czasu:

Table 5.

Nr Standard Wyświetlana wartość
Default mon dd yyyy hh:miAM (or PM)
101 U.S. mm/dd/yyyy
102 ANSI yy.mm.dd
103 British/French dd/mm/yyyy
104

German

dd.mm.yy
105 Italian dd-mm-yy
106 - dd mon yy
107 - Mon dd, yy
108 - hh:mi:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 USA mm-dd-yy
111 JAPAN yy/mm/dd
112 ISO yymmdd yyyymmdd
13 or 113 Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
114 - hh:mi:ss:mmm(24h)
20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)<
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127 ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ
130 Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
131 Hijri (5) dd/mm/yy hh:mi:ss:mmmAM

Kolejna tabela przedstawia jak zachowują się wartości liczbowe podczas konwersji

Tabela 6.

Z do
numeric numeric zaokrągla
numeric int przycina
numeric

money

zaokrągla
money int zaokrągla
money numeric zaokrągla
float int przycina
float numeric zaokrągla
float datetime zaokrągla
datetime int zaokrągla

Zajmiemy się teraz omówieniem funkcji DATEPART, pozwala ona uzyskać z daty np nr tygodnia, nr dnia, dzień tygodnia itd. jej składnia to DATEPART(część daty, data) jeśli chcemy uzyskać na informacje o dniu tygodnia wystarczy DATEPART(dw,getDate()) (ważne by zdefiniować w tym przypadku pierwszy dzień tygodnia za pomocą SET DATEFIRST np SET DATEFIRST= 1)

Tabela 7.

Część daty operator
rok yy, yyyy
kwartał qq, q
miesiąc mm, m
dzieńroku dy, y
dzień dd, d
tydzień wk, ww
dzień tygodnia dw
godzina hh
minuta mi, n
sekunda ss, s
milisekunda ms
mikrosekunda mcs
nanosekunda ns
TZoffset tz
ISO_WEEK isowk, isoww

Kolejną przydatną funkcją jest DATEDIFF, jej składnia to DATEDIFF(część daty/okres czasu, data początkowa, data końcowa) funkcja oblicza określony okres czasu (operatory takie same jak przy DATAPART) od daty startowej do daty końcowej.

Warto też napisać o DATEADD (część daty, ilość jednostek, data ) funkcja pozwala na dodanie do dowolnej daty określonej ilości jednostek np. DATEADD(dd,5,getDATE()) wynikiem tej funkcji będzie dzień dzisiejszy plus 5 (operatory opisane są w Tabeli 7.).

Czasami powstaje potrzeba zamiany wierszy z kolumnami jak tego dokonać... rozwiązanie jest prostem poleceniem PIVOT. Zułużmy że mamy table :

miesiac typ kwota
1 zakupy 2000000
1 wydatki 1250000
1 podatki 250000
1 zysk 500000
2 zakupy 2500000
2 wydatki 1250000
2 podatki 500000
2 zysk 750000
3 zalupy 500000
3 wydatki 250000
4 zakupy 800000

 

SELECT *
FROM #dane
PIVOT
(
SUM(kwota)
FOR [typ] IN ([zakupy],[wydatki],[podatki],[zysk])
)
AS p

Używając w/w zapytanie uzyskamy wynik:

misiac zakupy wydatki podatki zyski
1 2000000 1250000 250000 500000
2 2500000 1250000 500000 750000
3 500000 250000 null null
800000 350000 100000 null

Powyższy przykład pokazuje jak dokonać transpozycji przy pomocy PIVOT w SQL. Najpierw tworzymy zapytanie, za PIVOT określamy funkcje agregującą za FOR kolumny z wiersz.

Polecenie WITH AS pozwala na wywołanie np procedury w "imieniu innego użytkownia" przykład:

CREATE PROCEDURE zapytanie (
WITH EXECUTE AS user_name AS
SELECT
Koszty,
miesiac
FROM
t_dane )

W tym przypadku jeśli user_name nie ma dostępów do jakieś tabeli to dzięki w/w konstrukcji będzie miał możliwość wykonania np. zapytanie.

Inny sposób wykorzystanie to zadeklarowanie "widoku"

WITH dane (k,m) AS (
SELECT
Koszty,
miesiac
FROM
t_dane Where miesiac = 'styczeń')
Select k,m from dane

W ten sposób możemy wywołać tabele w T-SQL zdefiniowaną prze WITH AS

Czasami powstaje potrzeba zamiany wierszy z kolumnami jak tego dokonać... rozwiązanie jest prostem poleceniem PIVOT. Załóżmy że mamy tabele :

miesiac typ kwota
1 zakupy 2000000
1 wydatki 1250000
1 podatki 250000
1 zysk 500000
2 zakupy 2500000
2 wydatki 1250000
2 podatki 500000
2 zysk 750000
3 zalupy 500000
3 wydatki 250000
4 zakupy 800000

 

SELECT *
FROM #dane
PIVOT
(
SUM(kwota)
FOR [typ] IN ([zakupy],[wydatki],[podatki],[zysk])
)
AS p

Używając w/w zapytanie uzyskamy wynik:

misiac zakupy wydatki podatki zyski
1 2000000 1250000 250000 500000
2 2500000 1250000 500000 750000
3 500000 250000 null null
800000 350000 100000 null

Powyższy przykład pokazuje jak dokonać transpozycji przy pomocy PIVOT w SQL. Najpierw tworzymy zapytanie, za PIVOT określamy funkcje agregującą za FOR kolumny z wiersz.

Polecenie WITH AS pozwala na wywołanie np procedury w "imieniu innego użytkownika" przykład:

CREATE PROCEDURE zapytanie (
WITH EXECUTE AS user_name AS
SELECT
Koszty,
miesiac
FROM
t_dane )

W tym przypadku jeśli user_name nie ma dostępów do jakieś tabeli to dzięki w/w konstrukcji będzie miał możliwość wykonania np. zapytanie.

Inny sposób wykorzystanie to zadeklarowanie "widoku"

WITH dane (k,m) AS (
SELECT
Koszty,
miesiac
FROM
t_dane Where miesiac = 'styczeń')
Select k,m from dane

W ten sposób możemy wywołać tabele w T-SQL zdefiniowaną prze WITH AS

Bardzo przydatnym poleceniem podczas pracy z bazą danych jest UNPIVOT, gdy zajmiemy się już szerszą analizą danych lub chcemy je w ciekawy sposób zobrazować funkcja UNPIVOT w t-sql jest bardzo przydatna.

Kwartal Zakupy Wydatki Podatki Dochod
Q1 800000 350000 100000 NULL
Q2 2500000 1250000 500000 750000
Q3 2000000 250000 250000 500000
Q4 500000 250000 NULL NULL

 

SELECT kwartal, kwota, typ
FROM #t_test
UNPIVOT (kwota for typ in (zakupy,wydatki,podatki,dochod)) as kwota

 

Kwartal Amount AmountType
Q1 800000 Zakupy
Q1 350000 Wydatki
Q1 100000 Podatki
Q2 2500000 Zakupy
Q2 1250000 Wydatki
Q2 500000 Podatki
Q2 750000 Dochod
Q3 2000000 Zakupy
Q3 250000 Wydatki
Q3 250000 Podatki
Q3 500000 Dochod
Q4 500000 Zakupy
Q4 250000 Wydatki

Wyniku powyższego zapytania od trzymujemy w/w tablice z danymi. Nazwy podane jako kwota i typ możemy definiować dowolnie pamietać musimy by w nawiasach za klauzulą IN podać nazwy kolumn.

 

Funkcje analityczne i rankingowe.

DENSE_RANK() OVER ( order by miesiac )

Funkcja DENSE_RANK() pozwala nam na partycjonowanie danych oraz ich rankingowanie numerowanie powyższy przykład przestawia jak ponumerować dane w tabeli po kolumnie miesiąc. Możliwe jest również podzielenie na partycje należy tego dokonać w taki sposób:

DENSE_RANK() OVER (PARTITION region ORDER BY miesiac)

Funkcja powyżej pozwala podzielić zbiór na partycje podregionach uporządkowanych po miesiącach.

NTILE( podział)

Funkcja pozwala na podzielenie wyniku zapytania na kilka równych cześć w zależności od wartość podział. Można jej użyć na do liczenia kwartyli.

 

 

SQL-KURSY.pl poleca:

Książki

Copyright 2010-2011mariuszhk@op.pl

obob ob

Valid HTML 4.01 Transitional

Strona internetowa wykorzystuje pliki cookie zapisywane w pamięci przeglądarki internetowej.

OK