时间戳

首页 > 解决方案 > 计算合并到 PostgreSQL 中单个列的特定“日期”的累积总和

sql - 计算合并到 PostgreSQL 中单个列的特定“日期”的累积总和

问题描述

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

我已经完成了以下操作，但是它们每行提供一次累积值，我没有办法仅在日期而不是其他任何东西上进行分区，总和超过已付款。

在 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 特定查询）获取此输出？

标签： sqlpostgresqlwhere-clauseamazon-quicksight

解决方案

看起来你sum() over()计算的数量有误，试试

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

编辑 如果要格式化输出（每个日期仅累积一次），请用于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

推荐阅读