V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
noNOno
V2EX  ›  程序员

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

  •  
  •   noNOno ·
    thenoNOno · 2017-12-10 14:44:03 +08:00 · 7877 次点击
    这是一个创建于 2301 天前的主题,其中的信息可能已经有所发展或是发生改变。

    --.结果我自己没有想出来好的写法 数据库 mysql

    订单表有 3 列
    |城市|商品|订单金额|
    

    1.求每个城市[销量]前三的商品

    在 mysql 查询,可以一个 sql 做到么

    第 1 条附言  ·  2017-12-10 20:22:22 +08:00
    尴尬,帖子还没沉.
    无聊的话可以写 sql 试试,还是有套路的,不要回帖嘲笑我 sql 差了......
    52 条回复    2017-12-11 12:13:41 +08:00
    banixc
        1
    banixc  
       2017-12-10 14:49:01 +08:00
    noNOno
        2
    noNOno  
    OP
       2017-12-10 14:51:46 +08:00
    本来准备了后面几问..严重超纲
    2.求每个城市销量前 30%的商品
    3.增加时间维度,如何预计算指标计算每周 /每月销量前 30%的商品
    noNOno
        3
    noNOno  
    OP
       2017-12-10 14:53:21 +08:00
    @banixc 订单表是销售额,需要先聚合求销量...在想有没有更好的办法
    banixc
        4
    banixc  
       2017-12-10 14:54:58 +08:00
    @noNOno 一个 SQL 可以做到很多事情,只要不限制使用子查询就可以。
    function007
        5
    function007  
       2017-12-10 14:55:04 +08:00   ❤️ 13
    然后楼主成功认识到了自己的 sql 水平 😂
    tinyuu
        6
    tinyuu  
       2017-12-10 14:55:06 +08:00
    一个 sql 什么意思,不能用关联和子查询?
    noNOno
        7
    noNOno  
    OP
       2017-12-10 14:55:50 +08:00
    @function007 对-.-
    zhy0216
        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;
    noNOno
        9
    noNOno  
    OP
       2017-12-10 15:03:06 +08:00
    @zhy0216 mysql 没有行号函数.
    noNOno
        10
    noNOno  
    OP
       2017-12-10 15:04:56 +08:00
    @banixc 其实我发帖是想求教下优化方法..请看 2 楼,有什么常用的优化办法么...
    GGGG430
        11
    GGGG430  
       2017-12-10 15:08:50 +08:00 via Android
    我觉得应该冗余一张表,专门记录这种排名之类的结果,用一个定时任务(如每十分钟)来维护
    noNOno
        12
    noNOno  
    OP
       2017-12-10 15:13:52 +08:00
    @GGGG430 是相当于,建立一个商品表,然后定期更新标签,[南京][top3] 这样么
    noNOno
        13
    noNOno  
    OP
       2017-12-10 15:19:12 +08:00
    这问题的优化还是蛮有意思的吧,+_+...好奇有好办法么,尤其是大数据量的时候
    GGGG430
        14
    GGGG430  
       2017-12-10 15:20:22 +08:00
    是的, 定时任务查从表, 呵呵, 发现好像我答偏了, 楼主好像问 sql 优化方法
    noNOno
        15
    noNOno  
    OP
       2017-12-10 15:24:13 +08:00
    @GGGG430 是的,其实是离线批处理的问题.现实中订单会发生更新,嘿嘿嘿,增量不好搞
    fuyufjh
        16
    fuyufjh  
       2017-12-10 15:25:49 +08:00
    第一反应就是用定时任务做,然后把结果写到另一个表里。

    哎,毕业一年半了……
    billgreen1
        17
    billgreen1  
       2017-12-10 15:27:15 +08:00   ❤️ 1
    @noNOno mariadb 有了 row_number,rank 之类的函数,mysql 也快有了
    LxExExl
        18
    LxExExl  
       2017-12-10 15:28:33 +08:00 via iPhone
    更加坚定了我以后需要写 sql 的时候就直接去找组里的 data engineer 别自己耗时间了……
    noNOno
        19
    noNOno  
    OP
       2017-12-10 15:29:06 +08:00
    @billgreen1 哎..昨天想跟学弟炫耀下现写个 sql,结果 mysql 没 row_number,丢人丢到家....
    mooncakejs
        20
    mooncakejs  
       2017-12-10 15:32:12 +08:00
    没有 window 方法干这个就是自己找事。
    noNOno
        21
    noNOno  
    OP
       2017-12-10 15:39:46 +08:00
    我分享下自己的增量思路,不做伸手党.....
    同时求好的思路.

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

    从商品宽表计算每月销量,按[日期月份]分组,聚合 sum([销量])
    ToTChowChow
        22
    ToTChowChow  
       2017-12-10 15:46:51 +08:00 via Android
    你需要 Leetcode sql
    swulling
        23
    swulling  
       2017-12-10 15:55:07 +08:00   ❤️ 1
    你这种无论如何都要扫全表的,用 SQL 没有意义啊,随便用 Python、PHP 这种外部分析好了

    但是现实中,一般是两种方法:
    1. 时效性低:外置定时任务扫描,增量简单,根据日期分析即可
    2. 时效性高:kv 数据库存储,比如 Redis,也数据入库时,同时写入 Redis,然后用 Rank 就好了
    noNOno
        24
    noNOno  
    OP
       2017-12-10 16:01:54 +08:00
    @swulling 索引就可以避免全表扫描了...打扰了
    noNOno
        25
    noNOno  
    OP
       2017-12-10 16:02:29 +08:00
    下沉了.....
    Weny
        26
    Weny  
       2017-12-10 16:36:40 +08:00 via iPhone
    学弟怕是外包月入过十万 学长还是太年轻
    wujichao
        27
    wujichao  
       2017-12-10 16:56:33 +08:00
    @swulling 请问这样的知识哪里可以学到完整的呢, 新手入门后端, 有些生产上的知识找不到书来学习
    Seddas
        28
    Seddas  
       2017-12-10 17:33:23 +08:00
    Seddas
        29
    Seddas  
       2017-12-10 17:36:38 +08:00
    这种题 SQL 很熟的人也要想一阵好伐,而且真是很大的表执行速度肯定感人
    xierch
        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
    jhdxr
        31
    jhdxr  
       2017-12-10 17:54:40 +08:00
    只是想要实现目标的话还是有解的,先一个子查询根据城市统计出总额,然后找这个子查询中的记录,满足条件『金额大于等于当前记录的金额的条数小于等于 3 』即可

    但考虑到 MySQL 极其糟糕的查询分析器,这种东西只能是写着玩。。。
    noNOno
        32
    noNOno  
    OP
       2017-12-10 19:53:20 +08:00
    @Seddas 很多回答都看得很尴尬...都是不懂的人在回答
    @xierch 这个其实就是 dw&dm 层的增量...OLTP ,OLAP 就是数仓要开发的东西.....导入进去...懵逼..

    帖子怎么还没沉..
    noNOno
        33
    noNOno  
    OP
       2017-12-10 19:56:30 +08:00
    @jhdxr 是的-.-,都没幽默感......我的尴尬癌都犯了
    noNOno
        34
    noNOno  
    OP
       2017-12-10 19:59:54 +08:00
    @jhdxr 而且,你没有考虑到分组的情况,从子查询取『金额大于等于当前记录的金额的条数小于等于 3 』时,子查询中还包含了多个城市的多个商店.而要取每个城市的 top3,或 top30% .哈哈哈哈
    qiukun
        35
    qiukun  
       2017-12-10 22:14:54 +08:00
    @noNOno 索引还能顾及 partition ?
    yuriko
        36
    yuriko  
       2017-12-10 22:37:42 +08:00
    这个如果没啥特殊的要求,单纯的累加子查询,貌似就是我们以前上课作业的难度……
    可惜这之后再也咩用过,已经忘干净了
    noNOno
        37
    noNOno  
    OP
       2017-12-10 22:38:38 +08:00
    @qiukun 从订单表抽取时根据 updatetime 抽取,updatetime 加索引就好了.根本没有全表扫描的.
    因为订单表的数据是金额,并不是销量,所以增量统计一定时间间隔的销量,
    比如一天,将每天的销量放入宽化处理的 dw 表中,供 dm 层计算指标使用
    不明白 partition 是指什么
    noNOno
        38
    noNOno  
    OP
       2017-12-10 22:39:51 +08:00
    @yuriko 业务逻辑很复杂.
    仔细看题...
    swulling
        39
    swulling  
       2017-12-10 23:14:10 +08:00 via iPhone
    @noNOno 你这个题怎么加索引都要扫全表的

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

    你别告诉我你每条记录直接就是商品在一个城市的订单总金额,那表名就不应该是 订单表
    ooTwToo
        40
    ooTwToo  
       2017-12-10 23:36:59 +08:00 via Android
    这种聚合统计 es 应该比较专业。或者使用一张销售记录表,增量
    msg7086
        41
    msg7086  
       2017-12-11 01:00:13 +08:00   ❤️ 1
    说句实话,这样的业务逻辑放在存储上的意义是什么。
    放在以前,SQL 功能强大,编程语言相对复杂难用,而且程序和数据库之间传输数据开销很大的时候,全程 SQL 求值我可以理解。现在数据传输走 Unix Socket,编程语言也简单很多,业务逻辑放在程序里处理的同时还可以根据业务充分缓存数据提高性能,更不提 Web Server 可以分布式而数据库为了 ACID 通常只能小量部署。这种情况下还去写长长的 SQL 只会限制性能。别人家不知道,至少在我司,这样的「 SQL 水平」是不需要的,这样复杂的 SQL 语句也是禁止的。
    Theta
        42
    Theta  
       2017-12-11 01:09:25 +08:00 via iPhone
    似李,学长!有缘 internet 见啊,听说你要难倒英雄我😳
    ioth
        43
    ioth  
       2017-12-11 09:25:22 +08:00
    现在在一起了吧?
    realpg
        44
    realpg  
       2017-12-11 09:36:36 +08:00
    性能大坑……
    这就是我不爱要写 java 企业程序的码农的原因之一
    曾经面试一个 java 企业开发要转 php 互联网开发,我给出了个小面试题,大致就是给定的库结构跟想要的结果无法直接用简单查询得到,需要用点小技巧才能有效利用索引……

    然后面试的折腾了 20 分钟,牛逼哄哄的交卷给我看,写了一个 4KB 文本的单一 SQL 查询! 4KB 文本!嵌套 10+层子查询,交卷时候觉得自己老牛逼了……
    Chrics
        45
    Chrics  
       2017-12-11 10:14:13 +08:00
    说真的,这种可以用业务逻辑实现的代码,强行一句 SQL 无意义好吧
    openbsd
        46
    openbsd  
       2017-12-11 10:41:12 +08:00
    外行不懂,求大神解惑
    在单表数据比较大的时候(比如行数过亿)
    用 SQL 实现和用程序实现,性能有差距吗 ?
    noNOno
        47
    noNOno  
    OP
       2017-12-11 10:45:56 +08:00
    @swulling 每日计算当日的订单总额,存入宽表.计算总量从宽表计算
    noNOno
        48
    noNOno  
    OP
       2017-12-11 10:46:48 +08:00
    @msg7086 离线处理,供分析使用
    noNOno
        49
    noNOno  
    OP
       2017-12-11 10:50:17 +08:00
    @realpg 数仓题...
    yuriko
        50
    yuriko  
       2017-12-11 11:51:37 +08:00
    @noNOno
    复杂?我看了三遍也没觉得我理解错了啊……
    jitongxi
        51
    jitongxi  
       2017-12-11 12:12:01 +08:00
    一个存储过程, 里面多写点 sql, 就解决了吧. 写银行业务的, 这些是拿手小菜.
    qiukun
        52
    qiukun  
       2017-12-11 12:13:41 +08:00
    @realpg 求题目
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3132 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 10:56 · PVG 18:56 · LAX 03:56 · JFK 06:56
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.