Temat: Wydobywanie świeczek z danych tickowych

Witam,

Byłbym bardzo wdzięczy za pomoc w rozwiązaniu następującego problemu związanego z wybieraniem pierwszego i ostatniego rekordu w danej grupie GROUP BY:

Mam tabelę Ticks składającą się z pól:

Provider (int)
Symbol (int)
Time (datetime)
Bid (decimal(18,5))
Ask (decimal(18,5))

Tabela zawiera dane o każdej pojedynczej zmianie wartości wybranych instrumentów finansowych (Bid/Ask to ceny kupna/sprzedaży) w danym czasie Time, Symbol to identyfikator symbolu, Provider to dostawca danych (w bazie znajdują się informacje od kilku dostawców).

Ponieważ szeregi czasowe zazwyczaj analizuje się w formie tzw. świeczek japońskich, szukam sposobu na możliwie łatwą zamianę danych tickowych na świeczki.

Świeczka to najprościej rzecz ujmując struktura składająca się z: symbolu, czasu otwarcia świczki, ceny Open, ceny High, ceny Low oraz ceny Close.
Świeczki wyznacza się dla danego przedziału czasowego np. świeczka piętnastomintowa o czasie otwarcia 15:00 obejmuje wszystkie ticki z przedziału [15:00, 15:15).
W tym przypadku cena Open to pierwszy rekord w bazie o Time >= 15:00, High to maksymalna cena z tego przedziału czasowego, Low analogicznie, Close to ostatnia cena przed 15:15.

Oczywiście takie świeczki można zrobić w zewnętrznym programie który to przetworzy, ale po pierwsze będzie to mniej elastyczne, a po drugie niczego się nie nauczę (SQL Servera używam od niedawna).

Na chwilę obecną skonstruowałem część zapytania:

SELECT

	Provider, 

	Symbol, 

	CONVERT(Date,Time,101) AS Date,

	DATEPART(HOUR,Time) AS Hour,

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15)) AS Minute,

	MAX(Bid) AS High,

	MIN(Bid) AS Low,

	COUNT(*) AS TickCount

FROM 

	Ticks 	

GROUP BY 

	Provider, 

	Symbol,

	CONVERT(Date,Time,101),

	DATEPART(HOUR,Time),

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))

ORDER BY 

	Symbol, 

	Date, 

	Hour, 

	Minute

Moja prośba i problem dotyczy ustalenia cen Open i Close.
W jaki sposób mogę poprosić SQL Servera o podanie mi pierwszego i ostatniego rekordu w danej grupie?
(bo do tego cały problem się sprowadza, aby dla każdej wyjściowej grupy wydobyć pierwszy i ostatni chronologicznie [Time] rekord)

(Próbowałem kilku zapytań z subquery, no ale delikatnie mówiąc nie bardzo wychodzą...)

Temat: Wydobywanie świeczek z danych tickowych

Moim zdaniem musisz Bid odnalezc w dwoch podzapytaniach. Umiesc je zamiast pol High i Low, no i oczywiscie musisz polaczyc podzapytania z zapytaniem glownym. Mozesz tez, podzapytania umiescic w funkcjach ktore zwracaja wartosc skalarna.

Cos takiego:

SELECT

	Provider, 

	Symbol, 

	CONVERT(Date,Time,101) AS Date,

	DATEPART(HOUR,Time) AS Hour,

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15)) AS Minute,

	(SELECT MAX(Bid) FROM Ticks t1 WHERE t1.Provider=t.Provider AND t1.Symbol=t.Symbol AND CONVERT(Date,t1.Time,101)=CONVERT(Date,t.Time,101) GROUP BY t1.Provider, t1.Symbol) AS High,

	(SELECT MIN(Bid) FROM Ticks t1 WHERE t1.Provider=t.Provider AND t1.Symbol=t.Symbol AND CONVERT(Date,t1.Time,101)=CONVERT(Date,t.Time,101) GROUP BY t1.Provider, t1.Symbol) AS Low,

	COUNT(*) AS TickCount

FROM 

	Ticks t

GROUP BY 

	Provider, 

	Symbol,

	CONVERT(Date,Time,101),

	DATEPART(HOUR,Time),

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))

ORDER BY 

	Symbol, 

	Date, 

	Hour, 

	Minute

