Part One
初始数字经过2000轮的特定处理
将处理逻辑包装为一个函数
CREATE OR REPLACE FUNCTION calculate(secret_number bigint)
RETURNS BIGINT AS $$
DECLARE
result BIGINT;
current_secret BIGINT;
BEGIN
current_secret := (secret_number * 64) # secret_number;
current_secret := current_secret % 16777216;
current_secret := (current_secret / 32) # current_secret;
current_secret := current_secret % 16777216;
current_secret := (current_secret * 2048) # current_secret;
current_secret := current_secret % 16777216;
RETURN current_secret;
END;
$$ LANGUAGE plpgsql;
仔细观察下这些计算过程，其实都是简单的位移操作，16777216是2的24次方
CREATE OR REPLACE FUNCTION calculate(secret_number bigint)
RETURNS BIGINT AS $$
DECLARE
result BIGINT;
current_secret BIGINT;
BEGIN
current_secret := (secret_number << 6) # secret_number;
current_secret := current_secret & 16777215;
current_secret := (current_secret >> 5) # current_secret;
current_secret := current_secret & 16777215;
current_secret := (current_secret << 11) # current_secret;
current_secret := current_secret & 16777215;
RETURN current_secret;
END;
$$ LANGUAGE plpgsql;
再计算2000轮后的结果即可
with recursive result AS (
SELECT 0 as step, line :: BIGINT as id
FROM lance_input
UNION ALL
SELECT step + 1 as step, calculate(id) as id
FROM result
WHERE step < 2000
)
select sum(id) From result WHERE step = 2000;
Part Two
从每个数字的2000轮结果中，找出特定的diff序列，求出这些diff序列第一次出现后的结果之和。
with recursive result AS (
SELECT row_number() over () as seq, 0 as step, line :: BIGINT as id
FROM lance_input
UNION ALL
SELECT seq, step + 1 as step, calculate(id) as id
FROM result
WHERE step <= 2000
), prices AS (
SELECT seq, step, id, price, price - lag(price) over (partition by seq order by step) as diff
FROM (
SELECT seq, step, id, right(id :: text, 1) :: integer as price
FROM result
) t
), price_list AS (
SELECT seq, step, id, price,
lag(diff, 3) over (partition by seq order by step) as pre_3_diff,
lag(diff, 2) over (partition by seq order by step) as pre_2_diff,
lag(diff, 1) over (partition by seq order by step) as pre_1_diff,
diff
FROM prices
)
SELECT sum(price) FROM (
SELECT seq, pre_3_diff, pre_2_diff, pre_1_diff, diff, price,
row_number() over (partition by seq, pre_3_diff, pre_2_diff, pre_1_diff, diff order by step) as rn
FROM price_list
WHERE pre_3_diff is not null
AND pre_2_diff is not null
AND pre_1_diff is not null
AND diff is not null
) t
WHERE rn = 1
GROUP BY pre_3_diff, pre_2_diff, pre_1_diff, diff
ORDER BY 1 desc
LIMIT 3;