PostgreSQL, Window 函数使用问题(附具体例子) [英] PostgreSQL, Window function usage question (with specific example)

问题描述

我在 PostgreSQL 中遇到了困难.我正在使用的模式/模型不受我的控制，也不是我能够改变的，所以我试图找出处理我所发牌的最佳方法.

I have a situation in PostgreSQL that I'm struggling with. The schema/model that I'm working with is not under my control and not something I'm able to alter, so I am trying to figure out the best way to deal with the cards I've been dealt.

首先，针对此问题简化了架构，但本质上是发票(类型 = T)和交易(类型 <> T)行合并到同一个表中.每个发票可以并且将会有 n 个交易行和每个客户的 n 个发票可以混合，如下所示.

First, the schema, simplified for this question, but essentially it's invoice (Type = T) and transaction (Type <> T) lines combined into the same table. There can and will be n-number of tranaction lines per invoice and n-number of invoices per client that can be intermingled as seen below.

鉴于此架构:

CREATE TABLE t (
id serial PRIMARY KEY,
Type VARCHAR (50) NOT NULL,
InvoiceNo VARCHAR (50) NOT NULL,
ClientId VARCHAR (50) NOT NULL);

和这个数据:

insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '97', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '98', '2');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '99', '1');
insert into t (type, InvoiceNo, ClientId) values ('T', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('S', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('X', '0', '1');
insert into t (type, InvoiceNo, ClientId) values ('I', '100', '1');

看起来像:

<头>

ID	类型	发票号	ClientId
1	X	0	1
2	我	97	1
3	S	0	2
4	X	0	2
5	S	0	1
6	我	98	2
7	S	0	1
8	X	0	1
9	我	99	1
10	T	0	1
11	S	0	1
12	X	0	1
13	我	100	1

我正在寻找的结果:

<头>

ID	类型	发票号	ClientId
12	X	100	1
11	S	100	1
10	T	100	1
8	X	99	1
7	S	99	1
5	S	99	1
1	X	97	1
4	X	98	2
3	S	98	2

根据上一个问题的答案，我想出了:

Playing with an answer from a previous question, I came up with:

select * from (select t.*,
   max(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id DESC) as imputed_invoiceno 
from t) as x
where Type <> 'I';

这让我很接近:

<头>

ID	类型	发票号	ClientId	imputed_invoiceno
12	X	0	1	100
11	S	0	1	100
10	T	0	1	100
8	X	0	1	99
7	S	0	1	99
5	S	0	1	99
1	X	0	1	99
4	X	0	2	98
3	S	0	2	98

这里唯一的问题是 Id = 1 的记录应该有 imputed_invoiceno = 97，但没有，我不清楚为什么.

The only issue here is that record with Id = 1 should have imputed_invoiceno = 97, but doesn't and I'm unclear as to why.

推荐答案

字符串类型发生了一些奇怪的事情.您使用数字通过此查询获得预期结果:

Something strange is going on with the string type. You get the expected results with this query using numbers:

select *
from (select t.*,
             min(InvoiceNo) filter (where type = 'I') over (partition by clientid order by id desc) as imputed_invoiceno 
      from t
     ) x
where Type <> 'I';

请注意，我将 max() 更改为 min().

Note that I changed the max() to a min().

这里是db<>fiddle.

Here is a db<>fiddle.

问题在于字符串的排序不是您所期望的.它不是 0 <97 <98＜99＜100.它是 '0' <'100' <'97' <'98' <'99'.我认为您使用 max() 解决了这个问题.

The issue is that the ordering for strings is not what you expect. It is not 0 < 97 < 98 < 99 < 100. It is '0' < '100' < '97' < '98' < '99'. I think you worked around this using max().

这篇关于PostgreSQL, Window 函数使用问题(附具体例子)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！