SQL Server 2012：如何基于股票价格表计算回撤天数？

Let's break down how to compute the Days in Drawdown for your stock price data. First, let's recap your sample data structure to make sure we're working with the right foundation:

DECLARE @table TABLE (ClosingDate DATE, Ticker VarChar(6), Price Decimal (6,2))
INSERT INTO @Table VALUES 
('2013-01-01' , 'ABC' , 100.00),
('2013-01-02' , 'ABC' , 101.50),
('2013-01-03' , 'ABC' , 99.80),
('2013-01-04' , 'ABC' , 95.50),
('2013-01-05' , 'ABC' , 78.00),
('2013-01-01' , 'JKL' , 34.57),
('2013-01-02' , 'JKL' , 33.99),
('2013-01-03' , 'JKL' , 31.85),
('2013-01-04' , 'JKL' , 30.11),
('2013-01-05' , 'JKL' , 45.00),
('2013-01-01' , 'XYZ' , 11.50),
('2013-01-02' , 'XYZ' , 12.10)
-- Add remaining rows as needed

What is a "Day in Drawdown"? #

A day counts as being in drawdown if the stock's closing price is below the highest closing price it's reached up to that point in time. Once the price climbs back to (or exceeds) that previous peak, the drawdown period ends.

Step-by-Step Solution #

We'll use window functions (fully supported in SQL Server 2012) to track peaks and group consecutive drawdown days:

1. Calculate the running maximum price for each stock up to each date. This tells us the peak price the stock has hit so far.
2. Identify drawdown days by checking if the current price is less than the running maximum.
3. Group consecutive drawdown days using row numbering tricks to create unique IDs for each continuous drawdown period.
4. Compute the number of days in each drawdown period (or flag each day with its position in the current drawdown).

Full Query

WITH StockPeaks AS (
    -- Step 1: Get the running maximum price for each stock up to each date
    SELECT 
        ClosingDate,
        Ticker,
        Price,
        MAX(Price) OVER (
            PARTITION BY Ticker 
            ORDER BY ClosingDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS RunningMaxPrice
    FROM @table
),
DrawdownFlags AS (
    -- Step 2: Flag days where price is below the running max (drawdown days)
    SELECT 
        *,
        CASE WHEN Price < RunningMaxPrice THEN 1 ELSE 0 END AS IsInDrawdown
    FROM StockPeaks
),
DrawdownGroups AS (
    -- Step 3: Group consecutive drawdown days using row number differences
    SELECT 
        *,
        -- Create a unique group ID for each continuous drawdown period
        ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY ClosingDate) 
        - ROW_NUMBER() OVER (PARTITION BY Ticker, IsInDrawdown ORDER BY ClosingDate) AS DrawdownGroupID
    FROM DrawdownFlags
)
-- Step 4: Calculate days in each drawdown (or show daily drawdown status)
SELECT 
    ClosingDate,
    Ticker,
    Price,
    RunningMaxPrice,
    IsInDrawdown,
    -- For each drawdown group, count how many days have passed since the start of the drawdown
    CASE WHEN IsInDrawdown = 1 THEN 
        ROW_NUMBER() OVER (PARTITION BY Ticker, DrawdownGroupID ORDER BY ClosingDate) 
    ELSE 0 END AS DaysInCurrentDrawdown,
    -- Optional: Total days in the entire drawdown period
    MAX(CASE WHEN IsInDrawdown = 1 THEN 
        ROW_NUMBER() OVER (PARTITION BY Ticker, DrawdownGroupID ORDER BY ClosingDate) 
    ELSE 0 END) OVER (PARTITION BY Ticker, DrawdownGroupID) AS TotalDrawdownDays
FROM DrawdownGroups
ORDER BY Ticker, ClosingDate;

How This Works #

• StockPeaks: The MAX() OVER window function tracks the highest price each stock has seen up to each date. For example, ABC's running max on 2013-01-03 is 101.50 (from 2013-01-02), even though the price dropped to 99.80 that day.
• DrawdownFlags: We mark each day as in drawdown (1) or not (0) by comparing the current price to the running max.
• DrawdownGroups: The row number difference trick creates a unique ID for each stretch of consecutive drawdown days. For non-drawdown days, this ID will also group consecutive non-drawdown periods, but we only care about the groups where IsInDrawdown = 1.
• Final Select: We calculate both the current day's position in the drawdown (e.g., day 1, day 2 of the drawdown) and the total length of the drawdown period for context.

Example Output for ABC #

This shows ABC entered a drawdown on 2013-01-03, and was in it for 3 consecutive days (as of 2013-01-05).

内容的提问来源于stack exchange，提问作者Coding_Newbie