You need to normalise your data model... the basic dataset you describe will
probably fit in one table with three columns, but that might be simplistic...
All your aggregate queries are pretty basic SQL statements, which could be
represented as views or made simpler to invoke by turning them into functions.
So, one table describing agents, with columns for agent, date and projected
cashflow will store all your data...
(index agent & date for performance)
eg: for a summary for all agents for the last 12 months:
select agent, sum(cashflow)
from tablewhere date >= now() - interval '1 year'group by agentorder by agent;
or a cashflow aggregate summary for the latest month
select sum(cashflow), avg(cashflow), min(cashflow), max(cashflow)
from table
where date = (select max(date) from table);
or get the agent with highest projected cashflow for the latest month
select agentfrom tablewhere date = (select max(date) from table) and
cashflow=(select max(cashflow) from table
where date = (select max(date) from table));
From: a <372660...@qq.com>
To: pgsql-general <pgsql-gene...@postgresql.org>
Sent: Thursday, August 30, 2018 9:14 PM
Subject: Ways to deal with large amount of columns;
Hi all:
I need to make a table contains projected monthly cashflow for multiple agents
(10,000 around).
Therefore, the column number would be 1000+.
I would need to perform simple aggregate function such as count, sum or average
on each cashflow projected.
So if there is anyway of doing this? Will there be anything like define a macro
in C that I can manipulate multiple columns by simple word that representing
them.
Thanks so much!
Shore
