将数据传播到新列中，同时还要汇总(添加)值-R或SQL

Question

我有一个看起来像这样的表：

ID    YEAR    SOURCE    VALUE
 1    2014        AV       15
 1    2014        OV       25
 1    2015        AV       87
 2    2014        AX       44
 2    2015        AZ       32
 2    2015        NL        2
 3    2011        OV      104

我想重新结构表格，以提供一个新的列，其中将来自源的AV，AX和NL提供的值以及另一个给出了OV值的新列。例如，以上看起来像这样：

ID    YEAR    AV+AX+NL    OV
 1    2014          15    25
 1    2015          87    NA
 2    2014          44    NA
 2    2015          32    NA
 3    2011          NA   104

我想在R中这样做，但是如果更简单，可以使用SQL。到现在为止>

任何帮助都非常感谢

Answer 1

非常简单的SQL Server查询。您所要做的就是按标准有条件地总和。

SELECT ID, [YEAR]
    , SUM(CASE WHEN SOURCE IN ('AV', 'AX', 'NL') THEN [VALUE] ELSE NULL END) [AV+AX+NL]
    , SUM(CASE WHEN SOURCE = 'OV' THEN [VALUE] ELSE NULL END) [OV]
FROM src
GROUP BY ID, [YEAR]
ORDER BY ID, [YEAR]

结果：

| ID | YEAR | AV+AX+NL | OV   |
+----+------+----------+------+
| 1  | 2014 | 15       | 25   |
| 1  | 2015 | 87       | NULL |
| 2  | 2014 | 44       | NULL |
| 2  | 2015 | 2        | NULL |
| 3  | 2011 | NULL     | 104  |

我最初将0用作替代值，而情况不匹配。但是，如果您希望在没有匹配的情况下显示NULL而不是0，则需要将替代值更改为null。如果仅将零值传递到聚合函数，则将返回空值。

一个警告是，在总和中使用null而不是0将产生警告：零件或其他集合操作消除了零值。否则一切都很好。

编辑

无效警告困扰着我。将产生相同结果的替代方案是转换源列，以使值" AV"，" AX"，" NL"变成" AV AX NL"，然后使用Pivot来获得总和。" AV AX NL'。

的源值

SELECT pvt.ID, pvt.[YEAR], pvt.[AV+AX+NL], pvt.OV
FROM (
    SELECT ID, [YEAR], [VALUE]
        , CASE WHEN SOURCE IN ('AV', 'AX', 'NL') THEN 'AV+AX+NL' ELSE SOURCE END SOURCE2
    FROM src
) src2
PIVOT (
    SUM([VALUE])
    FOR SOURCE2 IN ([AV+AX+NL], [OV])
) pvt
ORDER BY ID, [YEAR]

Answer 2

一个选项是 dcast data.table

library(data.table)
dcast(setDT(df1), ID + YEAR ~ SOURCE %in% c("AV", "AX", "NL"),
                value.var = "VALUE", sum)

Answer 3

有多种方法可以在SQL Server上执行此操作，这是一个

WITH C AS(
    SELECT ID
          ,YEAR
          ,SUM(VALUE) AS [AVAXNL]
    FROM TABLE_1
    WHERE SOURCE IN('AV','AX','NL')
    GROUP BY ID,YEAR
),D AS(
    SELECT ID
          ,YEAR
          ,SUM(VALUE) AS [OV]
        FROM TABLE_1
    WHERE SOURCE IN('AV','AX','NL')
    GROUP BY ID,YEAR
)
SELECT  ISNULL(C.ID,D.ID) AS ID
       ,ISNULL(C.YEAR,D.YEAR) AS YEAR
       ,C.AVAXNL
       ,D.OV
FROM C
FULL JOIN D ON (C.ID = D.ID AND C.YEAR = D.YEAR)

还可以旋转表并添加每一行。