函数

1 单行函数和多行函数

单行函数：单入单出

多行函数：多入单出，最常见的就是聚合函数

应该不存在单入多出，多入多出可以简化为单入单出的多次

https://blog.csdn.net/lailai186/article/details/12570899

注意：

多行，单行结果组合返回一行

例子：select column1 ,count(column2) ，只返回一行

问题来了，多多不一样呢，比如3和2，应该不存在

2 用户自定义函数

https://blog.csdn.net/qq_23833037/article/details/53170789

3 聚合函数

顾名思义，将数据聚集返回单一的值

https://blog.csdn.net/qq_40456829/article/details/83657396

4 开窗函数（Window Function）

https://segmentfault.com/a/1190000040088969

https://www.51cto.com/article/639541.html

https://blog.csdn.net/weixin_43412569/article/details/107992998

作用

行数保持不变

输入多行（一个窗口）、返回一个值

计算过程

当前行-》分区-》排序-》范围-》计算-》结果填入当前行

语法

window_function ([expression]) OVER (
   [ PARTITION BY part_list ]
   [ ORDER BY order_list ]
   [ { ROWS | RANGE } BETWEEN frame_start AND frame_end ] )

• expression

• PARTITION BY

  表示将数据按 part_list 进行分区， 不加partition by 默认用全部（一个分区）

  partition by columns1,columns2

• ORDER BY

  表示将各个分区内的数据按 order_list进行排序

• ROWS / RANGE 决定数据范围

  https://blog.csdn.net/qq_42374697/article/details/115109386

分类

https://www.cnblogs.com/52xf/p/4209211.html

可以分为以下 3 类：

• 聚合（Aggregate）：AVG(), COUNT(), MIN(), MAX(), SUM()…

sum(frequency) over(partiton by   num )  --分组累加
sum(frequency) over()         			--total_frequency  全部累加
sum(frequency) over(order by num desc)  --desc_frequency, 逆序累加
sum(frequency) over(order by num asc)   --asc_frequency,正序累加
SUM(Salary) OVER (PARTITION BY Id ORDER BY Month asc range 2 PRECEDING) --range 2 PRECEDING 当前以及前面2行

avg(frequency) over()  					--total_frequency ，全部平均
avg(frequency) over(order by num desc)  --desc_frequency, 逆序平均
avg(frequency) over(order by num asc)   --asc_frequency,  正序平均

​ https://blog.csdn.net/qq_54494937/article/details/119537881

​ https://leetcode-cn.com/problems/find-median-given-frequency-of-numbers/

​ https://blog.csdn.net/qq_54494937/article/details/119537881

• 取值（Value）：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()…

• 排序（Ranking）：RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()…

  rank() OVER (PARTITION BY Id ORDER BY Month DESC) 

  https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee/

5 数学运算函数

https://blog.csdn.net/a_lllll/article/details/87880389

abs

select  distinct C1.seat_id as seat_id
from Cinema C1 join Cinema C2
on C1.free=1 and C2.free=1 and abs(C1.seat_id-C2.seat_id)=1
order by seat_id

power

# Write your MySQL query statement below
select  round(min(Power(Power(P1.x-P2.x,2)+Power(P1.y-P2.y,2),0.5)),2) as shortest 
from Point2D   P1 join Point2D  P2 
on P1.x!=P2.x or P1.y!=P2.y