Part One
987654321111111
从一个较长的字符串中，挑选两个数字，不改变顺序，组成最大的两位数，比如这里的98
可以先求出十位数的数字，再求出个位数的数字
- 十位数，就是未末尾的最大最靠前的数字
- 个位数，就是排在十位数后面的最大数字
with origin as (
SELECT row_number() over () as rn, line, length(line) as len FROM lance_input
), splits as (
SELECT rn, len, x.pos :: integer as num, idx
FROM origin, regexp_split_to_table(line, '') with ordinality as x(pos, idx)
), first_num AS (
SELECT rn, row_number() over (partition by rn order by case when idx != len then num else -1 end desc, idx) as first_rk, num, idx
FROM splits
), second_num AS (
SELECT rn, first_rk, row_number() over (partition by rn order by case when idx > t.first_idx then num else -1 end desc) as second_rk, num, idx
FROM first_num
JOIN (SELECT rn as first_rn, max(case when first_rk = 1 then idx end) as first_idx FROM first_num GROUP BY rn) t
ON rn = t.first_rn
), filter_num AS (
SELECT rn, first_rk, second_rk, num
FROM second_num
WHERE first_rk = 1
OR second_rk = 1
)
SELECT sum(num)
FROM (
SELECT rn, concat(max(case when first_rk = 1 then num end), max(case when second_rk = 1 then num end)) :: integer as num
FROM filter_num
GROUP BY rn
) t;
Part Two
987654321111111
挑选出12个数字，不改变顺序，组成最大的数字，比如这里的987654321111
这里不可以再用之前的办法了，但是思路是相似的
- 先找出第一位数字，最大最靠前
- 再找出第一位数字后面的最大最靠前
- …
\underbrace{787955955}_{\color{red}\text{当前计算段}} \
\underbrace{43232344}_{\color{blue}\text{预留段}}将长串数字分隔为两段
- 当前计算段，在这一段中，仅需要找出最大最靠前的数字即可
- 预留段，必须保留的数字，比如还剩余12个数字要计算，则需要保留11个数字
上面的例子中，计算出了第一个9，则新的两段数字串则变成了
\underbrace{559554}_{\color{red}\text{当前计算段}} \
\underbrace{3232344}_{\color{blue}\text{预留段}}
with recursive origin AS (
SELECT row_number() over () as rn, line, length(line) as len FROM lance_input
), walkthrough AS (
SELECT 0 as num, 12 as left_nums, rn, line, len
FROM origin
UNION ALL
SELECT num, left_nums, rn, line, len
FROM (
with inner_tbl as (
select * From walkthrough where left_nums > 0
), splits as (
SELECT left_nums, rn, len, x.pos :: integer as num, idx :: integer as idx
FROM inner_tbl, regexp_split_to_table(line, '') with ordinality as x(pos, idx)
), max_valid as (
SELECT rn, num, idx
FROM (
SELECT rn, num, idx, row_number() over (partition by rn order by num desc, idx) as num_rk
FROM (
SELECT rn, num, idx
FROM splits
WHERE idx <= len - left_nums + 1
) t
) t
WHERE num_rk = 1
)
SELECT b.num, left_nums - 1 as left_nums, a.rn, substring(line, b.idx + 1) as line, a.len - b.idx as len
FROM inner_tbl a
JOIN max_valid b
ON a.rn = b.rn
WHERE a.left_nums > 0
) t
)
select sum(num) from (
select rn,string_agg(num::text, '' order by left_nums desc) :: bigint as num
from walkthrough
where left_nums < 12
group by rn
) t;
将12换成2，同样可以计算出第一部分的结果。