Cos tendy, i nie jestem powien czy glowne group by nie powinno zawierac Provider, Symbol, Time

Aaa i napisz czy dziala :)Rafał Ziółkowski edytował(a) ten post dnia 02.02.10 o godzinie 09:10

Temat: Wydobywanie świeczek z danych tickowych

Niestety zapytanie jakie podałeś nie daję pożądanych wyników, bo MAX i MIN jest podawane w ogóle dla danego symbolu, a nie dla danego przedziału.

To ja może przedstawię przykład (pomijam pola Provider, Symbol i Ask, załóżmy, że jest tylko jeden):

Time                       Bid

2010-02-01 15:14:23.023    1.43564

2010-02-01 15:15:23.033    1.43550

2010-02-01 15:15:53.073    1.43530

2010-02-01 15:16:13.013    1.43560

2010-02-01 15:17:23.023    1.43555

2010-02-01 15:31:23.063    1.43543

Z mojego zapytania otrzymuję (jeden przykładowy rekord wyniku dla przedziału [15:15, 15:30) dla powyższych danych):

Date          Hour    Minute    High     Low

2010-02-01    15      15        1.43560  1.43530

A potrzebuję zapytać o pierwszy i ostatni rekord w tym przedziale, czyli aby wynik wyglądał tak:

Date          Hour    Minute    High     Low       Open      Close

2010-02-01    15      15        1.43560  1.43530   1.34550   1.34555

Czyli po prostu potrzebuję dla każdej grupy otrzymanej z mojego zapytania zrobić zapytanie:

SELECT

	TOP 1

	Bid AS [Open]

FROM 

	Ticks 

WHERE

	CONVERT(Date,Time,101)=POLE_DATE_Z_DANEJ_GRUPY AND

	DATEPART(HOUR,Time)=POLE_HOUR_Z_DANEJ_GRUPY AND

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=POLE_MINUTE_Z_DANEJ_GRUPY AND

	Symbol=POLE_SYMBOL_Z_DANEJ_GRUPY AND

	Provider=POLE_PROVIDER_Z_DANEJ_GRUPY

ORDER BY

	Time

I otrzymanego skalara wstawić jako pole [Open] do pierwotnego wyniku, oraz wykonać analogiczne zapytanie:

SELECT

	TOP 1

	Bid AS [Close]

FROM 

	Ticks 

WHERE

	CONVERT(Date,Time,101)=POLE_DATE_Z_DANEJ_GRUPY AND

	DATEPART(HOUR,Time)=POLE_HOUR_Z_DANEJ_GRUPY AND

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=POLE_MINUTE_Z_DANEJ_GRUPY AND

	Symbol=POLE_SYMBOL_Z_DANEJ_GRUPY AND

	Provider=POLE_PROVIDER_Z_DANEJ_GRUPY

ORDER BY

	Time DESC

(Różnica tylko w sortowaniu, tutaj jest DESC)
I wstawić otrzymany skalar do mojego wyniku jako pole [Close].

Tylko jak to zrobić (i czy w ogóle się da)?

Temat: Wydobywanie świeczek z danych tickowych

Nie wystarczy dodać dwóch dodatkowych kolumn do wyników?
Mam na myśli, żeby dołączyć OPEN i CLOSE jako:

         (SELECT TOP(1) Bid FROM Tics t2 WHERE t2.Provider=t.Provider AND t2.Symbol=t.Symbol AND CONVERT(Date,t2.Time,101)= CONVERT(Date,t.Time,101) AND CONVERT(Date,t2.Time,101)=(SELECT MIN(CONVERT(Date,t2.Time,101)) FROM Tics t3 WHERE t2.Provider=t3.Provider AND t2.Symbol=t3.Symbol)  GROUP BY t2.Provider, t2.Symbol, t2.Bid, t2.Time 

ORDER BY CONVERT(Date,t.Time,101) ASC ) AS [OPEN],



         (SELECT TOP(1) Bid FROM Tics t2 WHERE t2.Provider=t.Provider AND t2.Symbol=t.Symbol AND CONVERT(Date,t2.Time,101)=CONVERT(Date,t.Time,101) AND CONVERT(Date,t2.Time,101)=(SELECT MAX(CONVERT(Date,t3.Time,101)) FROM Tics t3 WHERE t2.Provider=t3.Provider AND t2.Symbol=t3.Symbol)  GROUP BY t2.Provider, t2.Symbol,t2.Bid, t2.Time 

ORDER BY CONVERT(Date,t.Time,101) DESC ) AS [CLOSE],

