计算在 PostgreSQL 中合并到单个列的特定'date'的累积总和

Question

我有一个数据库，其中包含每个用户支付的金额和日期。现在，一些用户在同一天付款，我想在数据透视表中每天只显示一次这些付款的累计金额，我正在使用Amazon QuickLight创建该数据透视表。

我已经完成了以下内容，但他们每行提供一次累积值，我没有办法只在日期而不是其他任何日期进行分区，总金额超过付款金额。

计算PostgreSQL 中的累计和

PostgreSQL 中使用日期过滤计算累计总和

计算PostgreSQL 中的累计日和

PostgreSQL，一次重新编号和累计

如何在PostgreSQL 9.3 中对两列进行条件求和

我的查询如下：

SELECT
s.id,
s.first_name,
s.last_name,
s.birth_date,
s.card,
p.datetime,
p.amount,
Sum(p.amount)OVER(partition BY p.datetime ORDER BY p.datetime ) AS "Daily Amount"
FROM payments AS p
LEFT JOIN users AS s
ON p.s_h_uuid = s.h_uuid
ORDER BY p.datetime DESC

我在这一行做Sum((Over((的地方：

Sum(pa.amount)OVER(partition BY p.datetime ORDER BY p.datetime ) AS "Daily Amount"

我的表的数据为：

用户：

| id | first_name | last_name | birth_date | card |
| 2  | first_nam2 | last_nam2 | 1990-02-01 | M    |
| 3  | first_nam3 | last_nam3 | 1987-07-23 | M    |
| 1  | first_nam1 | last_nam1 | 1954-11-15 | A    |
| 4  | first_nam4 | last_nam4 | 1968-05-07 | V    |

付款：

| p_uuid | datetime   | amount |
| 2      | 2021-05-01 | 100.00 |
| 3      | 2021-05-01 | 100.00 |
| 2      | 2021-05-02 | 100.00 |
| 1      | 2021-05-03 | 100.00 |
| 3      | 2021-05-03 | 100.00 |
| 4      | 2021-05-03 | 100.00 |
| 2      | 2021-05-05 | 100.00 |
| 1      | 2021-05-05 | 100.00 |
| 4      | 2021-05-06 | 100.00 |

我想要的输出是；每日金额"；对于特定日期只显示一次，如果有多行具有相同的日期，则对于其他行，它应该为空或显示类似"的内容；NA"：

| p.datetime | id | first_name | last_name | birth_date | card | pa.amount | "Daily Amount" |
| 2021-05-01 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 200.00         |
| 2021-05-01 | 3  | first_nam3 | last_nam3 | 1987-07-23 | M    | 100.00    |                |
| 2021-05-02 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 100.00         |
| 2021-05-03 | 1  | first_nam1 | last_nam1 | 1954-11-15 | A    | 100.00    | 300.00         |   
| 2021-05-03 | 3  | first_nam3 | last_nam3 | 1987-07-23 | M    | 100.00    |                |
| 2021-05-03 | 4  | first_nam4 | last_nam4 | 1968-05-07 | V    | 100.00    |                |
| 2021-05-05 | 2  | first_nam2 | last_nam2 | 1990-02-01 | M    | 100.00    | 200.00         |
| 2021-05-05 | 1  | first_nam1 | last_nam1 | 1954-11-15 | A    | 100.00    |                |
| 2021-05-06 | 4  | first_nam4 | last_nam4 | 1968-05-07 | V    | 100.00    | 100.00         |   

是否有某种方法可以从SQL(PostgreSQL特定的查询(中获得此输出？

Answer 1

看起来您的sum() over()计算错误的金额，请尝试

Sum(p.amount) OVER(partition BY s.id, p.datetime) AS "Daily Amount",

EDIT如果要格式化输出(每个日期仅一次累计金额(，请使用row_number()检测组中的第一行。确保over()子句与查询的ORDER BY同步。

SELECT 
id,
first_name,
last_name,
birth_date,
card,
datetime,
amount,
case when rn=1 then "Daily Amount" end "Daily Amount" 
FROM (
SELECT
s.id,
s.first_name,
s.last_name,
s.birth_date,
s.card,
p.datetime,
p.amount,
Sum(p.amount) OVER(partition BY s.id, p.datetime) AS "Daily Amount",
row_number() OVER(partition BY s.id, p.datetime ORDER BY p.amount) AS rn
FROM payments AS p
LEFT JOIN users AS s ON p.s_h_uuid = s.h_uuid
) t
ORDER BY datetime DESC, id, amount

Answer 2

如果每个日期只需要一次值，则使用row_number():

select (case when 1 = row_number() over (partition by p.date order by p.p_uuid)
then sum(p.amount) over (partition by p.date)
end) as day_payments