mysql小知识

参见https://www.bilibili.com/video/BV1k3411s7gt/?spm_id_from=pageDriver&vd_source=b879d4ff10c4ff63bbd44d7f0839ffa2

窗口函数: 如果在原始数据的基础上加上一些统计字段, 可以使用窗口函数. 直观的理解就是: 聚合函数 + over (PARTITION by xxx, yyy,zzz order by xxx) as ?

 个人演示:

CREATE TABLE `sales` (
`year` int NOT NULL,
`country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`product` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`profit` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (2020, 'cn', 'pc', 1000);
INSERT INTO `sales` VALUES (2020, 'usa', 'pc', 2100);
INSERT INTO `sales` VALUES (2020, 'india', 'pc', 1500);
INSERT INTO `sales` VALUES (2020, 'cn', 'phone', 2000);
INSERT INTO `sales` VALUES (2020, 'usa', 'phone', 2010);
INSERT INTO `sales` VALUES (2020, 'india', 'phone', 2111);
INSERT INTO `sales` VALUES (2022, 'cn', 'med', 1111);
INSERT INTO `sales` VALUES (2022, 'usa', 'med', 1211);
INSERT INTO `sales` VALUES (2022, 'india', 'med', 2300);

1. 每一行数据上加上按country,year 分组后的总profit

select *, sum(profit) over (PARTITION by country, year order by profit asc) as yc_profit  from sales;

2. 每一行数据上加上按country,year 分组后的此时累计的profit

 

select *, sum(profit) over (PARTITION by country, year order by product asc rows unbounded preceding ) as running_profit  from sales;    其中rows unbounded preceding 表示求running_profit的范围(向前不越界,不写结束表示默认到当前行)