SQL 为什么动不动就 N 百行以 K 计_oracle某支股票最长连续涨了多少交易日

最新推荐文章于 2025-12-11 22:00:34 发布

原创最新推荐文章于 2025-12-11 22:00:34 发布 · 600 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #oracle #数据库

1．将数据按产品分组，将每组排序，取出前 10 名；

2．将所有的前 10 名取交集；

由于我们事先不知道会有多个产品，这样需要把分组结果也存储在一个临时表中，而这个表有个字段要存储对应的分组成员，这是 SQL 不支持的，办法就行不通了。

如果有窗口函数的支持，可以转换思路，按产品分组后，计算每个销售员在所有分组的前 10 名中出现的次数，若与产品总数相同，则表示该销售员在所有产品销售额中均前在前 10 名内。

select sales
from ( select sales,
     from ( select sales,
                   rank() over (partition by product order by amount desc ) ranking
            from sales_amount)
     where ranking <=10 )
group by sales
having count(*)=(select count(distinct product) from sales_amount)

这样的 SQL，有多少人会写呢？

况且，窗口函数在有些数据库中还不支持。那么，就只能用存储过程写循环依次计算每个产品的前 10 名，与上一次结果做交集。这个过程比用高级语言编写程序并不简单多少，而且仍然要面向临时表的繁琐。

现在，我们知道了 SQL 的第二个重要缺点：集合化不彻底。虽然 SQL 有集合概念，但并未把集合作为一种基础数据类型提供，这使得大量集合运算在思维和书写时都需要绕路。

我们在上面的计算中使用了关键字 top，事实上关系代数理论中没有这个东西（它可以被别的计算组合出来），这不是 SQL 的标准写法。

我们来看一下没有 top 时找前 10 名会有多困难？

大体思路是这样：找出比自己大的成员个数作为是名次，然后取出名次不超过 10 的成员，写出的 SQL 如下：

select sales
from ( select A.sales sales, A.product product,
             (select count(*)+1 from sales_amount
              where A.product=product AND A.amount<=amount) ranking
       from sales_amount A )
where product='AC' AND ranking<=10

或

select sales
from ( select A.sales sales, A.product product, count(*)+1 ranking
       from sales_amount A, sales_amount B
       where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount
       group by A.sales,A.product )
where product='AC' AND ranking<=10

这样的 SQL 语句，专业程序员写出来也未必容易吧！而仅仅是计算了一个前 10 名。

退一步讲，即使有 top，那也只是使取出前一部分轻松了。如果我们把问题改成取第 6 至 10 名，或者找比下一名销售额超过 10% 的销售员，困难仍然存在。

造成这个现象的原因就是 SQL 的第三个重要缺点：缺乏有序支持。

SQL 继承了数学上的无序集合，这直接导致与次序有关的计算相当困难，而可想而知，与次序有关的计算会有多么普遍（诸如比上月、比去年同期、前 20%、排名等）。

SQL2003 标准中增加的窗口函数提供了一些与次序有关的计算能力，这使得上述某些问题可以有较简单的解法，在一定程度上缓解 SQL 的这个问题。但窗口函数的使用经常伴随着子查询，而不能让用户直接使用次序访问集合成员，还是会有许多有序运算难以解决。

我们现在想关注一下上面计算出来的“好”销售员的性别比例，即男女各有多少。一般情况下，销售员的性别信息会记在花名册上而不是业绩表上，简化如下：


employee	员工表
name	员工姓名，假定无重名
gender	员工性别

我们已经计算出“好”销售员的名单，比较自然的想法，是用名单到花名册时找出其性别，再计一下数。但在 SQL 中要跨表获得信息需要用表间连接，这样，接着最初的结果，SQL 就会写成：

