面试中遇到一道简单的 sql 题,小弟也能写出来,但写的非常乱,嵌套了很多子查询,我感觉有更美妙的写法,请教各位
题目

我的解答
SELECT a.id, a.name, a.group_id, b.name group_name, c.sum_goods_sales_volume, d.sum_group_sales_volume
FROM goods a,
goods_group b,
(select sum(sales_volume) sum_goods_sales_volume, goods_id
from goods_sales_record
group by goods_id
order by sum_goods_sales_volume desc
limit 3) c,
(select sum(sales_volume) sum_group_sales_volume, bb.group_id FROM goods_sales_record aa
JOIN goods bb WHERE aa.goods_id = bb.id GROUP BY bb.group_id) d
WHERE a.group_id = b.id AND a.id = c.goods_id AND a.group_id = d.group_id
order by sum_group_sales_volume desc, sum_goods_sales_volume desc;

附上表结构 及 数据 方便大佬们使用
goods
/*
Navicat Premium Data Transfer

Source Server : 本地 MySql
Source Server Type : MySQL
Source Server Version : 80028
Source Host : localhost:3306
Source Schema : transaction

Target Server Type : MySQL
Target Server Version : 80028
File Encoding : 65001

Date: 17/11/2022 12:43:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for goods


DROP TABLE IF EXISTS goods;
CREATE TABLE goods (
id int NOT NULL,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
group_id int NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


-- Records of goods


INSERT INTO goods VALUES (1, '苹果手机', 1);
INSERT INTO goods VALUES (2, '三星手机', 1);
INSERT INTO goods VALUES (3, '联想电脑', 2);
INSERT INTO goods VALUES (4, '华为手机', 1);
INSERT INTO goods VALUES (5, '华硕电脑', 2);
INSERT INTO goods VALUES (6, 'IKBC', 3);

SET FOREIGN_KEY_CHECKS = 1;

goods_group
/*
Navicat Premium Data Transfer

Source Server : 本地 MySql
Source Server Type : MySQL
Source Server Version : 80028
Source Host : localhost:3306
Source Schema : transaction

Target Server Type : MySQL
Target Server Version : 80028
File Encoding : 65001

Date: 17/11/2022 12:43:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for goods_group


DROP TABLE IF EXISTS goods_group;
CREATE TABLE goods_group (
id int NOT NULL,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


-- Records of goods_group


INSERT INTO goods_group VALUES (1, '手机');
INSERT INTO goods_group VALUES (2, '电脑');
INSERT INTO goods_group VALUES (3, '键盘');

SET FOREIGN_KEY_CHECKS = 1;

goods_sales_record
/*
Navicat Premium Data Transfer

Source Server : 本地 MySql
Source Server Type : MySQL
Source Server Version : 80028
Source Host : localhost:3306
Source Schema : transaction

Target Server Type : MySQL
Target Server Version : 80028
File Encoding : 65001

Date: 17/11/2022 12:43:26
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for goods_sales_record


DROP TABLE IF EXISTS goods_sales_record;
CREATE TABLE goods_sales_record (
id int NOT NULL,
goods_id int NULL DEFAULT NULL,
sales_volume varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


-- Records of goods_sales_record


INSERT INTO goods_sales_record VALUES (1, 1, '50');
INSERT INTO goods_sales_record VALUES (2, 2, '30');
INSERT INTO goods_sales_record VALUES (3, 3, '88');
INSERT INTO goods_sales_record VALUES (4, 4, '88');
INSERT INTO goods_sales_record VALUES (5, 5, '444');
INSERT INTO goods_sales_record VALUES (6, 6, '34');

SET FOREIGN_KEY_CHECKS = 1;

这种东西感觉没个半小时调试加搜索我感觉我写不出来🤦‍♂️

这题没有 copilot 做不出来

抖个机灵,我会分开查不会写复杂的子查询或者其他。原因我可以和面试官好好说说

用 with 拆开会更好一点

忘说了 是第二道题

第一问第二问不是连着的吗
SELECT TOP 3 A.name, C.sales_volume, B.name FROM goods A
JOIN goods_group B ON A.group_id = B.id
JOIN goods_sales_record C ON A.id = C.goods_id
ORDER BY sales_volume DESC

SELECT D.good_name, D.sales_volume, D.group_name, E.sum_sales FROM
(
SELECT TOP 3 A.name AS good_name, C.sales_volume, B.name AS group_name, B.id AS group_id FROM goods A
JOIN goods_group B ON A.group_id = B.id
JOIN goods_sales_record C ON A.id = C.goods_id
ORDER BY sales_volume DESC
) D
JOIN (
SELECT B.id AS id, SUM(C.sales_volume) AS sum_sales FROM goods A
JOIN goods_group B ON A.group_id = B.id
JOIN goods_sales_record C ON A.id = C.goods_id
GROUP BY B.id
) E ON D.group_id = E.id
ORDER BY sum_sales DESC, sales_volume DESC

首先把销量的类型改成数字, sales_volume INT

最后都需要套一层子查询来重新排序,就不写了。
这三种写法都是用 ORDER BY volume DESC LIMIT 3 来选出前 3 ,还可以用 rank <= 3 来选前 3 的,如果有重复的会得到不同结果
都可以在最新的 MySQL 上执行

SELECT goods.name,
goods_sales_record.sales_volume,
goods_group.name AS group_name,
t.group_volume
FROM goods
INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id
INNER JOIN goods_group ON goods.group_id = goods_group.id
INNER JOIN (SELECT goods_group.id, sum(goods_sales_record.sales_volume) AS group_volume
FROM goods_group
INNER JOIN goods ON goods.group_id = goods_group.id
INNER JOIN goods_sales_record on goods.id = goods_sales_record.goods_id
GROUP BY goods_group.id) AS t ON t.id = goods_group.id
ORDER BY 2 DESC limit 3;

SELECT goods.name,
goods_sales_record.sales_volume,
goods_group.name AS group_name,
t.group_volume
FROM goods
INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id
INNER JOIN goods_group ON goods.group_id = goods_group.id
CROSS JOIN LATERAL (SELECT sum(goods_sales_record.sales_volume) AS group_volume
FROM goods INNER JOIN goods_sales_record on goods.id = goods_sales_record.goods_id
WHERE group_id = goods_group.id) AS t
ORDER BY 2 DESC limit 3;

SELECT goods.name,
goods_sales_record.sales_volume,
goods_group.name AS group_name,
sum(sales_volume) OVER (PARTITION BY goods_group.id) AS group_volume
FROM goods
INNER JOIN goods_sales_record ON goods.id = goods_sales_record.goods_id
INNER JOIN goods_group ON goods.group_id = goods_group.id
ORDER BY 2 DESC
LIMIT 3;

这个题用 SQL 是能做出来,但是对于实际业务来说 SQL 不会写成这种 join 和 嵌套,商品和商品分组查出来,在单独去统计各个商品的销量以及商品分组总销量。要是我面试就可以跟面试官扯皮一下,这种题拿来面试是侮辱智商的。直接喷他

这种 SQL 在很多地方都用得到,而且这个题其实不难。
如果每个表都有几亿行,用 BigQuery 写一句 SQL 就能实现,速度也不慢。如果不写 SQL 你打算怎么做?

这应该是一道考窗口函数的题目,不用写复杂的分组语句

这不写嵌套 sql 就完事?除非有数据倾斜需要解决

select t0.* from (
select
t.goods_name,
t.sales_volume sv,
t.group_name,
sum(t.sales_volume) OVER (PARTITION BY t.group_name)gsv
from (
select g.id as goods_id, g.name as goods_name, gg.name as group_name, gsr.sales_volume
from goods_sales_record gsr
left join goods g on g.id = gsr.goods_id
left join goods_group gg on gg.id = g.group_id
)t
order by sv desc limit 3
)t0
order by t0.gsv desc, t0.sv desc;

这类的查询业务,数据量到达亿级的话,是把整个查询业务都放在 sql 呢(比如这种面试题需要一句 sql 语句得出最终结果,或窗口函数),还是多次 sql 查询分步处理好?(比如 php 或 java 先查出销量最好三个,再根据分组 id 查分组销量情况,再拼凑)

mysql8

select g.name as 商品名,
gsr.sales_volume as 商品销量,
gg.name as 商品所属分组名,
sum(gsr.sales_volume) over (partition by g.group_id) as 总销量
from goods_sales_record gsr
left join goods g on gsr.goods_id = g.id
left join goods_group gg on gg.id = g.group_id
order by 总销量 desc, 商品销量 desc
limit 3

大佬 你这个商品销量排序是失效的 为啥呢 我没看出来

你的销量字段类型改下,改为 int 再试试

如果能分步处理当然分步处理是很多人首选的方案,因为代码比较好懂,会写复杂 SQL 的人没那么多。

但就这个题来说,如果商品有 1 亿个,销量表是分小时的,要查所有商品,按近一个月的销量倒序排列,要能搜索能翻页,还是那种能直接跳到第 10000 页的设计。这样的不太可能分步来处理吧,每一个中间步骤的结果都很大。实际的需求通常都比这个复杂。

写一个大 SQL 可能直接就把数据库弄死了,当然也不行。
所以我上面说 BigQuery ,这东西就是干这个事用的,应该算是解决这种问题的方案之一。

PM:我想要个 xxx 数据,麻烦帮我跑一个吧

写 SQL 查十几分钟就搞定了,而且实际工作中,这种 SQL 都是丢到数仓里面去执行的,数仓里面几百行的 SQL 很常见

有的时候业务量没那么大的时候,假设想差一些临时性的数据,逻辑比较麻烦,这个时候写 sql 是很正常的做法。

cte 、rank

with goods_sales as(
-- 获取每种商品的总销售额
select distinct
g.id as goods_id,
g.name as goods_name,
g.group_id,
gg.name as group_name,
sum(gsr.sales_volume) over(partition by g.id, g.name, g.group_id, gg.name) sum_goods_sales_volume,
sum(gsr.sales_volume) over(partition by g.group_id, gg.name) sum_group_sales_volume
from goods g
inner join goods_group gg on g.group_id= gg.id
inner join goods_sales_record gsr on g.id= gsr.goods_id
order by sum_goods_sales_volume desc -- 以商品的总销售倒序排列,并取前三名
limit 3
)
select goods_id,
goods_name,
group_id,
group_name,
sum_goods_sales_volume, -- 每种商品的总销售额
sum_group_sales_volume -- 每种分类的总销售额
from goods_sales
order by sum_group_sales_volume desc, sum_goods_sales_volume desc -- 以每种分类的总销售额、商品的总销售倒序排列

wangxin3 goods_sales_record 表应该允许多次售出某个商品吧

因为楼主 也用了 FROM goods_sales_record GROUP BY goods_id

我试了下,goods_sales_record 表加个 (7, 1, '100'),#12 #14 结果就不对了

试着拿 SQLite 写了下:

( V 站排版原因,行首有全角空格,记得删除)

WITH
  goods(id, name, group_id) AS (
   VALUES
   (1, '苹果手机', 1),
   (2, '三星手机', 1),
   (3, '联想电脑', 2),
   (4, '华为手机', 1),
   (5, '华硕电脑', 2),
   (6, 'IKBC', 3)
 ),

  goods_group(id, name) AS (
   VALUES
   (1, '手机'),
   (2, '电脑'),
   (3, '键盘')
 ),

  goods_sales_record(id, goods_id, sales_volume) AS (
   VALUES
   (1, 1, '50'),
   (2, 2, '30'),
   (3, 3, '88'),
   (4, 4, '88'),
   (5, 5, '444'),
   (6, 6, '34')
 ),

  goods_sales(goods_id, total_sales) AS (
   SELECT goods_id, SUM(sales_volume)
   FROM goods_sales_record
   GROUP BY 1
 ),

  top3 AS (
   SELECT *
   FROM goods_sales
   ORDER BY total_sales DESC
   LIMIT 3
 )

SELECT
  g1.name 商品名,
  gg.name 商品所属分组名,
  t3.total_sales 商品销量,
  SUM(gs.total_sales) 分组内所有商品总销量
FROM top3 t3
JOIN goods g1 ON g1.id = t3.goods_id
JOIN goods g2 USING(group_id)
JOIN goods_group gg ON gg.id = g1.group_id
JOIN goods_sales gs ON g2.id = gs.goods_id
GROUP BY g1.id
ORDER BY 分组内所有商品总销量 DESC, 商品销量 DESC;

在 V2 ,笔试都是政治不正确的

问个问题,真实业务里允许像查商品销量写 from a, b, c 这种写法吗?
不是会导致查询结果过多吗?

大概…开窗函数可以解决?

如果性能敏感,以至于需要给其中的部分子查询增加额外的缓存,那才会选择拆开;否则这种简单查询直接一个 SQL 是最优解,不管是性能上还是功能上。

应该用开窗吧

个人不倾向这里用开窗,题目中感觉并没有明确提出开窗统计的需求,更多的其实是就是把多个统计维度的数据攒到一块了,那 with 几下感觉更好一点

他这个是商品类型的,可以参考电商,实际电商会写这种 SQL 吗?很多结果都是代码异步执行的,数据量一旦大起来,你看看那几个子查询的效率不拖垮数据库了。

这种需要全表扫来扫去的 SQL 一般都不会经常跑,也就是个定时任务,一个小时刷一遍排行榜之类的

销量类型得改为 int 类型, 不然排序回出错

select sum(sr.sales_volume) over sv '分组总销量',
sr.sales_volume '商品销量',
sr.goods_id as '商品 id',
g.name '商品名',
gg.name '分组名'
from goods_sales_record sr
join goods g on g.id = sr.goods_id
join goods_group gg on g.group_id = gg.id
window sv as (partition by g.group_id )
order by 1 desc, 2 desc
limit 3;

如果按你说的场景并且只能局限于此,的确只能 BigQuery 了。但我还是比较好奇在实际场景中,遇到这种 BigQuery 是几乎开放式的(多用户或多进程可同时进行请求),这时候还能这么搞?(我实际项目中反而遇到很多这种用户可能会频繁访问 BigQuery 的情景)
我理解中的“分步处理”不是为了“代码比较好懂”而是为了“不让 mysql 高负荷运行复杂查询“,维护简单的 sql 查询比较容易(比如简单的索引组合,分表之类),但 BigQuery 的查询维护就变得很难把控。
在结合一些实际业务情况,我或者会考虑这些:
1 、针对查询业务做一个汇总表,并动态更新,这相当于持久化的热缓存了;
2 、尽量减少搜素条件需求,或针对搜索条件对分步查询进行调整;
3 、慎重考虑分页。分析 BigQuery 过程其实很多情况下(特别是排序),sql 已经把相关数据全部扫描了,然后我们只取部分数据(估计考虑数据传输压力到中间层),我觉得这种情况不如“不分页查询”。数据库和中间层之间数据传输压力(如 in 查询的数量限制也算是),可以尝试在分步处理的各个步骤当中再进行”分步查询“,中间层和客户端层之间的传输压力,那就甩锅给带宽吧( nginx 已经有数据压缩传输处理了,不想管了)
总结我对 BigQuery 的使用场景是:业务量不大,且让业务快速到位。
以上,个人鄙见

SELECT a.id,a.name,c.sales_volume,gb.group_name,gb.g_sales_volumes FROM goods a LEFT JOIN goods_sales_record c ON a.id = c.goods_id LEFT JOIN (SELECT b.id as group_id,b.name AS group_name,sum(c.sales_volume) AS g_sales_volumes FROM goods_sales_record c LEFT JOIN goods a ON a.id=c.goods_id LEFT JOIN goods_group b ON a.group_id = b.id GROUP BY b.id) gb ON a.group_id = gb.group_id ORDER BY gb.g_sales_volumes,c.sales_volume DESC LIMIT 3

“不让 mysql 高负荷运行复杂查询”

这应该是一个对关系型数据库的误解,来自于 MySQL 的 planner 太弱。

要得到一个查询的结果,不论查询是不是分步的,总的工作量一定有下限。在 planner 足够好的情况下,一定是分步查询的工作量大,一条大 SQL 的工作量小(因为不需要把可能很大的中间结果传回来),所以写一条大 SQL 才是节约数据库的做法。

在 MySQL 上,“planner 足够好” 通常不成立,这事就反过来了,分步查才是工作小的做法,所以才有分开写简单 SQL 的习惯。换成 PostgreSQL 就不是那么回事了。

至于其它的很多都是需求问题,很多时候产品经理的需求就要分页要搜索,总不能直接说这东西做不了吧。

实际上 oracle 的 cbo 也就这样,所以该分几步还是分吧

没限制数据库吧? mysql 太渣~用 postgres 了

select * from (
select
g.name,
t.sales_volume_sum,
gg.name,
sum(t.sales_volume_sum) over(partition by g.group_id) total_sum
from (
select
goods_id,
sum(gsr.sales_volume) sales_volume_sum
from goods_sales_record gsr
group by gsr.goods_id
) t
left join goods g
on t.goods_id = g.id

left join goods_group gg
on g.group_id = gg.id
order by t.sales_volume_sum desc
limit 3
) tmp
order by total_sum desc, sales_volume_sum desc

这简单啊,不过销量字段类型先改成 int 吧,
求组内总销量用 sum(sales_volume) over(partition by group_id) ,
销量排序 rank()over(order by sales_volume desc)

# mysql8
select t.goods_name 商品名,
t.sales_volume 商品销量,
t.group_name 商品组名,
t.group_sales_volume 商品组总销量
from (
select t.goods_id,
t.sales_volume,
g.name goods_name,
gp.name group_name,
sum(t.sales_volume)over(partition by g.group_id) group_sales_volume,
rank() over (order by t.sales_volume) sales_rank
from goods_sales_record t
left join goods g on g.id = t.goods_id
left join goods_group gp on gp.id = g.group_id
) t
where t.sales_rank<=3
order by t.group_sales_volume desc,t.sales_volume desc;