三行五行的 SQL 只存在于教科书和培训班

2024-02-29 16:43:47 浏览数 (1)

教科书中 SQL 例句通常都很简单易懂，甚至可以当英语来读，这就给人造成 SQL 简单易学的印象。 但实际上，这种三行五行的 SQL 只存在于教科书和培训班，我们在现实业务中写的 SQL 不会论行，而是以 K 计的，一条 SQL 几百行 N 层嵌套，写出 3K5K 是常事，这种 SQL，完全谈不上简单易学，对专业程序员都是恶梦。 以 K 计本身倒不是大问题，需求真地复杂时，也只能写得长，Python/Java 代码可能会更长。但 SQL 的长和其它语言的长不一样，SQL 的长常常会意味着难写难懂，而且这个难写难懂和任务复杂度不成比例。除了一些最简单情况外，稍复杂些的任务，SQL 的难度就会陡增，对程序员的智商要求很高，所以经常用作应聘考题。

这是为什么呢？ 其中一个原因是我们之前讲过的，SQL 像英语而缺乏过程性，要把很多动作搅合在一句中，凭空地增大思维难度。 但是我们会发现，即使 SQL 增加了步骤化的 CTE 语法，面对稍复杂的任务时，仍然会写的非常难懂。 这是因为，SQL 的描述能力还有不少重要的缺失，这导致程序员不能按自然思维写代码，要换着方法绕。 我们通过一个简单的例子来看一下。

简化的销售业绩表 T 有三个字段：sales 销售员，product 产品，amount 销售额。我们想知道空调和电视销售额都在前 10 名的销售员名单。 这个问题并不难，可以很自然地设计出计算过程： 1．按空调销售额排序，找出前 10 名； 2．按电视销售额排序，找出前 10 名； 3．对 1、2 的结果取交集，得到我们想要的

用 CTE 语法后 SQL 可以写成这样：

代码语言：javascript复制
with A as (select top 10 sales from T where product='AC' order by amount desc),
     B as (select top 10 sales from T where product='TV' order by amount desc)
select * from A intersect B
with A as (select top 10 sales from T where product='AC' order by amount desc),
     B as (select top 10 sales from T where product='TV' order by amount desc)
select * from A intersect B

这个句子不太短，但思路还是清晰的。

现在，我们把问题复杂化一点，改为计算所有产品销售额都在前 10 名的销售员，延用上述的思路很容易想到： 1. 列出所有产品； 2. 算出每种产品销售额的前 10 名，分别保存； 3. 针对这些前 10 名取交集； 遗憾开始出现，CTE 语法只能写出确定个数的中间结果。而我们事先不知道总共有多个产品，也就是说 WITH 子句的个数是不确定的，这就写不出来了。 好吧，换一种思路： 1．将数据按产品分组，将每组排序，计算出每组前 10 名； 2．针对这些前 10 名取交集； 这需要把第一步的分组结果保存起来，而这个中间结果是一个表，其中有个字段要存储对应的分组成员的前 10 名，也就是字段的取值将是个集合，SQL 不支持这种数据类型，还是写不出来。

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

代码语言：javascript复制
select sales from ( 
    select sales from (
        select sales, rank() over (partition by product order by amount desc ) ranking
        from T ) where ranking <=10 )
group by sales having count(*)=(select count(distinct product) from T)
select sales from ( 
    select sales from (
        select sales, rank() over (partition by product order by amount desc ) ranking
        from T ) where ranking <=10 )
group by sales having count(*)=(select count(distinct product) from T)

在窗口函数支持下，终于能写出来了。但是，这样的思路，绕不绕呢，有多少人想到并写出来呢？ 前两种简单的思路无法用 SQL 实现，只能采用第三种迂回的思路。这里的原因在于 SQL 的一个重要缺失：集合化不彻底。 SQL 有集合概念，但并未把集合作为一种基础数据类型提供，不允许字段取值是集合，除了表之外也没有其它集合形式的数据类型，这使得大量集合运算在思维和书写时都非常绕。

我们刚才用了关键字 top，事实上关系代数理论中没有这个东西，这不是 SQL 的标准写法。 没有 top 如何找前 10 名呢？ 大体思路是这样：找出比自己大的成员个数作为是名次，然后取出名次不超过 10 的成员

