汇总函数不会汇总 - 使用总和函数汇总数据集

发布于 2025-01-30 01:55:50 字数 772 浏览 5 评论 0原文

我是Snowflake的新手，我正在尝试编写一个SQL查询，该查询将根据ref Ref列总结2行。总和函数似乎不起作用，因为我认为我缺少一些东西。

示例数据（交易）

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 500     | -500   |1234:234 |Confirmed |2022-05-04 |
| 100  | -500    |  500   |1234:234 |Pending   |2022-05-03 |

我想

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 0       |   0    |1234:234 |Confirmed |2022-05-04 |

在这里实现的目标是我到目前为止所做的

select id,
       sum(withdraw),
       sum(pending),
       ref,
       status,
       date,
from transaction
group by id,ref

原文

I am new to snowflake and I am trying to write an SQL query that would sum up 2 rows based on the ref column. The sum function seems to not work as I think im missing something.

sample data (transaction)

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 500     | -500   |1234:234 |Confirmed |2022-05-04 |
| 100  | -500    |  500   |1234:234 |Pending   |2022-05-03 |

what I want to achieve

| id   | withdraw| pending|   ref   |  Status  |   date   |
|:---- |:-------:| --- --:| -------:|---------:|---------:|
| 100  | 0       |   0    |1234:234 |Confirmed |2022-05-04 |

here is what I did so far

select id,
       sum(withdraw),
       sum(pending),
       ref,
       status,
       date,
from transaction
group by id,ref

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乄_柒ぐ汐 2025-02-06 01:55:50

如果您想要的是提取和的总和，以及最后的状态 and date per iD和ref然后使用窗口函数：

SELECT DISTINCT 
       id,
       SUM(withdraw) OVER (PARTITION BY id, ref) withdraw,
       SUM(pending) OVER (PARTITION BY id, ref) pending,
       ref,
       FIRST_VALUE(status) OVER (PARTITION BY id, ref ORDER BY date DESC) status,
       MAX(date) OVER (PARTITION BY id, ref) date
FROM transaction;

If what you want is the sum of withdraw and pending and the last status and date per id and ref then use window functions:

SELECT DISTINCT 
       id,
       SUM(withdraw) OVER (PARTITION BY id, ref) withdraw,
       SUM(pending) OVER (PARTITION BY id, ref) pending,
       ref,
       FIRST_VALUE(status) OVER (PARTITION BY id, ref ORDER BY date DESC) status,
       MAX(date) OVER (PARTITION BY id, ref) date
FROM transaction;

回复收藏 0 原文

~没有更多了~