In general, when I have to handle
Ledger type data (which this problem is), I tend to hold data in 3
tables
- Master Ledger ( Product ID, Name, etc)
- Master Ledger Balances(Product ID, Fiscal_Year, Opening
Balance, Net_Transaction_P1, Net_Transaction_P2, ... etc)
- Master Ledger Transactions(Product_ID, (Fiscal_Year), Date,
Amount......)
I use Triggers and Stored Procedures to maintain consistency. This
allows you to quickly navigate across the population of your data
and drill down to the detailed transaction when required.
Careful manipulation of the Master Ledger Balances table lets you
retrieve multiple different kinds of information at a single pass,
ie This Year To Date Actual, Last Year To Date Actual and Budget
This Year To Date. I usually create functions/SPs to do this even
more rapidly.
If you have many bulk updates, it can be better to drop all
indices prior to updating and then rebuilding them.
Robin St.Clair
On 18/11/2013 10:04, Hengky Liwandouw wrote:
Thanks a lot Ken,
I will try it soon.
But when the table becomes huge (how big 'huge' in postgres
?), how to optimize such command ?
I have index on all important field like date, productid,
supplierid, customerid and so on
Optimization is really an important thing as i plan to keep
all transaction data as long as possible.
On Nov 18, 2013, at 5:37 PM, Ken Tanzer wrote:
If the tables aren't huge, you're not
concerned about optimization, and you just want to get
your numbers, I think something like this would do the
trick. I haven't actually tried it 'cause I didn't have
easy access to your tables:
SELECT
a.product_id,
a.product_name,
b.initial_stock_sum,
c.in_out_sum,
c.in_sum,
c.out_sum
FROM
a
LEFT JOIN
(SELECT
product_id,
SUM(initial_stock) AS initial_stock_sum
FROM b
GROUP BY
product_id
) b USING
(product_id)
LEFT JOIN
(SELECT
product_id,
sum(CASE
WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0
END) AS in_out_sum,
sum(CASE
WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE'
THEN in ELSE 0 END) AS in_sum,
sum(CASE
WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE'
THEN out ELSE 0 END) AS out_sum
FROM c
GROUP BY
product_id
) c USING
(product_id)
WHERE
a.supplier_id='XXX';
Cheers,
Ken
|
