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');

-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `id` int(0) NULL DEFAULT NULL,
  `productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
  `price` int(0) NULL DEFAULT NULL,
  `stock_qty` int(0) NULL DEFAULT NULL,
  `supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture  ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

薪资最高的前三名

SELECT * FROM ( SELECT name, salary, ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 3;

销售额累计总计

SELECT date, sales, SUM(sales) OVER(ORDER BY date) AS running_total FROM sales;

按类别对产品进行排名

SELECT productname, category, price, RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rn FROM products;