昨晚为了让学弟认识到自己的 sql 水平,出题为难了他

MySQL

SQL

订单

写法

52 条回复 • 2017-12-11 12:13:41 +08:00

1

banixc

2017-12-10 14:49:01 +08:00

http://blog.csdn.net/ylqmf/article/details/39005949

2

noNOno

OP

2017-12-10 14:51:46 +08:00

本来准备了后面几问..严重超纲
2.求每个城市销量前 30%的商品
3.增加时间维度,如何预计算指标计算每周 /每月销量前 30%的商品

3

noNOno

OP

2017-12-10 14:53:21 +08:00

@banixc 订单表是销售额,需要先聚合求销量...在想有没有更好的办法

4

banixc

2017-12-10 14:54:58 +08:00

@noNOno 一个 SQL 可以做到很多事情，只要不限制使用子查询就可以。

5

function007

2017-12-10 14:55:04 +08:00

13

然后楼主成功认识到了自己的 sql 水平 😂

6

tinyuu

2017-12-10 14:55:06 +08:00

一个 sql 什么意思，不能用关联和子查询？

7

noNOno

OP

2017-12-10 14:55:50 +08:00

@function007 对-.-

8

zhy0216

2017-12-10 14:57:13 +08:00

select city, product_number, rank() over (partition by city order by desc (product_number * money)) as amount_rank
from sale where amount_rank <= 3;

9

noNOno

OP

2017-12-10 15:03:06 +08:00

@zhy0216 mysql 没有行号函数.

10

noNOno

OP

2017-12-10 15:04:56 +08:00

@banixc 其实我发帖是想求教下优化方法..请看 2 楼,有什么常用的优化办法么...

11

GGGG430

2017-12-10 15:08:50 +08:00 via Android

我觉得应该冗余一张表，专门记录这种排名之类的结果，用一个定时任务（如每十分钟）来维护

12

noNOno

OP

2017-12-10 15:13:52 +08:00

@GGGG430 是相当于,建立一个商品表,然后定期更新标签,[南京][top3] 这样么

13

noNOno

OP

2017-12-10 15:19:12 +08:00

这问题的优化还是蛮有意思的吧,+_+...好奇有好办法么,尤其是大数据量的时候

14

GGGG430

2017-12-10 15:20:22 +08:00

是的, 定时任务查从表, 呵呵, 发现好像我答偏了, 楼主好像问 sql 优化方法

15

noNOno

OP

2017-12-10 15:24:13 +08:00

@GGGG430 是的,其实是离线批处理的问题.现实中订单会发生更新,嘿嘿嘿,增量不好搞

16

fuyufjh

2017-12-10 15:25:49 +08:00

第一反应就是用定时任务做，然后把结果写到另一个表里。

哎，毕业一年半了……

17

billgreen1

2017-12-10 15:27:15 +08:00

1

@noNOno mariadb 有了 row_number，rank 之类的函数，mysql 也快有了

18

LxExExl

2017-12-10 15:28:33 +08:00 via iPhone

更加坚定了我以后需要写 sql 的时候就直接去找组里的 data engineer 别自己耗时间了……

19

noNOno

OP

2017-12-10 15:29:06 +08:00

@billgreen1 哎..昨天想跟学弟炫耀下现写个 sql,结果 mysql 没 row_number,丢人丢到家....

20

mooncakejs

2017-12-10 15:32:12 +08:00

没有 window 方法干这个就是自己找事。

21

noNOno

OP

2017-12-10 15:39:46 +08:00

我分享下自己的增量思路,不做伸手党.....
同时求好的思路.

----订单增量统计
每日增量统计销量,销量存入商品宽表
|城市|商品|销量|日期|
|武汉|切糕|1|2017-12-09|
|武汉|切糕|0|2017-12-10|

从商品宽表计算每月销量,按[日期月份]分组,聚合 sum([销量])

22

ToTChowChow

2017-12-10 15:46:51 +08:00 via Android

你需要 Leetcode sql

23

swulling

2017-12-10 15:55:07 +08:00

1

你这种无论如何都要扫全表的，用 SQL 没有意义啊，随便用 Python、PHP 这种外部分析好了

但是现实中，一般是两种方法：
1. 时效性低：外置定时任务扫描，增量简单，根据日期分析即可
2. 时效性高：kv 数据库存储，比如 Redis，也数据入库时，同时写入 Redis，然后用 Rank 就好了

24

noNOno

OP

2017-12-10 16:01:54 +08:00

@swulling 索引就可以避免全表扫描了...打扰了

25

noNOno

OP

2017-12-10 16:02:29 +08:00

下沉了.....

26

Weny

2017-12-10 16:36:40 +08:00 via iPhone

学弟怕是外包月入过十万学长还是太年轻

27

CDuXZMAPgHp1q9ew

2017-12-10 16:56:33 +08:00

@swulling 请问这样的知识哪里可以学到完整的呢, 新手入门后端, 有些生产上的知识找不到书来学习

28

Seddas

2017-12-10 17:33:23 +08:00

leetcode 上有差不多的题，https://leetcode.com/problems/department-top-three-salaries/description/

29

Seddas

2017-12-10 17:36:38 +08:00

这种题 SQL 很熟的人也要想一阵好伐，而且真是很大的表执行速度肯定感人

30

xierch

2017-12-10 17:44:41 +08:00

1

