Let's say this is the sample date coming from a join of 2 tables. Database is Postgres 9.6
id product_id invoice_id amount date 1 PROD1 INV01 2 01-01-2018 2 PROD2 INV02 3 01-01-2018 3 PROD1 INV01 2 05-01-2018 4 PROD1 INV03 1 05-01-2018 5 PROD2 INV02 3 08-01-2018 6 PROD2 INV04 4 08-01-2018
I want to know if it's possible in a optimized way to:
- Get all the PRODx with their respective INVx which have the latest date, but per product_id. Please note that records unused from a day may be reported to a new one. This means:
id product_id invoice_id amount date 3 PROD1 INV01 2 05-01-2018 4 PROD1 INV03 1 05-01-2018 5 PROD2 INV02 3 08-01-2018 6 PROD2 INV04 4 08-01-2018
- Get daily summed amounts for each PRODx but fill the gaps with the previous ones if day does not exist.
product_id amount date PROD1 2 01-01-2018 PROD2 3 01-01-2018 PROD1 2 02-01-2018 PROD2 3 02-01-2018 PROD1 2 03-01-2018 PROD2 3 03-01-2018 PROD1 2 04-01-2018 PROD2 3 04-01-2018 PROD1 3 05-01-2018 PROD2 3 05-01-2018 PROD1 3 06-01-2018 PROD2 3 06-01-2018 PROD1 3 07-01-2018 PROD2 3 07-01-2018 PROD1 3 08-01-2018 PROD2 7 08-01-2018
A few thoughts:
For first question I could obtain the
max(date)for each PRODx and the pick for each PRODx the rows that have the
date=with max(date)but I was wondering if there's faster way to obtain this given a large number of recors in the database
For the second question, I could generate a series of dates for the interval needed and then use
WITH rows Asand do the query grouping by
sumby amount and then select for each date the previous values from
limit 1but that does not sound that optimized either.
Looking forward for any input. Thank you.
Later edit: Trying to give DISTINCT ON () a try.
- If I have
distinct on(product_id, invoice_id)then I don't get only the most recent ones for the most recent date. If there were invoice_ids in the the past, beside the latest date, then they will be returned
- If I have
distinct on (product_id)then it returns from the most recent date, but as normal, only the last rows even if in the last day I have two positions for PROD1.
Basically I need something like 'I need for the most recent date, all the product_ids and their invoice_ids while keeping in mind that a product_id can have multiple invoice_ids'
Later edit 2:
Running a query like for first question seems to be reasonably fast:
select product_id, invoice_id, amount from mytable inner join myOtherTable on... inner join (select max(date) as last_date, product_id from mytable group by product_id) sub on mytable.date = sub.last_date