SQL Server递归CTE帮助 [英] SQL Server recursive cte help wanted
问题描述
我正在尝试生成数据集，并且我认为需要递归，但是我不能完全根据自己的目的来适应大量的示例.我想要的最终结果很容易描述.
I'm trying to generate a data set, and I think recursion is required, but I can't quite adapt the numerous examples I've found to my purposes. The end result I want is fairly simple to describe.
涉及两个表:第一个表具有相关字段 TradeDate ， Symbol 和 Clse (收盘价)的股票的交易价格.，以及列出交易日的表格.我想按价格和日期对符号划分价格数据，但是如果价格表中缺少交易日期，我希望分区/行编号可以打断，因为该数据来自Web服务.有时有些变幻莫测.
Two tables are involved: the first with trading pricing for stocks with the relevant fields TradeDate, Symbol, and Clse (closing price), and a table with trading days listed. I'd like to partition the pricing data by symbol, ordered by date, but I'd like the partition/ row numbering to break if a trade date is missing from the pricing table, since this data comes in from a web service that is a little unpredictable at times.
我的第一次尝试是一个足够简单的选择查询，该查询具有一个 WHERE 子句，该子句调用了我编写的udf，以查看该符号/日期组合是否存在连续数据.它运行了(运行了)，而当晚我运行了它，在7个小时之后，它几乎遍历了以'A'开头的符号.
My first attempt was a simple enough select query that had a WHERE clause that called a udf I wrote to see if there was continuous data for that symbol/ date combination. It worked (it ran) and the night I ran it, after 7 hours it was almost through the symbols starting with 'A.'
编辑:
我的这篇文章和响应稍早返回 SQL服务器选择SO上所需存储过程的查询帮助从概念上讲是有帮助的，但是我目前对该概念的使用需要更多(表连接，分区/行编号等)
EDIT:
This post of mine and the response a little while back SQL server select query help for stored procedure needed on SO was helpful conceptually, but my current use of the concept requires a little more (table join, partitioning/ row numbering etc)
如果它有助于理解问题，我想使用返回的值来计算每个符号，使用过去数据点的交易日期组合的各种汇总.示例:交易品种/交易日收盘价的5个周期移动平均值将是交易日期< =计算日期的五个收盘价(clse)的平均值.因此，使用第一张表中的以下示例数据，我想返回9.02的符号"A"，3个期间，交易日期1/3/12.如果其中一项计算缺少数据，我想为空.
If it helps understand the problem, I want to use what's returned to calculate various aggregations per symbol, trade date combination that use the past data points. Example: 5 period moving average of closing price for a symbol/ trade date would be the average of the five closing prices (clse) that have a trade date <= the date being calculated for. So using the sample data below from the first table, I want to return 9.02 for symbol 'A', 3 periods, trade date 1/3/12. If data is missing for one of the calculations I want null.
如果不是要检查丢失的日期，我想要的结果集将是一个简单的PARTITION select查询.因此，这是所涉及表的一些示例数据以及我的目标结果集:
This result set I want would be a simple PARTITION select query if it weren't for having to check for missing dates. So here is some sample data of the tables involved and my goal result set:
tblDailyPricingAndVol
TradeDate Symbol Clse
1/1/12 A 9.01
1/2/12 A 9.05
1/3/12 A 8.99
1/5/12 A 9.03
1/1/12 B 10.05
1/4/12 B 10.11
1/5/12 B 10.03
tblTradingDays
TradingDate
1/1/12
1/2/12
1/3/12
1/4/12
1/5/12
目标结果集:
RowNumber TradeDate Symbol Clse
1 1/1/12 A 9.01
2 1/2/12 A 9.05
3 1/3/12 A 8.99
1 1/5/12 A 9.03
1 1/1/12 B 10.05
1 1/4/12 B 10.11
2 1/5/12 B 10.03
希望如此.任何帮助，将不胜感激.我想，如果我可以在自己的数据上按预期方式运行递归cte，则会更加清楚.谢谢.
Hope that makes sense. Any help would be appreciated. I think I'll see recursive cte's much more clearly if I can get one running as expected on my own data. Thanks.
推荐答案
我认为将 dense_rank()和 row_number()结合使用会在这里起作用:
I think combining dense_rank() and row_number() will work here:
; with cte as (
select d.TradingDate,p.Symbol,p.Clse
, r=DENSE_RANK()over(order by d.TradingDate)
, r1=row_number()over(partition by p.Symbol order by p.TradeDate)
from tblTradingDays d
inner join tblDailyPricingAndVol p on d.TradingDate=p.TradeDate
)
select
RowNumber=row_number()over(partition by Symbol, (r-r1) order by TradingDate)
, TradingDate,Symbol,Clse
from cte
go
结果:
这篇关于SQL Server递归CTE帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
