记录在日常工作或者学习中中使用到的函数，以下是做一个备忘~
MySQL：
窗口函数：
原文地址：https://zhuanlan.zhihu.com/p/92654574
1、窗口函数使用场景
窗口函数有什么用？
在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：
- 排名问题：每个部门按业绩来排名
- topN问题：找出每个部门排名前N的员工进行奖励
面对这类需求，就需要使用sql的高级功能窗口函数了。
2、窗口函数的语法
窗口函数的基本语法如下：
csharp;gutter:true;
over (partition by order by )</p>
<pre><code>
那么语法中的
* 1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
* 2） 聚合函数，如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作， **所以窗口函数原则上只能写在select子句中**。
> 3、窗口函数：rank
班级表以及测试数据SQL：
</code></pre>
<p>SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;</p>
<hr />
<p>-- Table structure for class</p>
<hr />
<p>DROP TABLE IF EXISTS <code>class</code>;
CREATE TABLE <code>class</code> (
<code>ID</code> int NOT NULL AUTO_INCREMENT,
<code>student_id</code> int(4) UNSIGNED ZEROFILL NOT NULL COMMENT '学号',
<code>class_name</code> varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '班级',
<code>grade</code> int NULL DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (<code>ID</code>) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;</p>
<hr />
<p>-- Records of class</p>
<hr />
<p>INSERT INTO <code>class</code> VALUES (1, 0001, '1', 86);
INSERT INTO <code>class</code> VALUES (2, 0002, '1', 95);
INSERT INTO <code>class</code> VALUES (3, 0003, '2', 89);
INSERT INTO <code>class</code> VALUES (4, 0004, '1', 83);
INSERT INTO <code>class</code> VALUES (5, 0005, '2', 86);
INSERT INTO <code>class</code> VALUES (6, 0006, '3', 92);
INSERT INTO <code>class</code> VALUES (7, 0007, '3', 86);
INSERT INTO <code>class</code> VALUES (8, 0008, '1', 88);</p>
<p>SET FOREIGN_KEY_CHECKS = 1;</p>
<pre><code>
View Code
例如下图，是班级表中的内容:
如果我们想在每个班级内按成绩排名，得到下面的结果。(按照班级进行分组，按照每个班级的成绩进行降序排列)
以班级"1"为例，这个班级的成绩"95"排在第1位，这个班级的"83"排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。
得到上面结果的sql语句代码如下：
;gutter:true;
SELECT
*,
rank() over ( PARTITION BY class_name ORDER BY grade DESC ) AS ranking
FROM
class;
窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？
这是因为， group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。
4、其他专业窗口函数：rank、dense_rank、row_number
专用窗口函数 rank, dense_rank, row_number有什么区别呢？
它们的区别我举个例子，你们一下就能看懂：
csharp;gutter:true;
select *,
rank() over (order by grade desc) as ranking,
dense_rank() over (order by grade desc) as dese_rank,
row_number() over (order by grade desc) as row_num
from class</p>
<pre><code>
1. ranking：并列名次的行，会占用下一名次的位置
2. dense_rank：如果有并列名次的行，不占用下一名次的位置，并列第五
3. row_number：不考虑并列名次的情况
> 5、聚合函数作为窗口函数
聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。
我们来看一下窗口函数是聚合函数时，会出来什么结果：
;gutter:true;
select *,
sum(grade) over (order by student_id) as current_sum,
avg(grade) over (order by student_id) as current_avg,
count(grade) over (order by student_id) as current_count,
max(grade) over (order by student_id) as current_max,
min(grade) over (order by student_id) as current_min
from class;
如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。
比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。
比如avg函数，学号为003的学生，是(001+002+003)/3 = 90
不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。
如果想要知道所有人成绩的总和、平均等聚合结果，看最后一行即可。
6、这样使用窗口函数有什么用呢？
聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。
同时可以看出每一行数据，对整体统计数据的影响。
查询日期时间：
current_date：返回日期
示例：
sql;gutter:true;collapse:false
select current_date ;</p>
<pre><code>
> now()：返回时间
;gutter:true;collapse:false
select now();
获取时间函数：
sql;gutter:true;collapse:false
select current_date ;</p>
<p>SELECT CURRENT_TIMESTAMP ;</p>
<p>SELECT CURRENT_TIMESTAMP ();</p>
<p>SELECT NOW();</p>
<p>SELECT LOCALTIME;</p>
<p>SELECT LOCALTIME();</p>
<p>SELECT LOCALTIMESTAMP;</p>
<p>SELECT LOCALTIMESTAMP();</p>
<pre><code>
> 操作时间/日期：
;gutter:true;collapse:false
— 加一天:
select NOW() + INTERVAL 1 DAY ;
— 减一天:
select NOW() – INTERVAL 1 DAY ;
— 加 30 分钟:
select NOW() + INTERVAL 30 MINUTE ;
— 减 30 分钟:
select NOW() – INTERVAL 30 MINUTE ;
DATE_FORMAT() ：用于以不同的格式显示日期/时间数据
语法：
sql;gutter:true;
DATE_FORMAT(date,format)</p>
<pre><code>
date 参数是合法的日期。
format 规定日期/时间的输出格式。
> 示例：
;gutter:true;
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%s’); — 结果：2020-12-07 22:18:58
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i’); — 结果：2020-12-07 22:18
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H’); — 结果：2020-12-07 22
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’); — 结果：2020-12-07
SELECT DATE_FORMAT(NOW(),’%H:%i:%s’); — 结果：22:18:58
SELECT DATE_FORMAT(NOW(),’%H’); — 结果：22
统计每天的访问次数
csharp;gutter:true;
SELECT
DATE_FORMAT( login_time, '%Y-%m-%d' ) AS date,
COUNT(*) AS num_users
FROM
t_user_log
GROUP BY
DATE_FORMAT( login_time, '%Y-%m-%d' )
ORDER BY
date DESC;</p>
<pre><code>
### timestampdiff：计算两个日期时间的差值
> 语法
timestampdiff(unit,begin,end)
TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。
unit参数是确定(end-begin)的结果的单位，表示为整数。 以下是有效单位：
* MICROSECOND
* SECOND
* MINUTE
* HOUR
* DAY
* WEEK
* MONTH
* YEAR
> 使用
;gutter:true;
mysql> SELECT TIMESTAMPDIFF(MONTH, ‘2018-01-01’, ‘2018-06-01’) as result;
+——–+
| result |
+——–+
| 5 |
+——–+
1 row in set (0.00 sec)
mysql> SELECT TIMESTAMPDIFF(DAY, ‘2010-01-01’, ‘2010-06-01’) as result;
+——–+
| result |
+——–+
| 151 |
+——–+
1 row in set (0.00 sec)
mysql> SELECT TIMESTAMPDIFF(MINUTE, ‘2018-01-01 10:00:00’, ‘2018-01-01 10:45:00’) as result;
+——–+
| result |
+——–+
| 45 |
+——–+
1 row in set (0.00 sec)
mysql> SELECT TIMESTAMPDIFF(MINUTE, ‘2018-01-01 10:00:00’, ‘2018-01-01 10:45:59’) as result;
+——–+
| result |
+——–+
| 45 |
+——–+
1 row in set (0.00 sec)
mysql> SELECT TIMESTAMPDIFF(SECOND, ‘2018-01-01 10:00:00’, ‘2018-01-01 10:45:59’) as result;
+——–+
| result |
+——–+
| 2759 |
+——–+
1 row in set (0.00 sec)
字符串拼接操作：concat、concat_ws、group_concat
这个博主总结的很好，仅供参考：MySQL拼接函数CONCAT的使用心得
CAST：格式转换
参考链接：https://blog.csdn.net/Hudas/article/details/124399908
CAST函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
语法：
sql;gutter:true;collapse:false
CAST (value AS datatype)</p>
<pre><code>
参数说明：
value: 要转换的值
datatype: 要转换成的数据类型
datatype参数取值
值 描述 DATE 将value转换成'YYYY-MM-DD'格式 DATETIME 将value转换成'YYYY-MM-DD HH：MM：SS'格式 TIME 将value转换成'HH：MM：SS'格式 CHAR 将value转换成CHAR(固定长度的字符串)格式 SIGNED 将value转换成INT(有符号的整数)格式 UNSIGNED 将value转换成INT(无符号的整数)格式 DECIMAL 将value转换成FLOAT(浮点数)格式 BINARY 将value转换成二进制格式
> 示例：
将值转为Date类型：
;gutter:true;
— 2017-08-29
SELECT CAST(‘2017-08-29’ AS DATE);
— 2022-09-14 10:22:29
SELECT NOW();
— 2022-09-14
SELECT CAST(NOW() AS DATE);
将值转换为DATETIME数据类型
sql;gutter:true;
-- 2022-04-27 00:00:00
SELECT CAST('2022-04-27' AS DATETIME);</p>
<pre><code>
将值转换为TIME数据类型
;gutter:true;
— 14:06:10
SELECT CAST(’14:06:10′ AS TIME);
— 14:06:10
SELECT CAST(‘2022-04-27 14:06:10’ AS TIME);
将date转为char，将某一列的数据类型转为指定数据类型
sql;gutter:true;
SELECT
CAST( date AS CHAR ) AS date,
IF( STATUS = 1, '正常', '迟到' ) AS STATUS
FROM
tb_checkin
WHERE
user_id = 6
AND date BETWEEN '2022-09-13 00:00:00'
AND '2022-09-14 00:00:00';</p>
<pre><code>
### IF语句：条件判断
IF：如果expr1条件为TRUE，则IF()返回值为expr2，否则返回值为expr3
;gutter:true;
IF(expr1,expr2,expr3);
IFNULL：假如expr1不为null，则返回expr1，否则返回expr2
sql;gutter:true;
IFNULL(expr1,expr2)</p>
<pre><code>
### case whth：条件判断
语法：
;gutter:true;
case
when 条件1 then ‘值’
when 条件2 then ‘值’
……
else then ‘值’
end
示例：
sql;gutter:true;
SELECT
CASE
WHEN status= 1 THEN '正常'
WHEN status= 2 THEN '迟到'
ELSE '未知状态'
END AS status
FROM
tb_checkin
WHERE
user_id =6</p>
<pre><code>
## Oracle：
### 对集合操作
* 1、union:对两个结果集进行并集操作，去重，按照默认规则排序
* 2、union all：对两个结果集并集操作，不去重，不排序
* 3、intersect:对两个结果集进行交集操作，去重，按照默认规则排序
* 4、minus：对两个结果集进行差操作，去重，按照默认规则排序
* 5、or：满足两个条件的并集，不去重，不排序
**总结：**
* 1、如果or字段是索引字段，那么使用union all代替or操作，可以走索引
* 2、如果能用union all ，尽量不要用union，相当于想distinct又执行了order by
* 3、这里所说的默认排序规则是按照select后边的字段顺序排序的，先按照第一个字段排序，如果第一个字段相同，就按照第二个字段排序
### CASE WHEN
;gutter:true;
CASE F_TERMINAL
WHEN ‘1’ THEN ‘PC端’
WHEN ‘2’ THEN ‘移动端’
ELSE ‘其他设备’
END AS F_TERMINAL_NAME
窗口函数：
Oracle支持窗口函数，可以使用窗口函数对查询结果进行分组和排序操作。窗口函数在SELECT语句的SELECT列表和ORDER BY子句中使用。
以下是一个使用窗口函数的示例查询：
csharp;gutter:true;
SELECT last_name, salary, department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;</p>
<pre><code>
在这个查询中，ROW_NUMBER()函数是一个窗口函数，用于计算每个部门中工资最高的员工的排名。
PARTITION BY子句指定按照部门ID进行分组，而ORDER BY子句指定按照工资降序排序。查询结果将返回每个员工的姓氏、工资、所在部门ID以及该员工在其部门中的排名。
除了ROW_NUMBER()函数之外，Oracle还支持其他窗口函数，如RANK()、DENSE_RANK()、NTILE()、LAG()、LEAD()、SUM()等等。您可以在Oracle文档中查看完整的窗口函数列表和用法示例。
> 窗口函数：ROW_NUMBER() [对用户F_USER_ID进行去重(分组)，按照登录时间排序，取最开始登录的一条记录]
;gutter:true;
SELECT * FROM (
SELECT
ID,
F_USER_ID,
F_LOGIN_TIME,
CASE F_TERMINAL
WHEN ‘1’ THEN ‘PC端’
WHEN ‘2’ THEN ‘移动端’
ELSE ‘其他设备’
END AS F_TERMINAL_NAME,
ROW_NUMBER() OVER (PARTITION BY F_USER_ID ORDER BY F_LOGIN_TIME) RN
FROM
T_LOG_SESSION
WHERE
F_USER_LOGIN_STATE = 1
AND F_REQUEST_URL = ‘https://www.bjcc.gov.cn/vplayzb/2/6000585/600280123/0.html’
AND F_CONNECT_TIME >= TO_DATE(‘2023-01-23 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)
AND F_CONNECT_TIME
trunc：操作时间日期
1、截取时间
csharp;gutter:true;
select trunc(sysdate,'yyyy') from dual ;--返回当年第一天
select trunc(sysdate-3) from dual ;--返回前三天的数据
select trunc(sysdate,'mm') from dual ; --返回当月第一天
select trunc(sysdate,'dd') from dual ;--返回当前年月日
select trunc(sysdate,'d') from dual ; --返回当前星期的第一天(星期日)
select trunc(sysdate,'hh') from dual ;--返回当前日期截取到小时,分秒补0
select trunc(sysdate,'mi') from dual ;--返回当前日期截取到分,秒补0
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--查看当前实时时间</p>
<pre><code>
> 2、截取数字
;gutter:true;
select trunc(122.555) from dual t; –默认取整
select trunc(122.556,2) from dual t; –保留两位小数,是截取而不是四舍五入
select trunc(122.555,-2) from dual t;–负数表示从小数点左边开始截取2位
MERGE INTO：条件判断
作用:判断C表(源表)和Ａ表(目标表)是否满足ON中条件，如果满足则用C表去更新A表，如果不满足，则将C表数据插入A表.
简单的说就是， 判断表中有没有符合on（）条件中的数据，有了就更新数据，没有就插入数据。
sql;gutter:true;collapse:false
判断表中有没有符合 on （）条件中的数据，有了就更新数据，没有就插入数据。
MERGE INTO 目标表 A USING (
SELECT
B.ID,
B.F_LOGINNAME,
B.F_PASSWORD
FROM
T_MEM_MEMBER B where B.F_LOGINNAME= '???'
) 源表C ON (A.条件字段 = C.条件字段)
匹配成功执行更新语句
WHEN MATCHED THEN UPDATE SET A.F_PASSWORD = C.F_PASSWORD
匹配不成功执行插入语句
WHEN NOT MATCHED THEN
INSERT (A.ID, A.F_NAME, A.F_PASSWORD,A.F_CAPTION, A.F_TYPE,A.F_STATE,A.F_NOTE,A.F_ORDER,A.F_DEPT_ID,A.F_DEPT_NAME,A.F_MS_RECEIVED)
VALUES
(C.ID, C.F_LOGINNAME, C.F_PASSWORD, '区级管理员/街道管理员/...' , '2' , '0' , '1' , '1' , '0' , '管理员' , '0' );</p>
<pre><code>
### start with connect by prior：递归查询
> 基本语法：
;gutter:true;
select [字段] from [表名] start with … connect by prior id = pId
- start with：表示以什么为根节点，不加限制可以写1=1，要以id为123的节点为根节点，就写为start with id =123
- connect by：connect by是必须的，start with有些情况是可以省略的，或者直接start with 1=1不加限制
- prior：prior关键字可以放在等号的前面，也可以放在等号的后面，表示的意义是不一样的，比如 prior id = pid，就表示pid就是这条记录的根节点了
举例：
查询指定父栏目(根节点)下所有的一级(子)栏目，一级栏目下可能还有二级栏目，将这些栏目的信息都查询出来
sql;gutter:true;
SELECT
ID,
F_TITLE
FROM
T_MK_CMS_CHANNEL
START WITH id = '800535'
CONNECT BY PRIOR ID = F_PARENT_ID</p>
<pre><code>
> sys_connect_by_path：将递归查到的数据加上特定的符号
按照上面的例子来说，查询到的数据，并不能直观的看到哪些是父节点，哪些是子节点，使用sys_connect_by_path就可以很好的区分了：
**sys_connect_by_path第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！**
;gutter:true;
SELECT
ID,
sys_connect_by_path ( F_TITLE, ‘—-‘ )
FROM
T_MK_CMS_CHANNEL
START WITH ID = ‘800535’
CONNECT BY PRIOR ID = F_PARENT_ID
Original: https://www.cnblogs.com/zhangzhixi/p/16692001.html
Author: Java小白的搬砖路
Title: MYSQL/Oracle中常用函数总结
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/611888/
转载文章受原作者版权保护。转载请注明原作者出处！