趣味SQL题：力扣84. 柱状图中最大的矩形

〇〇

newkid 发表于 2024-8-28 22:06
你这个写法确实能拓展思路，可是最后那个CONNECT BY 不就局限于整数了吗？我给你改一下：select   lv as he ...

这个在鸭子里不行
Binder Error: correlated columns in window functions not supported
LINE 7:          select height ,sum(case when height>=lv then 0 else 1 end) over (order by id ) as grp


Binder Error: aggregate function calls cannot be nested
LINE 6: select max(count(*))

〇〇

postgresql也可以


select
   lv as height_lv
  ,(
    select max(group_cnt) from
        (
        select count(case when height>=lv then 1 end) over (partition by grp) group_cnt
        from
        (
         select height ,sum(case when height>=lv then 0 else 1 end) over (order by id ) as grp
         from rectangle a  
        )b
        )c
   )*lv as max_rectangle
from
(
select lv  
from generate_series(1,10,1) t(lv)
)d;

〇〇

select
   lv as height
  ,(
    select max(count(*))
    from
        (
         select height ,sum(case when height>=lv then 0 else 1 end) over (order by id ) as grp
         from rectangle a  
        )b
    where height>=lv
    group by grp
   )
   *lv as max_rectangle
from
(
select distinct height as lv  
from rectangle
)d;
ERROR:  aggregate function calls cannot be nested
LINE 4:     select max(count(*))
                       ^

〇〇

不用分析函数的版本
with t as
(
select left1,width1,min(height)min_height
from
rectangle a,range(1,7)t(left1),range(1,7)t1(width1)
where left1+width1-1<=6
and id between left1 and left1+width1-1
group by left1,width1
)
select *,min_height*width1 from t where min_height*width1 = (select max(min_height*width1)from t);

〇〇

用下面的表测试，2位的sql比我的效率高多了
create table rectangle as select i id, (random()*99)::int+1 height from range(1,1001)t(i);

〇〇

newkid 发表于 2024-8-28 22:06
你这个写法确实能拓展思路，可是最后那个CONNECT BY 不就局限于整数了吗？我给你改一下：select   lv as he ...

改成了duckdb能用的版本
select lv,max(cnt)*lv max_rectangle
from(select lv,grp,count(*) cnt
from(
select lv,height ,sum(case when height>=lv then 0 else 1 end) over (partition by lv order by id ) as grp
from rectangle ,(select distinct height as lv from rectangle)b
)a
where height>=lv
group by lv,grp
)c
group by lv
order by max_rectangle desc
;