postgresql - 查询以在亚马逊 Redshift 中获取从给定日期到 48 小时的数据

Question

使用 redshift 中的数据，我们需要了解 48 小时内在一个平台上购物并在另一个数字平台上购买的 id 的数量。

每个平台都有一个单独的事实表，用户 ID 在所有表中都是通用的。

我正在尝试使用下面的代码，但没有用。非常感谢任何帮助。

Select Distinct a.id ,
b.login AndroidLogin,
c.login iPadLogin,
d.login iPhoneLogin,
e.login MobileLogin,
f.login WebLogin
from
users a inner join
fct_android_20160101_20160331 b 
on a.id = b.userid 
inner join  
fct_ipad_20160101_20160331 c 
on a.id = c.userid
inner join
fct_iphone_20160101_20160331 d 
on a.id = d.userid
inner join
fct_mobilepolaris_20160101_20160331 e 
on a.id = e.userid
inner join
fct_polaris_20160101_20160331 f
on a.id = f.userid
where (b.date between '2016-01-01' and dateadd(day,2,date))
or (c.date between '2016-01-01' and dateadd(day,2,date)) 
or (d.date between '2016-01-01' and dateadd(day,2,date))
or (e.date between '2016-01-01' and dateadd(day,2,date)) 
or (f.date between '2016-01-01' and dateadd(day,2,date))
and id > 0 
and id <> 'null'
 and length(Id) = 10 
 and LEFT(Id,1) IN ('0','1','2','3','4','5','6','7','8','9')
order by 1
;

Answer 1

您需要 LEFT JOIN 表。执行常规 JOIN 意味着您将只检索在此期间在所有平台上购买过东西的那些 id。这样做并清理你得到的格式，更具可读性:

SELECT DISTINCT a.id,
                b.login AndroidLogin,
                c.login iPadLogin,
                d.login iPhoneLogin,
                e.login MobileLogin,
                f.login WebLogin
FROM users a
LEFT JOIN fct_android_20160101_20160331 b       ON a.id = b.userid AND b.date BETWEEN '2016-01-01' AND '2016-01-03'
LEFT JOIN fct_ipad_20160101_20160331 c          ON a.id = c.userid AND c.date BETWEEN '2016-01-01' AND '2016-01-03'
LEFT JOIN fct_iphone_20160101_20160331 d        ON a.id = d.userid AND d.date BETWEEN '2016-01-01' AND '2016-01-03'
LEFT JOIN fct_mobilepolaris_20160101_20160331 e ON a.id = e.userid AND e.date BETWEEN '2016-01-01' AND '2016-01-03'
LEFT JOIN fct_polaris_20160101_20160331 f       ON a.id = f.userid AND f.date BETWEEN '2016-01-01' AND '2016-01-03'
WHERE length(a.id) = 10 
  AND left(a.id,1) IN ('0','1','2','3','4','5','6','7','8','9')
ORDER BY 1;