[译]一次用SQL处理字符串的艰难探索

原文链接：https://www.endpointdev.com/blog/2022/02/sql-the-hard-way/
原文作者：Josh Tolley

  当我还是个孩子的时候，我和我的家人们一起去参观了一个博览馆，原来是史前的居民的居住地，后面经过了重建。
  这里的房子都建在悬崖上面的洞穴里面，虽然我是沿着一条标记的很清楚的路去参观的，但是原始居民还是通过岩壁上的洞爬进爬出。
  显然是为了阻止不受欢迎的游客，建造者小心地将落脚点隔开，登山者需要正确的路线去走上去。如果有人不知道这个路线或者忘记了，可能当他们爬到一半的时候发现无法到达下一个落脚点，只能往回走了。

  前几天我遇到一个问题，我需要在数据库中完成一个操作：首先我有一个逗号分隔的字符串列表，我需要在一定长度范围内从每个列表中找到最长的字符串，格式是以字符开头，以逗号结尾。
  如果你有一些SQL的编程经验，你可能说“小菜一碟啦”。事实上你是对的，除了我这种情况，我刚开始的时候就把自己钻进了死胡同，跟开头我故事里面的原始居民一样，从一开始就走错了路。

  接下来便是我艰难探索的历程了。
  我的习惯或者其他使得我的思路是想把列表划分成组成列表的每个元素，再逐个用这些单独的元素去构建结果集，最后在所有的结果集里面找到在我想要的长度范围内的最长的一个结果集。
  不过还好，我这个思路这里我没办法去对我划分的单个元素重新排序，不然我还在这个死胡同里没出来呢。
  以下是我最终构造的语句：

select
    length(c), c
from (
    select
        concat_ws(', ', variadic array_agg(f) over (rows between unbounded preceding and current row)) c
    from (
        select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+') as f
    ) f
) c
where length(c) < 30
order by length(c) desc nulls last
limit 1;

  这里最内层的子查询，基于regexp_split_to_table函数，将输入字符串划分为其组件元素的有序列表:

select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+');
 regexp_split_to_table
-----------------------
 alpha
 bravo
 charlie
 delta
 echo
 fox trot
(6 rows)

  我总是习惯在我的SQL里面用到window function，这里我们将使用frame子句。
  frame_clause指定构成窗口框架的行集，它是当前分区的子集，用于那些作用于框架而不是整个分区的窗口函数。框架中的行集可以根据当前行是哪一行而有所不同。我在这里简要总结一下与此查询相关的内容，了解更多细节可以参考PostgreSQL文档。
  我们可以设置frame_clause为rows between unbounded preceding and current row，意味着给定行的窗口框架应该包括从分区的第一行到当前行的所有行。 假设我们在窗口定义中没有按子句order by，像“first row”指的是按照提供给windowwagg执行器节点的行顺序的第一行。 如果没有特定的order by子句，那么能否按照这种顺序保持呢 ？ 但是为了实现我的目标，我需要保留regexp_split_to_table返回的元素顺序。默认的窗口框架子句是range unbounded previous，注意这是range unbounded previous和current row之间的缩写形式，没有特定的order by子句，这意味着窗口框架包含分区中的所有行。 作为一个有趣的练习，将这两个结果进行比较，它们的不同之处仅在于是否有order by:

select array_agg(f) over () c
    from (
        select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+') as word
    ) f
;
                              c
-------------------------------------------------------------
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
(6 rows)

select array_agg(f) over (order by word) c
    from (
        select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+') as word
    ) f
;
                              c
-------------------------------------------------------------
 {(alpha)}
 {(alpha),(bravo)}
 {(alpha),(bravo),(charlie)}
 {(alpha),(bravo),(charlie),(delta)}
 {(alpha),(bravo),(charlie),(delta),(echo)}
 {(alpha),(bravo),(charlie),(delta),(echo),"(\"fox trot\")"}
(6 rows)

  不管怎样，到这里我发现这是一个切入点，虽然默认的window frame容易出现问题。视线重新回到我的复杂查询上，每个重新构建的结果集，即字符串集比上一个多包含一个元素，及字符串。
  我计划使用concat_ws函数把我的包含元素的数组转换为字符串，同时利用frame子句可以让我传递数组值，并单独处理：

select
    concat_ws(', ', variadic array_agg(f) over (rows between unbounded preceding and current row)) c
from (
    select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+') as f
) f
;
                      c
----------------------------------------------
 alpha
 alpha, bravo
 alpha, bravo, charlie
 alpha, bravo, charlie, delta
 alpha, bravo, charlie, delta, echo
 alpha, bravo, charlie, delta, echo, fox trot

  这里我们已经很接近答案了，已经重组了多个结果集，最后一步是按照长度选择出最长的一个：

select
    length(c), c
from (
    select
        concat_ws(', ', variadic array_agg(f) over (rows between unbounded preceding and current row)) c
    from (
        select regexp_split_to_table('alpha, bravo, charlie, delta, echo, fox trot', ',\s+') as f
    ) f
) c
where length(c) < 30
order by length(c) desc nulls last
limit 1
;
 length |              c
--------+------------------------------
     28 | alpha, bravo, charlie, delta
(1 row)

  成功！只比实际需要多走了一百多步！ 其实，在我完成这个查询后不久，我就意识到我写的过于复杂了。
  我把它发布到我们公司的内部聊天中，几分钟后，我的同事给出了他的答案：

SELECT regexp_matches(substring('alpha, bravo, charlie, delta, echo, fox trot' from 1 for 30), '(.*),');
          regexp_matches
----------------------------------
 {"alpha, bravo, charlie, delta"}
(1 row)

  仅仅需要从输入文本中找到一个范围长度内的子字符串，并删除最后一个逗号之后的所有内容，只需要30s就可以完成，而我在构造我的复杂查询中花费了半个小时甚至更多。
  尽管如此，我们从自己的错误和别人的错误中学到了很多，这是事实，我希望你们在这个案例中也是如此。