I am suffering from Bigquery with StandardSQL joining method
0
I would like to use the following two tables (POS and STAY) with Bigquery's StandardSQL to produce the following results. However, the join part of the third line is not successful. Please tell me a nice way.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 2 r1 B
##dt = date type
This is the query I wrote.
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
)
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank
FROM
POS as p
LEFT JOIN
STAY as s
ON
p.dt = s.dt
and
p.rm = s.rm
This is the result.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 null null null
google-bigquery
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
add a comment |
0
I would like to use the following two tables (POS and STAY) with Bigquery's StandardSQL to produce the following results. However, the join part of the third line is not successful. Please tell me a nice way.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 2 r1 B
##dt = date type
This is the query I wrote.
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
)
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank
FROM
POS as p
LEFT JOIN
STAY as s
ON
p.dt = s.dt
and
p.rm = s.rm
This is the result.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 null null null
google-bigquery
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24
add a comment |
0
0
0
1
I would like to use the following two tables (POS and STAY) with Bigquery's StandardSQL to produce the following results. However, the join part of the third line is not successful. Please tell me a nice way.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 2 r1 B
##dt = date type
This is the query I wrote.
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
)
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank
FROM
POS as p
LEFT JOIN
STAY as s
ON
p.dt = s.dt
and
p.rm = s.rm
This is the result.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 null null null
google-bigquery
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
I would like to use the following two tables (POS and STAY) with Bigquery's StandardSQL to produce the following results. However, the join part of the third line is not successful. Please tell me a nice way.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 2 r1 B
##dt = date type
This is the query I wrote.
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
)
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank
FROM
POS as p
LEFT JOIN
STAY as s
ON
p.dt = s.dt
and
p.rm = s.rm
This is the result.
dt rm total s_dt s_rm s_rank
1 1 r1 100 1 r1 A
2 2 r1 100 2 r1 B
3 3 r1 100 null null null
google-bigquery
google-bigquery
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
edited Nov 21 '18 at 8:09
Mitsuhiro Okuda
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
asked Nov 21 '18 at 4:36
Mitsuhiro OkudaMitsuhiro Okuda
12
12
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24
add a comment |
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24
add a comment |
1 Answer
1
active
oldest
votes
0
I did it
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
),
POS_STAY as(
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank,
if(p.dt = s.dt,1,0) as flag,
max(if(p.dt = s.dt,1,0)) over (PARTITION BY p.dt) as dt_flag,
max(s.dt) over (PARTITION BY p.dt) as max_s_dt_flag
FROM
POS as p
CROSS JOIN
STAY as s
)
select
*
from
POS_STAY
WHERE
flag = 1 or (dt_flag = 0 and s_dt = max_s_dt_flag)
This is the result.
dt rm total s_dt s_rm s_rank flag dt_flag max_s_dt_flag
1 1 r1 100 1 r1 A 1 1 2
2 2 r1 100 2 r1 B 1 1 2
3 3 r1 100 2 r1 B 0 0 2
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
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%2f53405331%2fi-am-suffering-from-bigquery-with-standardsql-joining-method%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
I did it
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
),
POS_STAY as(
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank,
if(p.dt = s.dt,1,0) as flag,
max(if(p.dt = s.dt,1,0)) over (PARTITION BY p.dt) as dt_flag,
max(s.dt) over (PARTITION BY p.dt) as max_s_dt_flag
FROM
POS as p
CROSS JOIN
STAY as s
)
select
*
from
POS_STAY
WHERE
flag = 1 or (dt_flag = 0 and s_dt = max_s_dt_flag)
This is the result.
dt rm total s_dt s_rm s_rank flag dt_flag max_s_dt_flag
1 1 r1 100 1 r1 A 1 1 2
2 2 r1 100 2 r1 B 1 1 2
3 3 r1 100 2 r1 B 0 0 2
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
add a comment |
0
I did it
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
),
POS_STAY as(
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank,
if(p.dt = s.dt,1,0) as flag,
max(if(p.dt = s.dt,1,0)) over (PARTITION BY p.dt) as dt_flag,
max(s.dt) over (PARTITION BY p.dt) as max_s_dt_flag
FROM
POS as p
CROSS JOIN
STAY as s
)
select
*
from
POS_STAY
WHERE
flag = 1 or (dt_flag = 0 and s_dt = max_s_dt_flag)
This is the result.
dt rm total s_dt s_rm s_rank flag dt_flag max_s_dt_flag
1 1 r1 100 1 r1 A 1 1 2
2 2 r1 100 2 r1 B 1 1 2
3 3 r1 100 2 r1 B 0 0 2
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
add a comment |
0
0
0
I did it
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
),
POS_STAY as(
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank,
if(p.dt = s.dt,1,0) as flag,
max(if(p.dt = s.dt,1,0)) over (PARTITION BY p.dt) as dt_flag,
max(s.dt) over (PARTITION BY p.dt) as max_s_dt_flag
FROM
POS as p
CROSS JOIN
STAY as s
)
select
*
from
POS_STAY
WHERE
flag = 1 or (dt_flag = 0 and s_dt = max_s_dt_flag)
This is the result.
dt rm total s_dt s_rm s_rank flag dt_flag max_s_dt_flag
1 1 r1 100 1 r1 A 1 1 2
2 2 r1 100 2 r1 B 1 1 2
3 3 r1 100 2 r1 B 0 0 2
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
I did it
WITH POS AS(
select 1 as dt,"r1" as rm,100 as total
UNION ALL
select 2 as dt,"r1" as rm,100 as total
UNION ALL
select 3 as dt,"r1" as rm,100 as total
),
STAY AS(
select 1 as dt,"r1" as rm,"A" as rank
UNION ALL
select 2 as dt,"r1" as rm,"B" as rank
),
POS_STAY as(
SELECT
p.*,
s.dt as s_dt,
s.rm as s_rm,
s.rank as s_rank,
if(p.dt = s.dt,1,0) as flag,
max(if(p.dt = s.dt,1,0)) over (PARTITION BY p.dt) as dt_flag,
max(s.dt) over (PARTITION BY p.dt) as max_s_dt_flag
FROM
POS as p
CROSS JOIN
STAY as s
)
select
*
from
POS_STAY
WHERE
flag = 1 or (dt_flag = 0 and s_dt = max_s_dt_flag)
This is the result.
dt rm total s_dt s_rm s_rank flag dt_flag max_s_dt_flag
1 1 r1 100 1 r1 A 1 1 2
2 2 r1 100 2 r1 B 1 1 2
3 3 r1 100 2 r1 B 0 0 2
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
answered Nov 22 '18 at 7:24
Mitsuhiro OkudaMitsuhiro Okuda
12
12
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%2f53405331%2fi-am-suffering-from-bigquery-with-standardsql-joining-method%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
Can you clarify why you expect the values from second table in row 3 of the result. What is the logic behind it?
– Mikhail Berlyant
Nov 21 '18 at 5:24
I analyzed POS data in connection with accommodation data. The last purchase is a purchase after the last staying day, but I want to put it in the last staying day.
– Mitsuhiro Okuda
Nov 21 '18 at 7:24