sql - 使用两个 where 子句连接两个 Athena 表
问题描述
我有两个带有以下查询的 Athena 表:
select
date,
uid,
logged_hrs,
extract(hour from start_time) as hour
from schema.table1
where building = 'MKE'
and pt_date between date '2019-01-01' and date '2019-01-09'
和
select
associate_uid as uid,
date(substr(fcdate_utc, 1, 10)) as pt_date,
learning_curve_level
from tenure.learningcurve
where warehouse_id = 'MKE'
and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'
我想加入他们的uid
和pt_date
。我怎样才能做到这一点?
我试过了:
select (select
date,
uid,
logged_hrs,
extract(hour from start_time) as hour
from schema.table1
where building = 'MKE'
and pt_date between date '2019-01-01' and date '2019-01-09') as a
left join (select
associate_uid as uid,
date(substr(fcdate_utc, 1, 10)) as pt_date,
learning_curve_level
from tenure.learningcurve
where warehouse_id = 'MKE'
and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'
) as b
on a.uid=b.uid and a.pt_date = b.pt_date
但上述导致错误mismatched input 'left' expecting {<eof>, ',', 'from', 'where', 'group', 'order', 'having', 'limit', 'union', 'except', 'intersect'}
解决方案
任何 sql 中的连接语法如下
Select <column list>
from Table_1
left/right/inner Join Table_2
ON <join condition>
table_1 和 table_2 可以是表或其他选择语句
您缺少 select * from,试试这个,我无法检查其他语法错误,但这是一般的想法
select a.*, b.*
from (select date,
uid,
logged_hrs,
extract(hour from start_time) as hour
from schema.table1
where building = 'MKE'
and pt_date between date '2019-01-01' and date '2019-01-09'
) as a
left join
(select associate_uid as uid,
date(substr(fcdate_utc, 1, 10)) as pt_date,
learning_curve_level
from tenure.learningcurve
where warehouse_id = 'MKE'
and date(substr(fcdate_utc, 1, 10)) between date '2019-01-01' and date '2019-01-09'
) as b
on a.uid=b.uid and a.pt_date = b.pt_date
推荐阅读
- django - 编辑页面上的 Django 管理过滤器
- r - 在不同列中使用 R 将数据表中的零值替换为小随机数
- testng - 框架 - 使用 GEB 的 TestNg
- swift - 在 SwiftUI (Xcode 12) 中折叠侧边栏
- typescript - 创建一个包含类的所有静态属性的映射类型
- python-3.x - Pandas Dataframe 用 0.0 初始化并完成每个组
- java - 如果 autoStartup 设置为 false,如何手动启动 Spring Cloud Stream Binders
- python - 非数字字符的fail2ban模式与数字输入匹配
- python - Django Auth 用户和自定义用户
- c# - 无需打开即可将 XFA 文件打印为 PDF