sql-server - 将一行与前 12 个值进行比较
问题描述
我正在尝试将一个值与前 12 个月/12 行进行比较。如果过去 12 个月的值为“是”,则第 12 行为“是”。对于每一行也是如此(比较前 12 个值)。
我正在按照以下方式进行操作。它可以工作,但我必须为多个列执行此操作,因此代码将非常冗长。
有什么方法可以通过简单的步骤完成 n 列吗?
select *,
--Identify customers who have applied for principal relief within the past 12 months
lag(PrincipalReliefFlag,1) over(partition by id,productcode order by snapshot_date) PRflg1
,lag(PrincipalReliefFlag,2) over(partition by id,productcode order by snapshot_date) PRflg2
,lag(PrincipalReliefFlag,3) over(partition by id,productcode order by snapshot_date) PRflg3
,lag(PrincipalReliefFlag,4) over(partition by id,productcode order by snapshot_date) PRflg4
,lag(PrincipalReliefFlag,5) over(partition by id,productcode order by snapshot_date) PRflg5
,lag(PrincipalReliefFlag,6) over(partition by id,productcode order by snapshot_date) PRflg6
,lag(PrincipalReliefFlag,7) over(partition by id,productcode order by snapshot_date) PRflg7
,lag(PrincipalReliefFlag,8) over(partition by id,productcode order by snapshot_date) PRflg8
,lag(PrincipalReliefFlag,9) over(partition by id,productcode order by snapshot_date) PRflg9
,lag(PrincipalReliefFlag,10) over(partition by id,productcode order by snapshot_date) PRflg10
,lag(PrincipalReliefFlag,11) over(partition by id,productcode order by snapshot_date) PRflg11
into #test
from #temp
select *
,case when (PrincipalReliefFlag='Y' or PRflg1='Y' or PRflg2='Y' or PRflg3='Y' or PRflg4='Y' or PRflg5='Y' or PRflg6='Y' or PRflg7='Y' or PRflg8='Y' or PRflg9='Y' or PRflg10='Y' or PRflg11='Y')
then 'Y'
when (PrincipalReliefFlag='N'
and (PRflg1='N' or PRflg1 is null)
and (PRflg2='N' or PRflg2 is null)
and (PRflg3='N' or PRflg3 is null)
and (PRflg4='N' or PRflg4 is null)
and (PRflg5='N' or PRflg5 is null)
and (PRflg6='N' or PRflg6 is null)
and (PRflg7='N' or PRflg7 is null)
and (PRflg8='N' or PRflg8 is null)
and (PRflg9='N' or PRflg9 is null)
and (PRflg10='N' or PRflg10 is null)
and (PRflg11='N' or PRflg11 is null) )
then 'N'
end PrincipalRelief_applied_in_last12m
from #test
期待简单的步骤。也尝试使用连接,但无法成功。
解决方案
这是你想要的吗?
select t.*,
max(PrincipalReliefFlag) over (partition by id, productcode
order by snapshot_date
rows between 11 preceding and current row
) as PrincipalReliefFlag_12
from #temp t;
这将采用 12 个月期间的最大值 - 如果标志仅采用 和 的值,则将'Y'
起作用'N'
。
推荐阅读
- python - 排序方法返回不正确的输出
- javascript - 重定向到某些 div 中的 url
- java - 如何重置表单Java Spring
- python - 如何为完成的 MLflow 运行添加更多指标?
- r - 当多个逻辑向量为真时,是否有 R 函数来创建变量?
- xcode - 我的应用程序未显示在 testflight 中,但在 xcode 中一切都成功
- java - 递归合并排序不返回排序数组
- ios - TableView中SearchController过滤问题
- python - Hitboxes 仅从一侧缩放 pygame
- tensorflow - LSTM 平稳时间序列预测不稳定模型