Na pewno można krócej i prościej.Daniel Nikratowicz edytował(a) ten post dnia 02.02.10 o godzinie 13:37

Temat: Wydobywanie świeczek z danych tickowych

A czemu nie wstawisz Open i Close jako podzapytanie? Coś takiego napisałem u siebie dla przedziału 15:15-15:30:

SELECT  

    DATEPART(HOUR,Time) AS Hour,

    (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15)) AS Minute,

    MAX(Bid) AS High,

    MIN(Bid) AS Low,    

    (

      SELECT TOP 1 Bid

      FROM Bids 

      WHERE DATEPART(HOUR,Time)=15 AND (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=15 

      ORDER BY Time

    ) AS Open,

    (

      SELECT TOP 1 Bid

      FROM Bids 

      WHERE DATEPART(HOUR,Time)=15 AND (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=15 

      ORDER BY Time desc

    ) AS Close

FROM 

    Bids

WHERE 

    DATEPART(HOUR,Time)=15 AND

    (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=15 

GROUP BY	 

    DATEPART(HOUR,Time),

    (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))

EDIT: Kolega wyżej mnie uprzedził :-)Radosław Zajączkowski edytował(a) ten post dnia 02.02.10 o godzinie 11:21

Temat: Wydobywanie świeczek z danych tickowych

Wstawiasz te podzapytania ktore napisales jako pola w zapytaniu glownym (mozesz tez je wrzucic do funksji skalarnych - to tak dla wygody) - pozostaje kwestia zdefiniowania parametrow. Mozesz to zrobic analogicznie jak w zapytaniu ktore ja podalem, ale oczywiscie musisz poprawnie zdefiniowac powiazanie (tj tak jak Ty to zrobiles) Musisz tez wprowadzic aliasy do nazw tabel.

np.:

SELECT

	Provider, 

	Symbol, 

	CONVERT(Date,Time,101) AS Date,

	DATEPART(HOUR,Time) AS Hour,

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15)) AS Minute,

	MAX(Bid) AS High,

	MIN(Bid) AS Low,

	COUNT(*) AS TickCount

	(SELECT

	TOP 1

	Bid AS [Open]

FROM 

	Ticks t1

WHERE

	CONVERT(Date,t1.Time,101)=CONVERT(Date,t.Time,101) AND

	DATEPART(HOUR,t1.Time)=DATEPART(HOUR,t.Time) AND

	(DATEPART(MINUTE,t1.Time) - (DATEPART(MINUTE,t1.Time) % 15))=(DATEPART(MINUTE,t.Time) - (DATEPART(MINUTE,t.Time) % 15)) AND

	t1.Symbol=t.Symbol AND

	t1.Provider=t.Provider

ORDER BY

	Time) AS [Open]

FROM 

	Ticks t

GROUP BY 

	Provider, 

	Symbol,

	CONVERT(Date,Time,101),

	DATEPART(HOUR,Time),

	(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))

ORDER BY 

	Symbol, 

	Date, 

	Hour, 

	Minute

I analogicznie dla Close

Temat: Wydobywanie świeczek z danych tickowych

Ok, wygląda na to, że uzyskałem taki efekt jaki chciałem, wielkie dzięki wszystkim za pomoc.

Ostatecznie query przybrało postać:

SELECT  

	Provider,

	Symbol,

	CONVERT(Date,Time,101) AS Date,

    DATEPART(HOUR,Time) AS Hour,

    (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15)) AS Minute, 

    (

      SELECT TOP 1 Bid

      FROM Ticks 

      WHERE 

		Symbol=t.Symbol AND

		Provider=t.Provider AND

		DATEPART(HOUR,Time)=DATEPART(HOUR,t.Time) AND 

		(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=(DATEPART(MINUTE,t.Time) - (DATEPART(MINUTE,t.Time) % 15))

      ORDER BY Time

    ) AS [Open],

    MAX(Bid) AS High,

    MIN(Bid) AS Low,   

    (

      SELECT TOP 1 Bid

      FROM Ticks 

      WHERE

		Symbol=t.Symbol AND

		Provider=t.Provider AND

		DATEPART(HOUR,Time)=DATEPART(HOUR,t.Time) AND 

		(DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))=(DATEPART(MINUTE,t.Time) - (DATEPART(MINUTE,t.Time) % 15))

      ORDER BY Time DESC

    ) AS [Close],

    COUNT(*) AS TickCount

