Re: Materialized View
Date: Sat, 24 Oct 2009 10:38:19 +0200
Message-ID: <4ae2bcf4$0$83245$e4fe514c_at_news.xs4all.nl>
The Magnet schreef:
> Hi,
>
> Hope I can explain this properly:
>
> I'm hoping this can be done in just 1 long SQL statement. We have
> several queries that gather counts from different tables. Each takes
> a while and since they are part of the same app, the screen takes a
> while. So, we want to use a materialized view.
>
> Problem is that each column would need to be a query. The tables are
> different, though we basically need the same data from them, a date
> and a count of records on that date. So, each query will return
> different results, but yet we want 1 materialized view to contain the
> counts of the 6 tables. For example, here are 2 of the queries:
>
> SELECT
> event_date, count(*) DIVIDENDS
> FROM
> (
> SELECT
> DISTINCT(ticker) ticker,
> company_name company,
> div_rate_per_share dividend,
> announcement_date event_date,
> announcement_date announcement_date,
> div_expected_date ex_date,
> record_date record_date,
> payment_date payment_date
> FROM
> data_holder.expected_dividends
> UNION
> SELECT
> DISTINCT(ticker) ticker,
> company_name company,
> div_rate_per_share dividend,
> div_expected_date event_date,
> announcement_date announcement_date,
> div_expected_date ex_date,
> record_date record_date,
> payment_date payment_date
> FROM data_holder.expected_dividends
> UNION
> .
> .
> .
> And another one:
>
> SELECT
> t.q0_expected_report_date, count(*) earnings
> FROM
> master_table m,
> estimates e,
> data_holder.eps_reports_ex5 t
> WHERE
> e.m_ticker(+) = m.m_ticker AND
> m.ticker = t.ticker AND
> t.late_last_flag = 0 AND
> t.report_date = (SELECT MAX(a.report_date)
> FROM
> data_holder.eps_reports_ex5 a
> WHERE
> t.ticker = a.ticker AND
> a.late_last_flag = 0)
> group by t.q0_expected_report_date
> order by t.q0_expected_report_date;
>
>
> So, each column in the view will represent a query of counts from a
> particular table. Is this possible? It would speed everything up
> since we have the results already.
>
> I was able to create a view, but not with the proper data. My guess
> is some combination of ROW_NUMBER or COUNT or something like that....
>
Yes you can. Create your different queries per result column in the materialized view like
select max(first_column), max(second_column), max(third_column)
from
select first_column,0,0
union
select 0, second_column, 0
union
select 0, 0, third_column
The unions create a view with three (or n) rows, and you condense it by selecting with max. You get a result with one row in your materialized view. You could even consider using a table, and mark the row with a calculated date column, so you could even keep your history in the table!
(I even think there is a function to return the first non-null column you could use in stead of the max, and then you could use null in stead of 0)
You could get your results in *different* rows (if you would want that) by using a marker column in your query parts:
select 'count ticker' marker, <your query here>
union
select 'count earnings' marker, <your query here>
union
select 'count value3' marker, <your query here>
which would give you n rows with a column telling you which count is in that row.
Shakespeare Received on Sat Oct 24 2009 - 03:38:19 CDT