如何计算ARROZ SECANO MANUAL与MAIZ TECNIFICADO的has_sembrada总和
需求说明
你当前编写的SQL目标是获取编码前两位为23的区域内,两类种植系统(ARROZ SECANO MANUAL、MAIZ TECNIFICADO)的最新年度农业生产数据,需要在现有逻辑基础上补充实现两类记录对应has_sembrada字段值的总和计算。
你当前编写的原始SQL如下,存在语法和逻辑层面的缺陷:
select * from (select * except(rn) from (select sistema_productivo, cod_mpio, periodo, has_sembrada,has_sembrada_und, id_cultivo,anio, row_number() over(partition by cod_mpio order by anio desc) rn from TABLENAME where SUBSTR(cod_mpio,1,2) in("23") and sistema_productivo in("ARROZ SECANO MANUAL", "MAIZ TECNIFICADO") and periodo in("B")) t where rn = 1 and anio =(select max(anio) as date_max, from (select * except(rn) from (select sistema_productivo, cod_mpio, periodo, has_sembrada,has_sembrada_und, id_cultivo,anio, row_number() over(partition by cod_mpio order by anio desc) rn from TABLENAME where SUBSTR(cod_mpio,1,2) in("23") and sistema_productivo in("ARROZ SECANO MANUAL") and periodo in("B")) t where rn = 1)) )
现有查询的核心缺陷
- 语法错误:子查询中
max(anio) as date_max, from位置存在多余逗号,会直接导致SQL执行失败 - 逻辑漏洞:窗口函数
row_number() over(partition by cod_mpio ...)的分区维度缺失种植系统字段,会导致每个区县下仅保留排序第一的单条种植系统记录,直接丢失另一类种植系统的对应数据,无法获取两类记录的完整值 - 效率问题:对同一张表重复执行两次相同的筛选、排名逻辑,存在无意义的全表扫描损耗
可实现字段求和的修正方案
通过CTE分层简化查询逻辑,先筛选有效数据,再按正确维度取最新年度记录,最后完成求和计算,提供两种适配不同场景的实现:
场景1:保留两类种植系统明细,同时计算对应总和
如果需要保留每类种植系统的明细行,同时展示同维度下两类has_sembrada的合计值,可以使用窗口函数实现,不会打散明细结构:
WITH base_filtered AS ( -- 筛选符合区域、种植系统、周期条件的原始数据 SELECT sistema_productivo, cod_mpio, periodo, has_sembrada, has_sembrada_und, id_cultivo, anio FROM TABLENAME WHERE SUBSTR(cod_mpio,1,2) = '23' AND sistema_productivo IN ('ARROZ SECANO MANUAL', 'MAIZ TECNIFICADO') AND periodo = 'B' ), ranked_latest AS ( -- 按区县、种植系统双维度分区,保证每类种植系统独立取最新年度记录 SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_mpio, sistema_productivo ORDER BY anio DESC) rn FROM base_filtered ), latest_valid_records AS ( -- 过滤出所有满足“最新年度”要求的有效记录 SELECT * FROM ranked_latest WHERE rn = 1 ) SELECT cod_mpio, sistema_productivo, anio, has_sembrada, -- 按区县维度汇总两类种植系统的has_sembrada总和,保留原明细行 SUM(has_sembrada) OVER (PARTITION BY cod_mpio) AS municipio_total_has_sembrada FROM latest_valid_records
场景2:仅返回两类记录的全局/分组汇总值
如果不需要保留明细行,仅需要直接拿到两类has_sembrada的合计数,可以在有效记录层直接聚合:
WITH base_filtered AS ( SELECT cod_mpio, has_sembrada, sistema_productivo, anio FROM TABLENAME WHERE SUBSTR(cod_mpio,1,2) = '23' AND sistema_productivo IN ('ARROZ SECANO MANUAL', 'MAIZ TECNIFICADO') AND periodo = 'B' ), ranked_latest AS ( SELECT cod_mpio, has_sembrada, ROW_NUMBER() OVER (PARTITION BY cod_mpio, sistema_productivo ORDER BY anio DESC) rn FROM base_filtered ) -- 按区县分组的话增加GROUP BY cod_mpio即可,无分组则返回全区域总和 SELECT SUM(has_sembrada) AS total_has_sembrada FROM ranked_latest WHERE rn = 1
注意:如果业务要求两类种植系统必须使用同一年度的数据(比如只要水稻最新年度是2023,那玉米哪怕有2024的数据也统一取2023),可以在CTE层先取水稻的最新年度值,再给所有记录增加年度筛选条件,避免不同种植系统的年度不一致。
内容的提问来源于stack exchange,提问作者Nicolas Rondon
相关产品推荐
相关产品推荐

