我有三個以這種方式相關的表：
- 商店（商店有很多產品）
- 產品（一個產品有很多產品庫存歷史）
- 產品_庫存_歷史
產品有一個名為status的欄位。它具有當前的庫存狀態。可能的值為 1（有貨）或任何其他值（無貨）。
Product_Stock_History也有一個狀態欄位，具有相同的可能值。
我想在 SQL 中構建的查詢是：
對于所有商店，我想獲取所有沒有庫存的產品，這些產品在其歷史記錄中的最新 2 條記錄也沒有庫存。
總之，我想知道哪些產品已經斷貨3天了。
我還想知道如何構建索引，以便此查詢高效運行。
uj5u.com熱心網友回復：
select p.product_id
from Products p inner join Product_Stock_History ph
on ph.product_id = p.product_id
where p.status <> 1 and ph.status <> 1 and ph.date > current_date - interval '3 days'
group by p.product_id
having count(*) = 2
不參考當前日期：
select p.product_id
from Products p inner join Product_Stock_History ph
on ph.product_id = p.product_id
where p.status <> 1
qualify
row_number() over (
partition by p.product_id order by date desc
) = 1 and
count(*) over (
partition by p.product_id order by date desc
rows between current row and 1 following
) filter (ph.status <> 1) = 2
我忘了 Postgres 不允許qualify. 看看這個，直到我能回來：https : //dbfiddle.uk/?rdbms=postgres_14&fiddle=ad252c27153626eb6c3e33fae5ab1eb7
uj5u.com熱心網友回復：
試試這個
Select p.* from products p where productid in
Select productid from (
(Select PSH.productid,
row_number() over (partition by PSH.productid order by versionid desc) rn from Product_Stock_History psh where status<>1
)
where
rn<=2) where date_col= current_date-3 and status<>1
uj5u.com熱心網友回復：
您可以使用Himanshu 之類的視窗查詢或shawnt00 之類的group by/have來做您想做的事情。或者，您可以重新組織架構以保持簡單。
不是存盤標志，而是存盤兩個時間戳：stocked_at和out_of_stock_at。
stores
products
store_products
store_id references stores
product_id references products
unique(store_id, product_id)
stocked_at timestamp,
out_of_stock_at timestamp,
check (stocked_at != out_of_stock_at)
從它們計算它的狀態。
select
stocked_at > out_of_stock_at as in_stock
from store_products
您可以使用生成的列來方便地實作這一點。
in_stock boolean generated always as (stocked_at > out_of_stock_at) stored
總之，我想知道哪些產品已經斷貨3天了。
select product_id
from store_products
where not in_stock
and out_of_stock_at < current_timestamp - '3 days'::interval
我還想知道如何構建索引，以便此查詢高效運行。
在 上建立復合索引(out_of_stock_at, stocked_at)。
狀態標志通常可以由連接表替換。
我們可以進行一項批判性觀察。
- 商店的目錄與其庫存不同。
所以我們有...
- 有產品。
- 有商店。
- 商店有他們提供的產品目錄。
- 商店有庫存產品的庫存。
表示為表和約束...
stores
products
store_product_catalog
store_id references stores
product_id references products
unique(store_id, product_id)
-- This allows a store to have inventory not in their catalog.
-- If you don't want that, give store_product_catalog an id
-- and relate store_product_inventory to store_product_catalog
store_product_inventory
store_id references stores
product_id references products
unique(store_id, product_id)
quantity
updated_at
撰寫更新觸發器以在 store_product_inventory.quantity 更改時更改 store_product_inventory.updated_at。
總之，我想知道哪些產品已經斷貨3天了。
select product_id
from store_product_inventory
where quantity = 0
and updated_at < current_timestamp - '3 days'::interval
我還想知道如何構建索引，以便此查詢高效運行。
在 上建立復合索引(quantity, updated_at)。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/385349.html
標籤：sql PostgreSQL的