面试中遇到的一道 sql 题

2022-11-17 12:45:35 +08:00
 qiyong

面试中遇到一道简单的 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;
5220 次点击
所在节点    程序员
38 条回复
asmile1993
2022-11-17 19:33:40 +08:00
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 -- 以每种分类的总销售额、商品的总销售倒序排列
wxf666
2022-11-17 20:09:01 +08:00
@potatowish wangxin3 `goods_sales_record` 表应该允许多次售出某个商品吧

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


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


试着拿 `SQLite` 写了下：

*（ V 站排版原因，行首有全角空格，记得删除）*

```sql
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;
```
Goooooos
2022-11-17 21:22:23 +08:00
在 V2 ，笔试都是政治不正确的
rabbbit
2022-11-17 22:30:13 +08:00
问个问题，真实业务里允许像查商品销量写 from a, b, c 这种写法吗？
不是会导致查询结果过多吗?
c6h6benzene
2022-11-17 22:37:49 +08:00
大概…开窗函数可以解决？
iseki
2022-11-17 23:34:26 +08:00
@DinnyXu 如果性能敏感，以至于需要给其中的部分子查询增加额外的缓存，那才会选择拆开；否则这种简单查询直接一个 SQL 是最优解，不管是性能上还是功能上。
qinrui
2022-11-17 23:41:27 +08:00
应该用开窗吧
iseki
2022-11-17 23:52:45 +08:00
个人不倾向这里用开窗，题目中感觉并没有明确提出开窗统计的需求，更多的其实是就是把多个统计维度的数据攒到一块了，那 with 几下感觉更好一点
DinnyXu
2022-11-18 00:21:30 +08:00
@CRVV
@sadfQED2
@iseki
他这个是商品类型的，可以参考电商，实际电商会写这种 SQL 吗？很多结果都是代码异步执行的，数据量一旦大起来，你看看那几个子查询的效率不拖垮数据库了。
iseki
2022-11-18 01:45:11 +08:00
@DinnyXu 这种需要全表扫来扫去的 SQL 一般都不会经常跑，也就是个定时任务，一个小时刷一遍排行榜之类的
swcat
2022-11-18 09:48:16 +08:00
销量类型得改为 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;
raysonlu
2022-11-18 10:24:53 +08:00
@CRVV 如果按你说的场景并且只能局限于此，的确只能 BigQuery 了。但我还是比较好奇在实际场景中，遇到这种 BigQuery 是几乎开放式的（多用户或多进程可同时进行请求），这时候还能这么搞？（我实际项目中反而遇到很多这种用户可能会频繁访问 BigQuery 的情景）
我理解中的“分步处理”不是为了“代码比较好懂”而是为了“不让 mysql 高负荷运行复杂查询“，维护简单的 sql 查询比较容易（比如简单的索引组合，分表之类），但 BigQuery 的查询维护就变得很难把控。
在结合一些实际业务情况，我或者会考虑这些：
1 、针对查询业务做一个汇总表，并动态更新，这相当于持久化的热缓存了；
2 、尽量减少搜素条件需求，或针对搜索条件对分步查询进行调整；
3 、慎重考虑分页。分析 BigQuery 过程其实很多情况下（特别是排序），sql 已经把相关数据全部扫描了，然后我们只取部分数据（估计考虑数据传输压力到中间层），我觉得这种情况不如“不分页查询”。数据库和中间层之间数据传输压力（如 in 查询的数量限制也算是），可以尝试在分步处理的各个步骤当中再进行”分步查询“，中间层和客户端层之间的传输压力，那就甩锅给带宽吧（ nginx 已经有数据压缩传输处理了，不想管了）
总结我对 BigQuery 的使用场景是：业务量不大，且让业务快速到位。
以上，个人鄙见
nig001
2022-11-18 11:59:47 +08:00
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
CRVV
2022-11-18 14:01:25 +08:00
@raysonlu

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

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

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

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

至于其它的很多都是需求问题，很多时候产品经理的需求就要分页要搜索，总不能直接说这东西做不了吧。
weizhen199
2022-11-18 16:13:43 +08:00
实际上 oracle 的 cbo 也就这样，所以该分几步还是分吧
Pandaaaa906
2022-11-19 10:34:30 +08:00
没限制数据库吧？ 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
nuanshen
2022-11-19 14:44:01 +08:00
这简单啊，不过销量字段类型先改成 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;
raysonlu
2022-11-21 10:16:12 +08:00
@CRVV

我确实一直都在用 MySQL 做项目，其他数据库甚少了解。现今中小项目使用 MySQL 依然很普遍。
对关系型数据库的学习，我一直都是以“边用边学”的方式，有尝试去“系统性进阶学习”，但很难看进去（比如《高性能 MySQL 》），或者说是还没找到适合的教材、方法吧。
产品经理的需求，我觉得技术经理需要去进行平衡，当然要优先考虑实现需求，在不是 100%实现需求的情况下，实现的方式有多种。

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/895912

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX