Joining tables with recent date for each row then weighted averaging
0
There are three tables, such as equip_type , output_history, and time_history in Oracle DB.
Is there a way to join the three tables as shown below at (1) and then to get weighted average as shown below at (2)?
--equip_type table and the date
CREATE TABLE equip_type (
EQUIP_TYPE VARCHAR(60),
EQUIP VARCHAR(60)
);
INSERT INTO equip_type VALUES ('A','e1');
-- output_history and data
CREATE TABLE output_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data1 VARCHAR(60),
QUANTITY NUMBER(10)
);
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);
--time_history table and data
CREATE TABLE time_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data2 VARCHAR(60),
time NUMBER(10)
);
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);
(1) How to get joined table as below?
EQUIP MODEL DATE1 QUANTITY DATE2 TIME TYPE
---- ---- ---------- ------ -------- ---- ----
e1 m1 20180103 10 20180101 6 A
e1 m1 20180106 20 20180105 5 A
For each row in OUTPUT_HISTORY, *the most recent row at the point of the DATE1*in TIME_HISTORY is joined.
(2) Then, With the joined table above, how to get weighted average of TIME?
(QUANTITY * TIME) / sum of QUANTITY group by TYPE, MODEL
for example,(10×6 + 20×5)÷(10+20) for equip type A and model m1
sql oracle join aggregation
asked Jan 3 at 16:06
SoonSoon
377
add a comment |
0
There are three tables, such as equip_type , output_history, and time_history in Oracle DB.
Is there a way to join the three tables as shown below at (1) and then to get weighted average as shown below at (2)?
--equip_type table and the date
CREATE TABLE equip_type (
EQUIP_TYPE VARCHAR(60),
EQUIP VARCHAR(60)
);
INSERT INTO equip_type VALUES ('A','e1');
-- output_history and data
CREATE TABLE output_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data1 VARCHAR(60),
QUANTITY NUMBER(10)
);
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);
--time_history table and data
CREATE TABLE time_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data2 VARCHAR(60),
time NUMBER(10)
);
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);
(1) How to get joined table as below?
EQUIP MODEL DATE1 QUANTITY DATE2 TIME TYPE
---- ---- ---------- ------ -------- ---- ----
e1 m1 20180103 10 20180101 6 A
e1 m1 20180106 20 20180105 5 A
For each row in OUTPUT_HISTORY, *the most recent row at the point of the DATE1*in TIME_HISTORY is joined.
(2) Then, With the joined table above, how to get weighted average of TIME?
(QUANTITY * TIME) / sum of QUANTITY group by TYPE, MODEL
for example,(10×6 + 20×5)÷(10+20) for equip type A and model m1
sql oracle join aggregation
asked Jan 3 at 16:06
SoonSoon
377
add a comment |
0
0
0
There are three tables, such as equip_type , output_history, and time_history in Oracle DB.
Is there a way to join the three tables as shown below at (1) and then to get weighted average as shown below at (2)?
--equip_type table and the date
CREATE TABLE equip_type (
EQUIP_TYPE VARCHAR(60),
EQUIP VARCHAR(60)
);
INSERT INTO equip_type VALUES ('A','e1');
-- output_history and data
CREATE TABLE output_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data1 VARCHAR(60),
QUANTITY NUMBER(10)
);
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);
--time_history table and data
CREATE TABLE time_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data2 VARCHAR(60),
time NUMBER(10)
);
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);
(1) How to get joined table as below?
EQUIP MODEL DATE1 QUANTITY DATE2 TIME TYPE
---- ---- ---------- ------ -------- ---- ----
e1 m1 20180103 10 20180101 6 A
e1 m1 20180106 20 20180105 5 A
For each row in OUTPUT_HISTORY, *the most recent row at the point of the DATE1*in TIME_HISTORY is joined.
(2) Then, With the joined table above, how to get weighted average of TIME?
(QUANTITY * TIME) / sum of QUANTITY group by TYPE, MODEL
for example,(10×6 + 20×5)÷(10+20) for equip type A and model m1
sql oracle join aggregation
asked Jan 3 at 16:06
SoonSoon
377
There are three tables, such as equip_type , output_history, and time_history in Oracle DB.
Is there a way to join the three tables as shown below at (1) and then to get weighted average as shown below at (2)?
--equip_type table and the date
CREATE TABLE equip_type (
EQUIP_TYPE VARCHAR(60),
EQUIP VARCHAR(60)
);
INSERT INTO equip_type VALUES ('A','e1');
-- output_history and data
CREATE TABLE output_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data1 VARCHAR(60),
QUANTITY NUMBER(10)
);
INSERT INTO output_history VALUES ('e1','m1','20180103',10);
INSERT INTO output_history VALUES ('e1','m1','20180106',20);
--time_history table and data
CREATE TABLE time_history (
EQUIP VARCHAR(60),
MODEL VARCHAR(60),
Data2 VARCHAR(60),
time NUMBER(10)
);
INSERT INTO time_history VALUES ('e1','m1','20180101',6);
INSERT INTO time_history VALUES ('e1','m1','20180105',5);
(1) How to get joined table as below?
EQUIP MODEL DATE1 QUANTITY DATE2 TIME TYPE
---- ---- ---------- ------ -------- ---- ----
e1 m1 20180103 10 20180101 6 A
e1 m1 20180106 20 20180105 5 A
For each row in OUTPUT_HISTORY, *the most recent row at the point of the DATE1*in TIME_HISTORY is joined.
(2) Then, With the joined table above, how to get weighted average of TIME?
(QUANTITY * TIME) / sum of QUANTITY group by TYPE, MODEL
for example,(10×6 + 20×5)÷(10+20) for equip type A and model m1
sql oracle join aggregation
sql oracle join aggregation
asked Jan 3 at 16:06
SoonSoon
377
asked Jan 3 at 16:06
SoonSoon
377
edited Jan 5 at 18:01
Soon
asked Jan 3 at 16:06
SoonSoon
377
asked Jan 3 at 16:06
SoonSoon
377
asked Jan 3 at 16:06
SoonSoon
377
377
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
One method uses analytic functions to get the most recent record and then simple aggregation
select sum(quantity * time) / sum(quantity)
from output_history oh left join
(select th.*,
row_number() over (partition by equip, model order by date2 desc) as seqnum
from time_history th
) th
on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025899%2fjoining-tables-with-recent-date-for-each-row-then-weighted-averaging%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
One method uses analytic functions to get the most recent record and then simple aggregation
select sum(quantity * time) / sum(quantity)
from output_history oh left join
(select th.*,
row_number() over (partition by equip, model order by date2 desc) as seqnum
from time_history th
) th
on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
add a comment |
0
One method uses analytic functions to get the most recent record and then simple aggregation
select sum(quantity * time) / sum(quantity)
from output_history oh left join
(select th.*,
row_number() over (partition by equip, model order by date2 desc) as seqnum
from time_history th
) th
on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
add a comment |
0
0
0
One method uses analytic functions to get the most recent record and then simple aggregation
select sum(quantity * time) / sum(quantity)
from output_history oh left join
(select th.*,
row_number() over (partition by equip, model order by date2 desc) as seqnum
from time_history th
) th
on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
One method uses analytic functions to get the most recent record and then simple aggregation
select sum(quantity * time) / sum(quantity)
from output_history oh left join
(select th.*,
row_number() over (partition by equip, model order by date2 desc) as seqnum
from time_history th
) th
on oh.equip = th.equip and oh.model = th.model and th.seqnum = 1
group by equip, model;
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
edited Jan 5 at 17:45
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
answered Jan 3 at 16:11
Gordon LinoffGordon Linoff
792k36316419
792k36316419
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
add a comment |
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your code, But after executing your code, Date2 shows all 20180105. For Date1 20180103 , the most recent Date2 shoud be 20180101.
– Soon
Jan 5 at 17:57
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
Thank you for your edited code, But still the Date 2 shows all 20180105.
– Soon
Jan 5 at 18:21
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025899%2fjoining-tables-with-recent-date-for-each-row-then-weighted-averaging%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
