How to Implement Hierarchical Query in bigquery
0
I have some table "tab1", which contains the sample data as like follws
I am expecting the output like as follows
How to implement in "BIGQUERY", Please help me to achieve this.
Thanks
Kalyan
sql google-bigquery
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
add a comment |
0
I have some table "tab1", which contains the sample data as like follws
I am expecting the output like as follows
How to implement in "BIGQUERY", Please help me to achieve this.
Thanks
Kalyan
sql google-bigquery
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08
add a comment |
0
0
0
I have some table "tab1", which contains the sample data as like follws
I am expecting the output like as follows
How to implement in "BIGQUERY", Please help me to achieve this.
Thanks
Kalyan
sql google-bigquery
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
I have some table "tab1", which contains the sample data as like follws
I am expecting the output like as follows
How to implement in "BIGQUERY", Please help me to achieve this.
Thanks
Kalyan
sql google-bigquery
sql google-bigquery
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
edited Nov 20 '18 at 15:08
Mikhail Berlyant
59.2k43671
59.2k43671
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
asked Nov 20 '18 at 10:17
kalyan4uonlykalyan4uonly
244
244
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08
add a comment |
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08
add a comment |
1 Answer
1
active
oldest
votes
2
I think you just need lead():
select t.*,
lead(effective_from, 1, date('9999-12-31')) over
(partition by sno
order by effective_from
) as effective_to
from t
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
add a comment |
Your Answer
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%2f53390784%2fhow-to-implement-hierarchical-query-in-bigquery%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
2
I think you just need lead():
select t.*,
lead(effective_from, 1, date('9999-12-31')) over
(partition by sno
order by effective_from
) as effective_to
from t
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
add a comment |
2
I think you just need lead():
select t.*,
lead(effective_from, 1, date('9999-12-31')) over
(partition by sno
order by effective_from
) as effective_to
from t
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
add a comment |
2
2
2
I think you just need lead():
select t.*,
lead(effective_from, 1, date('9999-12-31')) over
(partition by sno
order by effective_from
) as effective_to
from t
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
I think you just need lead():
select t.*,
lead(effective_from, 1, date('9999-12-31')) over
(partition by sno
order by effective_from
) as effective_to
from t
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
answered Nov 20 '18 at 11:45
Gordon LinoffGordon Linoff
774k35306408
774k35306408
add a comment |
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%2f53390784%2fhow-to-implement-hierarchical-query-in-bigquery%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
could you please describe your logic of this output
– Zaynul Abadin Tuhin
Nov 20 '18 at 10:30
If you see the existed data table, term1 has definition 'xyz' and it was given on 01-jan-2018, but for the same term a new definition has given on 01-apr-2018. so the term1 first definition was valid from 01-jan-2018 to 01-apr-2018. this will continued as long as new definitions were added. its kind of slowly changing dimension
– kalyan4uonly
Nov 20 '18 at 11:08