CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
ataber
ataber

Reputation: 15

PostgreSQL, Window function usage question (with specific example)

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.

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.

Given this schema:

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

and this data:

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');

which looks like:

Id Type InvoiceNo ClientId
1 X 0 1
2 I 97 1
3 S 0 2
4 X 0 2
5 S 0 1
6 I 98 2
7 S 0 1
8 X 0 1
9 I 99 1
10 T 0 1
11 S 0 1
12 X 0 1
13 I 100 1

the result I'm looking for:

Id Type InvoiceNo 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';

Which gets me close:

Id Type InvoiceNo 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

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.

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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';

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

Here is a db<>fiddle.

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().

Upvotes: 2

Related Questions