R sqldf 计数、分组依据、具有 > 1

Question

我有一个有3列的表，有几千条记录，示例如下：

df1 <- data.frame(
  ID = c('V1', 'V1', 'V1', 'V3', 'V3', 'V3', 'V4', 'V5','V5','V5'),
  Category = c('a', 'a', 'a', 'a', 'b', 'b', 'a', 'b', 'c', 'c'),
  Amount   = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1))

需要使用sqldf查询按ID和类别对数据进行分组，然后对金额进行求和，并且仅当ID有多个类别时才返回行。结果如下所示：

ID  Category Amount_Sum
V3  a        1
V3  b        2
V5  b        1
V5  c        2

我尝试了以下代码，但它实际上不起作用，还有分区上的另一个 row_number ，它可以工作，但比需要许多额外查询所需的时间要长得多。

df2 <- sqldf::sqldf("
  SELECT   Count(*) [CNT]
           [ID], 
           [Category], 
           SUM([Amount]) [amount]
  FROM     df1
  GROUP BY [ID], 
           [Category]
  Having   Count(*) > 1")

在 R 中使用 sqldf 获取输出的最佳方法是什么？谢谢！

Answer 1

您可以添加一个联接，该联接仅限于具有多个类别的 ID：

SELECT t1.ID, t1.Category, t1.Amount_Sum
FROM
(
    SELECT ID, Category, SUM(Amount) AS Amount_Sum
    FROM df1
    GROUP BY ID, Category
) t1
INNER JOIN
(
    SELECT ID
    FROM df1
    GROUP BY ID
    HAVING COUNT(DISTINCT Category) > 1
) t2
    ON t2.ID = t1.ID;