函数

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

作用

行数保持不变

输入多行(一个窗口)、返回一个值

计算过程

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

语法

1
2
3
4
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()
1
2
3
4
5
6
7
8
9
10
11
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

5 数学运算函数

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

abs

1
2
3
4
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

1
2
3
4
# 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

:D 一言句子获取中...