select employee.gender,count(*)
from employee,
    ( ( select top 10 sales from sales_amount where product='AC' order by amount desc )
    intersect
    ( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A
where A.sales=employee.name
group by employee.gender

仅仅多了一个关联表就会导致如此繁琐，而现实中信息跨表存储的情况相当多，且经常有多层。比如销售员有所在部门，部门有经理，现在我们想知道“好”销售员归哪些经理管，那就要有三个表连接了，想把这个计算中的 where 和 group 写清楚实在不是个轻松的活儿了。

这就是我们要说的 SQL 的第四个重要困难：缺乏对象引用机制，关系代数中对象之间的关系完全靠相同的外键值来维持，这不仅在寻找时效率很低，而且无法将外键指向的记录成员直接当作本记录的属性对待，试想，上面的句子可否被写成这样：

select sales.gender,count(*)
from (…) // …是前面计算“好”销售员的SQL
group by sales.gender

显然，这个句子不仅更清晰，同时计算效率也会更高（没有连接计算）。

我们通过一个简单的例子分析了 SQL 的四个重要困难，这也是 SQL 难写或要写得很长的主要原因。基于一种计算体系解决业务问题的过程，也就是将业务问题的解法翻译成形式化计算语法的过程（类似小学生解应用题，将题目翻译成形式化的四则运算）。SQL 的上述困难会造成问题解法翻译的极大障碍，极端情况就会发生这样一种怪现象：将问题解法形式化成计算语法的难度要远远大于解决问题本身。

再打个程序员易于理解的比方，用 SQL 做数据计算，类似于用汇编语言完成四则运算。我们很容易写出 3+5*7 这样的算式，但如果用汇编语言（以 X86 为例），就要写成

mov ax,3
mov bx,5
mul bx,7
addax,bx

这样的代码无论书写还是阅读都远不如 3+5*7 了（要是碰到小数就更要命了）。虽然对于熟练的程序员也算不了太大的麻烦，但对于大多数人而言，这种写法还是过于晦涩难懂了，从这个意义上讲，FORTRAN 确实是个伟大的发明。

为了理解方便，我们举的例子还是非常简单的任务。现实中的任务要远远比这些例子复杂，过程中会面临诸多大大小小的困难。这个问题多写几行，那个问题多写几行，一个稍复杂的任务写出几百行多层嵌套的 SQL 也就不奇怪了。而且这个几百行常常是一个语句，由于工程上的原因，SQL 又很难调试，这又进一步加剧了复杂查询分析的难度。

更多例子

我们再举几个例子来分别说明这几个方面的问题。

为了让例子中的 SQL 尽量简捷，这里大量使用了窗口函数，故而采用了对窗口函数支持较好的 ORACLE 数据库语法，采用其它数据库的语法编写这些 SQL 一般将会更复杂。
这些问题本身应该也算不上很复杂，都是在日常数据分析中经常会出现的，但已经很难为 SQL 了。

计算不分步

把复杂的计算分步可以在很大程度地降低问题的难度，反过来，把多步计算汇成一步完成则会提高问题的复杂度。

任务 1：销售部的人数，其中北京籍人数，再其中女员工人数？

销售部的人数

select count(*) from employee where department='sales'

其中北京籍的人数

select count(*) from employee where department='sales' and native_place='Beijing'

再其中的女员工人数

select count (*) from employee
where department='sales' and native_place='Beijing' and gender='female'

常规想法：选出销售部人员计数，再在其中找出其中北京籍人员计数，然后再递进地找出女员工计数。每次查询都基于上次已有的结果，不仅书写简单而且效率更高。

但是，SQL 的计算不分步，回答下一个问题时无法引用前面的成果，只能把相应的查询条件再抄一遍。

任务 2：每个部门挑选一对男女员工组成游戏小组

with A as
       (select name, department,
              row_number() over (partition by department order by 1) seq
        from employee where gender=‘male’)
     B as
        (select name, department,
              row_number() over(partition by department order by 1) seq
        from employee where gender=‘female’)
select name, department from A
where department in ( select distinct department from B ) and seq=1
union all
select name, department from B
where department in (select distinct department from A ) and seq=1

计算不分步有时不仅造成书写麻烦和计算低效，甚至可能导致思路严重变形。

这个任务的直观想法：针对每个部门循环，如果该部门有男女员工则各取一名添进结果集中。但 SQL 不支持这种逐步完成结果集的写法（要用存储过程才能实现此方案），这时必须转变思路为：从每个部门中选出男员工，从每个部门选出女员工，对两个结果集分别选出部门出现在另一个结果集的成员，最后再做并集。

好在还有 with 子句和窗口函数，否则这个 SQL 语句简直无法看了。

集合无序

有序计算在批量数据计算中非常普遍（取前 3 名 / 第 3 名、比上期等），但 SQL 延用了数学上的无序集合概念，有序计算无法直接进行，只能调整思路变换方法。

任务 3：公司中年龄居中的员工

select name, birthday
from (select name, birthday, row_number() over (order by birthday) ranking
      from employee )
where ranking=(select floor((count(*)+1)/2) from employee)

中位数是个常见的计算，本来只要很简单地在排序后的集合中取出位置居中的成员。但 SQL 的无序集合机制不提供直接用位置访问成员的机制，必须人为造出一个序号字段，再用条件查询方法将其选出，导致必须采用子查询才能完成。

任务 4：某支股票最长连续涨了多少交易日

select max (consecutive_day)
from (select count(*) (consecutive_day
      from (select sum(rise_mark) over(order by trade_date) days_no_gain
            from (select trade_date,
                         case when
                              closing_price>lag(closing_price) over(order by trade_date)
                         then 0 else 1 END rise_mark
                from stock_price) )
     group by days_no_gain)

无序的集合也会导致思路变形。

常规的计算连涨日数思路：设定一初始为 0 的临时变量记录连涨日期，然后和上一日比较，如果未涨则将其清 0，涨了再加 1，循环结束看该值出现的最大值。

使用 SQL 时无法描述此过程，需要转换思路，计算从初始日期到当日的累计不涨日数，不涨日数相同者即是连续上涨的交易日，针对其分组即可拆出连续上涨的区间，再求其最大计数。这句 SQL 读懂已经不易，写出来则更困难了。

集合化不彻底

毫无疑问，集合是批量数据计算的基础。SQL 虽然有集合概念，但只限于描述简单的结果集，没有将集合作为一种基本的数据类型以扩大其应用范围。

任务 5：公司中与其他人生日相同的员工

select * from employee
where to_char (birthday, ‘MMDD’) in
    ( select to_char(birthday, 'MMDD') from employee
      group by to_char(birthday, 'MMDD')
      having count(*)>1 )

分组的本意是将源集合分拆成的多个子集合，其返回值也应当是这些子集。但 SQL 无法表示这种“由集合构成的集合”，因而强迫进行下一步针对这些子集的汇总计算而形成常规的结果集。

但有时我们想得到的并非针对子集的汇总值而是子集本身。这时就必须从源集合中使用分组得到的条件再次查询，子查询又不可避免地出现。

任务 6：找出各科成绩都在前 10 名的学生

select name
from (select name
      from (select name,
                   rank() over(partition by subject order by score DESC) ranking
            from score_table)
      where ranking<=10)
group by name
havingcount(*)=(selectcount(distinctsubject)fromscore_table)

用集合化的思路，针对科目分组后的子集进行排序和过滤选出各个科目的前 10 名，然后再将这些子集做交集即可完成任务。但 SQL 无法表达“集合的集合”，也没有针对不定数量集合的交运算，这时需要改变思路，利用窗口函数找出各科目前 10 名后再按学生分组找出出现次数等于科目数量的学生，造成理解困难。

缺乏对象引用

在 SQL 中，数据表之间的引用关系依靠同值外键来维系，无法将外键指向的记录直接用作本记录的属性，在查询时需要借助多表连接或子查询才能完成，不仅书写繁琐而且运算效率低下。

任务 7：女经理的男员工们

用多表连接

select A.*
from employee A, department B, employee C
where A.department=B.department and B.manager=C.name and
A.gender='male' and C.gender='female'

用子查询

select * from employee
where gender='male' and department in
    (select department from department
     where manager in
          (select name from employee where gender='female'))

如果员工表中的部门字段是指向部门表中的记录，而部门表中的经理字段是指向员工表的记录，那么这个查询条件只要简单地写成这种直观高效的形式：

where gender='male' and department.manager.gender='female'

但在 SQL 中则只能使用多表连接或子查询，写出上面那两种明显晦涩的语句。

任务 8：员工的首份工作公司

用多表连接

select name, company, first_company
from (select employee.name name, resume.company company,
             row_number() over(partition by resume. name
                               order by resume.start_date) work_seq
      from employee, resume where employee.name = resume.name)
wherework_seq=1

用子查询

select name,
    (select company from resume
     where name=A.name and
           start date=(select min(start_date) from resume
                       where name=A.name)) first_company
from employee A

没有对象引用机制和彻底集合化的 SQL，也不能将子表作主表的属性（字段值）处理。针对子表的查询要么使用多表连接，增加语句的复杂度，还要将结果集用过滤或分组转成与主表记录一一对应的情况（连接后的记录与子表一一对应）；要么采用子查询，每次临时计算出与主表记录相关的子表记录子集，增加整体计算量（子查询不能用 with 子句了）和书写繁琐度。