Re: Materialized View
From: The Magnet <art_at_unsu.com>
Date: Sat, 24 Oct 2009 09:54:00 -0700 (PDT)
Message-ID: <814e73c3-7cda-45f6-bd14-8929e74b0cd7_at_e18g2000vbe.googlegroups.com>
On Oct 24, 3:38 am, Shakespeare <what..._at_xs4all.nl> wrote:
> 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
Date: Sat, 24 Oct 2009 09:54:00 -0700 (PDT)
Message-ID: <814e73c3-7cda-45f6-bd14-8929e74b0cd7_at_e18g2000vbe.googlegroups.com>
On Oct 24, 3:38 am, Shakespeare <what..._at_xs4all.nl> wrote:
> 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
Thanks Shakespeare, one question I have though.
If each column comes from a different query, which has different FROM clauses, doesn't there have to be some common column? Maybe I am not looking at it right, but say query #1 returns 100 rows from TABLE1, and query #2 returns 95 rows from TABLE2 & TABLE3. The 5 rows that are missing from query #2 should be NULL in the final view. Does this mean that somehow I need to maybe create "virtual" tables/views and join them before creating the actual materialized view?? And if yes, this could get messy with so many columns, 6 to be exact.
What is good about this view, is that it is date driven, so I only need 1 date column.
Many thanks! Received on Sat Oct 24 2009 - 11:54:00 CDT