求教：docker 鏡像制作：由平時安裝作業系統的鏡像轉換成docker鏡像的方法，求教了
uj5u.com熱心網友回復：
Hive中求交集和差集的兩種方法:1.交集:
select id from t1 union select id from t2
select id from t1 intersect select id from t2
2.差集
select a.id from t1 left join t2 on t2.id = t1.id and t2.id is null;
select id from t1 except select id from t2
uj5u.com熱心網友回復：
Hive和Oracle行列轉換實體總結--hive寫法1
select dt,'開戶' as stat_item, if(move_channel is null,'',move_channel) as move_channel,stat_value,
case when grouping__id = '1' then '總計'
when grouping__id = '3' then '合計'
else move_path
end as move_path,
case row_number()over(partition by dt, move_channel, move_path, by stat_value)
when 1 then stat_type[0]
when 2 then stat_type[1]
when 3 then stat_type[2]
else null
end stat_type
from(select dt,move_channel,move_path,grouping__id,
(split(concat_ws(',','客戶數','比上日','比上月'),',')) as stat_type,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from pabrdm.tb1_ca01_move_channel_day
where move_channel_code like '01%'
group by dt,move_channel,move_path
with rollup
)x
lateral view explode(split(concat_ws(',',cust_cnt,cust_cnt_pre_day,cust_cnt_pre_mon),',')) cust as stat_value
where dt is not null
--hive寫法2: 改進SQL
select dt,'開戶' as stat_item, if(move_channel is null,'',move_channel) as move_channel,stat_value,
case when grouping__id = '1' then '總計'
when grouping__id = '3' then '合計'
else move_path
end as move_path,
explode(map('客戶數',cust_cnt,'比上日',cust_cnt_pre_day,'比上月', cust_cnt_pre_mon)) as stat_type
from(select dt,move_channel,move_path,grouping__id,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from pabrdm.tb1_ca01_move_channel_day
where move_channel_code like '01%'
group by dt,move_channel,move_path
with rollup
)x
where dt is not null
uj5u.com熱心網友回復：
--oracle寫法1select to_date(dt,'yyyy-mm-dd') "統計日期", move_channel "機構", move_path "遷徙路徑",'開戶' "指標型別",cn "指標項",cv "指標值"
from (select dt,
decode(grouping(move_channel)+grouping(move_path),2,null,move_channel) move_channel,
decode(grouping(move_channel)+grouping(move_path),1,'合計',2,'CA02專案總計',move_path) move_path,
sum(cust_cnt) cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) cust_cnt_pre_mon
end cust_cnt_pre_mon
from tbl_ca01_move_channel_day
where move_channel_code like '01%'
group by rollup(dt,move_channel,move_path)
)
where dt is not null
model
return updated rows
partition by (dt,move_channel,move_path)
dimension by (0 as n)
measures('xxxxxxxxxxxxxxxxxxxx' as cn, 'yyyyyyyyyyyyyyyyy' as cv,cust_cnt as c1, cust_cnt_pre_day as c2, cust_cnt_pre_mon as c3)
rules upsert all
(
cn[1] = '客戶數',
cn[2] = '比上日',
cn[3] = '比上月',
cv[1] = c1[0],
cv[2] = c2[0],
cv[3] = c3[0]
)
--oracle寫法2: 改進SQL
select move_channel "渠道",move_path "遷徙路徑",'開戶' "指標型別",cn "指標項",cv "指標值"
from (select decode(grouping(move_channel)+grouping(move_path),2,null,move_channel) move_channel,
decode(grouping(move_channel)+grouping(move_path),1,'合計',2,'CA01專案總計',move_path) move_path,
sum(cust_cnt) as cust_cnt,
sum(cust_cnt) - sum(cust_cnt_pre_day) as cust_cnt_pre_day,
sum(cust_cnt) - sum(cust_cnt_pre_mon) as cust_cnt_pre_mon
from tbl_ca01_move_channel_day
where move_channel_code like '01%'
group by rollup(dt,move_channel,move_path)
)
unpivot(cv for cn in(cust_cnt as '客戶數',cust_cnt_pre_day as '比上日', cust_cnt_pre_mon as '比上月')
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/58918.html
標籤：Docker
上一篇：spark讀取hdfs中lzo檔案出現問題！！！！！lzo-hadoop-2.6
下一篇：hashMap的實作原理