Redshift SQL:基于另一列的行计算

Question

我正在用红移建造一张保龄球图的基础。

我将数据达到了这种格式：

data：

月|product_id |kpi_type |值四月|1 |电流|330四月|1 |目标|300四月|2 |电流|340四月|2 |目标|300三月|2 |电流|270三月|2 |目标|300

我想插入一个 kpi_type = diff，其中 diff = current-target。

我希望做到这一点：

月|product_id |kpi_type |值四月|1 |电流|330四月|1 |目标|300四月|1 |diff |30四月|2 |电流|340四月|2 |目标|300四月|2 |diff |40三月|2 |电流|270三月|2 |目标|300三月|2 |diff |-30

我知道如何通过计算CTE中的差异，然后将其连接到原始表。但是，我想通过许多不同的values和更复杂的方差公式进行此操作，因此我正在寻找一个更有效的解决方案。

这是我得到的地方：

   选择        一个月，        a.product_id，        A.值为当前        b.target，        A.VALUE -B.Target AS diff    从数据a    左加入        ((            选择            月，            product_id，            值为目标            从数据            其中kpi_type ='target'        (b    在md5上(a.month || a.product_id(= md5(b.month || b.product_id(    其中kpi_type ='current'    组为1,2,3

从那里我可以将其与data结合并获得所需的结果，但似乎并不有效。

在SQL Server上接近此问题。

Answer 1

假设每月只能有一个值，kpi_types当前和目标的product_id，您可以汇总以获取diff行并使用 union all将其与原始结果相结合。

select month,product_id,kpi_type,values from data
union all
select month,product_id,'diff' as kpi_type,   
coalesce(max(case when kpi_type='current' then values end),0) - 
coalesce(max(case when kpi_type='target' then values end),0) as values
from data 
group by month,product_id

Answer 2

我无法使用评论功能。所以我会在这里写。我认为，除了使用联合以红移转换数据之外，别无他法。因此，您可以将窗口函数用于减法而不是左JOIN查询以获取差异。

 sum(values)
    OVER (
      PARTITION BY month, product_id ) AS diff

首先，您可能会在子查询中执行类似的操作：

    SELECT
       CASE WHEN type = 'target'
         THEN values * -1
       ELSE values END AS values
     FROM data

然后您可以将其结合。