SQL基础入门【4】函数

教程参考：
菜鸟教程
datawhale-wonderfulsql 3.3 各种各样的函数

数学函数

• ABS – 绝对值
  语法：ABS( 数值 )
  ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
  当 ABS 函数的参数为NULL时，返回值也是NULL。

• MOD – 求
  语法：MOD( 被除数，除数 )
  MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。
  注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

• ROUND – 四舍五入
  语法：ROUND( 对象数值，保留小数的位数 )
  ROUND 函数用来进行四舍五入操作。
  注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

字符串函数

• UCASE( )
  把字段的值转换为大写

• LCASE( )
  把字段的值转换为小写

• RIGHT(char_expr,int_expr)
  返回从字符表达式最右端根据指定的字符个数的到的字符。

• SUBSTRING(char_expr,start,length)
  返回从字符表达式的指定位置开始，截取指定长度得到的字符集。

• DIFFERENCE(char_expr1,char_expr2) 根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。

• PATINDEX(’%pattern%’,expr) 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。

别的函数

• group_concat(X, Y) 聚合函数，X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。

窗口函数

专用窗口函数，rank()

1. rank函数

RANK ( ) OVER 
( [ PARTITION BY <partition_column> ] ORDER BY <order_by_column> )

• partition by 指定一个列或多个列作为排名的分区依据
• order by 指定一个列或者多个列对每个区的输出数据进行排序

有两人有相同分数，相同排名N，下一人排名跳过重复人数2，排名N+2

2. dense_rank()
   相同的分数有相同的排名N，下一人排名N+1

3. row_number()
   依次递增，相同分数不同排名

4. ntile(N)
   将分数均分为N个等级，依据排名划分等级
   如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内。

分数	rank	dence_rank	row_number	ntile(4)
50	1	1	1	1
60	2	2	2	1
60	2	2	3	1
70	4	3	4	2
80	5	4	5	2
90	6	5	6	3
90	6	5	7	3
90	6	5	8	4
100	9	6	9	4

聚合函数作为窗口函数

这里基于牛客网sql60题的数据进行展示。

mysql> select emp_no, salary,
    -> sum(salary) over (order by emp_no) as current_sum,
    -> avg(salary) over (order by emp_no) as current_avg,
    -> count(salary) over (order by emp_no) as current_count,
    -> max(salary) over (order by emp_no) as current_max,
    -> min(salary) over (order by emp_no) as current_min
    -> from salaries where to_date='9999-01-01';
    
+--------+--------+-------------+-------------+---------------+-------------+-------------+
| emp_no | salary | current_sum | current_avg | current_count | current_max | current_min |
+--------+--------+-------------+-------------+---------------+-------------+-------------+
|  10001 |  88958 |       88958 |  88958.0000 |             1 |       88958 |       88958 |
|  10002 |  72527 |      161485 |  80742.5000 |             2 |       88958 |       72527 |
|  10003 |  43311 |      204796 |  68265.3333 |             3 |       88958 |       43311 |
+--------+--------+-------------+-------------+---------------+-------------+-------------+
3 rows in set (0.00 sec)

聚合函数在窗口函数中，只会对自身记录及位于自身记录以前的数据进行操作。
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

滑动窗口函数

有时我们需要统计前几年，或者某些滑动数据，可以将函数使用的更加复杂

SELECT col OVER (PARTITION BY col2 ORDER BY col3 ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING)
# perceding表示根据order之前的数据，following表示之后的数据

题：统计某视频7天内的总点赞量及最大单日转发量

SELECT tag, day, 
SUM(like_day) OVER (PARTITION BY tag ORDER BY day rows 6 preceding) AS sum_like_cnt_7d



WITH t AS
(SELECT tag, DATE(end_time) as day, SUM(if_like) as like_day ,SUM(if_retweet) as retweet_day
FROM tb_user_video_log vl
JOIN tb_video_info vi
ON vl.video_id = vi.video_id
GROUP BY tag,DATE(end_time))
SELECT tag, day, sum_like_cnt_7d,max_retweet_cnt_7d FROM(
SELECT tag, day, 
SUM(like_day) OVER (PARTITION BY tag ORDER BY day rows 6 preceding) AS sum_like_cnt_7d,
MAX(retweet_day) OVER (PARTITION BY tag ORDER BY day rows 6 preceding) AS max_retweet_cnt_7d
FROM t) t2
WHERE day BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY 1 DESC,2;