专栏首页bisal的个人杂货铺一道SQL问题,你来试试的?

一道SQL问题,你来试试的?

今天一位铁磁兄弟出了一道SQL题,按照如下SQL,构建测试表,

CREATE TABLE t (company VARCHAR2(10), product VARCHAR2(10), insert_time DATE);
INSERT INTO t VALUES('A', 'A1', to_date('20200109','yyyymmdd')); 
INSERT INTO t VALUES('A', 'A2', to_date('20200109','yyyymmdd'));
INSERT INTO t VALUES('A', 'A1', to_date('20200222','yyyymmdd'));
INSERT INTO t VALUES('A', 'A2', to_date('20200222','yyyymmdd'));
INSERT INTO t VALUES('B', 'B1', to_date('20200225','yyyymmdd'));
INSERT INTO t VALUES('B', 'B2', to_date('20200225','yyyymmdd'));
INSERT INTO t VALUES('B', 'B1', to_date('20200501','yyyymmdd'));
INSERT INTO t VALUES('B', 'B2', to_date('20200501','yyyymmdd'));
INSERT INTO t VALUES('C', 'C1', to_date('20200201','yyyymmdd')); 
INSERT INTO t VALUES('C', 'C2', to_date('20200201','yyyymmdd')); 
INSERT INTO t VALUES('C', 'C1', to_date('20200510','yyyymmdd')); 
INSERT INTO t VALUES('C', 'C2', to_date('20200510','yyyymmdd')); 

T表数据,

SQL> select * from t;


COMPANY    PRODUCT    INSERT_T
---------- ---------- --------
A          A1         20200109
A          A2         20200109
A          A1         20200222
A          A2         20200222
B          B1         20200225
B          B2         20200225
B          B1         20200501
B          B2         20200501
C          C1         20200201
C          C2         20200201
C          C1         20200510
C          C2         20200510


12 rows selected.

问题:

如何通过SQL获取每个company最靠近年初的一组A1和A2的product数据?

如下所示,获取这6条数据,

我的思路是,首先肯定得按照company进行分组,其次还得按照insert_time进行排序,第三挑选排名前两位的数据。

此时,可以考虑rank()/dense_rank() over(partition by ...),其中,

partition by company,按照company进行分区。

order by insert_time,按照insert_time进行排序。

rank()/dense_rank(),分级(必须带上order by)。

逻辑如下,

按照company分区的基础之上,按照insert_time进行分级(“级别”用由小到大的数字表示(从1开始)),作为子查询,用where条件过滤级别等于1的记录。

rank()的SQL执行,

dense_rank()的SQL执行,

可以看到,都是能得到每个company最靠近年初的一组A1和A2的product数据。

解释下rank()和dense_rank(),

rank函数

用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。

dense_rank函数

功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第三名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

执行一下,展示更形象,rank()函数,因为rank=1的是两条记录,因此排在第二位的rank=3,出现跳号,

dense_rank()函数,rank=1的有两条记录,但是第二位的rank还是等于2,

另外,SQL中用到的partition by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。他和聚合函数group by不同的地方在于他只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。

当然,这条SQL只考虑功能,未考虑性能,rank()/dense_rank()不能创建索引,如果数据量很大,这是个问题,还需要思索下,如果朋友们对这个需求有更好的解决方案,或是对性能提升有好的建议,欢迎留言,谢谢。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • truncate表,会将统计信息清除么?

    说明执行truncate,表的统计信息不会被删除,除非执行了统计信息采集,truncate table和表和索引的统计信息,没有任何关联。

    bisal
  • FTP的两种传输模式的坑

    上周的系统迁移,进了一个坑,可谓真是坑,从问题的定位,到问题的解决,有很多值得借鉴、学习的,可以算是一次非常有价值的故障排查,

    bisal
  • Oracle 12c CC安装部署攻略 (中)

    设置SYSMAN管理账号密码、Agent注册密码,会自动设置CC使用的表空间路径和文件名,

    bisal
  • MySQL:如何查询出每个 Group 的 Top n 条记录?

    可以看到,根据年、月、订单金额排序了,还多了一列order_rank,显示出了本条记录在本月的订单金额排名情况。

    dys
  • Python中的__init__()方法整理中(两种解释)

    __init__()方法是Python学习当中重要的基础知识,__init__()方法意义重大的原因有两个。第一个原因是在对象生命周期中初始化是最重要的一步;每...

    学到老
  • Python中的__init__()方法整理中(两种解释)

    解释一:看懂了就不用看第二种了 __init__()方法是Python学习当中重要的基础知识,__init__()方法意义重大的原因有两个。第一个原因是在对...

    学到老
  • Linode 免费升级硬盘容量操作过程记录

    魏艾斯博客www.vpsss.net
  • Powershell运行脚本报错:此系统上禁止运行脚本

    治电小白菜
  • Mac中安全地使用rm命令

    .example_responsive_1 { width: 200px; height: 50px; } @media(min-width: 290px)...

    草堂笺
  • python 生成器&迭代器

    1、列表生成器:列表生成式就像是一个厨师,他只会做这n(n为任意整数)道菜,想吃甚麽做甚麽,不吃不做,不浪费空间;而列表表达式就相当于已经做好的n盘菜,占用空间...

    py3study

扫码关注云+社区

领取腾讯云代金券