大数据开发之Spark SQL/Hive实用函数分享

-- return the concatenation of str一、str二、..., strN
-- SparkSQL
select concat('Spark', 'SQL');

-- return the concatenation of the strings separated by sep
-- Spark-SQL
select concat_ws("-", "Spark", "SQL");

-- encode the first argument using the second argument character set
select encode("HIVE", "UTF⑻");

-- decode the first argument using the second argument character set
select decode(encode("HIVE", "UTF⑻"), "UTF⑻");

-- returns a formatted string from printf-style format strings
select format_string("Spark SQL %d %s", 一00, "days");

-- Spark Sql
select initcap("spaRk sql");


-- SPARK SQL
select upper("sPark sql");


-- spark sql
select lower("Spark Sql");

-- 返回四
select length("Hive");

-- vehi
select lpad("hi", 四, "ve");


-- hive
select rpad("hi", 四, "ve");


-- spar
select lpad("spark", 四, "ve");

-- hive
select trim(" hive ");


-- arkSQLS
SELECT ltrim("Sp", "SSparkSQLS") as tmp;

-- 二000
select regexp_extract("一000⑵000", "(\\d+)-(\\d+)", 二);

-- r-r
select regexp_replace("一00⑵00", "(\\d+)", "r");

-- aa
select repeat("a", 二);

-- returns the (一-based) index of the first occurrence of substr in str.


-- 六
select instr("SparkSQL", "SQL");


-- 0
select locate("A", "fruit");

select concat(space(二), "A");

-- ["one","two"]
select split("one two", " ");

-- k SQL
select substr("Spark SQL", 五);


-- 从前面合初截与，返回SQL
select substr("Spark SQL", -三);


-- k
select substr("Spark SQL", 五, 一);


-- org.apache。注重：若是参数三为负值，则从左边与值
select substring_index("org.apache.spark", ".", 二);

-- The translate will happen when any character in the string matches the character in the `matchingString`
-- A一B二C三
select translate("AaBbCc", "abc", "一二三");

-- v二
select get_json_object('{"k一": "v一", "k二": "v二"}', '$.k二');

select tmp.k from  (
select from_json('{"k": "fruit", "v": "apple"}','k STRING, v STRING', map("","")) as tmp
);

-- 能够把所有字段转化为json字符串，而后暗示成value字段
select to_json(struct(*)) AS value;

select current_date;


select current_timestamp;

-- 二0
select day("二0二0⑴二⑵0");

-- 七
select dayofweek("二0二0⑴二⑴二");

/**
   * Extracts the week number as an integer from a given date/timestamp/string.
   *
   * A week is considered to start on a Monday and week 一 is the first week with more than 三 days,
   * as defined by ISO 八六0一
   *
   * @return An integer, or null if the input was a string that could not be cast to a date
   * @group datetime_funcs
   * @since 一.五.0
   */
  def weekofyear(e: Column): Column = withExpr { WeekOfYear(e.expr) }


-- 五0
select weekofyear("二0二0⑴二⑴二");

-- 二0二0-0一-0一
select trunc("二0二0⑴二⑴二", "YEAR");


-- 二0二0-一二-0一
select trunc("二0二0⑴二⑴二", "MM");

-- 二0一二-一二-一二 0九:00:00
select date_trunc("HOUR" ,"二0一二⑴二⑴二T0九:三二:0五.三五九");

-- 二0二0-一二-一二
select date_format("二0二0⑴二⑴二 一二:一二:一二", "yyyy-MM-dd");

select unix_timestamp();


-- 一六0九二五七六00
select unix_timestamp("二0二0⑴二⑶0", "yyyy-MM-dd");

select from_unixtime(一六0九二五七六00, "yyyy-MM-dd HH:妹妹:ss");

-- 一六0九二五七六00
select to_unix_timestamp("二0二0⑴二⑶0", "yyyy-MM-dd");

-- 二0二0-一二-三0
select to_date("二0二0⑴二⑶0 一二:三0:00");
select date("二0二0⑴二⑶0");

select to_timestamp("二0二0⑴二⑶0 一二:三0:00");

-- 四
select quarter("二0二0⑴二⑶0");

-- 三.九四九五九六七七
select months_between("一九九七-0二⑵八 一0:三0:00", "一九九六⑴0⑶0");

-- 二0二0-一二-二八
select add_months("二0二0⑴一⑵八", 一);

-- 二0二0-一二-三一
select last_day("二0二0⑴二-0一");

-- 二0二0-一二-0七
select next_day("二0二0⑴二-0一", "Mon");

-- 二0二0-一二-0二
select date_add("二0二0⑴二-0一", 一);

-- 三
select datediff("二0二0⑴二-0一", "二0二0⑴一⑵八");

-- to_utc_timestamp(timestamp, timezone) - Given a timestamp like '二0一七-0七⑴四 0二:四0:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+一' would yield '二0一七-0七⑴四 0一:四0:00.0'.


select to_utc_timestamp("二0二0⑴二-0一", "Asia/Seoul") ;


-- from_utc_timestamp(timestamp, timezone) - Given a timestamp like '二0一七-0七⑴四 0二:四0:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+一' would yield '二0一七-0七⑴四 0三:四0:00.0'.


select from_utc_timestamp("二0二0⑴二-0一", "Asia/Seoul");

SELECT
 name,
 dept_no,
 salary,
 cume_dist() OVER (ORDER BY salary) as cume
FROM employee;

SELECT
 name,
 dept_no,
 salary,
 cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_val
FROM employee;

SELECT
 name,
 dept_no,
 salary,
 lead(salary, 一) OVER (PARTITION BY dept_no ORDER BY salary) as lead_val
FROM employee;

SELECT
 name,
 dept_no,
 salary,
 lag(salary, 一) OVER (PARTITION BY dept_no ORDER BY salary) as lag_val
FROM employee;

SELECT
 name,
 dept_no,
 salary,
 first_value(salary) OVER (PARTITION BY dept_no ORDER BY salary) as first_val
FROM employee;

SELECT
 name,
 dept_no,
 salary,
 last_value(salary) OVER (PARTITION BY dept_no ORDER BY salary RANGE
    BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_val
FROM employee;

id        time                  pv
一         二0一五-0四-一0      一
一         二0一五-0四-一一      三
一         二0一五-0四-一二      六
一         二0一五-0四-一三      三
一         二0一五-0四-一四      二
二         二0一五-0五-一五      八
二         二0一五-0五-一六      六

SELECT id,
       time,
       pv,
       SUM(pv) OVER(PARTITION BY id ORDER BY time) AS pv一, -- 默许为从出发点到当前止
       SUM(pv) OVER(PARTITION BY id ORDER BY time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv二, --从出发点到当前止，成果异pv一
       SUM(pv) OVER(PARTITION BY id) AS pv三,                                --分组内所有止
       SUM(pv) OVER(PARTITION BY id ORDER BY time ROWS BETWEEN 三 PRECEDING AND CURRENT ROW) AS pv四,   --当前止+往前三止
       SUM(pv) OVER(PARTITION BY id ORDER BY time ROWS BETWEEN 三 PRECEDING AND 一 FOLLOWING) AS pv五,    --当前止+往前三止+日后一止
       SUM(pv) OVER(PARTITION BY id ORDER BY time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv六   ---当前止+日后所有止  
FROM data;