您现在的位置是：首页 >其他 >hive如何实现oracle中复杂的update sql网站首页其他

hive3.1有update语法，但是目前没用还是采用的非事务表，所以我们用其他的办法来解决hive的update问题

简单的update

oracle

update student  set name='cclovezbf' where id=1

hive  

insert overwrite table student

select 

id,

if(id=1,'cclovezbf',name) name

from student

最近在改oracle 的pck为hivesql的时候 遇到了一个相对复杂一丢丢的update，特意来分享下如何处理。

oracle pck

 UPDATE DWDMDATA.DM_CE_F_PORTRAIT_CASH_FORECAST t
 SET (T.YIELD_ON, T.YIELD_OFF, T.YIELD_TOTAL) =
     (SELECT 
     T.INTEREST_INCOME_ON /
     AVG(T1.GROSS_CASH_GROUP_USD_ON + T1.GROSS_CASH_GROUP_CNY_ON + T1.GROSS_CASH_INVEST_ON) * 12,

     T.INTEREST_INCOME_OFF /
     AVG(T1.GROSS_CASH_GROUP_USD_OFF + T1.GROSS_CASH_GROUP_CNY_OFF + T1.GROSS_CASH_INVEST_OFF) * 12,

     T.INTEREST_INCOME_TOTAL /
     AVG(T1.GROSS_CASH_GROUP_USD_ON +T1.GROSS_CASH_GROUP_CNY_ON +T1.GROSS_CASH_INVEST_ON +T1.GROSS_CASH_GROUP_USD_OFF +T1.GROSS_CASH_GROUP_CNY_OFF +T1.GROSS_CASH_INVEST_OFF) * 12

     FROM DWDMDATA.DM_CE_F_PORTRAIT_CASH_FORECAST t1
     WHERE T1.PERIOD_ID IN
     (T.PERIOD_ID, TO_CHAR(ADD_MONTHS(TO_DATE(T.PERIOD_ID, 'YYYYMM'), -1), 'YYYYMM'))
     AND T.PERIOD_TYPE = T1.PERIOD_TYPE
     AND T.VERSION_NAME = T1.VERSION_NAME
     AND T.FORECAST_YEAR = T1.FORECAST_YEAR
     AND T.DATA_TYPE = T1.DATA_TYPE)
     WHERE T.PERIOD_TYPE = 'MONTH'
     AND T.FORECAST_YEAR = SUBSTR('${p_period_id}', 0, 4);

原始sql比较长 贴出来有兴趣的可以后面自己改写。

为了方便大家学习和阅读，自己建了一个类似的看起来轻松点的表

CREATE TABLE test.cc_test_update2(
period_id varchar(200),
name varchar(200),
yuwen NUMBER,
shuxue NUMBER,
avg_score number
)

INSERT INTO test.cc_test_update2 values('2019','cc' ,70    ,80  ,null)
INSERT INTO test.cc_test_update2 values('2020','cc' ,80    ,90  ,null)
INSERT INTO test.cc_test_update2 values('2021','cc' ,90    ,100 ,null)
INSERT INTO test.cc_test_update2 values('2019','zbf',75    ,85  ,null)
INSERT INTO test.cc_test_update2 values('2020','zbf',85    ,95  ,null)
INSERT INTO test.cc_test_update2 values('2021','zbf',95    ,105 ,null)

oracle的update

UPDATE test.cc_test_update2 t 
SET AVG_SCORE =(SELECT  avg((t.YUWEN +t1.SHUXUE)/2)
FROM test.cc_test_update2 t1  
WHERE t.name =t1.NAME  
AND t.PERIOD_ID IN (t1.PERIOD_ID ,t1.PERIOD_ID+1 ) )

这里update的具体含义是计算平均分，是当年的语文和数学的平均分 与 当年的语文和去年数学的平均分  这两个平均分的平均分

这里可能有点绕，没法我要给个含义给你们好理解。后面我会详细介绍

update后的结果 

以前我接触的update比较少，当时第一次看到这种sql还愣了一下，还需要花点时间理解下。

我们先看下每年的平均分

SELECT t.*,(YUWEN+SHUXUE)/2  FROM test.cc_test_update2 t 

这个没啥说的 三岁小孩都会的sql 

再看当前的shuxue和yuwen平均分与  当前的yuwen和去年shuxue的平均分

WITH  tmp AS (
    SELECT PERIOD_ID ,name,YUWEN ,SHUXUE ,
    lag(SHUXUE ,1,SHUXUE)over(PARTITION BY NAME ORDER BY PERIOD_ID  ) pre_shuxue
    FROM test.cc_test_update2 t 
    )
SELECT  t.*,
(YUWEN+SHUXUE)/2 当年的语文和数学平均分,
(YUWEN+pre_shuxue)/2 当前的语文和去年数据的平均分,
((YUWEN+SHUXUE)/2+((YUWEN+pre_shuxue)/2))/2  上面两个平均分的平均分
FROM tmp t 

 可以看到和我们上面update后的结果一模一样，

所以我们只需要把上面的结果insert下就好了。

此次改写sql有几个注意点。

1.需求是需要去年的数学分数的 所以我们需要使用lag函数 或者lead。

   还有注意首年是没有上一年的比如2019年的上一年是2018年我们没有数据，所以lag需要有个默认值就是我们的自己本身  (当年+当年)/2=当年。

2.上面使用的是avg函数 所以我们后面使用的是/2  为什么呢？因为t.PERIOD_ID IN (t1.PERIOD_ID ,t1.PERIOD_ID+1 ) 这里就是2年，

如果这里改为3年了呢？我们要除以3，同时lag函数要获取去年 还有前年的数据，这样是不是很麻烦呢，我暂时也没办法。