组合函数使用案例
创建于 2022-11-01 / 最近更新于 2022-11-08 / 2484
字体:
[默认]
[大]
[更大]
案例一:聚合函数和条件函数的组合
当满足xx条件,才去做sum聚合;
一般的做法需要用筛选器多拉一条分支,再拼接回原逻辑,利用下面的组合公式,减少步骤且准确。
【示例】sum+case when
sum( case when [线路层级] = 'T1' then [回瓶箱数] else 0 end) over(partition by [End-to-End Route],[记帐期间],[实际路线],[Whole Course Route])
案例二:跨行赋值first_value
新建字段[排序] = case when [线路层级] = 'T1' then 1 else 2 end
first_value([T1层级回瓶箱数]) over(partition by [End-to-End Route],[记账期间],[Whole Course Route] order by [排序])
注:截图的第5行是WS段,原先自己的回瓶箱数是20,要取T1段的量
延展:案例一二的组合使用
first_value( sum( case when [线路层级] = 'T1' then [回瓶箱数] else 0 end) over(partition by [End-to-End Route],[记账期间],[实际路线],[Whole Course Route]) ) over(partition by [End-to-End Route],[记账期间],[Whole Course Route] order by [排序])
2 人点赞过