Обсуждение: SQL Agreate Functions

Hi,

I have a problem I dont really know how to solve except for writing a
function.

I have a table with prices;

SecCode| Price   | PriceDate
-------+------------+-----------
A0001  | 13.10   | 2004-10-30
A0001  | 13.03   | 2004-10-29
A0001  | 13.12   | 2004-10-28
A0001  | 12.45   | 2004-10-27
A0001  | 12.65   | 2004-10-26
A0001  | 12.45   | 2004-10-25

A0002  | 10.10   | 2004-10-30
A0002  | 10.45   | 2004-10-27
A0002  | 10.65   | 2004-10-26
A0002  | 10.45   | 2004-10-25
etc.

What I would like to calculate is:

a) the difference of the past 2 days for every security of
   available prices

b) a flag indicating, that the price of today-1 is yesterday's
   price (true in case A0001, false for A0002)

c) the variance of the past 30 days


Is it possible to do that within one query?

Thanks for any advise
Alex

Alex P wrote:
> Hi,
>
> I have a problem I dont really know how to solve except for writing a
> function.
[snip]
> What I would like to calculate is:
>
> a) the difference of the past 2 days for every security of
>   available prices

Find the maximum date for a give SecCode (simple enough) and then the
maximum date that is smaller than the one you just found (assuming no
repetition of dates for a given SecCode).

> b) a flag indicating, that the price of today-1 is yesterday's
>   price (true in case A0001, false for A0002)

SELECT ... (PriceDate = (CURRENT_DATE - 1)) AS is_yesterday, ...

> c) the variance of the past 30 days

Variance aggregate function

> Is it possible to do that within one query?

Three sub-queries and some joining, certainly. It'll be a big query
mind, perhaps worth wrapping in a function.

--
   Richard Huxton
   Archonet Ltd