数仓实习遇到的问题

sql

用法

with用法

https://blog.csdn.net/qq_36617521/article/details/55256382

https://blog.csdn.net/weixin_41600552/article/details/82958942

convert()

https://www.runoob.com/sql/func-convert.html

cube和rollup，grouping

https://www.cnblogs.com/zzhangyuhang/p/9986506.html

https://blog.csdn.net/qq_41805514/article/details/81777946

例子

--t4,t5多出一列作为总计client
t4 as --下一周活跃
(
    select t.weeknum_of_year
           ,decode(grouping(t.platform),1,'client',t.platform) platform --grouping，该函数在其参数列的值为超级分组记录所产生的null时返回1，其他情况返回0.
		                                                                --client 相当于android和ios的总计，group by 产生了null，对应的sum相当于所有platform的总计
           ,count(distinct case when t.is_active >= 1 and t2.is_active >= 1 then t.qimei end ) as cnt_active_w1
      from 
      (
          select *
            from t3 
      ) t 
      join 
      (
          select *
            from t3 
      ) t2 on t.platform = t2.platform and t.qimei = t2.qimei 
      where t2.weeknum_of_year = t.weeknum_of_year+1
      group by t2.weeknum_of_year
               ,cube(t.platform)  --group by() ,group by(t.platform),r如果是cube(a,b),select a,b;那么，在进行group a的时候，因为b为null，这是对比进行聚合函数的操作，可以得到关于a值的相关信息，进行group b的时候，a为null，同理。
			                      --而进行group a，b的时候，就得到了a，b组合起来的详细信息。
								  --group by() ,group by(t.platform)
)
,
t5 as --这一周活跃
(
    select weeknum_of_year
           ,decode(grouping(platform),1,'client',platform) platform
           ,count(distinct case when is_active >= 1 then qimei end ) as cnt_active
      from t3
    group by weeknum_of_year
            ,cube(platform)
)

t4的结果

 

Lateral view

https://blog.csdn.net/clerk0324/article/details/58600284

decode()

https://blog.csdn.net/hyfstyle/article/details/89851895

crontab()

https://www.cnblogs.com/xiao-lei/p/11084578.html

不等于null

is not null

查看数据表一条数据

select * from t limit 1\G;

left join，join

https://blog.csdn.net/minixuezhen/article/details/79763263

full outer join

https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html 

regexp_replace

https://www.cnblogs.com/woshimrf/p/hive-string-regexp-replace-usage.html

报错

Expression not in GROUP BY key

https://blog.csdn.net/u010003835/article/details/88189948

hive执行报错：Both left and right aliases encountered in JOIN 's1'

https://blog.csdn.net/wisdom_c_1010/article/details/78760454