使用 Teradata SQL查询限制结果

Question

下面的SQL导致结果下降，因为最低FYPD是在上一财政年度。 PS_DEPR_RPT表包含每个资产的 1 行，因为有 12 个会计期间。

以下查询的目的是返回资产 ID。 如果未在上一期间添加资产，则最低值效果很好。

select  business_unit, asset_id, book, 
min((fiscal_year * 100) + accounting_period) as FYPD
from psfs.PS_DEPR_RPT pdr
where book = 'PERFORM' 
having FYPD between 201901 and 201912
group by 1,2,3

有关如何从此表中返回 FY 19 和期间 1 至 12 的单个资产 ID 的任何建议将不胜感激。 看起来应该没有那么困难，但我今天没有穿过阴霾。 感谢您的帮助...

Answer 1

考虑使用条件聚合跨多个会计年度期间进行计算(将WHERE条件移动到CASE语句(：

select  business_unit, asset_id, book, 
min(case 
when FYPD between 201701 and 201712
then (fiscal_year * 100) + accounting_period
else NULL
end) as FYPD_17,
min(case 
when FYPD between 201801 and 201812
then (fiscal_year * 100) + accounting_period
else NULL
end) as FYPD_18,
min(case 
when FYPD between 201901 and 201912
then (fiscal_year * 100) + accounting_period
else NULL
end) as FYPD_19
from psfs.PS_DEPR_RPT pdr
where book = 'PERFORM' 
group by 1, 2, 3

如果需要跨所有FYPD列的非 null 最小值，请根据 CTEWITH语法的结果运行COALESCE。

with cte as (
-- ...above query...
)
select  business_unit, asset_id, book, 
coalesce(FYPD_19, FYPD_18, FYPD_17) as FYPD_min    
from cte