data warehouse 那门课好多这种题目…
不过那时候是先把数据导入到某个专门用于分析的数据库（ OLTP -> OLAP ），然后再用 MDX 写查询语句的…
https://en.wikipedia.org/wiki/Online_analytical_processing
https://en.wikipedia.org/wiki/MultiDimensional_eXpressions

31

jhdxr

2017-12-10 17:54:40 +08:00

只是想要实现目标的话还是有解的，先一个子查询根据城市统计出总额，然后找这个子查询中的记录，满足条件『金额大于等于当前记录的金额的条数小于等于 3 』即可

但考虑到 MySQL 极其糟糕的查询分析器，这种东西只能是写着玩。。。

32

noNOno

OP

2017-12-10 19:53:20 +08:00

@Seddas 很多回答都看得很尴尬...都是不懂的人在回答
@xierch 这个其实就是 dw&dm 层的增量...OLTP ,OLAP 就是数仓要开发的东西.....导入进去...懵逼..

帖子怎么还没沉..

33

noNOno

OP

2017-12-10 19:56:30 +08:00

@jhdxr 是的-.-,都没幽默感......我的尴尬癌都犯了

34

noNOno

OP

2017-12-10 19:59:54 +08:00

@jhdxr 而且,你没有考虑到分组的情况,从子查询取『金额大于等于当前记录的金额的条数小于等于 3 』时,子查询中还包含了多个城市的多个商店.而要取每个城市的 top3,或 top30% .哈哈哈哈

35

qiukun

2017-12-10 22:14:54 +08:00

@noNOno 索引还能顾及 partition ？

36

yuriko

2017-12-10 22:37:42 +08:00

这个如果没啥特殊的要求，单纯的累加子查询，貌似就是我们以前上课作业的难度……
可惜这之后再也咩用过，已经忘干净了

37

noNOno

OP

2017-12-10 22:38:38 +08:00

@qiukun 从订单表抽取时根据 updatetime 抽取,updatetime 加索引就好了.根本没有全表扫描的.
因为订单表的数据是金额,并不是销量,所以增量统计一定时间间隔的销量,
比如一天,将每天的销量放入宽化处理的 dw 表中,供 dm 层计算指标使用
不明白 partition 是指什么

38

noNOno

OP

2017-12-10 22:39:51 +08:00

@yuriko 业务逻辑很复杂.
仔细看题...

39

swulling

2017-12-10 23:14:10 +08:00 via iPhone

@noNOno 你这个题怎么加索引都要扫全表的

一条订单一个记录，你不扫全表怎么算出某个商品的订单总额？

你别告诉我你每条记录直接就是商品在一个城市的订单总金额，那表名就不应该是订单表

40

ooTwToo

2017-12-10 23:36:59 +08:00 via Android

这种聚合统计 es 应该比较专业。或者使用一张销售记录表，增量

41

msg7086

2017-12-11 01:00:13 +08:00

1

说句实话，这样的业务逻辑放在存储上的意义是什么。
放在以前，SQL 功能强大，编程语言相对复杂难用，而且程序和数据库之间传输数据开销很大的时候，全程 SQL 求值我可以理解。现在数据传输走 Unix Socket，编程语言也简单很多，业务逻辑放在程序里处理的同时还可以根据业务充分缓存数据提高性能，更不提 Web Server 可以分布式而数据库为了 ACID 通常只能小量部署。这种情况下还去写长长的 SQL 只会限制性能。别人家不知道，至少在我司，这样的「 SQL 水平」是不需要的，这样复杂的 SQL 语句也是禁止的。

42

Theta

2017-12-11 01:09:25 +08:00 via iPhone

似李，学长！有缘 internet 见啊，听说你要难倒英雄我😳

43

ioth

2017-12-11 09:25:22 +08:00

现在在一起了吧？

44

realpg

2017-12-11 09:36:36 +08:00

性能大坑……
这就是我不爱要写 java 企业程序的码农的原因之一
曾经面试一个 java 企业开发要转 php 互联网开发，我给出了个小面试题，大致就是给定的库结构跟想要的结果无法直接用简单查询得到，需要用点小技巧才能有效利用索引……

然后面试的折腾了 20 分钟，牛逼哄哄的交卷给我看，写了一个 4KB 文本的单一 SQL 查询！ 4KB 文本！嵌套 10+层子查询，交卷时候觉得自己老牛逼了……

45

Chrics

2017-12-11 10:14:13 +08:00

说真的，这种可以用业务逻辑实现的代码，强行一句 SQL 无意义好吧

46

openbsd

2017-12-11 10:41:12 +08:00

外行不懂，求大神解惑
在单表数据比较大的时候（比如行数过亿）
用 SQL 实现和用程序实现，性能有差距吗？

47

noNOno

OP

2017-12-11 10:45:56 +08:00

@swulling 每日计算当日的订单总额,存入宽表.计算总量从宽表计算

48

noNOno

OP

2017-12-11 10:46:48 +08:00

@msg7086 离线处理,供分析使用

49

noNOno

OP

2017-12-11 10:50:17 +08:00

@realpg 数仓题...

50

yuriko

2017-12-11 11:51:37 +08:00

@noNOno
复杂？我看了三遍也没觉得我理解错了啊……

51

jitongxi

2017-12-11 12:12:01 +08:00

一个存储过程, 里面多写点 sql, 就解决了吧. 写银行业务的, 这些是拿手小菜.

52

qiukun

2017-12-11 12:13:41 +08:00

@realpg 求题目