sql - 错误：记录类型尚未注册

Question

我得到ERROR: record type has not been registered了以下 postgresql 脚本，即使我已将 t 注册为记录。我不明白这段代码有什么问题。有人可以帮我吗？

do $$
declare
t record;
begin
create or replace view v_big_hitters as
with scale as (
    select /*+ materialize */ low, high from (
        select lag(v) over (order by v) low, v high from (
            select * from unnest(ARRAY[0,1,2,3,4,5,6,7,8,9,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100])
        ) v
    ) v where low is not null
),
match_count as (
    select /*+ materialize */ cn.nol_no, count(m.origin_id) nb_match
    from cases c
    join case_match cm on cm.case_id = c.id
    join match m on m.id = cm.match_id
    join case_nol cn on cn.case_id = c.id
    group by cn.nol_no
),
nol_by_bucket as (
    select t.*, case when lag(bucket_low) over (order by rn)=bucket_low then null else 'x' end first_in_bucket from (
        select
            round(t.running_match/t.overall_match*100,2) perct, (select COALESCE(min(low),100) from scale where t.running_match/t.overall_match*100<high) bucket_low
        from (
            select
                nol_no, nb_match,
                sum(nb_match) over (partition by null) overall_match,
                sum(nb_match) over (order by nb_match desc, nol_no ) running_match,
                row_number() over (order by nb_match desc, nol_no) rn
            from match_count
        ) t
    ) t
    order by nb_match desc ,nol_no
)
select
    high as distribution,
    running_nol-nvl(lag(running_nol) over (order by high),0) count_of_nol,
    running_nol cumulative_count_of_nol,
    running_match-nvl(lag(running_match) over (order by high),0) count_of_match,
    running_match cumulative_count_of_match
from (
    select
        s.high, COALESCE(max(nbb.running_match) over (order by s.high),0) running_match, COALESCE(max(nbb.rn) over (order by s.high),0) running_nol
    from
        scale s
        left join (select * from nol_by_bucket where first_in_bucket='x' and bucket_low>0) nbb on (s.high=nbb.bucket_low)
) s
order by high;
end;
$$

任何帮助深表感谢。非常感谢您提前。

更新 根据评论我更新了代码：

create or replace view v_big_hitters as
with scale as (
    select /*+ materialize */ low, high from (
        select lag(v) over (order by v) low, v high from (
            select * from unnest(ARRAY[0,1,2,3,4,5,6,7,8,9,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100])
        ) as x(v)
    ) v where low is not null
),
match_count as (
    select /*+ materialize */ cn.nol_no, count(m.origin_id) nb_match
    from cases c
    join case_match cm on cm.case_id = c.id
    join match m on m.id = cm.match_id
    join case_nol cn on cn.case_id = c.id
    group by cn.nol_no
),
nol_by_bucket as (
    select t.*, case when lag(bucket_low) over (order by rn--this is where the error occurs)=bucket_low then null else 'x' end as first_in_bucket from (
        select
            round(t.running_match/t.overall_match*100,2) perct, (select COALESCE(min(low),100) from scale where t.running_match/t.overall_match*100<high) bucket_low
        from (
            select
                nol_no, nb_match,
                sum(nb_match) over (partition by null) overall_match,
                sum(nb_match) over (order by nb_match desc, nol_no ) running_match,
                row_number() over (order by nb_match desc, nol_no) rn
            from match_count order by nb_match desc ,nol_no
        ) t
    ) t
)
select
    high as distribution,
    running_nol-nvl(lag(running_nol) over (order by high),0) count_of_nol,
    running_nol cumulative_count_of_nol,
    running_match-nvl(lag(running_match) over (order by high),0) count_of_match,
    running_match cumulative_count_of_match
from (
    select
        s.high, COALESCE(max(nbb.running_match) over (order by s.high),0) running_match, COALESCE(max(nbb.rn) over (order by s.high),0) running_nol
    from
        scale s
        left join (select * from nol_by_bucket where first_in_bucket='x' and bucket_low>0) nbb on (s.high=nbb.bucket_low)
) s
order by high;

现在我得到ERROR: column "rn" does not exist

标签： sqlpostgresql