时间戳

首页 > 解决方案 > 如何在 SQL 的同一查询中引用先前计算的输出？

sql - 如何在 SQL 的同一查询中引用先前计算的输出？

问题描述

我对 SQL 还是很陌生，我正在尝试执行一个计算，该计算引用在前一行/时间段内对同一表执行的计算的输出。

[表格样本]

| 完整部分 | 季度 | 姓名 | 价值 |
|-----------|------------|---------------|-------- ---|
| 470205-17 | 42019 | 20 财年第一季度 CCBP | 12524 |
| 470205-17 | 42019 | 20 财年第一季度 579.0005 |
| 470205-17 | 42019 | 21 财年第一季度 CCBP | 17436 |
| 470205-17 | 42019 | 21 财年第一季度 885.9997 |
| 470205-17 | 42019 | 20 财年第二季度 CCBP | 14807 |
| 470205-17 | 42019 | 20 财年第二季度 764.9999 |
| 470205-17 | 42019 | 21 财年第二季度 CCBP | 15220 |
| 470205-17 | 42019 | 21 财年第二季度 851.9996 |
| 470205-17 | 42019 | 20 财年第三季度 CCBP | 16196 |
| 470205-17 | 42019 | 20 财年第三季度 1066.9998 |
| 470205-17 | 42019 | 19 财年第四季度 CCBP | 4841 |
| 470205-17 | 42019 | 19 财年第四季度 SP | 730.0005 |
| 470205-17 | 42019 | 20 财年第四季度 CCBP | 17100 |
| 470205-17 | 42019 | 20 财年第四季度 SP | 1115.9995 |
| 470205-17 | 42019 | 总 QOH | 第2181章

CREATE TABLE mytable(
   Full_Part  VARCHAR(9) NOT NULL
  ,Quarter_Yr INTEGER  NOT NULL
  ,Name       VARCHAR(13) NOT NULL
  ,Value      NUMERIC(9,4) NOT NULL
);
INSERT INTO mytable(Full_Part,Quarter_Yr,Name,Value) VALUES
 ('470205-17',42019,'Q1 FY 20 CCBP',12524)
,('470205-17',42019,'Q1 FY 20 SP',579.0005)
,('470205-17',42019,'Q1 FY 21 CCBP',17436)
,('470205-17',42019,'Q1 FY 21 SP',885.9997)
,('470205-17',42019,'Q2 FY 20 CCBP',14807)
,('470205-17',42019,'Q2 FY 20 SP',764.9999)
,('470205-17',42019,'Q2 FY 21 CCBP',15220)
,('470205-17',42019,'Q2 FY 21 SP',851.9996)
,('470205-17',42019,'Q3 FY 20 CCBP',16196)
,('470205-17',42019,'Q3 FY 20 SP',1066.9998)
,('470205-17',42019,'Q4 FY 19 CCBP',4841)
,('470205-17',42019,'Q4 FY 19 SP',730.0005)
,('470205-17',42019,'Q4 FY 20 CCBP',17100)
,('470205-17',42019,'Q4 FY 20 SP',1115.9995)
,('470205-17',42019,'Total QOH',2181);

SP 是预测，QOH 是当前库存，CCBP 是供应

我需要作为 YR=42019 季度的输出是 (Total QOH+Q4 FY 19 CCBP)-Q4 FY 19 SP

然后对于 Yr=12020 季度，我需要期末库存（先前计算的输出）+（Q1 FY 20 CCBP）-Q1 FY 20 SP

以此类推，直到 QuarterYr=22021

输出需要采用相同的格式，即 Full Part, QuartrYr,Name='Ending Inventory', Value

| 完整部分 | 季度 | 姓名 | 价值 |
|-----------|------------|------------------|-- --|
| 470205-17 | 42019 | 期末库存 | 6292 |
| 470205-17 | 12020 | 期末库存 | 18237 |
| 470205-17 | 22020 | 期末库存 | 32280 |
| 470205-17 | 32020 | 期末库存 | 47409 |
| 470205-17 | 42020 | 期末库存 | 63393 |
| 470205-17 | 12021 | 期末库存 | 79944 |
| 470205-17 | 22021 | 期末库存 | 94313 |

我假设这需要递归/for循环来实现所需的输出。任何帮助完成这项工作将不胜感激。

标签： sqlsql-serverrecursionrecursive-query

解决方案

在 SQL Server 2017 上测试：

这是产生所需结果的示例查询：

