关于hive 的日常操作的小例子

[xujinli88]

日期处理
regexp_replace(substr(init_delivery_time,1,10),'-','')>=20190101
and regexp_replace(substr(init_delivery_time,1,10),'-','')<=20190218

from_unixtime(unix_timestamp(pay_time,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd')

select REGEXP_REPLACE(matnr,'^0*','') from zt_ssa.zg_atp_pb_val

if case
if(t4.scp_order_no is not null,t4.actual_qty,0) as actual_qty,
case when t3.scp_qty*0.95 <= nvl(t4.actual_qty,0) and t3.scp_qty*1.05 >= nvl(t4.actual_qty,0) then 'Y' else null end as flag

获得是周几
date_format(from_unixtime(unix_timestamp(a.statis_date,'yyyyMMdd'),'yyyy-MM-dd'),'u') dayc

获取当前时间 select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')

根据分不同时间段进行汇总
select gds_cd,supplier_cd,dwh_cd,
nvl(sum(case when real_complete_date=20190108 then dlvy_qty else 0 end),0) dlvy_qty,
nvl(sum(case when real_complete_date>=20190102 then dlvy_qty else 0 end)/7,0) nivtope1,
sum(nvl(dlvy_qty,0))/12 nivtope2
from
(
select t2.gds_cd,t2.supplier_cd,t2.dwh_cd,t2.real_complete_date,t2.dlvy_qty
from
(
select dlvy_cmd_no,dlvy_cmd_item_no,real_complete_date,dwh_cd,gds_cd,supplier_cd,dlvy_qty from dm.big__d
where statis_date>= 20181228 and statis_date<=20190108
and regexp_replace(substr(create_time,1,10),'-','')<=20190108
and regexp_replace(substr(update_time,1,10),'-','')<=20190108

group by dlvy_cmd_no,dlvy_cmd_item_no,real_complete_date,dwh_cd,gds_cd,supplier_cd,dlvy_qty
) t2
where t2.real_complete_date>= 20181228 and t2.real_complete_date<=20190108

)t1
where t1.gds_cd='10002059765' and t1.supplier_cd='10145871' and t1.dwh_cd='DC03'
group by gds_cd,supplier_cd,dwh_cd

获得相应的日期
select DATE('2019-02-28') from DUAL;

select DATE_ADD(DATE('2019-03-03'),INTERVAL -35 DAY) from DUAL;


row_number() over(partition by vendor_cd order by sss.eff_date desc) seq 通过日期降序，对供应商进行排序

select * from (
select sss.gds_cd,sss.vendor_cd,sss.location, sss.eff_date,sss.boxRegulation, row_number() over(partition by vendor_cd order by sss.eff_date desc) seq from (
select af.gds_cd,af.vendor_cd,af.location,af.eff_date,ab.boxRegulation from bi_sor.TSOR_CA_WORKNG_DTL_ED af,
(select a.gds_cd,a.store_cd,substr(b.vendor_cd,3,8) vendor_cd,b.boxRegulation from dm.tdestimate a ,dm.tdm__d b
where a.statis_date=20190303 and b.statis_date=20190303
and a.gds_cd=b.gds_cd and a.store_cd=b.storecode) ab
where af.gds_cd=ab.gds_cd and af.location=ab.store_cd and ab.vendor_cd=af.vendor_cd
) sss
) sa where sa.seq=1




select a.dlvy_cmd_no,a.dlvy_cmd_item_no,a.uuid,'X','30',a.asgmt_bill_type,a.real_complete_date,a.actual_qty,b.scp_order_no,b.item_no,
c.order_type,c.pcs_o,c.bu_code,c.brand_code,c.bsn_mode,c.cmmdty_code,c.cmmdty_name,c.depot_code,c.depot_name,c.supplier_code,
c.supplier_name,c.location_code,c.location_name,c.scp_qty,c.create_time,c.PRICE_KIND,d.str_busi_fmt_cd
from scpdm.TDM_SCPRS_DLVYCMDRCPT_4LOS_D a
left join
(select * from dm.tsor_er_ed where statis_date=20190314 ) b on a.dlvy_cm d_no=b.dlvy_cmd_no and a.dlvy_cmd_item_no=b.dlvy_cmd_item_no
left join
(select * from dm.tsor_m_ed where statis_date=20190314) c on b.scp_order_no=c.scp_order_no and c.item_no=c.item_no
right join
(select * from im.t_lant_td where str_busi_fmt_cd=9 ) d on d.plant_cd=c.depot_code
where a.statis_date=20190314 and a.asgmt_bill_type in ('ZVIN','ZVSI','ZVRE')


COALESCE ('','','',null) --返回第一个不为null 的值， 按顺序找 找到第一个不为null 的值 停止校验



/*造数据用*/
delimiter //
DROP PROCEDURE IF EXISTS mytestpro ; /*如果存在 删除*/
CREATE PROCEDURE mytestpro ()
BEGIN

DECLARE i INT ;
DECLARE j VARCHAR (200) ;
SET i = 2 ;
WHILE i < 1000 DO /*循环开始造数据啦*/

SET j = CONCAT('姓名', i) ;
INSERT INTO cd (`Cd_name`, `Cd_code`) VALUES (j, CONCAT('001', i)) ; /*造数据*/
SET i = i + 1 ;
END
WHILE ;
END//
delimiter ;

CALL mytestpro (); /*调用存储过程 */

DROP PROCEDURE mytestpro /*造完了删除*/




DELIMITER $$
CREATE PROCEDURE prcd_create_data2(IN totalCount int)
begin

DECLARE num int;
set num=10;

while num<totalCount do
set num=num+1;

INSERT INTO xjltest
(
Cd_name,
Cd_code
)
VALUES('1',num);
end while;


END $$
DELIMITER;



--插入表一条数据
insert into dm.T_ES_D PARTITION (statis_date='20191106' )
values
('107423328DC0163000550D0240001','2.0','A002','tyrt','S01','ert','1','1')

--插入表一条数据（会先清空表再插入）
insert overwrite table dm.T_DW_D PARTITION (statis_date='20191106' )
values
('107423328DC0163000550D0240001','2.0','A002','tyrt','S01','ert','1','1')

--插入一条数据利用虚表
insert OVERWRITE table dm.T_DWD__D PARTITION (statis_date='20191106' )
select '107423328DC0163000550D0240001','2.0','A002','tyrt','S01','ert','1','1' from dual


---给表增加一个分区
alter table dm.T_DWD_SCN_D add partition (statis_date='20191105')


create_user REGEXP '[0-9]*'


instr(t2.company_code,t1.PURCHASE_CO_CODE)>0;


select d.zjlf, concat_ws(',',collect_set(d.zsndm)) from
(
select zjlf, zsndm from ss.sap_mdm_z1 where statis_date=20201127 and zsqlx='Y'
group by zjlf, zsndm
) d
group by d.zjlf





select aa.GDS_CD,aa.SUPPLIER_CD,aa.CHNL_CD,
bb.channel_code,cc.channel_code ,
case when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='S' then '03'
when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='O' then '01'
when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='F' then '02'
else '00' end as rel
from (select * from dm.T_DW_TEST where statis_date='20210106') aa
left join
(
select * from ( select product_code,supplier_code,channel_code, ROW_NUMBER() OVER(PARTITION BY product_code,supplier_code ORDER BY version DESC) as rank
from DM.T_SUPP_CHANNEL_D where statis_date=20210106) a where a.rank=1
) bb
on aa.GDS_CD=bb.product_code and aa.SUPPLIER_CD=bb.supplier_code
left join
(
select * from ( select product_code,channel_code, ROW_NUMBER() OVER(PARTITION BY product_code ORDER BY version DESC) as rank
from DM.T_SNEL_D where statis_date=20210106) a where a.rank=1
) cc
on aa.GDS_CD=cc.product_code
where aa.CHNL_CD=case when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='S' then '03'
when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='O' then '01'
when nvl(bb.channel_code,nvl(cc.channel_code,'00')) ='F' then '02'
else '00' end

--bb.channel_code is not null and cc.channel_code is not null