用于余额交易量（财务查询）的SQL函数

Question

我想为平衡交易量（SQL函数）创建一个函数。 这太复杂了，但是这里是用户定义表函数的概要。如果有人能帮助我填补空白，我将不胜感激。 克雷格

CREATE FUNCTION [dbo].[GetStdDev3] (@TKR VARCHAR(10)) 
    RETURNS @results TABLE (
    dayno SMALLINT IDENTITY(1,1) PRIMARY KEY 
    , [date] DATETIME 
    , [obv] FLOAT 
    ) 
    AS BEGIN 

    DECLARE @rowcount SMALLINT 
    INSERT @results ([date], [obv]) 

// CREATE A FUNCTION FOR ON BALANCE VOLUME 
// On Balance Volume is the Summ of Volume for Total Periods 
// OBV = 1000 at Period = 0 
// OBV = OBV Previous + Previous Volume if Close > Previous Close 
// OBV = OBV Previous - Previous Volume if Close < Previous Close 
// OBV = OBV Previous if Close = Previous Close 

// The actual Value of OBV is not important so to keep the ratio low we reduce the 
// Total Value of Tickers by 1/10th or 1/100th 
// For Value of Volume = Volume * .01 if Volume < 999 
// For Value of Volume = Volume * .001 If Volume >= 999 
    FROM Tickers 

    RETURN 

    END 

这是代号表

[dbo].[Tickers](
[ticker] [varchar](10) NULL, 
[date] [datetime] NULL, 
[high] [float] NULL, 
[low] [float] NULL, 
[open] [float] NULL, 
[close] [float] NULL, 
[volume] [float] NULL, 
[time] [datetime] NULL, 
[change] [float] NULL 
) 

这里是数据的一例

ticker date close volume 
    pzi: 5-10-10 10.94 805 
    pzi; 5-11-10 11.06 444 
    pzi: 5-12-10 11.42 236 
    pzi: 5-13-10 11.3 635 
    pzi: 5-14-10 11 316 

    date obv 
    5-10 996.38 
    5-11 996.82 
    5-12 997.06 
    5-13 996.42 
    5-14 996.11 

Answer 1

这里的一个工作内嵌表值函数（最有效的优化）：

CREATE FUNCTION [dbo].[GetStdDev3] (@TKR VARCHAR(10)) 
    RETURNS TABLE 
    AS RETURN (
    WITH Y AS (SELECT * 
        ,OBV_Change = ISNULL(SIGN(currclose - prevclose) 
             * volume, 1000) 
      FROM  (SELECT curr.date 
           ,curr.[CLOSE] AS currclose 
           ,prev.[CLOSE] AS prevclose 
           ,curr.volume 
         FROM  Tickers AS curr 
         LEFT JOIN Tickers AS prev 
           ON prev.ticker = @TKR 
            AND prev.date = (SELECT MAX(date) 
                FROM Tickers 
                WHERE ticker = @TKR 
                  AND date < curr.date 
                ) 
         WHERE  curr.ticker = @TKR 
        ) AS X 
     ) 
SELECT y1.date 
     ,SUM(y2.OBV_Change) AS OBV 
     ,ROW_NUMBER() OVER(ORDER BY y1.date) AS dayno 
FROM Y AS y1 
LEFT JOIN Y AS y2 
     ON y2.date <= y1.date 
GROUP BY y1.date 
) 

我对标准化没有把握 - 我放弃了这一点 - 添加它可能需要您将其加入到多语句TVF中。