GROUP BY with OVER

Question

我有两个表：

事务处理表

+----+
| id |
+----+
| 1  |
+----+
| 2  |
+----+
| 3  |
+----+

条目表

+----+----------------+--------+---------+
| id | transaction_id | income | expense |
+----+----------------+--------+---------+
| A  | 1              |        | 500     |
+----+----------------+--------+---------+
| B  | 2              | 250    |         |
+----+----------------+--------+---------+
| C  | 2              | 250    |         |
+----+----------------+--------+---------+

这个查询可以很好地工作：

SELECT e.income, e.expense, sum(e.income - e.expense) OVER (ORDER BY e.id) AS cumulative_sum
FROM entries e
JOIN transactions t on t.id = e.transaction_id;

但是我需要一个类似这样的查询(它不起作用)：

SELECT sum(e.income), sum(e.expense), sum(e.income-e.expense) OVER (ORDER BY e.id) AS cumulative_sum
FROM entries e
JOIN transactions t on t.id = e.transaction_id
GROUP by t.id;

因此，我可以得到以下结果：

+----------------+--------+---------+---------+
| transaction_id | income | expense | balance |
+----------------+--------+---------+---------+
| 1              |        | 500     | 500     |
+----------------+--------+---------+---------+
| 2              | 500    |         | 0       |
+----------------+--------+---------+---------+

我从前面的查询中得到的错误：

Error : Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'e.income' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

该错误引用此处指定的列"sum(e.income - e.expense) OVER...“

那么，在这种情况下，我该如何实际使用OVER和GROUP BY呢？或者有没有其他的解决方案，使用带有交叉连接的派生表？谢谢。

Answer 1

我很惊讶sum(e.income - e.expense)为你工作。如果您的行中的收入或支出为NULL，则所有计算的差值都应为null，因此它们的总和也应为null。

您应该使用sum(e.income) - sum(e.expense)，甚至在没有收入或根本没有支出的情况下使用COALESCE。

无论如何，您希望从每个事务的简单聚合中获得：

SELECT
  t.id,
  SUM(e.income) AS total_income,
  SUM(e.expense) AS total_expense,
  COALESCE(SUM(e.income), 0) - COALESCE(SUM(e.expense), 0) AS balance
FROM entries e
JOIN transactions t on t.id = e.transaction_id
GROUP BY t.id
ORDER BY t.id;

到一个流动的余额总额。为此，只需添加

SUM(COALESCE(SUM(e.income), 0) - COALESCE(SUM(e.expense), 0))
  OVER (ORDER BY t.id) AS cumulated_balance

Answer 2

如下所示：

SELECT sum(e.income)  OVER (ORDER BY e.id) AS cum_income
     , sum(e.expense) OVER (ORDER BY e.id) AS cum_expense
     , COALESCE(sum(e.income)  OVER (ORDER BY e.id), 0)
     - COALESCE(sum(e.expense) OVER (ORDER BY e.id), 0) AS cumulative_sum
  FROM entries e
  JOIN transactions t on t.id = e.transaction_id
 ORDER BY e.transaction_id, e.id
;

不确定您想要哪种排序。

Answer 3

每个sum()都需要自己的OVER子句，而您不需要GROUP BY。

SELECT e.transaction_id,
       sum(e.income) OVER (ORDER BY e.id) AS cumulative_income,
       sum(e.expense) OVER (ORDER BY e.id) AS cumulative_expense,
       sum(e.income) OVER (ORDER BY e.id)
       - sum(e.expense) OVER (ORDER BY e.id) AS cumulative_sum
       FROM entries e
            INNER JOIN transactions t
                       ON t.id = e.transaction_id;

或者，如果您不想重复OVER子句(但仍然需要将其分别分配给每个sum() )，则可以使用命名窗口。

SELECT e.transaction_id,
       sum(e.income) OVER w AS cumulative_income,
       sum(e.expense) OVER w AS cumulative_expense,
       sum(e.income) OVER w
       - sum(e.expense) OVER w AS cumulative_sum
       FROM entries e
            INNER JOIN transactions t
                       ON t.id = e.transaction_id
       WINDOW w AS (ORDER BY e.id);