Part One
输入如下，需要上下左右与符号相邻的数字
467..114.....*........35..633.......#...
Find Surrounding Characters
通过开窗函数，获取前后行
SELECT row_number() over () as rn, line,
lag(line) over () as pre_line,
lead(line) over () as next_line,
0 as last_index, 0 as num, null, null, null, null
FROM lance_input
通过strpos，定位上(pre)、下(next)、左(left)、右(right)的字符串
case when pre_line is not null THEN substring(pre_line, line_pos - 1, length(num) + 2) end as p,
case when next_line is not null THEN substring(next_line, line_pos - 1, length(num) + 2) end as n,
substring(line, line_pos - 1, 1) as l,
substring(line, line_pos + length(num), 1) as r
Final SQL
WITH RECURSIVE result(rn, line, pre_line, next_line, last_index, num, p, n, l, r) AS (
SELECT row_number() over () as rn, line,
lag(line) over () as pre_line,
lead(line) over () as next_line,
0 as last_index, 0 as num, null, null, null, null
FROM lance_input
UNION ALL
SELECT rn, line, pre_line, next_line, line_pos + length(num) - 1, num::integer,
case when pre_line is not null THEN substring(pre_line, line_pos - 1, length(num) + 2) end as p,
case when next_line is not null THEN substring(next_line, line_pos - 1, length(num) + 2) end as n,
substring(line, line_pos - 1, 1) as l,
substring(line, line_pos + length(num), 1) as r
FROM (
SELECT rn, strpos(substring(line from last_index + 1), num) + last_index as line_pos, line, pre_line, next_line, num
FROM (
SELECT rn, (regexp_matches(substring(line from last_index + 1), '[0-9]+'))[1] as num, line, pre_line, next_line, last_index
FROM result
WHERE substring(line from last_index + 1) ~ '[0-9]+'
) r
) t
)
SELECT SUM(num) FROM result WHERE p ~ '[^0-9.]+' OR n ~ '[^0-9.]+' OR l ~ '[^0-9.]+' OR r ~ '[^0-9.]+'
Part Two
寻找与*相邻的数字乘积之和，比如467 * 35
467..114.....*........35..633.......#...
Find numbers sharing same gear
寻找p,n,l,r中含有*的num，并定位*的具体行号+列号，从而确定唯一的*
SELECT rn, num, last_index,
unnest(array[case when p like '%*%' then (rn - 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in p) - 1) end,
case when n like '%*%' then (rn + 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in n) - 1) end,
case when l = '*' then rn || '|' || last_index - length(num::text) end,
case when r = '*' then rn || '|' || last_index + 1 end]
) as gear
FROM result
WHERE p like '%*%' OR n like '%*%' OR l = '*' OR r = '*'
Possible Traps
是否一个*会有超过2个相邻数字？
SELECT gear, (array_agg(num))[1], (array_agg(num))[2]
FROM (
SELECT rn, num, last_index,
unnest(array[case when p like '%*%' then (rn - 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in p) - 1) end,
case when n like '%*%' then (rn + 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in n) - 1) end,
case when l = '*' then rn || '|' || last_index - length(num::text) end,
case when r = '*' then rn || '|' || last_index + 1 end]
) as gear
FROM result
WHERE p like '%*%' OR n like '%*%' OR l = '*' OR r = '*'
) t
WHERE gear is not null
GROUP BY gear having count(*) > 2;
gear | array_agg | array_agg
------+-----------+-----------
(0 rows)
是否一个数字会有两个相邻*？
SELECT rn, num, last_index
FROM (
SELECT rn, num, last_index,
unnest(array[case when p like '%*%' then (rn - 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in p) - 1) end,
case when n like '%*%' then (rn + 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in n) - 1) end,
case when l = '*' then rn || '|' || last_index - length(num::text) end,
case when r = '*' then rn || '|' || last_index + 1 end]
) as gear
FROM result
WHERE p like '%*%' OR n like '%*%' OR l = '*' OR r = '*'
) t
WHERE gear is not null
group by rn, num, last_index having count(*) > 1;
rn | num | last_index
----+-----+------------
(0 rows)
Final SQL
WITH RECURSIVE result(rn, line, pre_line, next_line, last_index, num, p, n, l, r) AS (
SELECT row_number() over () as rn, line,
lag(line) over () as pre_line,
lead(line) over () as next_line,
0 as last_index, 0 as num, null, null, null, null
FROM lance_input
UNION ALL
SELECT rn, line, pre_line, next_line, line_pos + length(num) - 1, num::integer,
case when pre_line is not null THEN substring(pre_line, line_pos - 1, length(num) + 2) end as p,
case when next_line is not null THEN substring(next_line, line_pos - 1, length(num) + 2) end as n,
substring(line, line_pos - 1, 1) as l,
substring(line, line_pos + length(num), 1) as r
FROM (
SELECT rn, strpos(substring(line from last_index + 1), num) + last_index as line_pos, line, pre_line, next_line, num
FROM (
SELECT rn, (regexp_matches(substring(line from last_index + 1), '[0-9]+'))[1] as num, line, pre_line, next_line, last_index
FROM result
WHERE substring(line from last_index + 1) ~ '[0-9]+'
) r
) t
)
SELECT sum(nums[1] * nums[2])
FROM (
SELECT gear, array_agg(num) nums
FROM (
SELECT rn, num, last_index,
unnest(array[case when p like '%*%' then (rn - 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in p) - 1) end,
case when n like '%*%' then (rn + 1) || '|' || (greatest(last_index - length(num::text), 1) + position('*' in n) - 1) end,
case when l = '*' then rn || '|' || last_index - length(num::text) end,
case when r = '*' then rn || '|' || last_index + 1 end]
) as gear
FROM result
WHERE p like '%*%' OR n like '%*%' OR l = '*' OR r = '*'
) t
WHERE gear is not null
GROUP BY gear
HAVING count(*) = 2
) t;