开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： 网站流量日志分析--统计分析--新老访客实现（join 语句）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12240
网站流量日志分析--统计分析--新老访客实现（join 语句）
1、每天新访客的一个 sql 编写的过程，如何编写如何计算？打开笔记，在计算新老访客时涉及到两个表之间的 join 关联，确定是哪两个表，关联的字段是谁。两个表进行左关联，以历史进行 ip 判断，关联上的为老仿客，关联不上显示为 null 的为新访客。
--框架
select
from今天left join 历史on今天.ip =历史.ip
左关联以左表为准，如果右边的为 null 就是新访客。
Select
今天.ip
from今天left join 历史on今天.ip =历史.ip
where历史.ip is null;--新访客
Select
今天.ip
from今天left join 历史on今天.ip =历史.ip
where历史.ip is not null;--老访客
is not null 意味着不为空，不为空意味着以关联上，两个框架就可以计算出新老访客。
2、根据业务分析，想要得到今天的访客，必须对今天的访问日志数据进行去重ip，去重完之后，所有的 ip 就是今天的访客记录，一款表为准，宽表中有 remote_addr字段，把所有 ip 进行去重得到今天所有的ip就是所有的访客。
--今天(根据今天收集的数据进行去重统计)
select
distinct t.remote_ addr as ip
把所有ip都拿出来，包括重复的，所以进行去重，起别名
from dw_ weblog_ detail t where t. datestr ="20181101";
复制，打开 hive 终端执行，
得到的 ip 是今天不重复的独立访客数，历史表是根据业务查询出得到还是需要自己提前创建好每天不断的进行填充，今天开业第一天没有任何人，表示空，开业第二天把第一天数据拿过来，所以根据业务的梳理可以发现，历史累计表是自己需要提前创建的，当然中必须要有 ip 字段，这样才能进行判断，因此历史累计表需要自己提前创建。
创建 dw user_ dsct_ history 历史用户去重累计表，有 ip 字段，历史表需要根据需求自己创建。
--历史
drop table dw user_ dsct_ history;
create table dw user_ dsct_ history (
day string , 表示哪一天
ip string ip很重要
partitioned by (datestr string) ;
代入框架，进行填充：
Select
今天.ip
from今天left join 历史on今天.ip =历史.ip
where历史.ip is null;--新访客
今天的表要进行替换，今天的表是 sql 语句查询，进行嵌套查询，起别名 today，历史起别名 history，确定了两个表，确定 join 字段，join 字 段要跟表对应上，把历史换成 history，把今天换成 today，得到的就是今天的新访客。
select
today.ip
from (select
distinct t.remote_ addr as ip
from dw_ weblog_ detail t where t datestr ="20181101") today left join
dw_ user_ dsct_ history history on today.ip = history.ip
where history.ip is null;--新访客
复制历史表打开终端进行执行，今天是上线的第一天，历史表中没有数据，所以所有访客都是新访客，执行报错，对sql进行修正，修改空格，格式问题。
Select today.ip from
(select distinct t.remote_ addr as ip from dw_ weblog_ detail t where t datestr ="20181101") today left join dw_ user_ dsct_ history history on today.ip = history.ip
where history.ip is null;--新访客
执行，可以看到今天的新访客，今天没有老访客的数据。
Select today.ip from
(select distinct t.remote_ addr as ip from dw_ weblog_ detail t where t datestr ="20181101") today left join dw_ user_ dsct_ history history on today.ip = history.ip
where history.ip is not null;--老访客
验证是否有老访客，结果返回为空，说明今天没有一个老访客，从 s ql 层面完成了新老访客的计算。
3、创建每日新访客表
drop table dw_ user_ new_ d;
create tab1e dw_ user_ new d (
day string ,
ip string
partitioned by (datestr string) ;
4、把每日新用户插入新访客表
insert into table dw_ user_ new_ d partition (datestr= '20181101')
select tmp.day as day, tmp.today_ addr as new_ ip from
select today.day as day, today. remote_ addr as today_ addr,old.ip as old_ addr
from
(select distinct remote_ addr as remote_ addr,"20181101" as day from dw_ weblog_ detail where datestr ="20181101") today
left outer join
dw_ user_ dsct_ history old
on today. remote_ addr=old. ip
) tmp
where tmp.old_ addr is null;
里面做了多重嵌套查询，today 和 history 进行关联，起别名是 old，关联完进行判断，就是新访客，进行 insert 插入语句，创建完之后还要进行追加的操作，根据需求要把当天的新访客追加到历史表中，作为老访客作为判断，因为当下的数据只有一天，把所有的新访客课插到历史表中，再用数据计算一遍，新访客就没有了，都是老访客，进行相反的过程，把新访客追加到历史表中进行插入，打开 hive 终端进行执行。
--每日新用户追加到累计表
insert into table dw_ user_ dsct_ history partition (datestr= '20181101')
select day,ip from dw_ user_ new_ d where datestr= '20181101' ;
验证新老访客，以老访客为例，没有新访客，都是老访客，验证复制 sql 执行，颠倒的过程。结果显示没有新访客，因为所有的都变成了老访客，这就是 sql 层面计算新老访客的过程。
验证:
select count (distinct remote_ addr) from dw_ weblog_ detail ;
select count(l) from dw_ user_ dsct_ history where datestr= '20181101' ;
select count(1) from dw_ user_ new_ d where datestr=
'20181101' ;
5、hive 中 join 的总结：
today 就不是真实的表，而是通过查询语句返回的结果作为一个表，表很重要，确定表，确认 join 字段后进行判断，join 返回什么字段，是业务的问题，没有具体的规定，但是很重要。确定表和字段，填充进去，就可以写出 sql，在需求中如果需求涉及到两元操作，就是一个对立的面，好，不好，有，没有，来，没有来，新，老访客，如果需求从两元操作都可以转化成 join sql 语句执行，因为 join 的本质最终也是两种结果，null，not null，把新老访客的新没有关联上做了个关联，新访客 null，老访客 not null，有业务上的扩展，空不空，判断好不好。
（1）join 语句最重要的是确定 join 的表和 join 字段。
（2）因为 join 的表可能真是存在也可能不存在。
（3）如果不存在设法通过嵌套查询生成表结构，实质是一个虚拟的表。
（4）如果涉及到需求中的两元操作(好或者不好来或者不来新或者旧)都可以往 join上进行扩展，因为 join 的结果本身也是两种情况( null, not null )。
