窗口函数笔记:来自宋红康老师视频
MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!_哔哩哔哩_bilibili
#20220403MySQL新特性
CREATE DATABASE dbtest18;
USE dbtest18;
CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15), county VARCHAR(15), sales_value DECIMAL );
INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);
#需求:现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售
#额中的比率,以及占总销售额中的比率
#方式1:
#第一步,计算总销售金额,并存入临时表 a:
CREATE TEMPORARY TABLE a #创建临时表
SELECT SUM(sales_value) AS sales_value #计算总计金额
FROM sales;
SELECT * FROM a;
#第二步,计算每个城市的销售总额并存入临时表 b:
CREATE TEMPORARY TABLE b -- 创建临时表
SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计
FROM sales GROUP BY city;
SELECT * FROM b;
#第三步,计算各区的销售占所在城市的总计金额的比例,和占全部销售总计金额的比例。
SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额,
b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率,
a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率
FROM sales s
JOIN b ON (s.city=b.city) -- 连接市统计结果临时表
JOIN a -- 连接总计金额临时表
ORDER BY s.city,s.county;
#方式2:窗口函数
SELECT city AS 城市,county AS 区,sales_value AS 区销售额,
SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额
sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额
sales_value/SUM(sales_value) OVER() AS 总比率
FROM sales
ORDER BY city,county;
CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT,
category VARCHAR