FROM 

    Ticks t

WHERE Symbol=1    

GROUP BY

	Provider,	 

	Symbol,

	CONVERT(Date,Time,101),

    DATEPART(HOUR,Time),

    (DATEPART(MINUTE,Time) - (DATEPART(MINUTE,Time) % 15))

ORDER BY

	Symbol,

	Date, 

	Hour,

	Minute

Temat: Wydobywanie świeczek z danych tickowych

Łukasz T.:
Ostatecznie query przybrało postać:

Co do poprawności to zakładając, że kolumna [Time] jest unikatowa to wszystko gra - mam tylko uwagę odnośnie implementacji.

Jak to z SQLem bywa można dużo wytargować na optymalizacji: wydaje mi się, że zamiast podzapytań da radę w tym wypadku w ogóle zrezygnować z klauzuli group by i przełączyć się na odpowiedniki

row_number() over(partition by Provider, Symbol,itd... order by Time DESC | Time ASC | Bid ASC | Bid DESC

i wybrać odpowiednio tylko te rekordy, które dają row_number == 1.

Jakbyś podesłał swoją tabelkę z przykładowymi danymi mógłbym podać konkretne zapytanie, natomiast dla mojego (przyciętego) przykładu wygląda to tak (darowałem sobie pivota) - grupowanie po miesiącu dla ilości szt. zamówienia:

CREATE TABLE [dbo].[Orders](

	[OrderId] [int] primary key clustered,

	[OrderDate] [datetime] NOT NULL,

	[Qty] [int] NOT NULL

);

--napelnij tabelke

;

SET STATISTICS IO ON;



with CTE_agg as (

	select

		OrderDate,

		Qty,

		OrderId,

		row_number() over(partition by convert(char(6),OrderDate,112) order by OrderDate asc, OrderId asc) [n_first],

		row_number() over(partition by convert(char(6),OrderDate,112) order by OrderDate desc, OrderId desc) [n_last],

		row_number() over(partition by convert(char(6),OrderDate,112) order by Qty desc) [n_max],

		row_number() over(partition by convert(char(6),OrderDate,112) order by Qty asc) [n_min]

	from dbo.Orders

)

select

	OrderDate,

	Qty,

	case

		when a.n_first = 1 then 'first'

		when a.n_last = 1 then 'last'

		when a.n_max = 1 then 'max'

		when a.n_min = 1 then 'min'

	end [desc]--TODO: zrobić pivota

from CTE_agg a

where a.n_first = 1 or a.n_last = 1 or a.n_max = 1 or a.n_min = 1

order by OrderDate asc;

po włączeniu SET STATISTICS IO ON mam o wiele mniej odczytów niż w wersji z podzapytaniem, oraz z query planu widać, że cała tabelka jest skanowana tylko jeden raz (versus 3x przy podzapytaniach).

Nie wiem czy to się odnosi do twojego przykładu ze świecami, ale z opisu wynika, że to problem tego samego typu. Moim zdaniem warto spróbować.
EDIT: problemy z formatowaniem kodumaciek kański edytował(a) ten post dnia 03.02.10 o godzinie 16:29

Temat: Wydobywanie świeczek z danych tickowych

Heh, aż tak dobry w SQL Serverze jeszcze nie jestem (bawię się nim od niecałych 2 tygodni), żeby takie cuda wyczyniać, ale wygląda obiecująco ;)

Co do bazy:

http://dl.dropbox.com/u/27539/Quotations.mdf
http://dl.dropbox.com/u/27539/Quotations_log.ldf

(dane nie są ciągłe, jest kilka serii dla kilku dni, bo to baza testowa)Łukasz T. edytował(a) ten post dnia 04.02.10 o godzinie 12:42

Temat: Wydobywanie świeczek z danych tickowych

Po wykonaniu porównania na prawdziwej bazie wyszło, że row_number wcale nie dodają wyraźnego kopa. Ta 3x mniejsza ilość logical_read przekłada się raptem na trochę szybsze zapytanie (w wypadku sterty jak i z clustered index), ale tak to jest z optymalizacją, że trzeba popróbować.