SQL代码根据现有的行生成新的行 [英] SQL code to generate new rows based on an existing rows
问题描述
最近的问题给了我一个简单的方法来生成这样的格式的数据：
日期1.0 2.0 3.0已完成
2017-01-01 10 10 5 0
2017-02-01 10 10 5 5
2017-03-01 15 10 5 15
2017-04-01 15 10 5 25
该数据表示敏捷项目的耗尽数据。这只是过去的数据，我们也需要预测未来的数据。只有Date和Completed值将来会改变，并且会在另一个表中增加值，比如
Project Velocity天
Foo 25 14
Bar 35 21
我需要生成额外的行在第一个表格中，Date和Completed值按第二个表格中的值递增，
是这样的：
日期1.0 2.0 3.0已完成
2017-04-15 15 10 5 50
2017-04-29 15 10 5 75
2017-05-13 15 10 5 100
2017 -05-28 15 10 5 125
还没有额外的行数未来预测），但我现在正在拍摄10个额外的行。
因为SQL似乎不是很大的FOR循环，我怎么可能生成这些额外的行和UNION他们（或其他）与原始数据？
$ b
更新＃1：
根据评论，我看了一个Numbers表（关于SQL我从来没有听说过的东西），创建了一个，并像这样使用它：
DECLARE @EndDate date ='20170731'
DECLARE @StartDate date =（select top 1 DateHistory中的日期按日期排序DESC）
DECLARE @StartCompleted int =从VersionHistory中选择顶级的1个StoryPoint，其中Version ='Completed'按日期排序DESC）
DECLARE @DateIncrement int =（从Velocities中选择前1天）
DECLARE @Velocity int =（select top 1 Velocity ）
SELECT
[Date] = DATEADD（DAY，Number * @DateIncrement，@StartDate），
[StoryPoints] = @StartCompleted + Number * @Velocity
FROM dbo.Numbers
WHERE Number< = DATEDIFF（DAY，@StartDate，@EndDate）/ @ DateIncrement
ORDER BY Date;
这样会产生新的未来值：
日期StoryPoints
2017-04-15 50
2017-04-29 75
2017-05-13 100
2017-05-27 125
2017-06-10 150
2017-06-24 175
2017-07-08 200
2017-07-22 225
几乎就是。我仍然需要重复来自原始表中其他列的值，以便最终输出如下所示：
日期1.0 2.0 3.0已完成
2017-04-15 15 10 5 50
2017-04-29 15 10 5 75
2017-05-13 15 10 5 100
2017 -05-28 15 10 5 125
...
由于我的原始数据只是来自另一个声明（请参阅相关问题），如何从该数据的最后一行获取这些值，并将其包含在这个新的数据？请记住，除了日期和已完成之外，列名不会事先知道。
我假设我必须更改我的原始数据以将其存储在临时表中以提取我所需要的，将这个新的Numbers-Table派生的数据存储在另一个临时表中，然后UNION它们，但我坚持一些这个机制。 h2_lin>解决方案
感谢您的好评。我能够使用Numbers表来帮助为我的表创建预测行。从某些列（@columnsVersions）的最后一行（@columnsVersions）获取值是有点困难的，但是这里是使用这些值（@versionValues）并在表中填充其他行的代码：
DECLARE @StartDate date =（select top 1从VersionHistory得到的日期其中project = @ Project和Version ='Completed'按日期排序DESC）
DECLARE @StartCompleted int =（从VersionHistory中选择1个StoryPoints，其中Project = @ Project和Version ='Completed'按日期排序DESC）
DECLARE @DateIncrement int =（从Velocities中选择前1天where project = @Project）
DECLARE @Velocity int =（选择top Velocity中的Velocity项目= @Project）
DECLARE @EndDate date ='2017-12-31'
set @ q ='
insert into＃T1（Date，Predicted，'+ @columnsVersions +'）
SELECT
DATEADD（DAY，Number *'+ CONVERT（varchar（10） ，@DateIncrement）+'，'''+ CONVERT（varch ar（10），@StartDate）+'''）
，'+ CONVERT（varchar（10），@StartCompleted）+'+ Number *'+ CONVERT（varchar（10），@Velocity）+'
，'+ @versionValues +'
FROM dbo.Numbers
WHERE Number< = DATEDIFF（DAY，'''+ CONVERT（varchar（10），@ StartDate）+'''， 'CONVERT（varchar（10），@ DateIncrement）
$ b exec（@q）
select *从＃T1
A recent question of mine gave me a simple method of generating data in a format like this:
Date 1.0 2.0 3.0 Completed
2017-01-01 10 10 5 0
2017-02-01 10 10 5 5
2017-03-01 15 10 5 15
2017-04-01 15 10 5 25
That data represents the "burnup" data for an Agile project. That is just the past data, though, and we need to predict the future data too. Only the Date and the Completed values will change in the future, and they will increment by values in another table, like
Project Velocity Days
Foo 25 14
Bar 35 21
I need to generate additional rows in the 1st table, with the Date and Completed values incrementing by the values in the 2nd table, like this:
Date 1.0 2.0 3.0 Completed
2017-04-15 15 10 5 50
2017-04-29 15 10 5 75
2017-05-13 15 10 5 100
2017-05-28 15 10 5 125
Do not yet have the number of additional rows needed (how far into the future to predict), but I'm shooting for 10 additional rows for now.
Since SQL doesn't seem to be big into FOR loops, how could I generate these additional rows and UNION them (or something else) with the original data?
UPDATE #1: Based on the comments, I looked into a Numbers table (something else about SQL I had never heard of!), created one, and used it like this:
DECLARE @EndDate date = '20170731'
DECLARE @StartDate date = (select top 1 Date from VersionHistory order by Date DESC)
DECLARE @StartCompleted int = (select top 1 StoryPoints from VersionHistory where Version='Completed' order by Date DESC)
DECLARE @DateIncrement int = (select top 1 Days from Velocities)
DECLARE @Velocity int = (select top 1 Velocity from Velocities)
SELECT
[Date] = DATEADD(DAY, Number * @DateIncrement, @StartDate),
[StoryPoints] = @StartCompleted + Number * @Velocity
FROM dbo.Numbers
WHERE Number <= DATEDIFF(DAY, @StartDate, @EndDate)/@DateIncrement
ORDER BY Date;
This generated the new "future" values like this:
Date StoryPoints
2017-04-15 50
2017-04-29 75
2017-05-13 100
2017-05-27 125
2017-06-10 150
2017-06-24 175
2017-07-08 200
2017-07-22 225
This is almost all the way. I still need to "repeat" the values from the other columns in the original table, so that the final output looks like this:
Date 1.0 2.0 3.0 Completed
2017-04-15 15 10 5 50
2017-04-29 15 10 5 75
2017-05-13 15 10 5 100
2017-05-28 15 10 5 125
...
Since my original data is just coming from another statement (see related question), how can I grab those values from the LAST row in that data and include in this new data? Remember that the column names are NOT known in advance, other than Date and Completed.
I assume I'll have to change my original data to store it in a temp table to extract what I need, store this new Numbers-table derived data in another temp table, and then UNION them, but I'm stuck on some of the mechanics of this.
Thanks for the great comments. I was able to use a Numbers table to help create the "predicted" rows for my table. It was a little difficult to get the values from the last row (of original data) of certain columns (@columnsVersions) to use in this, but here's the code for using those values (@versionValues) and populating additional rows in the table:
DECLARE @StartDate date = (select top 1 Date from VersionHistory where project=@Project and Version='Completed' order by Date DESC)
DECLARE @StartCompleted int = (select top 1 StoryPoints from VersionHistory where project=@Project and Version='Completed' order by Date DESC)
DECLARE @DateIncrement int = (select top 1 Days from Velocities where project = @Project)
DECLARE @Velocity int = (select top 1 Velocity from Velocities where project = @Project)
DECLARE @EndDate date = '2017-12-31'
set @q='
insert into #T1 (Date, Predicted, ' + @columnsVersions + ')
SELECT
DATEADD(DAY, Number * ' + CONVERT(varchar(10), @DateIncrement) + ',''' + CONVERT(varchar(10), @StartDate) + ''')
,' + CONVERT(varchar(10), @StartCompleted) + ' + Number * ' + CONVERT(varchar(10), @Velocity) + '
,' + @versionValues + '
FROM dbo.Numbers
WHERE Number <= DATEDIFF(DAY,''' + CONVERT(varchar(10),@StartDate) + ''',''' + CONVERT(varchar(10), @EndDate) + ''')/' + CONVERT(varchar(10),@DateIncrement)
exec(@q)
select * from #T1
这篇关于SQL代码根据现有的行生成新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
