我不知道如何继续清点库存并将库存存储在生成的发票中。
实时数据：http://sqlfiddle.com/#!17/20ff4/4号
表 articles 静态产品信息存储在那里：

create table articles
(
    prod_article_pvt_uuid uuid default uuid_generate_v1() not null
        constraint proxies_pk
            primary key,
    prod_article_pub_uuid uuid default uuid_generate_v1mc(),
    prod_article_pub_id serial not null,
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    cat integer,
    prod_brand_id integer,
    barcode bigint,
    supplier_code varchar
);

此表打开一个文档，并定义文档的类型：

create table documents
(
    sale_doc_pvt_uuid uuid default uuid_generate_v1() not null
        constraint documents_pk
            primary key,
    sale_doc_pub_uuid uuid default uuid_generate_v1mc(),
    sale_doc_alpha varchar,
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    sale_type_id integer not null,
    frozen boolean default false
);

create table types
(
    sale_type_id serial not null
        constraint types_pk
            primary key,
    name varchar,
    draft boolean default false
);

如果将每一项添加为文档的一项：

create table items
(
    sale_unit_pvt_uuid uuid default uuid_generate_v1() not null
        constraint units_pk
            primary key,
    sale_unit_pub_uuid uuid default uuid_generate_v1mc(),
    t timestamp with time zone default CURRENT_TIMESTAMP(6),
    archived boolean default false,
    sale_doc_pvt_uuid uuid not null,
    prod_article_pvt_uuid uuid not null,
    quantity numeric(16,6) not null
);

这些是仓库中可用的产品：

create table products
(
    whse_prod_pvt_uuid uuid default uuid_generate_v1() not null,
    whse_prod_pub_uuid uuid default uuid_generate_v1mc(),
    t_created timestamp with time zone default CURRENT_TIMESTAMP(6),
    prod_article_pvt_uuid uuid not null,
    quantity numeric(16,6) default 1,
    global_ctry_id integer
);

我试图这样质疑：

SELECT
     (sum(products.quantity) - COALESCE(_items.quantity, 0)) as quantity
     ,articles.prod_article_pub_id as article

 FROM products

 LEFT JOIN articles ON products.prod_article_pvt_uuid = articles.prod_article_pvt_uuid

 LEFT JOIN (
     SELECT DISTINCT ON (items.prod_article_pvt_uuid)
         items.prod_article_pvt_uuid
         ,items.sale_doc_pvt_uuid
         ,sum(items.quantity) as quantity
     FROM items
     LEFT JOIN documents ON items.sale_doc_pvt_uuid = documents.sale_doc_pvt_uuid
     LEFT JOIN types ON documents.sale_type_id = types.sale_type_id
     WHERE
         items.archived = false
     AND documents.archived = false
     AND types.draft = false
     GROUP BY
         items.prod_article_pvt_uuid
         ,items.sale_doc_pvt_uuid
 ) AS _items ON _items.prod_article_pvt_uuid = products.prod_article_pvt_uuid

 GROUP BY
     articles.prod_article_pub_id
     ,_items.quantity

 ORDER BY
     article ASC,
     quantity DESC

它确实给了我想要的结果。但我想知道这是否是正确的方法，因为它看起来可能会与数百万行斗争。
基本上每次我需要显示可用库存量时，我都要计算生成的每个文档。
这是个好主意吗？
这会不会是一个性能瓶颈与数百万行？