sql - 在 SQL Server 2012 中对计算列使用 CASE

Question

我有一个名为 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 

我计算了每只股票的最高价格和每天每只股票的累计回撤的总和:(最高价格 - 当前价格)/最高价格)

       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 将有一个二进制输出:如果 drawdown = 0，则为 1；如果为其他值，则为 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 或任何其他解决方案有什么建议吗？

Answer 1

不，这行不通。
您需要将主查询移至子查询，并使用用例表达式引用外部查询中的新 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;