苏旭晖先生写的纯SQL求解Advent of Code 2025第9题 最大矩形面积 第2部分

原贴地址，我把它改成了能在DuckDB运行，主要是把connect by level 改为 range函数

-- 去除match_recognize, 就是合并连续区间的老套路：



with day9 as(select row_number()over()id,t.* from read_csv('2509-input.txt',header=0)t(x,y) ),
 v as (select day9.*,count(*) over() as cnt from day9)
,s as (----------轮廓线段
select d1.id as sid
      ,d1.cnt
      ,case when d1.x=d2.x then 'X' else 'Y' end as s_direction
      ,case when d1.x=d2.x then d1.x else d1.y end as s_val
      ,case when d1.x=d2.x then least(d1.y,d2.y) else least(d1.x,d2.x) end as s_v1
      ,case when d1.x=d2.x then greatest(d1.y,d2.y) else greatest(d1.x,d2.x) end as s_v2
  from v d1,v d2
where d2.id=d1.id+1
       or (d2.id=1 and d1.id=d2.cnt)
)
,ss as (  
------------ s1 和 s2分别是轮廓线s在起点和终点折了一次之后再回到和s平行的另外两端轮廓线（所以sid会间隔）。
----------- 如果S和S1(或S和S2)构成Z字形则，则方向没有改变，在和那个端点的垂直线相交时，两段只算一段(通过least使得这两段值一样，dense_rank()把它们分到一组)
----------- 如果S和S1(或S和S2)构成U字形则，则方向改变，分别算做两段，所以各自保留自己的s_val
select s.*
      ,case when s.s_v1 in (s1.s_v1,s2.s_v1) then s.s_val
            when s.s_v1 = s1.s_v2 then least(s.s_val,s1.s_val)
            when s.s_v1 = s2.s_v2 then least(s.s_val,s2.s_val)
       end as s_val1  ----- 当s的v1端点落在垂直线上时，用这个代替s_val来排序
      ,case when s.s_v2 in (s1.s_v2,s2.s_v2) then s.s_val
            when s.s_v2 = s1.s_v1 then least(s.s_val,s1.s_val)
            when s.s_v2 = s2.s_v1 then least(s.s_val,s2.s_val)
       end as s_val2 ----- 当s的v2端点落在垂直线上时，用这个代替s_val来排序
   from s,s s1, s s2
  where case when s.sid>s.cnt-2 then s.sid+2-s.cnt else s.sid+2 end=s1.sid
        and case when s.sid<3 then s.sid-2+s.cnt else s.sid-2 end=s2.sid
)
,e2 as (
select s_direction,s_val,s_v1,s_v2
      ,CASE WHEN s_v1 <= MAX(s_v2) OVER (PARTITION BY s_direction,s_val order by s_v1,s_v2 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)+1 then 0 else 1 end as flag
from (
      select s_direction,s_val,min(val2) s_v1,max(val2) s_v2
        from (
              select a.s_direction,a.s_val,ss.s_val val2
                    ,dense_rank() over(partition by a.s_direction,a.s_val
                                       order by case a.s_val
                                                     when ss.s_v1 then ss.s_val1
                                                     when ss.s_v2 then ss.s_val2
                                                     else ss.s_val
                                                end) rn
               from (select distinct s_direction,s_val from s) a
                    join ss on a.s_direction<>ss.s_direction and a.s_val between ss.s_v1 and ss.s_v2
             )
      group by s_direction,s_val,ceil(rn/2)
     union all
     select s_direction,s_val,s_v1,s_v2 from s
     )
)
,e as (
select /*+ materialize */ s_direction,s_val,min(s_v1) s_v1, max(s_v2) s_v2
from (
  select e2.*,sum(flag) over(partition by s_direction,s_val order by s_v1) grp
  from e2
      )
group by s_direction,s_val,grp
)
,r as (------- 找出所有矩形，并拆成四条边
select d1.x x1,d1.y y1,d2.x x2,d2.y y2
      ,case when n in (1,2) then 'X' else 'Y' end as r_direction
      ,case n when 1 then least(d1.x,d2.x)
              when 2 then greatest(d1.x,d2.x)
              when 3 then least(d1.y,d2.y)
              when 4 then greatest(d1.y,d2.y)
       end as r_val
      ,case when n in (1,2) then least(d1.y,d2.y) else least(d1.x,d2.x) end as r_v1
      ,case when n in (1,2) then greatest(d1.y,d2.y) else greatest(d1.x,d2.x) end as r_v2
  from day9 d1,day9 d2,(select i n from range(1, 5)t(i))
where d1.id<d2.id and d1.x<>d2.x and d1.y<>d2.y
)
select * from (
select x1,y1,x2,y2,(abs(x1-x2)+1)*(abs(y1-y2)+1) area
  from r
where exists (select 1
                 from e
                where r.r_direction=e.s_direction
                      and r.r_val=e.s_val
                      and r.r_v1 between e.s_v1 and e.s_v2
                      and r.r_v2 between e.s_v1 and e.s_v2
              )
group by x1,y1,x2,y2
having count(*)=4  ----- 四条边都在多边形内
)
order by area desc limit 1;
-- fetch  first row with ties;

这个版本比调用spatial插件的版本快了10倍。