SQL日期函数
| 函数 | 功能 |
|---|---|
| now() | 返回当前的日期和时间 |
| curdate() | 返回当前的日期 |
| curtime() | 返回当前的时间 |
| date() | 提取日期，日期/时间表达式的日期部分 |
| extract() | 返回日期/时间的单独部分 |
| date_add() | 给日期添加指定的时间间隔 |
| date_sub() | 从日期减去指定的时间间隔 |
| datediff() | 返回两个日期之间的天数 |
| date_format() | 用不同的格式显示日期/时间 |
| day() | 取时间字段的天值 |
| month() | 取时间字段的月值 |
| year() | 取时间字段的年值 |
分析函数
聚合函数
SUM -- 该函数计算组中表达式的累积和
COUNT -- 对一组内发生的事情进行累积计数
MIN -- 在一个组中的数据窗口中查找表达式的最小值
MAX -- 在一个组中的数据窗口中查找表达式的最大值
AVG -- 用于计算一个组和数据窗口内表达式的平均值
排名函数
ROW_NUMBER -- 正常排序[1,2,3,4] -- 必须有order_by
RANK -- 跳跃排序[1,2,2,4] -- 必须有order_by
DENSE_RANK -- 密集排序[1,2,2,3] -- 必须有order_by
FIRST -- 从DENSE_RANK返回的集合中取出排在最前面的一个值的行
LAST -- 从DENSE_RANK返回的集合中取出排在最后面的一个值的行
FIRST_VALUE -- 返回组中数据窗口的第一个值
LAST_VALUE -- 返回组中数据窗口的最后一个值。
数学分析函数
STDDEV -- 计算当前行关于组的标准偏离
STDDEV_POP -- 该函数计算总体标准偏离，并返回总体变量的平方根
STDDEV_SAMP -- 该函数计算累积样本标准偏离，并返回总体变量的平方根
VAR_POP -- 该函数返回非空集合的总体变量（忽略null）
VAR_SAMP -- 该函数返回非空集合的样本变量（忽略null）
VARIANCE -- 如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP
COVAR_POP -- 返回一对表达式的总体协方差
COVAR_SAMP -- 返回一对表达式的样本协方差
CORR -- 返回一对表达式的相关系数
CUME_DIST -- 计算一行在组中的相对位置
NTILE -- 将一个组分为"表达式"的散列表示（类于Hive的分桶原理）
PERCENT_RANK -- 和CUME_DIST（累积分配）函数类似
PERCENTILE_DISC -- 返回一个与输入的分布百分比值相对应的数据值
PERCENTILE_CONT -- 返回一个与输入的分布百分比值相对应的数据值
RATIO_TO_REPORT -- 该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比
REGR_ (Linear Regression) Functions -- 这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用
行比较分析函数
– 一次查询中取出同一字段的前N行的数据和后N行的值。
– 语法：lag(exp_str,offset,defval) over()
-- 可以访问结果集中的其它行而不用进行自连接
LAG -- LAG可以访问组中当前行之前的行
LEAD -- LEAD与LAG相反，LEAD可以访问组中当前行之后的行
exp_str是要做对比的字段；offset是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
开窗函数
目的
我们都知道在
sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,
这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚
集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,
这时我们便引入了窗口函数。
开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。
应用
- 用于分区排序
- 动态
GROUP BY TOP N- 累计计算
- 层次查询
格式
-
分区（
partition by） -
排序（
order by） -
范围（
rows between或range between）
over(partition by col1 order by col2 rows between col3)
开窗函数=分析函数+Over();
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)
SQL执行顺序
当一条查询语句中包含所有的子句，执行顺序依下列子句次序：
FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。ORDER BY子句：执行顺序为从左到右排序，消耗资源。
代码书写使用的顺序依次
select --> from --> where --> group by --> having --> order by
引用
https://hiszm.cn/
https://bryanadamss.github.io/2019/10/16/sql-basic/
https://stonehell.github.io/2020/09/14/Hive%E5%BC%80%E7%AA%97%E5%87%BD%E6%95%B0/
https://lanjingling.github.io/2015/10/09/oracle-fenxihanshu-2/
