Использование CASE для вычисляемого столбца в SQL Server 2012
У меня есть база данных с именем temp, с ежедневными ценами на акции:
Ticker Date price
ABC 01/01/13 100.00
ABC 01/02/13 101.50
ABC 01/03/13 99.80
ABC 01/04/13 95.50
ABC 01/05/13 78.00
XYZ 01/01/13 11.50
XYZ 01/02/13 12.10
XYZ 01/03/13 17.15
XYZ 01/04/13 14.10
XYZ 01/05/13 15.55
Я рассчитал промежуточную сумму максимальной цены для каждой акции и совокупного drawdown за каждую акцию за каждый день: (максимальная цена - текущая цена) / максимальная цена)
SELECT t.Ticker,
t.Date,
t.price,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1
as Drawdown
FROM [temp] t;
Ticker Date price max_price Drawdown
ABC 01/01/13 100.00 100.00 0.00000
ABC 01/02/13 101.50 101.50 0.00000
ABC 01/03/13 99.80 101.50 -0.01675
ABC 01/04/13 95.50 101.50 -0.05911
ABC 01/05/13 78.00 101.50 -0.23153
XYZ 01/01/13 11.50 11.50 0.00000
XYZ 01/02/13 12.10 12.10 0.00000
XYZ 01/03/13 17.15 17.15 0.00000
XYZ 01/04/13 14.10 17.15 -0.17784
XYZ 01/05/13 15.55 17.15 -0.09329
Теперь я хочу создать еще один столбец с именем peak_cnt.
Peak_cnt будет иметь двоичный вывод: 1 если drawdown = 0 и 0 для всего остального.
Вот что я хочу произвести:
Ticker Date price max_price Drawdown Peak_cnt
ABC 01/01/13 100.00 100.00 0.00000 1
ABC 01/02/13 101.50 101.50 0.00000 1
ABC 01/03/13 99.80 101.50 -0.01675 0
ABC 01/04/13 95.50 101.50 -0.05911 0
ABC 01/05/13 78.00 101.50 -0.23153 0
XYZ 01/01/13 11.50 11.50 0.00000 1
XYZ 01/02/13 12.10 12.10 0.00000 1
XYZ 01/03/13 17.15 17.15 0.00000 1
XYZ 01/04/13 14.10 17.15 -0.17784 0
XYZ 01/05/13 15.55 17.15 -0.09329 0
Будет ли здесь работать CASE? Я пробовал несколько разных версий CASE, но не имел успеха. Это самое дальнее, что я получил с CASE:
SELECT t.Ticker,
t.Date,
t.price,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown,
CASE WHEN 'Drawdown' < 0 Then 0
ELSE
END as Peak_cnt
FROM [temp] t;
Conversion failed when converting the varchar value 'Drawdown' to data type int.
Есть предложения по успешному использованию CASE или другого решения?
2 ответа
Нет, это не сработает.
Вам нужно переместить основной запрос в подзапрос и использовать ссылку на выражение прецедента на новый Drawdown псевдоним во внешнем запросе (на один уровень выше):
SELECT x.*,
CASE WHEN Drawdown <> 0 Then 0
ELSE 1
END as Peak_cnt
FROM (
SELECT t.Ticker,
t.Date,
t.price,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
FROM [temp] t
) x
в качестве альтернативы вы можете скопировать все выражение в CASE WHEN ... таким образом
SELECT t.Ticker,
t.Date,
t.price,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown,
CASE WHEN (t.price / max(t.price)
over (partition by ticker order by date)) - 1 < 0
Then 0
ELSE 1
END as Peak_cnt
FROM [temp] t;
Часть кода, где происходит ошибка, находится здесь:
.... CASE WHEN 'Drawdown' < 0 Then 0
ELSE
END as Peak_cnt...
Причина в том, что вы пытаетесь использовать 'Drawdown' ссылка на указатель на псевдоним столбца, который вы создали в том же select пункт. Компилятор читает тот же код и пытается сравнить слово "просадка" с числом "0" и не может понять, как, потому что он еще не обработал псевдоним столбца.
Один из способов выполнить вычисление, которое вы ищете, - это использовать подзапрос или CTE, который будет выглядеть как запрос ниже.
; with first_query as
(
SELECT t.Ticker,
t.Date,
t.price,
max(t.price) over (partition by ticker order by date) as max_price,
(t.price / max(t.price) over (partition by ticker order by date)) - 1 as Drawdown
FROM [temp] t
)
select a.Ticker
, a.Date
, a.max_price
, a.Drawdown
, case when a.drawdown < 0 then 0 else 1 end as peak_cnt
from first_query as a