У меня есть список значений и дат для символов биржевых котировок и вы хотите рассчитать ежеквартальные возвраты в SQL.
CREATE TABLE `symbol_details` (
`symbol_header_id` INT(11) DEFAULT NULL,
`DATE` DATETIME DEFAULT NULL,
`NAV` DOUBLE DEFAULT NULL,
`ADJ_NAV` DOUBLE DEFAULT NULL)
Для фиксированных дат начала и окончания квартала, которые отлично работают:
set @quarterstart='2008-12-31';
set @quarterend='2009-3-31';
select sha, (100*(aend-abegin)/abegin) as q1_returns from
(select symbol_header_id as sha, ADJ_NAV as abegin from symbol_details
where date=@quarterstart) as a,
(select symbol_header_id as she, ADJ_NAV as aend from symbol_details
where date=@quarterend) as b
where sha=she;
Это вычисляет все квартальные возвраты для всех символов. Иногда этот конец квартала является неторговым днем, или акции прекратили свою деятельность, поэтому я хочу получить самые близкие даты начала и окончания до дат начала и окончания квартала.
Решение заключалось бы в том, чтобы каким-то образом получить только одно начальное и одно значение конца четверти для каждого символа_header_id с помощью некоторого оператора GROUP BY, например (1)
SET @quarterstart = '2009-03-01';
SET @quarterend = '2009-4-31';
SELECT symbol_header_id, DATE, ADJ_NAV AS aend FROM symbol_details
WHERE
DATE BETWEEN @quarterstart AND @quarterend
AND symbol_header_id BETWEEN 18540 AND 18550
GROUP BY symbol_header_id asc;
Это дает значение ADJ_NAV для даты, ближайшей к началу четверти для каждого из значений symbol_header_id.
Итак, наконец, (2)
SET @quarterstart = '2008-12-31';
SET @quarterend = '2009-3-31';
SELECT sh1, a.date, b.date, aend, abegin,
(100*(aend-abegin)/abegin) AS quarter_returns FROM
(SELECT symbol_header_id sh1, DATE, ADJ_NAV AS abegin FROM symbol_details
WHERE
DATE BETWEEN @quarterstart AND @quarterend
GROUP BY symbol_header_id DESC) a,
(SELECT symbol_header_id sh2, DATE, ADJ_NAV AS aend FROM symbol_details
WHERE
DATE BETWEEN @quarterstart AND @quarterend
GROUP BY symbol_header_id ASC) b
WHERE sh1 = sh2;
должен вычислять ежеквартальные значения для каждого символа.
К сожалению, это не сработает. По какой-то причине, когда я ограничиваю идентификаторы, как в (1), используются правильные даты начала и окончания, но когда инструкции "AND symbol_header_id BETWEEN 18540 AND 18550" удаляются, появляются одинаковые даты начала и окончания. Почему????
Ответ с развернутыми JOINs:
SET @quarterstart = '2008-12-31';
SET @quarterend = '2009-3-31';
SELECT tq.sym AS sym,
(100*(alast.adj_nav - afirst.adj_nav)/afirst.adj_nav) AS quarterly_returns
FROM
-- First, determine first traded days ("ftd") and last traded days
-- ("ltd") in this quarter per symbol
(SELECT symbol_header_id AS sym,
MIN(DATE) AS ftd,
MAX(DATE) AS ltd
FROM symbol_details
WHERE DATE BETWEEN @quarterstart AND @quarterend
GROUP BY 1) tq
JOIN symbol_details afirst
-- Second, determine adjusted NAV for "ftd" per symbol (see WHERE)
ON afirst.DATE BETWEEN @quarterstart AND @quarterend
AND afirst.symbol_header_id = tq.sym
JOIN
-- Finally, determine adjusted NAV for "ltd" per symbol (see WHERE)
symbol_details alast
ON alast.DATE BETWEEN @quarterstart AND @quarterend
AND alast.symbol_header_id = tq.sym
WHERE
afirst.date = tq.ftd
AND
alast.date = tq.ltd;
UPDATE:
Включить @Hogan предложение полностью... и проверить его.:) Этот EXPLAIN
намного проще и должен быть лучшим исполнителем.
Опять же, предполагая поведение ANSI_QUOTES
:
SELECT tq.sym AS sym,
(100*(alast.adj_nav - afirst.adj_nav)/afirst.adj_nav) AS quarterly_returns
FROM
(SELECT symbol_header_id AS sym, -- find first/last traded day ("ftd", "ltd")
MIN("date") AS ftd,
MAX("date") AS ltd
FROM symbol_details
WHERE "date" BETWEEN @quarterstart AND @quarterend
GROUP BY 1) tq
JOIN symbol_details afirst -- JOIN for ADJ_NAV on first traded day
ON tq.sym = afirst.symbol_header_id
AND
tq.ftd = afirst."date"
JOIN symbol_details alast -- JOIN for ADJ_NAV on last traded day
ON tq.sym = alast.symbol_header_id
AND
tq.ltd = alast."date"
ORIGINAL:
Предполагая SET SESSION sql_mode = 'ANSI_QUOTES'
, попробуйте следующее:
SELECT tq.sym AS sym,
(100*(adj_end - adj_begin)/adj_begin) AS quarterly_returns
FROM
-- First, determine first traded days ("ftd") and last traded days
-- ("ltd") in this quarter per symbol
(SELECT symbol_header_id AS sym,
MIN("date") AS ftd,
MAX("date") AS ltd
FROM symbol_details
WHERE "date" BETWEEN @quarterstart AND @quarterend
GROUP BY 1) tq
JOIN
-- Second, determine adjusted NAV for "ftd" per symbol (see WHERE)
(SELECT symbol_header_id AS sym,
"date" AS adate,
adj_nav AS adj_begin
FROM symbol_details) afirst
ON afirst.sym = tq.sym
JOIN
-- Finally, determine adjusted NAV for "ltd" per symbol (see WHERE)
(SELECT symbol_header_id AS sym,
"date" AS adate,
adj_nav AS adj_end
FROM symbol_details) alast
ON alast.sym = tq.sym
WHERE
afirst.adate = tq.ftd
AND
alast.adate = tq.ltd;
Пример разворачивания последнего подзапроса из следующего запроса:
(примечание - я не тестировал)
SELECT tq.sym AS sym,
(100*(alast.adj_nav - adj_begin)/adj_begin) AS quarterly_returns
FROM
-- First, determine first traded days ("ftd") and last traded days
-- ("ltd") in this quarter per symbol
(SELECT symbol_header_id AS sym,
MIN("date") AS ftd,
MAX("date") AS ltd
FROM symbol_details
WHERE "date" BETWEEN @quarterstart AND @quarterend
GROUP BY 1) tq
JOIN
-- Second, determine adjusted NAV for "ftd" per symbol (see WHERE)
(SELECT symbol_header_id AS sym,
"date" AS adate,
adj_nav AS adj_begin
FROM symbol_details) afirst
ON afirst.sym = tq.sym
-- Finally, determine adjusted NAV for "ltd" per symbol (see WHERE)
LEFT JOIN symbol_details alast ON tq.sym = alast.symbol_header_id AND tg.ltd = alast."date"
WHERE
afirst.adate = tq.ftd
Вы уверены, что используете свои запросы GROUP BY... ASC? GROUP BY группы, строки, удовлетворяющие некоторым критериям и использующие ASC/DESC, не имеют смысла. Кстати, в вашем случае ваш критерий равен равенству столбца symbol_header_id, который, считая, что "id" означает, что он является ключом, эффективно делает группы, состоящие из одной строки.
В любом случае, если ваши группы действительно являются группами, у вас могут быть проблемы, потому что вы выбираете не только группировку атрибутов и агрегатных функций, но и некоторые другие атрибуты. В этом случае их значение непредсказуемо.