insert into dcf_sum.b_mx_fczj_and_industryphy_t0 ( dses_uuid, entity_id, entity_name, atom_id, atom_name, industry, total_fczj, gldm_create_time, dat_dt, etl_dt, gldm_sta_dt, gldm_end_dt, gldm_del_flag, src_update_time, src_table_key, is_current) SELECT src.dses_uuid, src.entity_id, src.entity_name, src.atom_id, src.atom_name, src.industry, src.total_fczj, src.gldm_create_time, src.dat_dt, src.etl_dt, src.gldm_sta_dt, src.gldm_end_dt, src.gldm_del_flag, src.src_update_time, src.src_table_key, src.is_current FROM ( SELECT sys_guid() as dses_uuid, '100076' as entity_id, 'tt' as entity_name, '109549' as atom_id, '各行业年度扶持资金总和' as atom_name, SUM(SRC.pfje) AS total_fczj, null::varchar AS industry, null::numeric AS total_fczj, src.etl_dt as gldm_create_time, '${DAT_DT}' as dat_dt, '${ETL_DT}' as etl_dt, '${DAT_DT}' as gldm_sta_dt, '30001231' as gldm_end_dt, '0' as gldm_del_flag, null::timestamp AS src_update_time, null::varchar AS src_table_key, '1' as is_current, ROW_NUMBER() OVER (PARTITION BY SRC.null ORDER BY SRC.DAT_DT DESC ) ADQ_RN FROM dcf_atm.b_st_yz_ex_ss_86_fczj_and_industryphy src GROUP BY SRC.industry )SRC WHERE SRC.ADQ_RN = 1 ; 在上述sql语句中ROW_NUMBER() OVER (PARTITION BY SRC.null ORDER BY SRC.DAT_DT DESC ) ADQ_RN的作用是什么呢?我得到了这个报错,ERROR: column src.null does not exist,你可以帮我解决他吗?
时间: 2023-10-11 14:10:46 浏览: 107
在上述SQL语句中,ROW_NUMBER() OVER (PARTITION BY SRC.null ORDER BY SRC.DAT_DT DESC) ADQ_RN的作用是对查询结果进行排序并为每一行分配一个序号。这个序号被命名为ADQ_RN。
根据你提供的错误信息,报错提示`column src.null does not exist`,这是因为在PARTITION BY子句中使用了`SRC.null`,但是`SRC`并没有一个名为`null`的列。你可能想要使用`SRC.industry`来替代`SRC.null`,因为你的GROUP BY子句是按照`SRC.industry`分组的。
请修改ROW_NUMBER() OVER子句如下所示:
ROW_NUMBER() OVER (PARTITION BY SRC.industry ORDER BY SRC.DAT_DT DESC) ADQ_RN
这样应该可以解决报错问题。
阅读全文