一場pandas與SQL的巔峰大戰(zhàn)（四）
一場pandas與SQL的巔峰大戰(zhàn)（六）

日志表/離線表（不準(zhǔn)是正常的，可能因為系統(tǒng)切換/關(guān)機(jī)重啟等導(dǎo)致異常），而t_tznew_date_user_uv是緩存表（準(zhǔn)的）。

排序

【ROW_NUMBER() 】會根據(jù)順序計算。
【DENSE_RANK()】排序相同時會重復(fù)，總數(shù)會減少。例如1223
【RANK() =sumproduct：造成少發(fā)錢】排序相同時會重復(fù)，總數(shù)不會變。例如1334
EXCEL中的sumproduct函數(shù)即此。
【EXCEL中的rank函數(shù)：造成多發(fā)錢】例如1224

EXCEL中的rank函數(shù)

字符串

【1】兩者都是替代函數(shù)，但HIVE中有translate，而HIVE中無replace函數(shù)；
【2】replace針對的是字符串，而translate針對的是單個字符。

時間的不同表示替換方法
SELECT translate(CAST(stat_date as STRING),'-','') as dt

日期

10位和13位時間戳分別都是怎么產(chǎn)生的：
10位時間戳是把時間精確到秒級；
13位時間戳是把時間精確到毫秒級，所以兩者是1000倍的關(guān)系；

今日：
SELECT CURRENT_DATE

上周的今日：
select date_sub(CURRENT_DATE,7)

字符串變換20201115轉(zhuǎn)換成2020-11-15、字符串變?nèi)掌冢╰o_date()）、并獲取周數(shù)：
【1：concat_ws】weekofyear(to_date(concat_ws('-',substr(dt,1,4),substr(dt,5,2),substr(dt,7,2))))`weekofyear`
【2：concat】concat('19',substr(idcard,7,2),
                            '-', substr(idcard,9,2),
                            '-', substr(idcard,11,2)
                            ) 

日期>>>>時間戳
select unix_timestamp()   --1565858389
日期>>>>時間戳   【不需要加單引號】
select 1607434525,'1607434525'
,unix_timestamp(),from_unixtime(unix_timestamp(),'yyyy-MM-dd'),from_unixtime(1607434525,'yyyy-MM-dd')

將20190410這種格式的日期轉(zhuǎn)換為2019-04-10的格式【先將ord_dt轉(zhuǎn)換為Unix時間戳，再將時間戳秒數(shù)轉(zhuǎn)換為指定格式的日期】
select from_unixtime(unix_timestamp('20190410','yyyymmdd'),'yyyy-mm-dd');
select from_unixtime(unix_timestamp('2019-04-10','yyyy-mm-dd'),'yyyymmdd');

時間戳>>>>日期
select from_unixtime(unix_timestamp(),'yyyy-MM-dd'),from_unixtime(unix_timestamp(),'yyyyMMdd')

取當(dāng)前天的下一個周一：
select next_day('2019-12-12','MO');

取當(dāng)前周的周一：
select date_add(next_day('2019-12-12','MO'),-7);

其他

【下面的寫法是對的，否則union all (select ...)aa】

不去重的union all：
select a.id,a.name from a
union all
select b.sid,b.sname from b

不去重的union all【下面的寫法是對的，否則union all (select ...)aa】：
坑1：
            select dt,uid,success_uv from hdp_58_ubu_sjmobile_defaultdb.month10
            group by dt,uid,success_uv
            union ALL
            SELECT  dt,uid,(uv + new_uv) AS success_uv
            FROM  hdp_ubu_tech_wei_defaultdb.t_tznew_date_user_uv
            where dt>='20201101' and dt<='20201116'  --此處我出錯了：dt>='20201101' 而非dt>='20201001'
            group by dt,uid,(uv + new_uv)
坑2：改變success_uv的字段類型（int與str，會強行合并成str，造成與int比較報錯！）


顯示百分比形式【保留小數(shù)點后四位 * 100，連接%】
concat(round(x/y, 4) * 100, '%')

避免重復(fù)值的2種方法：
【1】select  distinct user_id
【2】select  user_id...group by user_id

【case when的用法】
case when length(idcard) = 18 then
            case when substr(idcard,17,1)%2 = 0 then 'F' 
                 when substr(idcard,17,1)%2 <> 0 then 'M'
                 else null end
       when length(idcard) = 15 then 
            case when substr(idcard,15)%2 = 0 then 'F' 
                 when substr(idcard,15)%2 <> 0 then 'M'
                 else null end 
       else null end  as sex

【HIVE不支持非等值連接，但可以通過locate()函數(shù)進(jìn)行功能轉(zhuǎn)換】Both left and right aliases encountered in JOIN 'pipei'
select * from aa
    left join 
    hdp_58_ubu_sjmobile_defaultdb.ceshi cc
    on 1=1
    where concat(bb.city1_name,bb.city2_name,bb.city3_name) >= cc.pipei)dd
-------此為true------ concat(bb.city1_name,bb.city2_name,bb.city3_name) >= cc.pipei

    left join 
    hdp_58_ubu_sjmobile_defaultdb.ceshi cc
    ON bb.city1_name=cc.province  
    -- 不用on(True),因為需要設(shè)置 set hive.mapred.mode=nonstrict，而云窗貌似不支持？
    where locate(cc.pipei,concat(bb.city1_name,bb.city2_name,bb.city3_name))>0

Hive的不等值連接
JouyPub重要博客
工作中常見的hive語句總結(jié)