Re: Options for complex materialized views sharing most of the same logic?

On Sun, 2015-12-27 at 15:39 -0600, Wells Oliver wrote:
> I then have four different views which do largely the same thing,
> totaling the days four different ways:
>
> 1) by last 10
> 2) by the last 30
> 3) by the last 60
> 4) by the entire year
>
> Each of these views is basically a copy of one another for 99% of the
> code (the summing, percentages, etc). The only differences are:
>
> 1) checks the days_back <= 10
> 2) checks days_back <= 30
> 3) checks days_back <= 60
> 4) does not check days_back
>

Hello, I do not follow why you have four views and presumably run four
separate selects.
Assuming one of the base tables to the view has a column containing a
timestamp, why can't you define the column days_back in the view along
the lines of:-

select ((extract(epoch from current_date) - extract(epoch from
tables_date_column)) / (24 * 60 * 60)) as days_back

Then you can test days_back for the appropriate values using CASE, do
the calculations, and end up running a single select query.

Don't know if this helps. 

Cheers,
Rob