Hive 常見函數

1. HQL 判斷空的函數

https://www.w3school.com.cn/sql/func_date_format.asp

2. 時間轉換函數

year(string date)：傳回年份部分的日期或時間戳字元串：year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970

https://www.yiibai.com/hive/hive_built_in_functions.html

3. group by 函數，需要多次進行分組

4. hive擷取上個月第一天和最後一天的時間

//擷取上個月第一天
date('Y-m-01',strtotime('-1 month'));
 
//擷取上個月最後一天
date('Y-m-t',strtotime('-1 month'));      

SELECT
--上個月第一天
concat(SUBSTR(DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP()))),1,7) ,'-01'),
--上個月最後一天
DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))

select
--上個月第一天
trunc(add_months(CURRENT_TIMESTAMP,-1),'MM'),
--上個月第一天
concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01'),
--上個月最後一天
date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);      

5. 當月第一天

-- 當月第一天
SELECT TRUNC(sysdate(0), 'MM') ;      

6. 前兩個月第一天

-- 前兩個月第一天
select TRUNC(add_months(CURRENT_TIMESTAMP, -2), 'MM');      

7. CAST 轉換小數保留位數

cast(column_name as decimal(10,2)) cast函數四舍五入（推薦使用）

select cast(68.666666666666668 as decimal(10,2));
_c0
68.67

select cast(68.666666666666668 as decimal(10,3));
_c0
68.667      

8. 求兩數除數,保留兩位小數

ROUND((SUM(delivered_num) / SUM(plan_num) * 100),2)

cast(column_name as decimal(10,2)) cast函數四舍五入（推薦使用）

CAST((SUM(delivered_num) / SUM(plan_num) * 100) AS DECIMAL(10, 2))      

9. Presto 引擎的數值轉換

SELECT 
CAST(
    CAST('913' AS DOUBLE) / CAST('1000' AS DOUBLE) * cast('100' as DOUBLE) as DECIMAL(10,2)
);

先轉成 double,在進行乘除運算,最後再轉成 decimal
==> 91.3

列别名一定要用"雙引号"      

10. if 語句

if null 判斷語句

函數：
1. coalesce( value1,value2,… )
2. if( value1 is null, value2, value1)
IF( Test Condition, True Value, False Value ) 

例子：
hive> select coalesce(col1, col2, cols) as res1, if(col1 is null, if(col2 is null, col3, col2), col1) as res2
    > from(
    >     select 1 as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, 2 as col2, 3 as col3 
    >     union all
    >     select null as col1, null as col2, 3 as col3 
    >     union all 
    >     select null as col1, null as col2, null as col3
    > ) as test

結果：
    res1   res2
    1      1
    2      2
    3      3
    null   null      

11. 一對多表關聯去重統計

背景：假設我們是一個電商網站，用使用者下單，訂單有來源資料，每個訂單會有多個訂單明細，訂單明細記錄了商品相關資訊、以及商品的出庫、發貨、收貨時間都不同。

需求：想要統計訂單次元相關的資料，要求統計出訂單不同來源下的出庫單量、發貨單量、收貨單量。

分析：訂單和訂單明細是一對多的關系，一個訂單有多個明細，每個明細的商品都有各自的出庫、發貨、收貨時間，也有可能未及時發貨、出庫、收貨之類的導緻為空，想要按照訂單次元統計這些量，隻需要判斷訂單中對應的多個明細中，隻有出庫、發貨。收貨時間即可，然後再彙總。

實作：

1. 先統計基礎的資料，訂單與訂單明細關聯，查詢訂單id、訂單來源、以及根據出庫時間、發貨時間、收貨時間是否存在來判斷是否出庫、是否發貨、是否收貨；
2. 根據訂單來源、訂單id進行分組，然後利用單行函數 max 取出具體id分組下的是否出庫、發貨、收貨值；
3. 最後在統計上一步查的資料，再按照訂單來源分組，利用單行函數 sum 來求訂單總數、出庫、發貨、收貨總單量。

with base_data as (
  select
    source, -- 下單來源
    max(if_out) if_out, -- 是否出庫
    max(if_send) if_send, -- 是否發貨
    max(if_received) if_received -- 是否收貨
  from (
    select
      o.id, -- 訂單id
      o.source, -- 來源
      (
        case when od.out_time is not null then 1
        else 0
        end
      ) as if_out, -- 是否出庫
      (
        case when od.send_time is not null then 1
        else 0
        end
      ) as if_send, -- 是否發貨
      (
        case when od.received_time is not null then 1
        else 0
        end
      ) as if_received -- 是否收貨
    from order o
    left join order_detail od on o.id = od.order_id
    where o.create_time > '2021-04-01'
  ) a 
  group by source,id
)
select
  source, -- 下單來源
  sum(1), -- 訂單數量
  sum(if_out), -- 出庫總單量
  sum(if_send), -- 發貨總單量
  sum(if_received) -- 收貨總單量
from base_data
group by source