google-bigquery - 我正在遭受使用 StandardSQL 连接方法的 Bigquery

Question

我想在 Bigquery 的 StandardSQL 中使用以下两个表（POS 和 STAY）来产生以下结果。但是，第三行的join部分并不成功。请告诉我一个好方法。

    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

这是我写的查询。

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

---

这就是结果。

    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

Answer 1

我做的

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)

这就是结果。

    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