动态列依赖于以前的动态列 - TSQL [英] Dynamic columns depend on previous dynamic columns - TSQL
问题描述
我尝试根据之前的列(月)创建 18(！)个月的动态预测，但我卡住了:
I try to create dynamic forecast for 18(!) months depend on previous columns (months) and i am stuck:
我有三列:
- 库存
- 安全库存
- 需要生产 - 另一个带有子句 WHERE date = getdate() 的选择
我需要实现的目标:指数、库存-当前月份、安全库存-当前月份、生产需求(从 Nfp 中选择 *，其中日期= getdate())、库存 - 本月 + 1、安全库存 - 本月 + 1、生产需求 - 本月 + 1... 直到 18 个月
what i need to achieve: Index, Stock- Current month, SafetyStock-Current month, Need for production (select * from Nfp where date = getdate()), Stock - Current month + 1, Safetystock - Current Month + 1, Need for Production - Current Month + 1 ... etc till 18 months
计算:库存 - 本月 + 1 = 上月库存 + 上月安全库存 - 本月生产需求
calculations: Stock - Current month + 1 = Stock previous month + SafetyStock previous month - Needs for production of current month
有可能创造出这样的东西吗?它必须是动态的并计算当前日期和未来 18 个月.所以现在我必须计算从 2020-10 到 2022-04
there is any possibility to create something like this ? it has to be dynamic and get calculation for current date and next 18 months. So now i have to calculate from 2020-10 till let's say 2022-04
我尝试过的:
我准备了 18 cte 并加入了一切.然后我做计算 - 它有效，但速度很慢，我认为它不专业.
I prepared 18 cte and joins everything. Then i do calculations - it works but it slow and i think it is not profesional.
我尝试做动态 sql，在下面你可以看到我的代码，但是当我想要做依赖于前一个计算列的计算列时我卡住了:
I have tried to do dynamic sql, below you can see my code but i have stucked when i wanted to do computed column depended on previous computed column:
------------------- 代码 -------------------------
------------------- CODE -------------------------
if object_id('tempdb..#tmp') is not null
drop table #tmp
if object_id('tempdb..#tmp2') is not null
drop table #tmp2
declare @cols as int
declare @iteration as int
declare @Mth as nvarchar(30)
declare @data as date
declare @sql as nvarchar(max)
declare @sql2 as nvarchar(max)
set @cols = 18
set @iteration = 0
set @Mth = month(getdate())
set @data = cast(getdate() as date)
select
10 as SS,
12 as Stock
into #tmp
WHILE @iteration < @cols
begin
set @iteration = @iteration + 1
set @sql =
'
alter table #tmp
add [StockUwzgledniajacSS - ' + cast(concat(year(DATEADD(Month, @Iteration, @data)),'-', month(DATEADD(Month, @Iteration, @data))) as nvarchar(max)) +'] as (Stock - SS)
'
exec (@sql)
set @Mth= @Mth+ 1
set @sql2 =
'
alter table #tmp
add [StockUwzgledniajacSS - ' + @Mth +'] as ([StockUwzgledniajacSS - ' + @Mth +'])
'
end
select * from #tmp
提前致谢！
推荐答案
更新 1 注意:我在您发布数据之前写了这个.我相信这仍然成立，但当然，库存水平大不相同.鉴于您的 NFP 数据是按天计算的，而您的报告是按月计算的，我建议添加一些内容以将该数据预处理为月份，例如按月分组的 NPS 值的总和.
Update 1 note: I wrote this before you posted your data. This still holds I believe but, of course, stock levels are way different. Given that your NFP data is by day, and your report is by month, I suggest adding something to preprocess that data into months e.g., sum of NPS values, grouped by month.
更新 2(第二天)注意:从下面的 OP 评论中，我尝试将其与所写内容相结合，并更直接地回答问题，例如，创建报告表 #tmp.鉴于 OP 还提到了数百万行，我想每一行都代表一个特定的部分/项目 - 我将其作为一个名为 StockNum 的字段包含在内.
Update 2 (next day) note: From the OPs comments below, I've tried to integrate this with what was written and more directly answering the question e.g., creating a reporting table #tmp. Given that the OP also mentions millions of rows, I imagine each row represents a specific part/item - I've included this as a field called StockNum.
我做了一些可能无法正确计算的事情，但演示了该方法，应该可以帮助您克服当前的障碍.事实上，如果您以前没有使用过这些，那么用您自己的计算更新此代码将有助于您了解它的工作原理，以便您可以对其进行维护.
I have done something that probably doesn't do your calculations properly, but demonstrates the approach and should get you over your current hurdle. Indeed, if you haven't used these before, then updating this code with your own calculations will help you to understand how it works so you can maintain it.
我假设这里计算的关键问题是本月的库存是基于上个月的库存，然后是本月的新库存减去旧库存.
I'm assuming the key issue here for calculation is that this month's stock is based on last month's stock and then new stock minus old stock for this month.
可以在 18 个单独的语句中计算这个(更新表集 col2 = col1 的某个函数，然后更新表集 col3 = col2 的某个函数，等等).但是，多次更新同一个表通常是导致性能不佳的反模式 - 特别是如果您需要一次又一次地读取基础数据.
It is possible to calculate this in 18 separate statements (update table set col2 = some function of col1, then update table set col3 = some function of col2, etc). However, updating the same table multiple times is often an anti-pattern causing poor performance - especially if you need to read the base data again and again.
相反，通常最好使用 Recusive CTE (这是一个示例说明)，它根据以前的结果构建"一组数据.
Instead, something like this is often best calculated using a Recusive CTE (here's an example description), where it 'builds' a set of data based on previous results.
这种方法的主要区别在于它
The key difference in this approach is that it
- 创建报告表(不输入任何数据/计算)
- 将数据计算为单独的步骤 - 但包含可用于链接到报告表的列/字段
- 将计算中的数据作为单个插入语句插入到报表中.
我已经大量使用临时表/等，以帮助演示该过程.
I have used temporary tables/etc liberally, to help demonstrate the process.
您还没有解释什么是安全库存，也没有解释如何衡量进货量，因此在下面的示例中，我假设安全库存是生产量，是每月 5 件.然后我假设 NFP 是每个月的支出金额(例如，销售额的预测).关键结果将是月底的库存(例如，您可以随后查看库存是否过高或过低).
You haven't explained what safety stock is, nor how you measure what's coming in, so for the example below, I have assumed safety stock is the amount produced and is 5 per month. I've then assumed that NFP is amount going out each month (e.g., forward estimates of sales). The key result will be stock at the end of month (e.g., which you could then review whether it's too high or too low).
由于您希望将其存储在以每个月为列的表中，因此第一步是创建一个包含相关存储桶(月)的列表.这些包括用于在以后的计算/等中匹配的字段.注意我已经包含了一些日期字段(开始日期和结束日期)，它们在您自定义代码时可能很有用.这部分 SQL 设计得尽可能简单明了.
As you want to store it in a table that has each month as columns, the first step is to create a list with the relevant buckets (months). These include fields used for matching in later calculations/etc. Note I have included some date fields (startdate and enddate) which may be useful when you customise the code. This part of the SQL is designed to be as straightforward as possible.
然后，我们创建包含我们股票走势参考数据的临时表，替换您的 SELECT * FROM NFP WHERE date = getdate()
We then create the scratch table that has our reference data for stock movements, replacing your SELECT * FROM NFP WHERE date = getdate()
/* SET UP BUCKET LIST TO HELP CALCULATION */
CREATE TABLE #RepBuckets (BucketNum int, BucketName nvarchar(30), BucketStartDate datetime, BucketEndDate datetime)
INSERT INTO #RepBuckets (BucketNum) VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),(16),(17),(18)
DECLARE @CurrentBucketStart date
SET @CurrentBucketStart = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1)
UPDATE #RepBuckets
SET BucketName = 'StockAtEnd_' + FORMAT(DATEADD(month, BucketNum, @CurrentBucketStart), 'MMM_yy'),
BucketStartDate = DATEADD(month, BucketNum, @CurrentBucketStart),
BucketEndDate = DATEADD(month, BucketNum + 1, @CurrentBucketStart)
/* CREATE BASE DATA */
-- Current stock
CREATE TABLE #Stock (StockNum int, MonthNum int, StockAtStart int, SafetyStock int, NFP int, StockAtEnd int, PRIMARY KEY(StockNum, MonthNum))
INSERT INTO #Stock (StockNum, MonthNum, StockAtStart, SafetyStock, NFP, StockAtEnd) VALUES
(12422, 0, NULL, NULL, NULL, 10)
-- Simulates SELECT * FROM NFP WHERE date = getdate()
CREATE TABLE #NFP_by_month (StockNum int, MonthNum int, StockNFP int, PRIMARY KEY(StockNum, MonthNum))
INSERT INTO #NFP_by_month (StockNum, MonthNum, StockNFP) VALUES
(12422, 1, 4), (12422, 7, 4), (12422, 13, 4),
(12422, 2, 5), (12422, 8, 5), (12422, 14, 5),
(12422, 3, 2), (12422, 9, 2), (12422, 15, 2),
(12422, 4, 7), (12422, 10, 7), (12422, 16, 7),
(12422, 5, 9), (12422, 11, 9), (12422, 17, 9),
(12422, 6, 3), (12422, 12, 3), (12422, 18, 3)
然后我们使用递归 CTE 来计算我们的数据.它将这些存储在表 #StockProjections 中.
We then use the recursive CTE to get calculate our data. It stores these in table #StockProjections.
这是做什么的
- 从您当前的股票开始(#Stock 表中的最后一行).请注意，唯一重要的价值是月底的股票.
- 使用上月末的库存水平作为新月初的库存水平
- 加上安全库存，减去 NFP，最后计算您的库存.
请注意，在 CTE 的递归部分中，SBM"(StockByMonth)指的是上个月的数据).然后将其与任何外部数据(例如#NFP)结合使用以计算新数据.
Note that within the recursive part of the CTE, 'SBM' (StockByMonth) refers to last month's data). This is then used with whatever external data (e.g., #NFP) to calculate new data.
这些计算创建了一个表格
These calculations create a table with
- StockNum(相关库存项目的 ID 号 - 在此示例中，我使用了一个库存项目 12422)
- MonthNum(为了清晰/简单起见，我使用了整数而不是日期)
- BucketName(代表月份的 nvarchar，用于列名)
- 月初库存
- 安全库存(我假设是进货库存，每月 5 个)
- NFP(我假设它是即将发货的库存，因月份而异，并且来自此处的暂存表 - 您需要根据您的选择进行调整)
- 月底库存
/* CALCULATE PROJECTIONS */
CREATE TABLE #StockProjections (StockNum int, BucketName nvarchar(30), MonthNum int, StockAtStart int, SafetyStock int, NFP int, StockAtEnd int, PRIMARY KEY (StockNum, BucketName))
; WITH StockByMonth AS
(-- Anchor
SELECT TOP 1 StockNum, MonthNum, StockAtStart, SafetyStock, NFP, StockAtEnd
FROM #Stock S
ORDER BY MonthNum DESC
-- Recursion
UNION ALL
SELECT NFP.StockNum,
SBM.MonthNum + 1 AS MonthNum,
SBM.StockAtEnd AS NewStockAtStart,
5 AS Safety_Stock,
NFP.StockNFP,
SBM.StockAtEnd + 5 - NFP.StockNFP AS NewStockAtEnd
FROM StockByMonth SBM
INNER JOIN #NFP_by_month NFP ON NFP.MonthNum = SBM.MonthNum + 1
WHERE NFP.MonthNum <= 18
)
INSERT INTO #StockProjections (StockNum, BucketName, MonthNum, StockAtStart, SafetyStock, NFP, StockAtEnd)
SELECT StockNum, BucketName, MonthNum, StockAtStart, SafetyStock, NFP, StockAtEnd
FROM StockByMonth
INNER JOIN #RepBuckets ON StockByMonth.MonthNum = #RepBuckets.BucketNum
现在我们有了数据，我们建立了一个表格用于报告目的.请注意，该表将月份名称嵌入到列名称中(例如，StockAtEnd_Jun_21).使用通用名称会更容易(例如，StockAtEnd_Month4)，但为了演示，我在这里使用了稍微复杂一点的情况.
Now we have the data, we set up a table for reporting purposes. Note that this table has the month names embedded into the column names (e.g., StockAtEnd_Jun_21). It would be easier to use a generic name (e.g., StockAtEnd_Month4) but I've gone for the slightly more complex case here for demonstration.
/* SET UP TABLE FOR REPORTING */
DECLARE @cols int = 18
DECLARE @iteration int = 0
DECLARE @colname nvarchar(30)
DECLARE @sql2 as nvarchar(max)
CREATE TABLE #tmp (StockNum int PRIMARY KEY)
WHILE @iteration <= @cols
BEGIN
SET @colname = (SELECT TOP 1 BucketName FROM #RepBuckets WHERE BucketNum = @iteration)
SET @sql2 = 'ALTER TABLE #tmp ADD ' + QUOTENAME(@colname) + ' int'
EXEC (@sql2)
SET @iteration = @iteration + 1
END
最后一步是将数据添加到您的报表中.我在这里使用了一个支点，但你可以随意使用任何你喜欢的东西.
The last step is to add the data to your reporting table. I've used a pivot here but feel free to use whatever you like.
/* POPULATE TABLE */
DECLARE @columnList nvarchar(max) = N'';
SELECT @columnList += QUOTENAME(BucketName) + N' ' FROM #RepBuckets
SET @columnList = REPLACE(RTRIM(@columnList), ' ', ', ')
DECLARE @sql3 nvarchar(max)
SET @sql3 = N'
;WITH StockPivotCTE AS
(SELECT *
FROM (SELECT StockNum, BucketName, StockAtEnd
FROM #StockProjections
) StockSummary
PIVOT
(SUM(StockAtEnd)
FOR [BucketName]
IN (' + @columnList + N')
) AS StockPivot
)
INSERT INTO #tmp (StockNum, ' + @columnList + N')
SELECT StockNum, ' + @columnList + N'
FROM StockPivotCTE'
EXEC (@sql3)
这是一个 DB<>fiddle 显示它运行的结果子步骤.
Here's a DB<>fiddle showing it running with results of each sub-step.
这篇关于动态列依赖于以前的动态列 - TSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