declare @mytable table(
   Full_Part  VARCHAR(9) NOT NULL
  ,Quarter_Yr INTEGER  NOT NULL
  ,Name       VARCHAR(13) NOT NULL
  ,Value      NUMERIC(9,4) NOT NULL
);

INSERT INTO @mytable(Full_Part,Quarter_Yr,Name,Value) VALUES
 ('470205-17',42019,'Q1 FY 20 CCBP',12524)
,('470205-17',42019,'Q1 FY 20 SP',579.0005)
,('470205-17',42019,'Q1 FY 21 CCBP',17436)
,('470205-17',42019,'Q1 FY 21 SP',885.9997)
,('470205-17',42019,'Q2 FY 20 CCBP',14807)
,('470205-17',42019,'Q2 FY 20 SP',764.9999)
,('470205-17',42019,'Q2 FY 21 CCBP',15220)
,('470205-17',42019,'Q2 FY 21 SP',851.9996)
,('470205-17',42019,'Q3 FY 20 CCBP',16196)
,('470205-17',42019,'Q3 FY 20 SP',1066.9998)
,('470205-17',42019,'Q4 FY 19 CCBP',4841)
,('470205-17',42019,'Q4 FY 19 SP',730.0005)
,('470205-17',42019,'Q4 FY 20 CCBP',17100)
,('470205-17',42019,'Q4 FY 20 SP',1115.9995)
,('470205-17',42019,'Total QOH',2181);

WITH PARSE1 AS (
    SELECT 
        CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,2,1)  ELSE NULL END AS N1,
        CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,7,2)  ELSE NULL END AS N2,
        CASE WHEN Name LIKE 'Q_ FY%' THEN SUBSTRING(Name,10,4) 
             WHEN NAME = 'Total QOH' THEN 'QOH' ELSE NULL END AS N3,
        Full_Part, Quarter_Yr, Value
    FROM @mytable
), PARSE2 AS (
    SELECT Full_Part, Quarter_Yr, Value, N3, CASE WHEN N3 = 'SP' THEN -1 ELSE 1 END AS mValue, CAST(N1 AS INT) AS Q, 2000 + CAST(N2 AS INT) AS YR
    FROM PARSE1
), PIVOT1 AS (
    SELECT Q, YR, Full_Part, Quarter_Yr, SUM(Value * mValue) AS Qtr_Value 
    FROM PARSE2
    GROUP BY Q, YR, Full_Part, Quarter_Yr
), ANALYTIC1 AS (
    SELECT Q, YR, Full_Part, Quarter_Yr, Qtr_Value,
        SUM(Qtr_Value) OVER (PARTITION BY [Full_Part] ORDER BY YR, Q) AS Run_Value
    FROM PIVOT1
) 
SELECT Full_Part AS [Full Part], CONCAT(Q,YR) AS [Quarter Yr], 'Ending Inventory' AS Name, CAST(ROUND(Run_Value,1) AS INT) AS Value
FROM ANALYTIC1 
WHERE Q IS NOT NULL

输出：

Full Part   Quarter Yr  Name                Value
470205-17   42019       Ending Inventory    6292
470205-17   12020       Ending Inventory    18237
470205-17   22020       Ending Inventory    32279
470205-17   32020       Ending Inventory    47408
470205-17   42020       Ending Inventory    63392
470205-17   12021       Ending Inventory    79942
470205-17   22021       Ending Inventory    94310

关于不同的公用表表达式 - CTE(s)

解析1/解析2：

  • 一些关键数据隐藏在字符串“名称”中。
  • 我们需要提取数据类型、季度和年份信息（假设 2000 + 两位数可用）。
  • 我们还计划通过将 Value 乘以负 1 来减去 SP。

枢轴1：

  • 现在我们可以按功能应用我们的组来总结每个季度的部分。

分析1：

  • 使用特殊的总和分析函数，我们可以在按 Quarter_Yr 订购时创建运行计数。
  • 包含一个 partition by 子句以在每个逻辑数据集内创建一个运行计数。

最终选择：

  • 提供最终清理查询以根据需要呈现数据。

您可以将 ROUND() 替换为 CEILING() 或 FLOOR() 以微调结果。

要检查每个单独的 CTE，您可以将最终选择替换为：

  • 从 PARSE1 中选择 *
  • 从 PARSE2 中选择 *
  • 从 PIVOT1 中选择 *
  • 从分析 1 中选择 *

推荐阅读