如何创建关联视图获取同一客户同商品的上一个InvoicePosition？

现有表结构 #

create table Article (
  ID int generated by default as identity primary key, 
  Name text);
create table Client (
  ID int generated by default as identity primary key, 
  Name text);
create table Invoice (
  ID int generated by default as identity primary key, 
  Date date, 
  Client int references client(id));
create table InvoicePosition (
  ID int generated by default as identity primary key, 
  Invoice int references invoice(id), 
  Article int references article(id), 
  Quantity numeric, 
  Price numeric,
  constraint each_invoice_can_only_contain_one_invoiceposition_for_each_article
    unique (Invoice,Article));

示例数据 #

insert into article(id,name) values
  (1,'article1'),
  (2,'article2'),
  (3,'article3');
insert into client(id,name) values
  (1,'client1'),
  (2,'client2'),
  (3,'client3');
insert into invoice(id,date,client) values
  (1,'yesterday',1),    -- 客户1的发票
  (2,'today',1),        -- 客户1的发票
  (3,'tomorrow',1),     -- 客户1的发票
  (4,'yesterday', 2);   -- 客户2的发票
insert into InvoicePosition values
  (1,1,1,11.0,77.0),    -- 客户1昨日发票的商品1
  (2,1,2,22.0,88.0),    -- 客户1昨日发票的商品2
  (3,1,3,33.0,99.0),    -- 客户1昨日发票的商品3
  (4,2,1,12.0,78.0),    -- 客户1今日发票的商品1
  (5,2,2,23.0,89.0),    -- 客户1今日发票的商品2
  (6,4,1,34.0,100.0);   -- 客户2昨日发票的商品1

需求说明 #

查询InvoicePosition时，需附带同一客户、同一商品的最近历史发票行，满足以下条件：

• 历史发票行的商品ID与当前行一致
• 历史发票所属客户与当前行所属客户一致
• 历史发票的日期早于当前发票的日期
• 仅取日期最近的那一条历史记录

目标是创建视图，支持后续用WHERE子句筛选特定发票的所有行（含对应历史记录）。

实现视图的SQL #

利用PostgreSQL的LATERAL JOIN搭配排序和限制，精准获取最近的历史记录：

CREATE OR REPLACE VIEW InvoicePosView AS
SELECT
  ip.ID AS InvoicePosId,
  ip.Invoice AS InvoiceId,
  ip.Article AS ArticleID,
  ip.Quantity,
  ip.Price,
  prev_ip.ID AS LastInvoicePosId
FROM InvoicePosition ip
JOIN Invoice inv ON ip.Invoice = inv.ID
LEFT JOIN LATERAL (
  SELECT ip_prev.ID
  FROM InvoicePosition ip_prev
  JOIN Invoice inv_prev ON ip_prev.Invoice = inv_prev.ID
  WHERE
    ip_prev.Article = ip.Article
    AND inv_prev.Client = inv.Client
    AND inv_prev.Date < inv.Date
  ORDER BY inv_prev.Date DESC
  LIMIT 1
) prev_ip ON true;

验证示例查询 #

执行以下查询：

-- 查询客户1今日发票中商品1的记录
SELECT * FROM InvoicePosView WHERE InvoicePosId = 4;

将得到预期结果：

InvoicePosId | InvoiceId | ArticleID | Quantity | Price | LastInvoicePosId
-------------|-----------|-----------|----------|-------|------------------
4            | 2         | 1         | 12.0     | 78.0  | 1

Supabase/PostgREST适配说明 #

• 该视图完全兼容Supabase的PostgreSQL环境，创建后可直接通过PostgREST接口访问
• 支持在PostgREST请求中添加筛选条件（比如?InvoiceId=eq.2来获取某发票的所有行）
• 若需要返回更多历史行字段（如历史数量、价格），只需在子查询和视图的SELECT列表中添加对应字段即可

内容的提问来源于stack exchange，提问作者Robert P