聚合函数、窗口函数、条件语句_聚合函数和条件函数的组合

一、聚合字段

聚合函数出现在select之后，对记录按照分组字段进行汇总

sum/avg/stddev/variance/max/min(字段)——计算分组后组内的所有记录的 总和/平均值/标准差/方差/最大值/最小值

count(字段)——计算分组后组内记录的数量

percentile(字段,p)——计算分组后组内记录的p分位数，p的取值为0-1

对表中所有记录计算聚合函数时，可以不使用group by 语句，但写在Select后面时不能出现非聚合字段

二、DISTINCT--去重

用法1：放在select之后，对select后的所有字段进行去重--只去重所有字段值都相同的记录

用法2：distinct在聚合函数中使用，实现分组后的去重再进行聚合计算

三、CASE WHEN---条件语句

问题：根据城市名生成新的字段province

方式1：等值判断

case city when '青岛' then '山东'
          when '济南' then '山东'
          when '南京' then '江苏'
          else '其他'
end as province

方法2：区间判断

case when city in ('青岛','济南') then '山东'
     when city = '南京' then '江苏'
     else '其他'
end as province

1、case when写在分组语句group by之后--提供新的分组字段--但不能使用字段别名

2、case when写在选择语句select之后--基于现有字段生成新的字段

3、case when用在聚合函数中----聚合函数+distinct+case when常用

四、窗口函数

窗口函数只出现在select语句后，不能出现在group by和聚合函数中；使用窗口函数后不能使用group by语句

窗口函数的基本结构

函数名() over (partition by 字段1,字段2 order by 字段2 asc/desc)

常见窗口函数：

1、row_number() over() ----记录同一分组内的排序行号，与rank、dense_rank区别

2、percent_rank() over() ---记录同一分组内的排序分位数

3、sum/avg/max/min(字段1) over()---记录同一分组内所有记录的 字段1 的总和/平均/最大值/最小值，同一分组的记录返回值一样

今日练习所得

1、日期函数：day/month/year(时间字段)---提取时间字段的日期值/月份值/年份值

2、count(字段名)---count对字段记录进行计数-行数-如果非NULL就加1

3、DATEDIFF(开始日期，结束日期)---返回两个日期之间的时间

3、截取字符串函数：substring_index(字符串字段,'间隔形式',number)--number为截取字符串的位置，意思为截取第number个间隔形式前的字符；当number为负数为截取倒数第 正number 的间隔形式后面的字符串---substring_index可以重复嵌套使用

其他字符串截取函数，substr或substring是截取固定长度的字符串

4、from后面接的嵌套语句生成的表，要取别名，不然会报错

5、IF和IFNULL的区别，IF是判断 表达式，IFNULL是判断字段值是否为NULL