SQL Server 2012多列输出查询及股票14日RSI计算脚本问题

1. Completing the RSI Calculation Script #

Your initial WHILE loop approach is a valid starting point, but row-by-row loops are often less efficient in SQL than set-based operations. Let’s finish your loop-based script first, then share a recommended set-based alternative for better performance.

WHILE Loop Version #

Assume you have a StockQuotes table with columns StockID, QuoteDate, and ClosePrice (adjust to match your actual schema):

DECLARE @StartingDate smalldatetime 
DECLARE @EndingDate smalldatetime 
DECLARE @FinalDate smalldatetime 
DECLARE @StockID char(15) 
DECLARE @DAYS INT 
DECLARE @AG FLOAT(4) 
DECLARE @AL FLOAT(4) 
DECLARE @RS FLOAT(4) 
DECLARE @RSI FLOAT(4)

-- Initialize variables
SET @StartingDate = '20180101' 
SET @FinalDate = '20180405' 
SET @StockID = 'ACE' 
SET @DAYS = 14 

-- Start with the first date where we have 14 full days of prior data
SET @EndingDate = DATEADD(day, @DAYS - 1, @StartingDate)

-- Temp table to store results (optional but useful for analysis)
CREATE TABLE #RSIResults (
    StockID char(15),
    CalculationDate smalldatetime,
    RSI float(4)
)

WHILE (@EndingDate <= @FinalDate)
BEGIN
    -- Calculate average gain (avg of positive daily changes over 14 days)
    SELECT @AG = AVG(CASE WHEN DailyChange > 0 THEN DailyChange ELSE 0 END)
    FROM (
        SELECT 
            ClosePrice - LAG(ClosePrice, 1) OVER (ORDER BY QuoteDate) AS DailyChange
        FROM StockQuotes
        WHERE StockID = @StockID
          AND QuoteDate BETWEEN DATEADD(day, -@DAYS + 1, @EndingDate) AND @EndingDate
    ) AS Changes

    -- Calculate average loss (avg of absolute negative daily changes over 14 days)
    SELECT @AL = AVG(CASE WHEN DailyChange < 0 THEN ABS(DailyChange) ELSE 0 END)
    FROM (
        SELECT 
            ClosePrice - LAG(ClosePrice, 1) OVER (ORDER BY QuoteDate) AS DailyChange
        FROM StockQuotes
        WHERE StockID = @StockID
          AND QuoteDate BETWEEN DATEADD(day, -@DAYS + 1, @EndingDate) AND @EndingDate
    ) AS Changes

    -- Compute RS and RSI (handle division by zero)
    SET @RS = CASE WHEN @AL = 0 THEN NULL ELSE @AG / @AL END
    SET @RSI = CASE WHEN @RS IS NULL THEN 100 ELSE 100 - (100 / (1 + @RS)) END

    -- Save result to temp table
    INSERT INTO #RSIResults (StockID, CalculationDate, RSI)
    VALUES (@StockID, @EndingDate, @RSI)

    -- Move to next date
    SET @EndingDate = DATEADD(day, 1, @EndingDate)
END

-- Output final results
SELECT * FROM #RSIResults ORDER BY CalculationDate

-- Cleanup temp table
DROP TABLE #RSIResults

Set-Based Alternative (Recommended) #

Window functions make RSI calculation far more efficient for large datasets:

DECLARE @StartingDate smalldatetime = '20180101'
DECLARE @FinalDate smalldatetime = '20180405'
DECLARE @StockID char(15) = 'ACE'
DECLARE @DAYS INT = 14

WITH DailyChanges AS (
    SELECT 
        StockID,
        QuoteDate,
        ClosePrice - LAG(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY QuoteDate) AS DailyChange
    FROM StockQuotes
    WHERE StockID = @StockID
      AND QuoteDate BETWEEN DATEADD(day, -@DAYS + 1, @StartingDate) AND @FinalDate
),
AvgGainsLosses AS (
    SELECT 
        StockID,
        QuoteDate,
        AVG(CASE WHEN DailyChange > 0 THEN DailyChange ELSE 0 END) OVER (ORDER BY QuoteDate ROWS BETWEEN @DAYS -1 PRECEDING AND CURRENT ROW) AS AvgGain,
        AVG(CASE WHEN DailyChange < 0 THEN ABS(DailyChange) ELSE 0 END) OVER (ORDER BY QuoteDate ROWS BETWEEN @DAYS -1 PRECEDING AND CURRENT ROW) AS AvgLoss
    FROM DailyChanges
)
SELECT 
    StockID,
    QuoteDate AS CalculationDate,
    ROUND(CASE 
        WHEN AvgLoss = 0 THEN 100
        WHEN AvgGain = 0 THEN 0
        ELSE 100 - (100 / (1 + (AvgGain / AvgLoss))) 
    END, 2) AS RSI_14Day
