选择最新的可用值SQL [英] Select latest available value SQL

问题描述

下面是一个测试表，用于简化我要在查询中实现的目标。我正在尝试使用运行总和创建查询，该总和插入到列b 中，而最后的总和结果不为null。如果您可以想象，我希望每天获得某个客户的累计购买金额，那么某些天没有为特定客户购买任何商品，因此我想显示该特定客户的最新金额，而不是0 / null。

Below is a test table for simplification of what I am looking to achieve in a query. I am attempting to create a query using a running sum which inserts into column b that last sum result that was not null. If you can imagine, i'm looking to have a cumulative sum the purchases of a customer every day, some days no purchases occurs for a particular customer thus I want to display the latest sum for that particular customer instead of 0/null.

CREATE TABLE test (a int, b int);
insert into test values (1,null);
insert into test values (2,1);
insert into test values (3,3);
insert into test values (4,null);
insert into test values (5,5);
insert into test values (6,null);

1- select sum(coalesce(b,0)),coalesce(0,sum(b)) from test
2- select a, sum(coalesce(b,0)) from test group by a order by a asc
3- select a, sum(b) over (order by a asc rows between unbounded preceding and current row) from test group by a,b order by a asc

我不确定我对合并方式的解释是否正确。我以为这个 sum（coalesce（b，0））会在0为b的情况下插入0，并且总是采用b列的最新累计值。

I'm not sure if my interpretation of how coalesce works is correct. I thought this sum(coalesce(b,0)) will insert 0 where b is null and always take the latest cumulative sum of column b.

认为我可能已经通过查询3解决了这个问题。

Think I may have solved it with query 3.

我期望的结果看起来像这样：

The result I expect will look like this:

a  | sum
--------
1   
2    1
3    4
4    4
5    9
6    9

a的每条记录都显示b列的最后累计值。

Each records of a displays the last cumulative sum of column b.

任何方向都是有价值的。
谢谢

Any direction would be of valuable. Thanks

推荐答案

在Postgres中，您还可以使用SUM的窗口函数求和。

In Postgres you can also use the window function of SUM for a cummulative sum.

示例：

create table test (a int, b int);
insert into test (a,b) values (1,null),(2,1),(3,3),(4,null),(5,5),(6,null);

select a, sum(b) over (order by a, b) as "sum"
from test;

 a |  sum
-- | ----
 1 | null
 2 |    1
 3 |    4
 4 |    4
 5 |    9
 6 |    9

db<> fiddle 此处

如果 a是 t唯一，但您想对a分组？

然后您可以使用一个总和：

And if "a" isn't unique, but you want to group on a?
Then you could use a suminception:

select a, sum(sum(b)) over (order by a) as "sum"
from test
group by a

这篇关于选择最新的可用值SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！