AdventOfCode 2022 Day 8

作者

Day 8: Treetop Tree House

Part One

每个数字代表树的高度，需要找出所有从外侧能够观测到的树的数量

30373
25512
65332
33549
35390

从外围能够观测到，代表从上下左右四个方向上，仍一方向上其他的树高度低于目标，则可以被观测到，因此只需要自关联，找出某方向上满足要求的树。

比如从左侧观察

SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col > y._col
AND y.num >= x.num
AND x._row = y._row
group by x._row, x._col 
having count(y._col) = 0

从上侧观察

SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col = y._col
AND y.num >= x.num
AND x._row > y._row
group by x._row, x._col 
having count(y._row) = 0

整体SQL

with origin AS (
  SELECT _row, row_number() over(partition by _row) as _col , num
  FROM (
    SELECT row_number() over() :: integer as _row,  regexp_split_to_table(line, '')::integer as num
    FROM lance_input
  ) t
)
SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col > y._col
AND y.num >= x.num
AND x._row = y._row
group by x._row, x._col 
having count(y._col) = 0
UNION
SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col < y._col
AND y.num >= x.num
AND x._row = y._row
group by x._row, x._col 
having count(y._col) = 0
UNION
SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col = y._col
AND y.num >= x.num
AND x._row > y._row
group by x._row, x._col 
having count(y._row) = 0
UNION
SELECT x._row, x._col
FROM origin x 
LEFT JOIN origin y
ON x._col = y._col
AND y.num >= x.num
AND x._row < y._row
group by x._row, x._col 
having count(y._row) = 0

Part Two

计算某一个位置上，上下左右能观测到几棵树。也就是求出某个方向上，最多连续几棵树高度不高于此位置的高度。

从左侧观察

 l AS (
  SELECT x._row, x._col, x._col - coalesce(max(y._col), 1) as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col > y._col
  AND y.num >= x.num
  AND x._row = y._row
  group by x._row, x._col 
)

从下侧观察

 d AS (
  SELECT x._row, x._col, coalesce(min(y._row), 99) - x._row as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col = y._col
  AND y.num >= x.num
  AND x._row < y._row
  group by x._row, x._col 
)

完整SQL

with origin AS (
  SELECT _row, row_number() over(partition by _row) as _col , num
  FROM (
    SELECT row_number() over() :: integer as _row,  regexp_split_to_table(line, '')::integer as num
    FROM lance_input
  ) t
), l AS (
  SELECT x._row, x._col, x._col - coalesce(max(y._col), 1) as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col > y._col
  AND y.num >= x.num
  AND x._row = y._row
  group by x._row, x._col 
), r AS (
  SELECT x._row, x._col, coalesce(min(y._col), 99) - x._col as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col < y._col
  AND y.num >= x.num
  AND x._row = y._row
  group by x._row, x._col 
), u AS (
  SELECT x._row, x._col, x._row - coalesce(max(y._row), 1) as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col = y._col
  AND y.num >= x.num
  AND x._row > y._row
  group by x._row, x._col 
), d AS (
  SELECT x._row, x._col, coalesce(min(y._row), 99) - x._row as seen
  FROM origin x 
  LEFT JOIN origin y
  ON x._col = y._col
  AND y.num >= x.num
  AND x._row < y._row
  group by x._row, x._col 
)
SELECT l._row, l._col, l.seen * r.seen * u.seen * d.seen
FROM l JOIN r
ON l._row = r._row
AND l._col = r._col
JOIN u
ON l._row = u._row
AND l._col = u._col
JOIN d
ON l._row = d._row
AND l._col = d._col
order by 3 desc limit 3;

发表评论