populating a aggregate table
I have a question
target table
str_week_day end_week_day age_id usage_ratio eq_type
------------ ------------ ------ ----------- -------
11-Jul-10 17-Jul-10 1.00 0.5 RECEIVER
11-Jul-10 17-Jul-10 2.00 0.5 HUB
18-Jul-10 24-jul-10 1.00 0.5 RECEIVER
18-Jul-10 24-jul-10 2.00 0.5 HUB
.......
......
and so on
source table
Start_date End_Date age_id eq_type
---------- -------- ------ -------
13-Jul-10 30-Jul-10 1.00 RECEIVER
15-Jul-10 25-Jul-10 2.00 HUB
ill give the sample
source_data
DIM_PANELIST_ID E E_SERIAL_NMBR DIM_PANEL_ID AGE_ID BEGIN_DATE END_DATE P_BEGIN_DATE INSERT_TS UPDATE_TS
--------------- - ------------- ------------ ---------- ---------- ---------- ------------ --------------------------------------------------------------------------- ----------------------------------------------------
1 M 172241 12 2 1616 1742 977 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
1 R 812890 12 2 1616 1742 977 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
2 M 154918 12 3 1560 1639 894 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
2 M 275351 12 3 1483 1560 894 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
3 M 155758 12 3 1560 1639 894 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
4 M 240087 12 4 1508 1557 1324 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
4 M 151575 12 4 1557 1601 1324 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
4 M 116520 12 4 1602 1324 13-JUL-10 09.07.35.000000 PM 开发者_JAVA百科 13-JUL-10 09.07.35.000000 PM
5 M 158929 12 4 1576 1588 868 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
6 R 812346 12 4 1621 1676 1112 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
6 M 170735 12 4 1621 1676 1112 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
7 M 279409 12 4 1662 1686 944 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
7 M 272720 12 4 1508 1661 944 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 M 194139 12 5 1712 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 M 279839 12 5 1484 1511 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 R 319711 12 5 1512 1620 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 R 812067 12 5 1620 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 M 274505 12 5 1512 1620 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
8 M 171353 12 5 1620 1709 1155 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
9 M 173784 12 5 1617 1315 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
9 R 814566 12 5 1617 1315 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
10 M 154363 12 5 1557 1324 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
11 M 145473 12 5 1558 1324 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
11 R 322260 12 5 1558 1324 13-JUL-10 09.07.35.000000 PM 13-JUL-10 09.07.35.000000 PM
12 M 158807 12 5 1576 1588 868 13-JUL-10 09.07.35.000000 PM 13-JUL-10
expected_target_data
STR_WK_DAY END_WK_DAY EQ_TYPE USAGE_RATIO
4-Jan-09 10-Jan-09 R 0.5
4-Jan-09 10-Jan-09 M 0.5
11-Jan-09 17-Jan-09 M 1
18-Jan-09 24-Jan-09 M 1
18-Jan-09 24-Jan-09 M 1
18-Jan-09 24-Jan-09 M 1
the data in the target is not accurate except the date's
I want to populate the target table I don’t know how to populate it The couple of scripts I have tried are
Mfrp1 table is the source table
select (select count(*)
from mfrp1
where aid=a.aid )/(select count(*)
from mfrp1) as ratio
, a.aid as ageid
, (case when a.m>a.r then 'M' else 'R'end) as eq_type
from (select aid
, sum(case when eq_type='M'then 1 else 0 end) as "M",
sum(case when eq_type='R'then 1 else 0 end) as "R"
from mfrp1 group by aid) a;
To populate a table you need to INSERT.
INSERT INTO TargetTable
(
str_week_day,
end_week_day,
age_id,
eq_type
)
SELECT
start_date,
end_date,
age_id,
eq_type
FROM
SourceTable
Have you considered creating a view instead of a table? The CREATE VIEW statement is just a query, and the same query you'd use to populate the table would do to define the view. The view won't become out of date.
As @JonH stated, you need to use an insert to populate a table. Making a number of assumptions, your query might look like this:
insert into Target_Table
(str_week_day, end_week_day, age_id, usage_ratio eq_type)
select str_week_day,
week_start+6 as end_week_day
a.aid as age_id,
a.cnt/m.cnt as usage_ratio,
(case when a.m>a.r then 'M' else 'R'end) as eq_type
from
(select aid,
sum(case when eq_type='M'then 1 else 0 end) as "M",
sum(case when eq_type='R'then 1 else 0 end) as "R" ,
count(*) as cnt,
trunc(start_date,"D") as str_week_day
from mfrp1
group by aid,
trunc(start_date,"D")) a,
(select count(*) as cnt from mfrp1) m;
However, as @Brian Hooper stated, you'd be much better off with a view. As he mentioned, the view would be always be consistent with the source data, as it is simply a stored query that runs against that data on demand. As a view is accessed in exactly the same manner as a table, they are mostly indistinguishable to a user (be it a person or an application).
create or replace view Target_Table as
select str_week_day,
week_start+6 as end_week_day
a.aid as age_id,
a.cnt/m.cnt as usage_ratio,
(case when a.m>a.r then 'M' else 'R'end) as eq_type
from
(select aid,
sum(case when eq_type='M'then 1 else 0 end) as "M",
sum(case when eq_type='R'then 1 else 0 end) as "R" ,
count(*) as cnt,
trunc(start_date,"D") as str_week_day
from mfrp1
group by aid,
trunc(start_date,"D")) a,
(select count(*) as cnt from mfrp1) m;
If this is too slow, you might consider a materialized view, which will do a lot of the work of keeping the aggregated data up-to-date, but stores the data separately to speed access. In most cases, this isn't necessary though.
加载中,请稍侯......
精彩评论