杂记3_first_value窗口+表追加指标开发+中位数+ntile窗口开发

最新推荐文章于 2023-08-07 09:41:37 发布

原创

最新推荐文章于 2023-08-07 09:41:37 发布 · 811 阅读

0 ·

CC 4.0 BY-SA版权

本文探讨了在数据处理中如何使用窗口函数first_value实现特定需求，详细介绍了新增指标开发的完整流程，包括测试库建表、增列及对数检查等步骤。同时，深入讲解了不同数据库如Impala和Hive中计算中位数的方法，特别是利用ntile()窗口函数进行中位数计算的多种情况分析，包括未分组、单分组与多分组的实例展示。

1.1、需求：获取id1的最新id2

with t as (
select 1 as id1,80 as id2,1 as time 
union all
select 1 as id1,90 as id2,2 as time 
union all 
select 1 as id1,70 as id2,3 as time 
union all
select 2 as id1,20 as id2,1 as time 
union all
select 2 as id1,30 as id2,2 as time 
union all
select 2 as id1,40 as id2,3 as time
)
select id1,id2,time,max(id2) over(partition by id1 order by time desc) latestid2 from t;

结果却是，所以要谨慎使用窗口函数的 order by：
在这里插入图片描述

预想目标列-latestid2是，通过first_value() 窗口函数才能实现此功能：
在这里插入图片描述

with t as (
select 1 as id1,80 as id2,1 as time 
union all
select 1 as id1,90 as id2,2 as time 
union all 
select 1 as id1,70 as id2,3 as time 
union all
select 2 as id1,20 as id2,1 as time 
union all
select 2 as id1,30 as id2,2 as time 
union all
select 2 as id1,40 as id2,3 as time
)
select id1,id2,time,first_value(id2) over(partition by id1 order by time desc) latestid2 from t;

1.2、获取id1最大的id2，order by 可以去掉：

 with t as (
select 1 as id1,80 as id2,1 as time 
union all
select 1 as id1,90 as id2,2 as time 
union all 
select 1 as id1,70 as id2,3 as time 
union all
select 2 as id1,20 as id2,1 as time 
union all

最低0.47元/天解锁文章