代码语言：javascript复制
select sales from (
    select A.sales sales, A.product product,
        (select count(*) 1 from T
         where A.product=product and A.amount<=amount) ranking
    from T A )where product='AC' and ranking<=10
select sales from (
    select A.sales sales, A.product product,
        (select count(*) 1 from T
         where A.product=product and A.amount<=amount) ranking
    from T A )where product='AC' and ranking<=10

注意，这里的子查询没办法用 CTE 语法分步写，因为它用到了主查询中的信息作为参数。

或可以用连接来写，这样子查询倒是可以用 CTE 语法分步了：

代码语言：javascript复制
select sales from (
    select A.sales sales, A.product product, count(*) 1 ranking from T A, T 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
select sales from (
    select A.sales sales, A.product product, count(*) 1 ranking from T A, T 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

无论如何，这种东西都太绕了，专业程序员也要想一阵子，仅仅是计算了一个前 10 名。

造成这个现象的原因就是 SQL 的另一个缺失：缺乏有序支持。SQL 继承了数学上的无序集合，与次序有关的计算相当困难，而可想而知，与次序有关的计算会有多么普遍（诸如比上月、比去年同期、前 20%、排名等）。 SQL2003 标准中增加的窗口函数提供了一些与次序有关的计算能力，这在一定程度上缓解 SQL 有序计算的困难，前 10 名可以这样写：

代码语言：javascript复制
select sales from ( 
    select sales, rank() over (partition by product order by amount desc ) ranking
    from T )
where ranking <=10
select sales from ( 
    select sales, rank() over (partition by product order by amount desc ) ranking
    from T )
where ranking <=10

还是要用子查询。

窗口函数并没有根本改变 SQL 无序集合的基础，还是会有许多有序运算难以解决。比如我们经常用来举例的，计算一支股票最长连续上涨了多少天：

代码语言：javascript复制
select max(ContinuousDays) from (
    select count(*) ContinuousDays from (
        select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
            select TradeDate,case when Price>lag(price) over ( order by TradeDate) then 0 else 1 end UpDownTag from Stock ))
    group by NoRisingDays )
select max(ContinuousDays) from (
    select count(*) ContinuousDays from (
        select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
            select TradeDate,case when Price>lag(price) over ( order by TradeDate) then 0 else 1 end UpDownTag from Stock ))
    group by NoRisingDays )

自然思维是这样，按日期排序后开始计数，碰到涨了就加 1，跌了就清 0，看计数器最大计到几。但这个思路写不出 SQL，只能绕成这样多层嵌套的。 这个问题真地是当作应聘考题的，通过率不到 20%。

这么一个简单的例子就能暴露出 SQL 缺失的能力，SQL 缺失的内容还有更多，限于篇幅，这里就不再深入讨论了。 反正结果就是，SQL 实现查询时无法应用自然思路，经常需要绕路迂回，写得又长又难懂。 现实任务要远远比这些例子复杂，过程中会面临诸多大大小小的困难。这个问题绕一下，那个问题多几行，一个稍复杂的任务写出几百行多层嵌套的 SQL 也就不奇怪了，过两月自己也看不懂也不奇怪了。 事实上 SQL 一点也不容易。

SQL 很难写怎么办？用 esProc SPL! esProc SPL 是个 Java 写的开源软件，在这里https://github.com/SPLWare/esProc。 SPL 在 SQL 已有的集合化基础上增加了离散性，从而获得了彻底的集合化和有序能力，上面的例子就 SPL 就可以延用自然思路写出来： 所有产品销售额都在前 10 名的销售员，按产品分组，取每个组的前 10 名再算交集；

代码语言：javascript复制
T.group(product).(~.top(10;-amount)).isect()
T.group(product).(~.top(10;-amount)).isect()

SPL 支持集合的集合，top 也只是常规的聚合计算，有了这些基础，实现自然思路很容易。 一支股票最长连续上涨了多少天，只要按自然思路写就行了

代码语言：javascript复制
cnt=0
Stock.sort(TradeDate).max(cnt=if(Price>Price[-1],cnt 1,0))
cnt=0
Stock.sort(TradeDate).max(cnt=if(Price>Price[-1],cnt 1,0))

SPL 有强大的有序计算能力，即使实现和上面 SQL 同样的逻辑也非常轻松：

代码语言：javascript复制
Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())
Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())
高性能计算 开源 java sql

0 人点赞