时间戳

首页 > 解决方案 > 如何在 SQL 中执行此操作（PostgreSQL 窗口函数？）

sql - 如何在 SQL 中执行此操作（PostgreSQL 窗口函数？）

问题描述

我在 SQL（特别是 PostgreSQL）中遇到了我正在苦苦挣扎的情况。我正在使用的模式/模型不在我的控制之下，也不是我能够改变的东西，所以我试图找出处理我处理过的卡片的最佳方法。

首先，为这个问题简化的模式，但本质上它是将发票（Type = T）和交易（Type <> T）行组合到同一个表中。每张发票可以有 n 个交易行，每个客户有 n 个发票。

ID 类型 发票号码 客户 ID
100 一世 100 1
99 X 0 1
98 小号 0 1
97 吨 0 1
96 一世 99 1
95 X 0 1
94 小号 0 1

我最终想要得到的是类似于下面的内容，其中删除了 Invoice (Type = I) 记录，并且在每个 Invoice 记录填充了它的相应 InvoiceId 值之后的 Transaction (Type <> T) 记录。

ID 类型 发票号码 客户 ID
99 X 100 1
98 小号 100 1
97 吨 100 1
95 X 99 1
94 小号 99 1

到目前为止，我能得到的最接近的，不是很接近，是使用下面的 SQL：

select 
    t1.Id, 
    t1.Type, 
    t2.InvoiceNo, 
    t1.ClientId 
from table AS t1 
join (select 
          Id, 
          InvoiceNo,
          ClientId
      from table
      where type = 'I') as t2
on t1.ClientId = t2.ClientId
where t1.ClientId = t2.ClientId and t1.Id <= t2.Id and t1.Type <> 'I'

结果如下所示，它适用于每个客户的第一张发票，然后为每张发票创建额外的交易记录

ID 类型 发票号码 客户 ID
99 X 100 1
98 小号 100 1
97 吨 100 1
95 X 100 1
95 X 99 1
94 小号 100 1
94 小号 99 1

非常感谢任何帮助或指导！

**更新了更复杂的示例**

来源：

ID 类型 发票号码 客户 ID
1 X 0 1
2 一世 97 1
3 小号 0 2
4 X 0 2
5 小号 0 1
6 一世 98 2
7 小号 0 1
8 X 0 1
9 一世 99 1
10 吨 0 1
11 小号 0 1
12 X 0 1
13 一世 100 1

玩下面的答案，我想出了：

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 类型 发票号码 客户 ID imputed_invoiceno
12 X 0 1 100
11 小号 0 1 100
10 吨 0 1 100
8 X 0 1 99
7 小号 0 1 99
5 小号 0 1 99
1 X 0 1 99
4 X 0 2 98
3 小号 0 2 98

最佳案例结果：

ID 类型 发票号码 客户 ID
12 X 100 1
11 小号 100 1
10 吨 100 1
8 X 99 1
7 小号 99 1
5 小号 99 1
1 X 97 1
4 X 98 2
3 小号 98 2

标签： sqlpostgresql

解决方案

根据您的样本数据，您可以使用累积窗口函数：

select t.*,
       min(invoiceno) filter (where type = 'I') over (order by id desc) as imputed_invoiceno
from t;

推荐阅读