In article <20040525190703.BA1D61A2676@stripped>,
<mysql@stripped> writes:
> I am trying to come up with a query that takes two tables, one with
> non-split-adjusted historical stock prices, and one with information on
> splits, for instance:
> CREATE TABLE quotes (
> symbol VARCHAR(127) NOT NULL,
> date DATE NOT NULL,
> quote FLOAT NOT NULL,
> PRIMARY KEY (symbol, date),
> INDEX (date),
> );
> INSERT quotes VALUES ("A", "2004-01-01", 3);
> INSERT quotes VALUES ("A", "2004-01-02", 3);
> INSERT quotes VALUES ("A", "2004-01-03", 3);
> INSERT quotes VALUES ("A", "2004-01-04", 3);
> INSERT quotes VALUES ("A", "2004-01-05", 2);
> INSERT quotes VALUES ("A", "2004-01-06", 2);
> INSERT quotes VALUES ("A", "2004-01-07", 2);
> INSERT quotes VALUES ("A", "2004-01-08", 1);
> INSERT quotes VALUES ("A", "2004-01-09", 1);
> CREATE TABLE splits (
> symbol VARCHAR(127) NOT NULL,
> date DATE NOT NULL,
> split_from INT UNSIGNED NOT NULL,
> split_to INT UNSIGNED NOT NULL,
> PRIMARY KEY (symbol, date),
> );
> INSERT splits VALUES ("A", "2004-01-05", 2, 3);
> INSERT splits VALUES ("A", "2004-01-08", 1, 2);
> I need to be able to pull out split-adjusted quotes, like this:
> SELECT symbol, date, ...some magic... FROM quotes WHERE symbol = "A" ORDER
> BY date;
> +--------+------------+-------+----------------+
> | symbol | date | quote | adjusted_quote |
> +--------+------------+-------+----------------+
> | A | 2004-01-01 | 3 | 1 |
> | A | 2004-01-02 | 3 | 1 |
> | A | 2004-01-03 | 3 | 1 |
> | A | 2004-01-04 | 3 | 1 |
> | A | 2004-01-05 | 2 | 1 |
> | A | 2004-01-06 | 2 | 1 |
> | A | 2004-01-07 | 2 | 1 |
> | A | 2004-01-08 | 1 | 1 |
> | A | 2004-01-09 | 1 | 1 |
> +--------+------------+-------+----------------+
> Split-adjusting means that on a split date all previous prices are
> multiplied by "split_from/split_to" ratio. In my example two splits took
> place, one on 2004-01-05, which multiplied all previous prices by 2/3 and
> another one on 2004-01-08, which multiplied all previous prices (including
> those already affected by first split) by 1/2.
> Any help would be appreciated.
This would be something like
SELECT q.symbol, q.date,
q.quote * product (s.split_from / s.split_to) AS adjusted_quote
FROM quotes q
LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date
GROUP BY q.symbol, q.date, q.quote
ORDER BY q.symbol, q.date
The problem is how to define the 'product' aggregate (along the lines
of 'sum'). In PostgreSQL you could do something like
CREATE FUNCTION multiply (float8, float8) RETURNS float8 AS '
SELECT coalesce ($1, 1) * coalesce ($2, 1)
' LANGUAGE SQL STRICT IMMUTABLE;
CREATE AGGREGATE product (
sfunc = multiply,
basetype = float8,
stype = float8,
initcond = 1
);
but I have no idea how to do that in MySQL. Perhaps by a UDF?
