今天是工作的第一天,一位分析师给了一组微软的收盘股票报价。她希望给她一个报告,显示收盘数据、收盘价、10日移动平均、30日移动平均和信号。信号是"Over"或"Below",取决于10日移动平均是否大于或小于30日移动平均。输出应该像这样:
DECLARE @DailyQuote TABLE (
MarketDate DATE,
ClosingPrice DECIMAL(10,2)
)
下载这个脚本来创建表并用示例数据填充它。
在开始SQL之前,让确保知道在计算什么!要计算收盘价的10日移动平均,需要计算当前和过去9天的收盘价。对30日移动平均做同样的操作,但在那种情况下,会包含更多的天数。
计算移动平均的一个简单方法是设置一个窗口。可以使用OVER子句来做到这一点。以下是计算10日移动平均MA10的语句:
SELECT MarketDate,
ClosingPrice,
AVG(ClosingPrice)
OVER (
ORDER BY MarketDate ASC
ROWS 9 PRECEDING
) AS MA10
FROM @DailyQuote
在OVER子句中,按MarketDate排序,然后使用PRECEDING子句定义窗口,从当前行开始,然后向上九行。这使得窗口总共有10行。
在这种方法中,10日移动平均是为结果的前几行计算的;平均值是一天、两天、三天的移动平均,直到真正到达第十天。技术上,它应该从第十行开始计算。为了解决这个问题,还计算了ROW_NUMBER,如果ROW_NUMBER小于10,则返回NULL。
为此,计算了ROW_NUMBER、10日、30日的移动平均值在一个公共表表达式中。会在下面的绿色部分看到这一点。
WITH CTE_DailyQuote (MarketDate, ClosingPrice, RowNumber, MA10, MA30)
AS (
SELECT MarketDate,
ClosingPrice,
ROW_NUMBER()
OVER (
ORDER BY MarketDate ASC
) RowNumber,
AVG(ClosingPrice)
OVER (
ORDER BY MarketDate ASC
ROWS 9 PRECEDING
) AS MA10,
AVG(ClosingPrice)
OVER (
ORDER BY MarketDate ASC
ROWS 29 PRECEDING
) AS MA30
FROM @DailyQuote
)
SELECT MarketDate,
RowNumber,
ClosingPrice,
IIF(RowNumber > 9, MA10, NULL) MA10,
IIF(RowNumber > 29, MA30, NULL) MA30,
CASE
WHEN RowNumber > 29 AND MA10 > MA30 THEN 'Over'
WHEN RowNumber > 29 AND MA10 < MA30 THEN 'Below'
ELSE NULL
END as Signal
FROM CTE_DailyQuote
ORDER BY MarketDate