根据SQL中的两列创建计算的列

Question

我有一个下表，我需要创建一个基于类别和月份列的计算列(RA)。

    Oa  Sa  Ai  month   MDY
    5   10  2   Jan     J302022
    16  32  38  Jan     J302022
    15  14  4   Feb     J302022
    46  32  81  Jan     J302022
    3   90  0   Mar     J302022
    51  10  21  Jan     J302021
    19  32  3   Jan     J302021
    45  16  41  Feb     J302021
    46  7   81  Jan     J302022
    30  67  14  Mar     J302021
    45  16  41  Apr     J302021
    46  7   81  Apr     J302021
    30  67  0   Jan     J302021
    56  17  0   Mar     J302022

首先，它需要考虑一个类别，例如，J302022，然后它需要根据该类别的月份计算"RA“列。例如，J302022，Jan，((5+16+46+46)+(10+32+32+7)) / (2+38+81+81) = 0.96。下面是预期的输出。

    Oa  Sa  Ai  month   category    RA
    5   10  2   Jan     J302022     0.96
    16  32  38  Jan     J302022     0.96
    15  14  4   Feb     J302022     7.25
    46  32  81  Jan     J302022     0.96
    3   90  0   Mar     J302022     0
    51  10  21  Jan     J302021     8.70
    19  32  3   Jan     J302021     8.70
    45  16  41  Feb     J302021     1.48    
    46  7   81  Jan     J302022     0.96
    30  67  14  Mar     J302021     6.92
    45  16  41  Apr     J302021     1.48
    46  7   81  Apr     J302022     0.65    
    30  67  0   Jan     J302021     8.70
    56  17  0   Mar     J302022     0

在SQL中可以这样做吗？

提前感谢！

Answer 1

select Oa, Sa, Ai, month, category, 
  coalesce((ra1+ra2)/ra3, 0) as RA 
from (
  select Oa, Sa, Ai, month, mdy as category, 
    sum(oa) over (partition by month, mdy) as ra1, 
    sum(sa) over (partition by month, mdy) as ra2, 
    sum(ai) over (partition by month, mdy) as ra3 
  from WhateverYourTableNameIs
) as t;

MySQL 8.0.29上的输出：

+------+------+------+-------+----------+--------+
| Oa   | Sa   | Ai   | month | category | RA     |
+------+------+------+-------+----------+--------+
|   45 |   16 |   41 | Apr   | J302021  | 0.9344 |
|   46 |    7 |   81 | Apr   | J302021  | 0.9344 |
|   45 |   16 |   41 | Feb   | J302021  | 1.4878 |
|   15 |   14 |    4 | Feb   | J302022  | 7.2500 |
|   51 |   10 |   21 | Jan   | J302021  | 8.7083 |
|   19 |   32 |    3 | Jan   | J302021  | 8.7083 |
|   30 |   67 |    0 | Jan   | J302021  | 8.7083 |
|    5 |   10 |    2 | Jan   | J302022  | 0.9604 |
|   16 |   32 |   38 | Jan   | J302022  | 0.9604 |
|   46 |   32 |   81 | Jan   | J302022  | 0.9604 |
|   46 |    7 |   81 | Jan   | J302022  | 0.9604 |
|   30 |   67 |   14 | Mar   | J302021  | 6.9286 |
|    3 |   90 |    0 | Mar   | J302022  | 0.0000 |
|   56 |   17 |    0 | Mar   | J302022  | 0.0000 |
+------+------+------+-------+----------+--------+

Answer 2

用于SQL Server

select Oa, Sa, Ai, [Month],Category,
case when (sum(Ai) over(partition by [Month], Category) *1.0) = 0 then 0 else 
(sum(Oa) over(partition by [Month], Category) +
sum(Sa) over(partition by [Month], Category))/
(sum(Ai) over(partition by [Month], Category) *1.0) end Ra
from #temp
order by Category desc

以分母乘以1.0，将输出转换为浮点数。