FROM AvgGainsLosses
WHERE QuoteDate >= DATEADD(day, @DAYS -1, @StartingDate) -- Only include dates with full 14-day history
  AND QuoteDate <= @FinalDate
ORDER BY QuoteDate

2. Multi-Column Output in SQL Server 2012 #

Multi-column output is straightforward—just specify the columns you want in your SELECT clause. You can include raw table columns, computed values, or joined data.

Example 1: Add Stock Metrics to RSI Results #

Extend the set-based query to include closing price, daily change, and percentage change:

DECLARE @StartingDate smalldatetime = '20180101'
DECLARE @FinalDate smalldatetime = '20180405'
DECLARE @StockID char(15) = 'ACE'
DECLARE @DAYS INT = 14

WITH DailyData AS (
    SELECT 
        StockID,
        QuoteDate,
        ClosePrice,
        ClosePrice - LAG(ClosePrice,1) OVER (PARTITION BY StockID ORDER BY QuoteDate) AS DailyChange,
        ROUND(((ClosePrice - LAG(ClosePrice,1) OVER (PARTITION BY StockID ORDER BY QuoteDate)) / LAG(ClosePrice,1) OVER (PARTITION BY StockID ORDER BY QuoteDate)) *100,2) AS DailyPercentChange
    FROM StockQuotes
    WHERE StockID = @StockID
      AND QuoteDate BETWEEN DATEADD(day, -@DAYS +1, @StartingDate) AND @FinalDate
),
AvgGainsLosses AS (
    SELECT 
        *,
        AVG(CASE WHEN DailyChange >0 THEN DailyChange ELSE 0 END) OVER (ORDER BY QuoteDate ROWS BETWEEN @DAYS -1 PRECEDING AND CURRENT ROW) AS AvgGain,
        AVG(CASE WHEN DailyChange <0 THEN ABS(DailyChange) ELSE 0 END) OVER (ORDER BY QuoteDate ROWS BETWEEN @DAYS -1 PRECEDING AND CURRENT ROW) AS AvgLoss
    FROM DailyData
)
SELECT 
    StockID,
    QuoteDate,
    ClosePrice,
    DailyChange,
    DailyPercentChange,
    ROUND(CASE 
        WHEN AvgLoss =0 THEN 100
        WHEN AvgGain =0 THEN 0
        ELSE 100 - (100/(1 + (AvgGain/AvgLoss))) 
    END,2) AS RSI_14Day
FROM AvgGainsLosses
WHERE QuoteDate >= DATEADD(day, @DAYS -1, @StartingDate)
  AND QuoteDate <= @FinalDate
ORDER BY QuoteDate

Example 2: Join Tables for Contextual Data #

If you have a Stocks table with company details, join it to add context to your results:

SELECT 
    s.StockID,
    s.CompanyName,
    r.CalculationDate,
    r.RSI_14Day,
    q.ClosePrice
FROM (
    -- Insert the set-based RSI query here as a subquery
    SELECT 
        StockID,
        QuoteDate AS CalculationDate,
        ROUND(CASE 
            WHEN AvgLoss =0 THEN 100
            WHEN AvgGain =0 THEN 0
            ELSE 100 - (100/(1 + (AvgGain/AvgLoss))) 
        END,2) AS RSI_14Day
    FROM AvgGainsLosses
    WHERE QuoteDate >= DATEADD(day, @DAYS -1, @StartingDate)
      AND QuoteDate <= @FinalDate
) r
JOIN StockQuotes q ON r.StockID = q.StockID AND r.CalculationDate = q.QuoteDate
JOIN Stocks s ON r.StockID = s.StockID
ORDER BY r.CalculationDate

Key takeaways:

• Separate columns with commas in the SELECT clause.
• Use functions like ROUND() to format numeric values.
• Use CASE statements for conditional computed columns.
• Join tables to pull in related data from multiple sources.

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