2020-09-08 21:24 Columbia University 产品经理
关注
#携程笔试# 请问各位大佬数据方向最后一道SQL
SELECT userid, cnt_1d, amt_1d, cnt_7d, amt_7d, cnt_15d, amt_15d
FROM
(SELECT userid, COUNT(*) as cnt_1d,SUM(amount) as amt_1d FROM edw_htl_order where orderdate between '2020-07-15' and '2020-07-16' group by userid)a
JOIN
(SELECT userid, COUNT(*) as cnt_7d,SUM(amount) as amt_7d FROM edw_htl_order where orderdate between '2020-07-09' and '2020-07-16' group by userid)b
on a.userid = b.userid
JOIN
(SELECT userid, COUNT(*) as cnt_15d,SUM(amount) as amt_15d FROM edw_htl_order where orderdate between '2020-07-01' and '2020-07-16' group by userid)c
on a.userid = c.userid
这个为啥过不了呀？每次合并SELECT查询都出问题
SELECT userid, cnt_1d, amt_1d, cnt_7d, amt_7d, cnt_15d, amt_15d
FROM
(SELECT userid, COUNT(*) as cnt_1d,SUM(amount) as amt_1d FROM edw_htl_order where orderdate between '2020-07-15' and '2020-07-16' group by userid)a
JOIN
(SELECT userid, COUNT(*) as cnt_7d,SUM(amount) as amt_7d FROM edw_htl_order where orderdate between '2020-07-09' and '2020-07-16' group by userid)b
on a.userid = b.userid
JOIN
(SELECT userid, COUNT(*) as cnt_15d,SUM(amount) as amt_15d FROM edw_htl_order where orderdate between '2020-07-01' and '2020-07-16' group by userid)c
on a.userid = c.userid
这个为啥过不了呀？每次合并SELECT查询都出问题
全部评论
不想泡池子的中国人allin春招
微软_数据科学家
第二个单词应该是a.userid, 否则他不知道是那个表里面的userid。改了后我可以运行成功了
白日梦Xxxxxn
门头沟学院 数据分析师
select userid
,count(case when orderdate >= '2020-07-15' and orderdate <= '2020-07-16' then orderid else null end) cnt_1d
,sum(case when orderdate >= '2020-07-15' and orderdate <= '2020-07-16' then amount else 0 end) amt_1d
,count(case when orderdate >= '2020-07-09' and orderdate <= '2020-07-16' then orderid else null end) cnt_7d
,sum(case when orderdate >= '2020-07-09' and orderdate <= '2020-07-16' then amount else 0 end) amt_7d
,count(case when orderdate >= '2020-07-01' and orderdate <= '2020-07-16' then orderid else null end) cnt_15d
,sum(case when orderdate >= '2020-07-01' and orderdate <= '2020-07-16' then amount else 0 end) amt_15d
from edw_htl_order
group by userid
foreverwinner
山东大学 数据分析师
为啥写的这么复杂呀，可以写6个case when只查询一次滴
相关推荐
科大讯飞公司氛围 437人发布 10-12 19:23
重庆邮电大学 Java 
敢逐云霄志：你打招呼语怎么能这么长，hr都没看下去的欲望，简明扼要说重点，就读于某某学校某某专业，26届应届毕业生，学信网可查，先后在某某公司实习过（如有），然后做过什么项目，想找一份什么样的工作，可实习几个月以上，期待您的回复。 点赞 评论 收藏
分享
点赞 评论 收藏
分享
查看3道真题和解析