我有一个用户事务表（购买、销售等）。从这个表中，我想创建一个用户语句，在前端呈现

tbu事务

id  | issuer_id | recipient_id | amount | operation_id
----|-----------|--------------|--------|---------|
 1  | 100       |  105         | 5      | 1
 2  | 101       |  105         | 3      | 1
 3  | 102       |  105         | 1      | 1
 4  | 103       |  105         | 5      | 2
 5  | 105       |  104         | 7      | 3
 6  | 102       |  104         | 1      | 4

tbu操作

id  | type      |
----|-----------|
 1  | PURCHASE  |
 2  | PURCHASE  |
 3  | PURCHASE  |
 4  | PURCHASE  |

让我们考虑生成用户语句id 105。我想返回按操作分组的数量。

[
  {user_id: 105, amount: 9, operation_id: 1, op: 'CREDIT'}, 
  {user_id: 105, amount: 7, operation_id: 3, op: 'DEBIT'}
]

我有一些选择：
1 . 为用户的语句创建新的合并表。例子：

id  | user_id   | amount | operation_id | operation
----|-----------|--------|--------------|----------|
1   | 105       |  9     | 1            | CREDIT  
2   | 100       |  5     | 1            | DEBIT  
3   | 101       |  3     | 1            | DEBIT  
4   | 102       |  1     | 1            | DEBIT 
5   | 105       |  5     | 2            | CREDIT 
6   | 103       |  5     | 2            | DEBIT 
7   | 104       |  7     | 3            | CREDIT 
8   | 105       |  7     | 3            | DEBIT 
9   | 104       |  1     | 4            | CREDIT
10  | 102       |  1     | 4            | DEBIT

2 . 创建物化视图；
三。使用cte和窗口函数创建查询（我担心性能，因为表有1000多万条记录）；

WITH w1_data AS (
 SELECT t.*,
 SUM(t.amount) OVER(PARTITION BY t.operation_id
 ORDER BY t.id ASC ROWS UNBOUNDED PRECEDING) AS sum_group,
 ROW_NUMBER() OVER(PARTITION BY t.operation_id
 ORDER BY t.id ASC) AS rnk
 FROM tb_transaction t
 WHERE issuer_id = 505 OR recipient_id = 505)
SELECT distinct on (w1.operation_id) operation_id, w1.*
 FROM w1_data w1
 ORDER BY w1.operation_id, w1.rnk DESC

谢谢你的帮助。