1. 关于count(*)
资料链接：
mysql中：
hive中：
总结：
1.无论是mysql还是hive，用count(*)和count(1)都没有任何区别，mysql中没有索引读块头，5.7之后读主键索引，hive中读行偏移量
2.count(列)需要拿出列的所有值，取非null的，所以需要扫描列，速度慢，但和count(*)需求不同所以没有可比性
3.在mysql中如果给列加上允许空值的索引则count(列)也会很快，和count(*)基本没有区别
3.在hive中因为是列式存储所以count(列)，列放在前后都无所谓，但mysql中将需要查个数的列尽量放在前面，因为不是列存需要向后找
2.关于select*
资料链接：
Mysql中：
总结：
1.mysql中没索引情况下，select*和所有列没有太大区别，但有索引的情况下，select所有列更快，因为select*不仅要取索引值还要取data
2.hive目前还没有找到相关资料说明，不是太清楚，但除平时练习使用，其他情况都不推荐使用 SELECT * FROM XXX ，也是为了方便后期维护。
3.parquet文件格式
总结：
1.列式存储方便计算，但不方便按行取字段
2.相同格式的数据存储在一起，列式存储方便压缩
3.例如可以记录列中数据重复几次，映射字符串，记录时间戳差值来节省空间
4.巧用max(case when)函数进行行转列
资料链接：
总结：
1.虽然concat_ws结合collect_set或直接使用group_concat()也能够进行行转列，但这种行转列只能将多行转为1列而不是多列
2.使用max行转列就是先多行分组，然后再用多个聚合函数max将组内的行进行判断
原理：
如果匹配上则拿出来，匹配不上则给null或者0，最后max一定是取出匹配上的那个
问题：
1.为什么非要用聚合函数，而不能直接判断？直接判断则会取组内第一个元素，也就是所有的判断中只会有一条满足
2.为什么非要用max聚合，min不可以吗？我们匹配不上的其他行都给null，而min是会取null的并不合适，而avg会过滤Null其实也可以但没有必要
举例：
班级 姓名 学科 分数->班级 姓名 语文 数学 英语 理综 文综 总成绩
另外加一行全班平均分并排名
--建表
CREATE TABLE tmp.test001(
`class` STRING,
`name` STRING,
`course` STRING,
`score` INT
)
LOCATION 'hdfs://HDFSNS/Data/d1/hive/warehouse/tmp.db/test001';
--插入数据
INSERT INTO tmp.test001(`class`,name,course,score) VALUES
("一班","张三","语文",56),
("一班","张三","数学",35),
("一班","张三","英语",87),
("一班","张三","物理",36),
("一班","张三","化学",78),
("一班","张三","生物",52),
("一班","李四","语文",72),
("一班","李四","数学",91),
("一班","李四","英语",59),
("一班","李四","物理",36),
("一班","李四","化学",78),
("一班","李四","生物",52);
--查询sql
select
*
from
(
select
`class`,
name,
`语文`,
rank() over(partition by `class` order by `语文` desc) rk1,
`数学`,
rank() over(partition by `class` order by `数学` desc) rk2,
`英语`,
rank() over(partition by `class` order by `英语` desc) rk3,
`理综`,
rank() over(partition by `class` order by `理综` desc) rk4,
`总成绩`,
rank() over(partition by `class` order by `总成绩` desc) rk
from
(
select
`class`,
name,
max(if(course="语文",score,0)) as `语文`,
max(if(course="数学",score,0)) as `数学`,
max(if(course="英语",score,0)) as `英语`,
sum(if(course in ("物理","化学","生物"),score,0)) as `理综`,
sum(score) as `总成绩`
from tmp.test001
group by `class`, name
)t
union all
select
`class`,
'全班平均分',
avg(if(course="语文",score,null)) as `语文`,
0,
avg(if(course="数学",score,null)) as `数学`,
0,
avg(if(course="英语",score,null)) as `英语`,
0,
sum(if(course in ("物理","化学","生物"),score,0))/count(distinct name) as `理综`,
0,
sum(score)/count(distinct name) as `总成绩`,
0
from tmp.test001
group by `class`
)tt
order by rk;
--注意：其他成绩可以用avg方法用null过滤掉空行，但理综和总成绩不可以，是几行加在一起，但分母是人数，比行数少
5.求年龄精确到月
#!/bin/bash
echo "
select
concat(
if(substr('$1',6,5) >= substr('$2',6,5),
cast((cast(substr('$1',1,4) as int) - cast(substr('$2',1,4) as int)) as string),
cast((cast(substr('$1',1,4) as int) - cast(substr('$2',1,4) as int) -1) as string)), '岁',
if(substr('$1',9,2) >= substr('$2',9,2),
cast((cast(substr('$1',6,2) as int) + 12 - cast(substr('$2',6,2) as int)) % 12 as string),
cast((cast(substr('$1',6,2) as int) + 12 - cast(substr('$2',6,2) as int) -1) % 12 as string)), '月'
)
"说明：
- 这是一段sql，但是我为了测试方便，使用脚本传值来生成sql，以此来快速赋值测试sql逻辑
- 为什么取月份要先加12再模12，因为年份是没有进制的，只会越来越大，但月份是有的，12为一个循环，例如今年的2月肯定是比去年的4月大10个月的，而不是-2
6.sql中为什么不建议使用full join
hive中尽量不用full join的原因是它在底层会先Left join，然后再right join，最后再Union去重，效率很差。
7.宽表构建列为什么不建议join
对于新手来讲，可能觉得，构建宽表时把需要的列直接join起来不就好了吗，多省事，但生产环境中往往不这样使用，为什么？
- join时容易产生笛卡尔积
- join的效率很差，而且容易造成数据倾斜
那应该采用什么方法？
一般能使用join的地方就可以使用union all，我们只需要把每段sql中的其他列给0，最后每段sql使用union all，最后再求sum即可
8.关于grouping sets
grouping sets可以完成多段sql按不同条件分组再Union的情况，写起来更方便，而且只需要读一次原表即可，节省了IO
具体用法参考
9.关于timestamp
timestamp到底是什么？是时间戳吗，是Linux里的ts那种时间戳吗？并不是，是一种时间格式
Mysql的timestamp类型在确定范围时不可以直接写>某个日期，因为他是时间格式，不是hive的时间字符串，会自动补全时间00:00:00
所以会出现hive可以取出这个时间的数据，但mysql取不到，所以应该>=
10.关于spark引擎的谓词下推
首先需要明白何为谓词下推？即在join操作时不需要将from内的表提前where过滤作为子表来节省读取的数据量，而是直接在where条件里过滤，程序在读文件的时候就可以直接过滤出我们想要的那部分，而不是先拿出全表数据，再过滤出想要的数据
1.当join时where中条件为and且筛选不同表的时候，spark可以做谓词下推，提前过滤再做join
2.当join时where中条件为or且筛选不同表的时候，spark不会做谓词下推，因为如果下推，可能会提前过滤走虽然不满足左表条件，但链接后的行满足右表条件的行，这样是有问题的
3.当join时where中条件为or或为and但筛选同一个表的时候，spark都会做谓词下推，因为和另一个没关系，结果集不受影响
4.当join时where中条件为or且筛选不同表但该条件也是链接条件的时候，spark会做谓词下推，因为链接条件必定要满足都满足，要不满足都不满足，此时的or其实和and没有区别
5.当join时where中的或条件里为分区条件时，spark会做谓词下推，因为会将不符合查询分区条件的目录直接排除在待扫描的目录之外，来节省链接开销，此时将不再考虑上述2中的特殊情况
参考链接：
11.关于union all数量过多
当union all数量过多时，程序会直接在分配容器时立马报错结束，而不是在运行到某个exctor的stage结束，也就是说两次失败执行的时间都很短，这就说明是driver端出现了问题
问题原因：在Spark程序中，SparkContext，DAGScheduler都是运行在Driver端的。对应rdd的Stage切分也是在Driver端运行，如果用户自己写的程序有过多的步骤，切分出过多的Stage，这部分信息消耗的是Driver的内存，这个时候就需要调大Driver的内存
解决方式：将driver端内存从3g增加到5g，excutor不用动，之后就可以正常运行了
12.关于group by和partition by联用
原数据：
张三 河南省 郑州市
李四 河南省 郑州市
王五 河南省 焦作市
赵六 湖北省 武汉市
首先试想一个问题：
如果对于一个包含省份和城市的数据，我先对城市进行group by，再对省份进行partition by，结果会怎样？
- 求出每个省份的人数；2.求出每个城市的人数；3.求出每个人所在的省份有几口人；4.求出每个人所在的城市有几口人
select
city，
count(1) over(partition by province) as cnt
from t
group by province，city
--虽然按城市分组和省份城市一起分组结果都是一样，都是按最细粒度城市来分，
--但还是要这么写，因为开窗函数的partition里要用到省份，开窗函数不是聚合函数其实上面的答案全部错误，为什么？
group by分组也是一种去重，他会一组数据中的城市先distinct，保证每个城市只有一份，接着，他会按照partion by进行分区，按省份分区，也就是当前城市所在的省份有多少个城市，他就统计几个，所以最后统计出来的是每个市区所在省份的城市数量
目标数据：
郑州市 2
焦作市 2
武汉市 1
13.关于group by里有些字段为什么不可以省略
之前写过一段这样的sql，在当时就很不理解：
1.为什么size为最外层函数，为什么size的右括号不括在patientid外面而是括在最后？
2.什么patientid要在group by中出现？collect_set明明是聚合函数啊，聚合函数里面的字段不是随便用吗？
原因：
- 因为开窗函数是over 连带前面那个函数，是collect开窗，不是size开窗，size只是对开窗的结果做处理，当然这里的size想进行开窗也不是不可以，外层继续跟over就好了，但是没必要
- 只要开窗了，他就已经不是聚合函数了，只不过这个开窗函数有分区内聚合的功能而已，这个函数为collect加over里的所有，所以开窗函数里的字段必须要在group by中出现，这是原因1，而且他不这么干也不行啊，他拿什么开窗，拿group by分组后的结果，其实也就是去重后的结果，去重后的结果没有该字段，那他下一步又要用到，是没法操作的，所以必须将其放入group by中
