【MySQL】SQL语句02_sql amount-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43797885/article/details/103881930

1.完整的SQL结构

select 字段名
from 表名
where 过滤条件
group by 按照某个条件分组
    having 分组之后值的条件过滤
order by 排序
limit 间隔数 offset 起点

2.Case语句

和我们java中的case有相同的功能和用法:

结构:

case 字段 when 条件 then 结果1 else 结果2 end

习题:

题目:

create table `case_demo`(
  `year` int,
  `month` int,
  `amount` varchar(20)
);
  
insert into `case_demo` (year,month,amount) values(1991,1,"1.1");
insert into `case_demo` (year,month,amount) values(1991,2,"1.2");
insert into `case_demo` (year,month,amount) values(1991,3,"1.3");
insert into `case_demo` (year,month,amount) values(1991,4,"1.4");
insert into `case_demo` (year,month,amount) values(1992,1,"2.1");
insert into `case_demo` (year,month,amount) values(1992,2,"2.2");
insert into `case_demo` (year,month,amount) values(1992,3,"2.3");
insert into `case_demo` (year,month,amount) values(1992,4,"2.4");

SELECT `year`,
case `month` WHEN 1 THEN `amount` ELSE 0 END as m1,
case `month` WHEN 2 THEN `amount` ELSE 0 END as m2,
case `month` WHEN 3 THEN `amount` ELSE 0 END as m3,
case `month` WHEN 4 THEN `amount` ELSE 0 END as m4
FROM case_demo

列转行

SELECT YEAR,
MAX(CASE MONTH WHEN 1 THEN amount ELSE 0 END) as m1,
MAX(CASE MONTH WHEN 2 THEN amount ELSE 0 END) as m2,
MAX(CASE MONTH WHEN 3 THEN amount ELSE 0 END) as m3,
MAX(CASE MONTH WHEN 4 THEN amount ELSE 0 END) as m4
FROM case_demo
GROUP BY YEAR

如果想要列转行,我们可以向对其进行分组,然后,然后用对应的数值进行填充,如果对应的数值不存在,那么我们就选取最大值.

3.窗口函数:

1.简单入门

从某些方面来说，窗口函数与聚合函数（Aggregate Function）有些类似，他们都是对一系列的行进行聚合计算，然而不同于聚合函数的是，窗口函数的输出并不是被聚集到单独的一行。像 SUM, AVG, COUNT 这些聚合函数最终输出都是一行记录。

窗口函数可以通过计算每行周围窗口上的集合值来分析数据，例如：计算一定记录范围内、一定值域内、或者一段时间内的累计和以及移动平均值等等。之所以使用窗口这个术语，是因为对结果的处理使用了一个滑动的查询结果集范围。

2.窗口函数有什么作用?

分组和排序

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

3.什么是窗口函数？

OLAP函数,可以对数据库数据进行实时分析处理。

4.窗口函数的基本语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

5.窗口函数都有哪些呢

专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
聚合函数，如sum. avg, count, max, min等

6.使用

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

7.实例

sql文件

/*
Navicat MySQL Data Transfer

Source Server         : 百度云服务
Source Server Version : 50727
Source Host           : 106.13.115.64:3306
Source Database       : test01

Target Server Type    : MYSQL
Target Server Version : 50727
File Encoding         : 65001

Date: 2020-01-08 10:29:49
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for clas
-- ----------------------------
DROP TABLE IF EXISTS `clas`;
CREATE TABLE `clas` (
  `id` varchar(255) NOT NULL,
  `grade` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of clas
-- ----------------------------
INSERT INTO `clas` VALUES ('0001', '1', '86');
INSERT INTO `clas` VALUES ('0002', '1', '95');
INSERT INTO `clas` VALUES ('0003', '2', '89');
INSERT INTO `clas` VALUES ('0004', '1', '83');
INSERT INTO `clas` VALUES ('0005', '2', '86');
INSERT INTO `clas` VALUES ('0006', '3', '92');
INSERT INTO `clas` VALUES ('0007', '3', '86');
INSERT INTO `clas` VALUES ('0008', '1', '88');

专用窗口函数rank

SELECT *,
rank() over(PARTITION BY grade ORDER BY score DESC) as ranking
from clas

为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

窗口函数有一下功能:

同时具有分组和排序的功能;
不减少原表的行数

其他窗口函数:

rank, dense_rank, row_number之间的区别

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。
dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。
row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。

SELECT *,
COUNT(score) over(ORDER BY score DESC) as count1,
SUM(score) over(ORDER BY score DESC) as sum1,
MAX(score) over(ORDER BY score DESC) as score1
from clas

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，以此类推。

不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算.

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响

8.窗口函数的功能