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