SQL查询之窗口函数

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int(0) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `salary` int(0) NULL DEFAULT NULL,
  `manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales`  (
  `SaleID` int(0) NOT NULL,
  `EmployeeID` int(0) NULL DEFAULT NULL,
  `Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Sales` decimal(10, 2) NULL DEFAULT NULL,
  `Date` date NULL DEFAULT NULL,
  PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');

SET FOREIGN_KEY_CHECKS = 1;

为行分配唯一的连续序号。
SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rank
FROM employees;

分配排名，但在出现并列时会跳过数字。
SELECT name, salary, RANK() OVER(ORDER BY salary DESC) AS rank
FROM employees;

DENSE_RANK()
类似于 RANK，但不会跳过数字。
SELECT name, salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank FROM employees;

对每个部门内的员工进行排名
SELECT name, department, salary,RANK() OVER(
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees;

将销售额与前一天进行比较。
SELECT date, sales, LAG(sales) OVER(ORDER BY date) AS previous_sales
FROM sales;
用于访问下一行的值。
SELECT date, sales, LEAD(sales) OVER(ORDER BY date) AS next_sales
FROM sales;