Hive last_value/first_value实现ignore nulls

最新推荐文章于 2024-08-04 02:19:45 发布
原创 最新推荐文章于 2024-08-04 02:19:45 发布 · 3.1k 阅读 · 6 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#hadoop #spark #hdfs #大数据 #hive

本文介绍如何创建测试表并使用SQL查询，包括使用`last_value`函数按ID和时间顺序获取最新值，展示在默认test_tab表中的数据处理。

创建一张测试表

drop table if exists default.test_tab purge;
create table default.test_tab
as
select date_add(current_timestamp(),-10) as event_time,'1' as id, 'A' as a,'B' as b,'C' as c
union all select date_add(current_timestamp(),-9)
最低0.47元/天 解锁文章
SQL进阶技巧：近距离有效的缺失值填充问题【last_value实现版】
石榴姐yyds
08-21 865
本文利用last_value()时序函数优化了缺失值填充问题的解决方案，该方法简单有效，但是对于一些复杂情况的缺失值填充问题，断点分组思想依然是行之有效的通用解法。本文要点是对窗口函数last_value()函数的深刻认识，当该函数第二个参数为true时，表示窗口范围内取截止当前时间点不为NULL的最近的数据，利用该特点我们可以有效解决最近有效的缺失值填充问题
hive的first_value、last_value函数，可取非null值
sinat_35197112的博客
06-02 2390
介绍hive函数first_value、last_value用法
orlace里使用first_value去除第一个空值的方法（原创）
shsh1234567890的博客
02-08 332
orlace里使用first_value去除第一个空值的方法（原创）
HIVE函数
weixin_42923199的博客
03-04 591
show functions; desc function xxx; 窗口函数 row_number：row_number() over(distribute by dt sort by score desc) 没有并列，相同名次顺序排 rank：有并列，相同名次空位 dense_rank：有并列，相同名次不空位 lag：向上取第n行的数据 lead：向下取...
Oracle last_value
weixin_45728484的博客
07-24 676
3、用前一行的值填充，忽略空值，第一行因前面无值，故直接为空：(1可自由更改）*PS：*可以增加 PARTITION BY 进行分组！
extraneous input ''' expecting {'(', 'ADD', 'AFTER', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'ANTI', 'ANY', 'ARCHIVE', 'ARRAY', 'AS', 'ASC', 'AT', 'AUTHORIZATION', 'BETWEEN', 'BOTH', 'BUCKET', 'BUCKETS', 'BY', 'CACHE', 'CASCADE', 'CASE', 'CAST', 'CHANGE', 'CHECK', 'CLEAR', 'CLUSTER', 'CLUSTERED', 'CODEGEN', 'COLLATE', 'COLLECTION', 'COLUMN', 'COLUMNS', 'COMMENT', 'COMMIT', 'COMPACT', 'COMPACTIONS', 'COMPUTE', 'CONCATENATE', 'CONSTRAINT', 'COST', 'CREATE', 'CROSS', 'CUBE', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'CURRENT_USER', 'DATA', 'DATABASE', DATABASES, 'DBPROPERTIES', 'DEFINED', 'DELETE', 'DELIMITED', 'DESC', 'DESCRIBE', 'DFS', 'DIRECTORIES', 'DIRECTORY', 'DISTINCT', 'DISTRIBUTE', 'DIV', 'DROP', 'ELSE', 'END', 'ESCAPE', 'ESCAPED', 'EXCEPT', 'EXCHANGE', 'EXISTS', 'EXPLAIN', 'EXPORT', 'EXTENDED', 'EXTERNAL', 'EXTRACT', 'FALSE', 'FETCH', 'FIELDS', 'FILTER', 'FILEFORMAT', 'FIRST', 'FOLLOWING', 'FOR', 'FOREIGN', 'FORMAT', 'FORMATTED', 'FROM', 'FULL', 'FUNCTION', 'FUNCTIONS', 'GLOBAL', 'GRANT', 'GROUP', 'GROUPING', 'HAVING', 'IF', 'IGNORE', 'IMPORT', 'IN', 'INDEX', 'INDEXES', 'INNER', 'INPATH', 'INPUTFORMAT', 'INSERT', 'INTERSECT', 'INTERVAL', 'INTO', 'IS', 'ITEMS', 'JOIN', 'KEYS', 'LAST', 'LATERAL', 'LAZY', 'LEADING', 'LEFT', 'LIKE', 'LIMIT', 'LINES', 'LIST', 'LOAD', 'LOCAL', 'LOCATION', 'LOCK', 'LOCKS', 'LOGICAL', 'MACRO', 'MAP', 'MATCHED', 'MERGE', 'MSCK', 'NAMESPACE', 'NAMESPACES', 'NATURAL', 'NO', NOT, 'NULL', 'NULLS', 'OF', 'ON', 'ONLY', 'OPTION', 'OPTIONS', 'OR', 'ORDER', 'OUT', 'OUTER', 'OUTPUTFORMAT', 'OVER', 'OVERLAPS', 'OVERLAY', 'OVERWRITE', 'PARTITION', 'PARTITIONED', 'PARTITIONS', 'PERCENT', 'PIVOT', 'PLACING', 'POSITION', 'PRECEDING', 'PRIMARY', 'PRINCIPALS', 'PROPERTIES', 'PURGE', 'QUERY', 'RANGE', 'RECORDREADER', 'RECORDWRITER', 'RECOVER', 'REDUCE', 'REFERENCES', 'REFRESH', 'RENAME', 'REPAIR', 'REPLACE', 'RESET', 'RESTRICT', 'REVOKE', 'RIGHT', RLIKE, 'ROLE', 'ROLES', 'ROLLBACK', 'ROLLUP', 'ROW', 'ROWS', 'SCHEMA', 'SELECT', 'SEMI', 'SEPARATED', 'SERDE', 'SERDEPROPERTIES', 'SESSION_USER', 'SET', 'MINUS', 'SETS', 'SHOW', 'SKEWED', 'SOME', 'SORT', 'SORTED', 'START', 'STATISTICS', 'STORED', 'STRATIFY', 'STRUCT', 'SUBSTR', 'SUBSTRING', 'TABLE', 'TABLES', 'TABLESAMPLE', 'TBLPROPERTIES', TEMPORARY, 'TERMINATED', 'THEN', 'TIME', 'TO', 'TOUCH', 'TRAILING', 'TRANSACTION', 'TRANSACTIONS', 'TRANSFORM', 'TRIM', 'TRUE', 'TRUNCATE', 'TYPE', 'UNARCHIVE', 'UNBOUNDED', 'UNCACHE', 'UNION', 'UNIQUE', 'UNKNOWN', 'UNLOCK', 'UNSET', 'UPDATE', 'USE', 'USER', 'USING', 'VALUES', 'VIEW', 'VIEWS', 'WHEN', 'WHERE', 'WINDOW', 'WITH', 'ZONE', '+', '-', '*', '~', STRING, BIGINT_LITERAL, SMALLINT_LITERAL, TINYINT_LITERAL, INTEGER_VALUE, EXPONENT_VALUE, DECIMAL_VALUE, FLOAT_LITERAL, DOUBLE_LITERAL, BIGDECIMAL_LITERAL, IDENTIFIER, BACKQUOTED_IDENTIFIER}(line 411, pos 19) == SQL == create table orca01_dr_data.ads_rpt_fsm_sfm_kpi_funnel_dmo_t_1218 stored as parquet as with dealership_full as ( select distinct dealer_id ,region_code ,dealer_short_name ,dealer_short_name_en ,region_bmw_sale ,region_mini_sale ,region_moto_sale ,region_bmw_sale_en ,region_mini_sale_en ,region_moto_sale_en ,small_region_bmw_sale_en ,small_region_mini_sale_en ,small_region_moto_sale_en ,dealer_investor_name_en ,organization_id ,investor_id from cdl.cdl_dim_com_dmx_dealership_full_t where pday='20251124' ), emp_delear as ( select distinct emp.company_id ,emp.id ,emp.emp_name ,dealer.dealer_id ,dealer.organization_id ,dealer.region_bmw_sale_en ,dealer.region_mini_sale_en ,dealer.region_moto_sale_en ,dealer.small_region_bmw_sale_en ,dealer.small_region_mini_sale_en ,dealer.small_region_moto_sale_en ,dealer.investor_id from dwc.dwc_dim_com_dmo2_t_employee_full_t as emp left join dealership_full as dealer on emp.company_id=dealer.organization_id where emp.pday='20251124' ) ,funnel_base as ( select month_id ,brand_code ,series_code ,eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_vol ,leads_vol_lm ,leads_vol_ly ,leads_vol_mom ,leads_vol_yoy ,leads_oppty_conv_rate ,leads_oppty_conv_rate_mom ,leads_oppty_conv_rate_yoy ,oppty_vol ,oppty_vol_lm ,oppty_vol_ly ,oppty_vol_mom ,oppty_vol_yoy ,'' as oppty_vol_others ,oppty_sr_conv_rate ,oppty_sr_conv_rate_mom ,oppty_sr_conv_rate_yoy ,sr_vol ,sr_vol_lm ,sr_vol_ly ,sr_vol_mom ,sr_vol_yoy ,sr_td_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4)) as sr_order_conv_rate ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_lm=0 then null else order_vol_lm/sr_vol_lm end as decimal(32,4)) as sr_order_conv_rate_mom ,cast(case when sr_vol=0 then null else order_vol/sr_vol end as decimal(32,4))-cast(case when sr_vol_ly=0 then null else order_vol_ly/sr_vol_ly end as decimal(32,4)) as sr_order_conv_rate_yoy ,first_sr_vol ,re_sr_vol ,td_vol ,td_vol_lm ,td_vol_ly ,td_vol_mom ,td_vol_yoy ,order_vol ,order_vol_lm ,order_vol_ly ,order_vol_mom ,order_vol_yoy ,order_handover_conv_rate ,order_handover_conv_rate-order_handover_conv_rate_lm as order_handover_conv_rate_mom ,order_handover_conv_rate-order_handover_conv_rate_ly as order_handover_conv_rate_yoy ,canceled_order_vol ,handovered_order_vol ,handover_tbd_vol ,order_intake_vol ,order_cancel_vol ,order_cancel_rate ,order_cancel_rate_mom ,order_cancel_rate_yoy ,order_vol_with_td ,order_vol_without_td ,handover_vol ,handover_vol_lm ,handover_vol_ly ,handover_vol_mom ,handover_vol_yoy ,handover_intake_vol ,handover_cancel_vol ,cast(round(delivery_days/cus_handover_intake_vol,0) as int) as avg_delivery_days ,case when brand_code='bmw' and month_id<'202212' then cast(null as int) when brand_code='mini' and month_id<'202304' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_lm/cus_handover_intake_vol_lm,0) as int) end as avg_delivery_days_mom ,case when month_id<'202401' then cast(null as int) else cast(round(delivery_days/cus_handover_intake_vol,0) - round(delivery_days_ly/cus_handover_intake_vol_ly,0) as int) end as avg_delivery_days_yoy ,case when data_agg_type='ytdm' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyymmdd'),'yyyy-mm-dd')+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')) else 1 end when data_agg_type='ytd' then case when substr(month_id,1,4)=substr('20251123',1,4) and month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp('20251123','yyyymmdd'),'yyyy-mm-dd')+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) else datediff(from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')+ interval '1 month',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) end end as pass_day ,case when data_agg_type='ytdm' then case when month_id=substr('20251123',1,6) then datediff(from_unixtime(unix_timestamp(month_id||'01','yyyymmdd')) + interval '1 month'+ interval '1 day',from_unixtime(unix_timestamp(month_id||'01','yyyymmdd'),'yyyy-mm-dd')) else 1 end when data_agg_type='ytd' then datediff(from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd')) + interval '1 year'+ interval '1 day',from_unixtime(unix_timestamp(substr(month_id,1,4)||'0101','yyyymmdd'),'yyyy-mm-dd')) end as actual_day from dws.dws_fact_sal_ncs_fsm_ops_funnel_kpi_t where pday='20251123' and dim_geo_type in ('emp','agent') and same_eseries_flag=0 and source_type<>'other' and ! ( (coalesce(brand_code,'')='bmw' and coalesce(source_type,'') in ('retail','nsc','bdc') ) ) ) ,tgt_base as ( select month_id ,dim_type ,dim_value ,brand_code ,series_code ,eseries_code ,data_agg_type ,leads_channel ,cast(leads_tgt as int) as leads_vol_tgt ,cast(oppty_tgt as int) as oppty_vol_tgt ,cast(sr_tgt as int) as sr_vol_tgt ,cast(order_tgt as int) as order_vol_tgt ,cast(handover_tgt as int) as handover_vol_tgt from dws.dws_fact_sal_ncs_fsm_tgt_mon_t where pmonth='{$pmonth}' and leads_channel='all' and dim_type in ('emp','agent') ) ,leads_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,leads_timely_follow_up_rate ,leads_timely_follow_up_rate_mom ,leads_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_leads_followup_his_t where pday='20251123' and leads_star_level='all' and dim_geo_type in ('emp','agent') ) ,oppty_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,oppty_timely_follow_up_rate ,oppty_timely_follow_up_rate_mom ,oppty_timely_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_oppty_followup_his_t where pday='20251123' and oppty_priority_level='all' and dim_geo_type in ('emp','agent') ) ,td_follow_base as ( select month_id ,leads_brand_code ,leads_series_code ,leads_eseries_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,source_type ,td_follow_up_rate ,td_follow_up_rate_mom ,td_follow_up_rate_yoy from dws.dws_fact_sal_ncs_fsm_td_followup_t where pday='20251123' and dim_geo_type in ('emp','agent') ) ,sr_purpose_base as ( select month_id ,leads_brand_code ,dim_geo_type ,dim_geo_code ,dim_geo_name_cn ,dim_geo_name_en ,data_agg_type ,sr_record_vol_first ,sr_record_vol_re ,sr_record_vol_order ,sr_record_vol_invalid from (select month_id,leads_brand_code,dim_geo_type,dim_geo_code,dim_geo_name_cn,dim_geo_name_en,data_agg_type,sr_purpose,sr_vol from dws.dws_fact_sal_ncs_fsm_showroom_purpose_analysis_t where pday='20251123')t pivot (max(sr_vol) for sr_purpose in ('1' as sr_record_vol_first,'2' as sr_record_vol_re,'3' as sr_record_vol_order,'4' as sr_record_vol_invalid)) ) ,tgt_achv as ( select month_id ,brand_code ,series_code ,eseries_code ,leads_achv_rate_ref_floor as leads_achv_rate_ref_floor ,oppty_achv_rate_ref_floor as oppty_achv_rate_ref_floor ,sr_achv_rate_ref_floor as sr_achv_rate_ref_floor ,order_achv_rate_ref_floor as order_achv_rate_ref_floor ,handover_achv_rate_ref_floor as handover_achv_rate_ref_floor ,leads_achv_rate_ref_ceiling as leads_achv_rate_ref_ceiling ,oppty_achv_rate_ref_ceiling as oppty_achv_rate_ref_ceiling ,sr_achv_rate_ref_ceiling as sr_achv_rate_ref_ceiling ,order_achv_rate_ref_ceiling as order_achv_rate_ref_ceiling ,handover_achv_rate_ref_ceiling as handover_achv_rate_ref_ceiling from (select month_id,brand_code,series_code,eseries_code,kpi,achv_rate_ref_floor,achv_rate_ref_ceiling from upload.dwc_fact_sal_ncs_fsm_kpi_tgt_achv_mn_t)t pivot (max(achv_rate_ref_floor) as achv_rate_ref_floor,max(achv_rate_ref_ceiling) as achv_rate_ref_ceiling for kpi in ('leads' as leads,'oppty' as oppty,'showroom' as sr,'order' as order,'handover' as handover)) ) select t1.month_id ,t1.brand_code ,t1.series_code ,t1.eseries_code ,coalesce(dim1.dealer_id,dim2.dealer_id) as dealer_id ,coalesce(dim1.organization_id,dim2.organization_id) as organization_id ,case when t1.brand_code='bmw' then coalesce(dim1.region_bmw_sale_en,dim2.region_bmw_sale_en) when t1.brand_code='mini' then coalesce(dim1.region_mini_sale_en,dim2.region_mini_sale_en) when t1.brand_code='moto' then coalesce(dim1.region_moto_sale_en,dim2.region_moto_sale_en) end as region_en ,case when t1.brand_code='bmw' then coalesce(dim1.small_region_bmw_sale_en,dim2.small_region_bmw_sale_en) when t1.brand_code='mini' then coalesce(dim1.small_region_mini_sale_en,dim2.small_region_mini_sale_en) when t1.brand_code='moto' then coalesce(dim1.small_region_moto_sale_en,dim2.small_region_moto_sale_en) end as small_region_en ,coalesce(dim1.investor_id,dim2.investor_id) as investor_id ,t1.dim_geo_type ,t1.dim_geo_code ,t1.dim_geo_name_cn ,t1.dim_geo_name_en ,t1.data_agg_type ,t1.source_type ,t1.leads_vol ,t1.leads_vol_mom ,t1.leads_vol_yoy ,t2.leads_vol_tgt ,cast(case when t2.leads_vol_tgt=0 then null else (t1.leads_vol/t1.pass_day) / (t2.leads_vol_tgt/t1.actual_day) end as decimal(32,4)) as leads_achv_rate ,tgt_achv.leads_achv_rate_ref_floor ,tgt_achv.leads_achv_rate_ref_ceiling ,t1.leads_oppty_conv_rate ,t1.leads_oppty_conv_rate_mom ,t1.leads_oppty_conv_rate_yoy ,t3.leads_timely_follow_up_rate ,t3.leads_timely_follow_up_rate_mom ,t3.leads_timely_follow_up_rate_yoy ,t1.oppty_vol ,t1.oppty_vol_mom ,t1.oppty_vol_yoy ,tt.oppty_vol_others ,t2.oppty_vol_tgt ,cast(case when t2.oppty_vol_tgt=0 then null else (t1.oppty_vol/t1.pass_day) / (t2.oppty_vol_tgt/t1.actual_day) end as decimal(32,4)) as oppty_achv_rate ,tgt_achv.oppty_achv_rate_ref_floor ,tgt_achv.oppty_achv_rate_ref_ceiling ,t4.oppty_timely_follow_up_rate ,t4.oppty_timely_follow_up_rate_mom ,t4.oppty_timely_follow_up_rate_yoy ,t1.oppty_sr_conv_rate ,t1.oppty_sr_conv_rate_mom ,t1.oppty_sr_conv_rate_yoy ,t1.sr_vol ,t1.sr_vol_mom ,t1.sr_vol_yoy ,t2.sr_vol_tgt ,cast(case when t2.sr_vol_tgt=0 then null else (t1.sr_vol/t1.pass_day) / (t2.sr_vol_tgt/t1.actual_day) end as decimal(32,4)) as sr_achv_rate ,tgt_achv.sr_achv_rate_ref_floor ,tgt_achv.sr_achv_rate_ref_ceiling ,t1.sr_td_conv_rate ,t1.sr_order_conv_rate ,t1.sr_order_conv_rate_mom ,t1.sr_order_conv_rate_yoy ,t1.first_sr_vol ,t1.re_sr_vol ,t5.sr_record_vol_first ,t5.sr_record_vol_re ,t5.sr_record_vol_order ,t5.sr_record_vol_invalid ,t1.td_vol ,t1.td_vol_mom ,t1.td_vol_yoy ,t6.td_follow_up_rate ,t6.td_follow_up_rate_mom ,t6.td_follow_up_rate_yoy ,t1.order_vol ,t1.order_vol_mom ,t1.order_vol_yoy ,t2.order_vol_tgt ,cast(case when t2.order_vol_tgt=0 then null else (t1.order_vol/t1.pass_day) / (t2.order_vol_tgt/t1.actual_day) end as decimal(32,4)) as order_achv_rate ,tgt_achv.order_achv_rate_ref_floor ,tgt_achv.order_achv_rate_ref_ceiling ,t1.order_handover_conv_rate ,t1.order_handover_conv_rate_mom ,t1.order_handover_conv_rate_yoy ,t1.canceled_order_vol ,t1.handovered_order_vol ,t1.handover_tbd_vol ,t1.order_intake_vol ,t1.order_cancel_vol ,t1.order_cancel_rate ,t1.order_cancel_rate_mom ,t1.order_cancel_rate_yoy ,t1.order_vol_with_td ,t1.order_vol_without_td ,t1.handover_vol ,t1.handover_vol_mom ,t1.handover_vol_yoy ,t2.handover_vol_tgt ,cast(case when t2.handover_vol_tgt=0 then null else (t1.handover_vol/t1.pass_day) / (t2.handover_vol_tgt/t1.actual_day) end as decimal(32,4)) as handover_achv_rate ,tgt_achv.handover_achv_rate_ref_floor ,tgt_achv.handover_achv_rate_ref_ceiling ,t1.handover_intake_vol ,t1.handover_cancel_vol ,t1.avg_delivery_days ,t1.avg_delivery_days_mom ,t1.avg_delivery_days_yoy ,cast(case when t7.leads_vol=0 then null else t1.leads_vol/t7.leads_vol end as decimal(32,4)) as leads_ratio ,cast(case when t7.leads_vol=0 or t7.leads_vol_lm=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_lm/t7.leads_vol_lm end as decimal(32,4)) as leads_ratio_mom ,cast(case when t7.leads_vol=0 or t7.leads_vol_ly=0 then null else t1.leads_vol/t7.leads_vol - t1.leads_vol_ly/t7.leads_vol_ly end as decimal(32,4)) as leads_ratio_yoy ,cast(case when t7.oppty_vol=0 then null else t1.oppty_vol/t7.oppty_vol end as decimal(32,4)) as oppty_ratio ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_lm=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_lm/t7.oppty_vol_lm end as decimal(32,4)) as oppty_ratio_mom ,cast(case when t7.oppty_vol=0 or t7.oppty_vol_ly=0 then null else t1.oppty_vol/t7.oppty_vol - t1.oppty_vol_ly/t7.oppty_vol_ly end as decimal(32,4)) as oppty_ratio_yoy ,cast(case when t7.sr_vol=0 then null else t1.sr_vol/t7.sr_vol end as decimal(32,4)) as sr_ratio ,cast(case when t7.sr_vol=0 or t7.sr_vol_lm=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_lm/t7.sr_vol_lm end as decimal(32,4)) as sr_ratio_mom ,cast(case when t7.sr_vol=0 or t7.sr_vol_ly=0 then null else t1.sr_vol/t7.sr_vol - t1.sr_vol_ly/t7.sr_vol_ly end as decimal(32,4)) as sr_ratio_yoy ,cast(case when t7.td_vol=0 then null else t1.td_vol/t7.td_vol end as decimal(32,4)) as td_ratio ,cast(case when t7.td_vol=0 or t7.td_vol_lm=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_lm/t7.td_vol_lm end as decimal(32,4)) as td_ratio_mom ,cast(case when t7.td_vol=0 or t7.td_vol_ly=0 then null else t1.td_vol/t7.td_vol - t1.td_vol_ly/t7.td_vol_ly end as decimal(32,4)) as td_ratio_yoy ,cast(case when t7.order_vol=0 then null else t1.order_vol/t7.order_vol end as decimal(32,4)) as order_ratio ,cast(case when t7.order_vol=0 or t7.order_vol_lm=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_lm/t7.order_vol_lm end as decimal(32,4)) as order_ratio_mom ,cast(case when t7.order_vol=0 or t7.order_vol_ly=0 then null else t1.order_vol/t7.order_vol - t1.order_vol_ly/t7.order_vol_ly end as decimal(32,4)) as order_ratio_yoy ,cast(case when t7.handover_vol=0 then null else t1.handover_vol/t7.handover_vol end as decimal(32,4)) as handover_ratio ,cast(case when t7.handover_vol=0 or t7.handover_vol_lm=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_lm/t7.handover_vol_lm end as decimal(32,4)) as handover_ratio_mom ,cast(case when t7.handover_vol=0 or t7.handover_vol_ly=0 then null else t1.handover_vol/t7.handover_vol - t1.handover_vol_ly/t7.handover_vol_ly end as decimal(32,4)) as handover_ratio_yoy ,from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss') as etl_create_time ,from_unixtime(unix_timestamp(),'yyyy-mm-dd hh:mm:ss') as etl_update_time ,'bi' as etl_create_by ,'bi' as etl_update_by from funnel_base t1 left join tgt_base t2 on t1.month_id=t2.month_id and t1.dim_geo_type=t2.dim_type and t1.dim_geo_code=t2.dim_value and t1.brand_code=t2.brand_code and t1.eseries_code=t2.eseries_code and t1.data_agg_type=t2.data_agg_type and t1.source_type='overview' left join leads_follow_base t3 on t1.month_id=t3.month_id and t1.dim_geo_type=t3.dim_geo_type and t1.dim_geo_code=t3.dim_geo_code and t1.brand_code=t3.leads_brand_code and t1.eseries_code=t3.leads_eseries_code and t1.data_agg_type=t3.data_agg_type and t1.source_type=t3.source_type left join oppty_follow_base t4 on t1.month_id=t4.month_id and t1.dim_geo_type=t4.dim_geo_type and t1.dim_geo_code=t4.dim_geo_code and t1.brand_code=t4.leads_brand_code and t1.eseries_code=t4.leads_eseries_code and t1.data_agg_type=t4.data_agg_type and t1.source_type=t4.source_type left join sr_purpose_base t5 on t1.month_id=t5.month_id and t1.dim_geo_type=t5.dim_geo_type and t1.dim_geo_code=t5.dim_geo_code and t1.brand_code=t5.leads_brand_code and t1.data_agg_type=t5.data_agg_type and t1.eseries_code='all' and t1.source_type='overview' left join td_follow_base t6 on t1.month_id=t6.month_id and t1.dim_geo_type=t6.dim_geo_type and t1.dim_geo_code=t6.dim_geo_code and t1.brand_code=t6.leads_brand_code and t1.eseries_code=t6.leads_eseries_code and t1.data_agg_type=t6.data_agg_type and t1.source_type=t6.source_type left join funnel_base t7 on t1.month_id=t7.month_id and t1.dim_geo_type=t7.dim_geo_type and t1.dim_geo_code=t7.dim_geo_code and t1.brand_code=t7.brand_code and t1.eseries_code=t7.eseries_code and t1.data_agg_type=t7.data_agg_type and t1.source_type<>'overview' and t7.source_type='overview' left join dealership_full dim1 on t1.dim_geo_type='agent' and t1.dim_geo_code=dim1.dealer_id left join emp_delear dim2 on t1.dim_geo_type='emp' and t1.dim_geo_code=dim2.id left join tgt_achv on t1.brand_code=tgt_achv.brand_code left join dws.dws_fact_sal_ncs_fsm_ops_leads_oppty_sr_td_channel_t tt on tt.pday='20251123' and t1.month_id=tt.month_id and t1.dim_geo_type=tt.dim_geo_type and t1.dim_geo_code=tt.dim_geo_code and t1.brand_code=tt.brand_code and t1.eseries_code=tt.eseries_code and t1.data_agg_type=tt.data_agg_type and t1.source_type=tt.source_type and tt.same_eseries_flag=0 and tt.channel_name='all' where t1.month_id>='202301; -------------------^^^这是啥原因
最新发布
11-25
> ⚠️ Hive/Spark 默认对字符串是区分大小写的。如果你源数据中是 `'BMW'`，而你查 `'bmw'`，会查不到！ --- ### ✅ 2. `from_unixtime(unix_timestamp(),'yyyy-MM-dd hh:mm:ss')` 注意时间格式中的 `hh` 是 12 ...
Oracle/Hive/Impala SQL比较（5）
热门推荐
大米瓶子的博客
05-15 2万+
5 Function      指数据库内置的function，不讨论UDF。另外，操作符都不比较了，区别不大。 5.1 数学函数 功能 Oracle Hive Impala ABS 绝对值，有 有 有 SIN/SINH/ASIN/COS/COSH/ACOS/TAN/T
Hive官方使用手册——DDL使用说明
三米学习笔记杂货铺
03-28 4248
本文为自己翻译的译文，原文地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDLHive官方使用手册——DDL使用说明Hive Data Definition Language概述这里是HiveQL DDL语法说明文档 包括:CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTIO...
Hive 的窗口函数
程序员小陶的大数据分享
01-31 917
本文首发于：微信公众号【大数据每日哔哔，文章：Hive SQL 窗口函数 在 SQL 中有一类函数叫做聚合函数，例如 sum()、avg()、max()、min() 等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲聚集后的行数是要少于聚集前的行数的。但是，有时候我们既要显示聚集前的数据，又要显示聚集后的数据，此时我们便引入了窗口函数。窗口函数主要用于 OLAP 数据分析。 在深入研究Ove...
1.hive开窗函数，分析函数
weixin_30258027的博客
02-07 212
http://yugouai.iteye.com/blog/1908121分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化drop table if exists student;create table student( name...
hive-分析函数 LAG、LEAD、FIRST_VALUE和LAST_VALUE OVER(PARTITION BY order by) 的使用
小哇
10-22 3848
OVER(PARTITION BY order by)函数的其它用法，可查看博文https://blog.youkuaiyun.com/qq_41712271/article/details/109224538创建表和数据 cookie1,2015-04-10 10:00:02,url2 cookie1,2015-04-10 10:00:00,url1 cookie1,2015-04-10 10:03:04,1url3 cookie1,2015-04-10 10:50:05,url6 cookie1,2015-0.
oracle lead/lag 函数 ignore nulls 功能在11g以下版本的实现
nayi_224的博客
05-15 4412
建立测试表 11g中的写法 model写法 使用first_value/last_value+ignore的方法 自连接 其他 oracle在11g中允许对lead/lag分析函数使用ignore nulls 语法以排除空值。这是一个很实用的语法，然而在低版本中却不能使用。本文将介绍几种可以在较低版本中实现类似功能的sql写法。 建立测试表 create tabl...
HiveSQL中last_value和first_value函数的应用
weixin_36230541的博客
08-04 253
概述今天做一个数据分析，其中有一列数据有些有数据有些没数据，因此我们需要把每数据的进行补充进来因此我们需要使用last_value 函数和over 结合使用，但是遇到一个比较奇葩的问题不能按照预期进行处理。 新说原因： 由于我们要处理的列，不是 null 而是空字符导致函数失效遇到这个问题我思考的几个思路：last_val...
oracle sql last_value,図でイメージするOracle DatabaseのSQL全集 第3回 分析関数
weixin_31181381的博客
04-03 157
指定したソートキーでの、最初の行の値を求めるのが、First_Value関数。指定したソートキーでの、最後の行の値を求めるのが、Last_Value関数。指定したソートキーでの、(Row_Numberな順位が)n番目の行の値を求めるのが、nth_Value関数となります。Oracle11gR2でnth_Value関数が追加されました。create table FirstLastSample(ID,...
hive的max()、min()等内置计算函数会自动忽略null
攻城狮Kevin
03-25 8101
select max(id) from table时，如果字段id中存在空值null，或者字段中全部为空值null，sql语法不会报错，前者会忽略null取个最大值，后者直接max出来的结果就是null
Hive 窗口函数LEAD LAG FIRST_VALUE LAST_VALUE
SmartSi
02-22 9062
窗口函数（window functions）对多行进行操作，并为查询中的每一行返回一个值。 OVER()子句能将窗口函数与其他分析函数（analytical functions）和报告函数（reporting functions）区分开来。1. 常用窗口函数下表列出了一些窗口函数以及描述信息：窗口函数描述LAG()LAG()窗口函数返回分区中当前行之前行（可以指定第几行）的值。 如果没有行，则返回
OVER(PARTITION BY)函数用法
weixin_34273046的博客
10-26 3992
 2010年10月26日OVER(PARTITION BY)函数介绍开窗函数               Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：1：over后的写法：     ...
hive first_value/last_value函数
05-31
在Hive中，first_value和last_value函数是窗口函数之一，用于计算指定列的第一个和最后一个非空值。 以first_value为例，语法如下： ``` first_value(expr) OVER ( [PARTITION BY partition_expression, ...] [ORDER BY sort_expression [ASC|DESC], ...] ) ``` 其中，expr是要计算第一个非空值的列或表达式。PARTITION BY子句指定要分区的列，ORDER BY子句指定按照哪个或哪些列排序。 例如，以下查询返回每个部门的第一个雇员姓名： ``` SELECT dept, first_value(name) OVER (PARTITION BY dept ORDER BY hire_date) as first_employee FROM employees; ``` last_value函数的使用方法与first_value类似，只是计算的是指定列的最后一个非空值。 需要注意的是，使用窗口函数需要在Hive版本0.11及以上才支持。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值