SQL多表查询思维总结2018-12-18

一、需求背景
需求方给定了ID清单，要求获取ID清单上的其它指标数据。

需求样表

二、需求分析★
1、相关指标存在于三张表内，因此涉及联表查询。
2、需求方给定了ID清单，因此ID清单是基准表。
3、需求需要获取2个月的汇总数据，因此涉及聚合函数。

表字段分布
三、需求处理思想★★★
1、为了保证数据的准确性，因此ID清单是基准表，所有其他表均以user_id字段为基准，特别是在关联表时，均采用left join其它表。
2、为提高数据的计算速度，考虑到底层数据是以时间作为分区的，因此，联结的表均先进行子查询（按时间分区提取出来）再进行联结查询。

四、过程总结★★
1、一开始将聚合计算涉及的表当做了主表，过程中使用right join与inner join去联结其它表，导致逻辑关系比较复杂，容易出错。
2、一开始，直接联结其它表，导致系统计算迟迟无法完成，甚至报错。后期将直接联结表改为联结标的子查询，子查询内先行将所需分区的数据提取出来，此时不需要处理整张表，计算速度明显加快。
3、本次查询分别在Hive与Spark环境下处理，发现Spark速度明显优于Hive。

---

SELECT
a.user_id
,b.local1_name AS `城市`
,c.user_name AS `用户姓名`
,c.account_name AS `用户名`

--is_pm = 1推广的消息
,sum(case when a.cal_dt BETWEEN '2018-06-01' AND '2018-06-30' then a.vppv end) as `6月VPPV总`
,count(case when a.cal_dt BETWEEN '2018-06-01' AND '2018-06-30' and a.is_pm = 1 then info_id end) AS `6月推广消息量`

,sum(case when a.cal_dt BETWEEN '2018-07-01' AND '2018-07-31' then a.vppv end) as `7月VPPV总`
,count(case when a.cal_dt BETWEEN '2018-07-01' AND '2018-07-31' and a.is_pm = 1 then info_id end) AS `7月推广消息量`

FROM user_id_table d

    left join (select * from user_news_table where cal_dt BETWEEN '2018-06-01' AND '2018-07-31'  )a
      on a.user_id = d.user_id

    left join (select * from local_table where cal_dt BETWEEN '2018-06-01' AND '2018-07-31' )b
    on a.disp_area1=b.local1 AND a.cal_dt=b.cal_dt

    left  join (select * from customer_detail_daily where cal_dt BETWEEN '2018-06-01' AND '2018-07-31' )c
    on a.user_id = c.user_id AND a.cal_dt=c.cal_dt

where a.cal_dt BETWEEN '2018-06-01' AND '2018-07-31' 
group by a.user_id,b.local1_name,c.user_name,c.account_name