问题

SELECT m.MergedIn AS MergedIn, (SUM(b.Value) - SUM(a.Value)) / SUM(a.Value) AS Growth
FROM MyMerge AS m
INNER JOIN MyData AS a ON a.Branch = m.OldBranch AND a.Date = 20220701
INNER JOIN MyData AS b ON b.Branch = m.MergedIn AND b.Date = 20230501
GROUP BY m.MergedIn

但是我的代码计算如下：

A = (110 - 50) / 50 !!

<details>
<summary>英文:</summary>

I have 2 tables in SQL Server:

Table MyData:

|   Date   |  Branch  |   Value  |
| -------- | -------- | -------- |
| 20220701 |     A    |    50    |
| 20220701 |     B    |    40    |
| 20220701 |     C    |    25    |
| 20230501 |     A    |   110    |
| 20230501 |     C    |    35    | 

and many dates and branched values in this table.

Table MyMerge:

| OldBranch | MergedIn |
| --------- | -------- |
|     A     |     A    |
|     B     |     A    |
|     C     |     C    |

My goal is to write code that gives the following output:

| MergedIn |  Growth  |
| -------- | -------- |
|    A     |   0.22   |
|    C     |   0.40   |

For example: the growth of A between 1 Jul 2022 (A+B before the merge) and 1 May 2023 is  (110-(50+40))/(50+40) = 0.22

SELECT m.MergedIn as MergedIn, (sum(b.Value)-sum(a.Value))/sum(a.Value) as Growth
From MyMerge as m
Inner join MyData as a on a.branch=m.OldBranch
Inner join MyData as b on b.branch=m.OldBranch
Where a.date=20220701 and b.date=20230501
Group by m.MergedIn

But my code calculate like this:

A=(110-50)/50 !!

</details>


# 答案1
**得分**: 0

如果我理解问题正确，并且你想计算两个期间内每个分支的增长，使用 `LAG()` 函数是解决你问题的一种可能方法：

示例数据：

```sql
SELECT *
INTO MyData
FROM (VALUES
   (20220701, 'A', 50),
   (20220701, 'B', 40),
   (20220701, 'C', 25),
   (20230501, 'A', 110),
   (20230501, 'C', 35)
) v (Date, Branch, Value)  

SELECT *
INTO MyMerge
FROM (VALUES
   ('A', 'A'),
   ('B', 'A'),
   ('C', 'C')
) v (OldBranch, MergedIn)  

查询语句：

SELECT 
   Date, MergedIn,
   (Value - LAG(Value, 1) OVER (PARTITION BY MergedIn ORDER BY Date)) * 1.0 / 
   LAG(Value, 1) OVER (PARTITION BY MergedIn ORDER BY Date) AS Growth
FROM (  
   SELECT md.Date, mm.MergedIn, SUM(md.Value) AS Value
   FROM MyData md
   LEFT JOIN MyMerge mm ON md.Branch = mm.OldBranch  
   GROUP BY md.Date, mm.MergedIn
) t

结果（未四舍五入）：

更新:

如果你只需要分析两个期间的数据，你可以尝试基于条件聚合的方法：

SELECT 
   MergedIn, (Value1 - Value2) * 1.0 / Value2 AS Growth
FROM (  
   SELECT 
      mm.MergedIn, 
      SUM(CASE WHEN md.Date = 20230501 THEN md.Value END) AS Value1,
      SUM(CASE WHEN md.Date = 20220701 THEN md.Value END) AS Value2
   FROM MyData md
   LEFT JOIN MyMerge mm ON md.Branch = mm.OldBranch  
   GROUP BY mm.MergedIn
) t

结果（未四舍五入）：

SELECT *
INTO MyData
FROM (VALUES
   (20220701, 'A', 50),
   (20220701, 'B', 40),
   (20220701, 'C', 25),
   (20230501, 'A', 110),
   (20230501, 'C', 35)
) v (Date, Branch, Value)  

SELECT *
INTO MyMerge
FROM (VALUES
   ('A', 'A'),
   ('B', 'A'),
   ('C', 'C')
) v (OldBranch, MergedIn)  

SELECT 
   Date, MergedIn,
   (Value - LAG(Value, 1) OVER (PARTITION BY MergedIn ORDER BY Date)) * 1.0 / 
   LAG(Value, 1) OVER (PARTITION BY MergedIn ORDER BY Date) AS Growth
FROM (  
   SELECT md.Date, mm.MergedIn, SUM(md.Value) AS Value
   FROM MyData md
   LEFT JOIN MyMerge mm ON md.Branch = mm.OldBranch  
   GROUP BY md.Date, mm.MergedIn
) t

SELECT 
   MergedIn, (Value1 - Value2) * 1.0 / Value2 AS Growth
FROM (  
   SELECT 
      mm.MergedIn, 
      SUM(CASE WHEN md.Date = 20230501 THEN md.Value END) AS Value1,
      SUM(CASE WHEN md.Date = 20220701 THEN md.Value END) AS Value2
   FROM MyData md
   LEFT JOIN MyMerge mm ON md.Branch = mm.OldBranch  
   GROUP BY mm.MergedIn
) t

答案2

得分: 0

以下是您提供的代码的翻译结果：

;WITH cte AS (
    SELECT d.Date, m.MergedIn, SUM(d.Value) Val 
    FROM MyMerge m
    INNER JOIN MyData d ON m.OldBranch = d.Branch
    WHERE d.Date IN ('20220701', '20230501') AND m.MergedIn IN ('A', 'C')
    GROUP BY d.Date, m.MergedIn
), cte2 AS (
    SELECT cte.MergedIn, CAST((val - LAG(val) OVER (PARTITION BY cte.MergedIn ORDER BY cte.Date)) AS DECIMAL) / LAG(val) OVER (PARTITION BY cte.MergedIn ORDER BY cte.Date) Growth
    FROM cte
)
SELECT *
FROM cte2 WHERE cte2.Growth IS NOT NULL

生成的结果为：

MergedIn    Growth
A           0.22222222222
C           0.40000000000

请注意，这是您提供的代码的中文翻译和结果。如果您有任何其他翻译需求，请告诉我。

;WITH cte AS (
	SELECT d.Date, m.MergedIn, SUM(d.Value) Val 
	FROM MyMerge m
	INNER JOIN MyData d ON m.OldBranch = d.Branch
    WHERE d.Date IN ('20220701', '20230501') AND m.MergedIn IN ('A', 'C')
	GROUP BY d.Date,
             m.MergedIn
), cte2 AS (
	SELECT cte.MergedIn, CAST((val - LAG(val) OVER (PARTITION BY cte.MergedIn ORDER BY cte.Date)) AS DECIMAL) / LAG(val) OVER (PARTITION BY cte.MergedIn ORDER BY cte.Date) Growth
	FROM cte
)
SELECT *
FROM cte2 WHERE cte2.Growth IS NOT NULL

MergedIn	Growth
A	        0.22222222222
C	        0.40000000000