How do I apply a condition on a related table for the last records?-CodePudding

I have three tables related this way:

Store (a store has many products)
Products (a product has many product stock histories)
Product_Stock_History

Products has a field named status. It has the current stock status. The possible values are 1 (in stock) or any other value (not in stock).

Product_Stock_History also has a status field, with the same possible values.

The query I want to build in SQL is:

For all stores, I want to get all products not in stock, which have the latest 2 records in their history not in stock either.

In short, I want to know which products have been out of stock for 3 days.

I would also like to know how to build the index, so this query runs efficiently.

CodePudding user response：

Try this

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

CodePudding user response：

You can do what you want with a window query like Himanshu or a group by/having like shawnt00. Or you can reorganize your schema to keep it simple.

Instead of storing a flag, store two timestamps: stocked_at and 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)

Calculate its status from them.

select
  stocked_at > out_of_stock_at as in_stock
from store_products

You can make this convenient with a generated column.

  in_stock boolean generated always as (stocked_at > out_of_stock_at) stored

In short, I want to know which products have been out of stock for 3 days.

select product_id
from store_products
where not in_stock
  and out_of_stock_at < current_timestamp - '3 days'::interval

I would also like to know how to build the index, so this query runs efficiently.

Make a composite index on (out_of_stock_at, stocked_at).

Status flags can often be replaced by join tables.

We can make one critical observation.

A store's catalog is different from its inventory.

So we have...

There are products.
There are stores.
Stores have a catalog of products they offer.
Stores have an inventory of products they have in stock.

Expressed as tables and constraints...

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

Write an update trigger to change store_product_inventory.updated_at when the store_product_inventory.quantity changes.

In short, I want to know which products have been out of stock for 3 days.

select product_id
from store_product_inventory
where quantity = 0
  and updated_at < current_timestamp - '3 days'::interval

I would also like to know how to build the index, so this query runs efficiently.

Make a composite index on (quantity, updated_at).

CodePudding user response：

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

Without referencing current date:

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

I forgot that Postgres doesn't allow qualify. Take a look at this until I can come back: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ad252c27153626eb6c3e33fae5ab1eb7