SELECT 列名称
FROM 表名
WHERE 条件1
GROUP BY 列名 HAVING 条件2
ORDER BY  列名 ASC/DESC;

(8)SELECT (9)DISTINCT  (11)<Top Num><select list>
(1)FROM[left_table]
(3)<join_type>JOIN<right_table>
(2)        ON<join_condition>
(4)WHERE<where_condition>
(5)GROUPBY<group_by_list>
(6)WITH<CUBE | RollUP>
(7)HAVING<having_condition>
(10)ORDERBY<order_by_list>

count([distinct|all] 字段1)
count(*),计算包含null行数
count(a),计算不包含a位null的行数

max, min, sum, avg, sum, avg
var_pop, var_samp 方差和样本方差
stddev_pop, stddev_samp 偏差和样本偏差
covar_pop(col1,col2), vovar_samp 协方差和样本协方差
corr(col1,col2) 相关系数
count(DISTINCT col1) - 合适的函数，都可以接受DISTINCT

-- 只取出分组内的个数大于2的那些的分组
HAVING count(*)>2;

-- 平均工资高于 20000 的哪些分组
HAVING AVG(salary)>20000;

-- 总销售额大于 100 的
HAVING SUM(amount) > 100;

SELECT field1, field2, ... ,fieldn
from join_tablename1
inner join join_tablename2
on joincondition;

SELECT a.id, a.name, b.salary, c.department
FROM table_name1 a
INNER JOIN table_name2 b
ON a.id=b.id
INNER JOIN table_name3 c
ON a.id=c.id;

SELECT a.id, a.name, b.salary, c.department
FROM table_name1 a, table_name2 b, table_name3 c
WHERE a.id=b.id AND a.id=c.id;

SELECT * FROM t_employee
WHERE 
-- 支持多行多列
(sal,job) IN (SELECT sal,job FROM t_employee)
-- NOT IN
AND (sal,job) NOT IN (SELECT sal,job FROM t_manager)
-- 等号
AND (dept_id,dept_name) = (SELECT dept_id,dept_name FROM t_engineer)
-- 可以是枚举值
AND job IN ("算法工程师", "会计师");
;

>
<
=
!=、<>
>=
<=
between 取值1 and 取值2

is null
in（1，2)

like            通配符匹配
-- %表示多个字符或0个，
-- _表示单个字符,
-- [charlist]字符列中的任意单一字符,
-- [^charlist]不在字符列中的任意单一字符


regexp        正则表达式
    举例：‘cjgong’regexp 'g$' 返回1
    ^        匹配开始部分
    $        匹配结束部分
    .        匹配任意一个字符
    [字符集合]        匹配集合中的任意一个字符
    [^字符集合]        匹配集合中外的任意一个字符
    str1|str2|str3        匹配任意一个字符串
    *
    +
    字符串{N}            字符串出现N次
    字符串(M,N)        字符串出现至少M次，最多N次

-- 公式：
case 列名
    when   条件值1   then  选择项1
    when   条件值2    then  选项2.......
    else     默认值      
end

-- 例子：
select
    case    job_level
        when    '1'     then    'rich'
        when    '2'     then    'middle'
        when    '3'     then    'poor'
        else       'not sure'
    end
from table_employee

-- 公式：
case  
    when  条件值1   then  选择项1
    when  条件值2   then  选择项2
    ...
    else 选择项n
end


-- 例子：
case
    when   job_level = '1'    then e_wage*1.97
    when   job_level = '2'   then e_wage*1.07
    when   job_level = '3'   then e_wage*1.06
    else     e_wage*1.05
end

distinct 字段1

decimal, double, Integer, smallint,real  
Hex(arg):转化为参数的16进制表示。  
转化为字符串类型的：  
char, varchar  
Digits(arg):返回arg的字符串表示法，arg必须为decimal。  
转化为日期时间的：  
date, time,timestamp  

year, quarter, month, week, day, hour, minute, second  
dayofyear(arg)  
Dayofweek(arg)  
days(arg):返回日期的整数表示法，从0001-01-01来的天数。   
midnight_seconds(arg):午夜和arg之间的秒数。  
Monthname(arg):返回arg的月份名。  
Dayname(arg):返回arg的星期。  

SELECT to_date('2008-12-29 16:25:46'); -- 返回日期
SELECT date_sub('2008-12-29',5); -- 日期减去一个天数
SELECT date_add('2008-12-29',5); -- 日期增加一个天数
SELECT datediff('2008-12-19','2008-12-10'); -- 两个日期之差

coalesce(arg1,arg2….) -- 返回参数集中第一个非null参数。
greatest(col1,col2,...,coln) -- 返回多列中，最大的那个值
least()

length,lcase, ucase, ltrim , rtrim  
CONCAT(arg1,arg2) -- 连接两个字符串arg1和arg2。  
insert(arg1,pos,size,arg2) -- 返回一个，将arg1从pos处删除size个字符，将arg2插入该位置。  
left(arg,length) -- 返回arg最左边的length个字符串。  
right(arg,length) -- 返回一个有arg右边length个字节组成的字符串。
locate(arg1,arg2,<pos>;) -- 在arg2中查找arg1第一次出现的位置；如果指定pos，则从arg2的pos处开始查找  
posstr(arg1,arg2) -- 返回arg2第一次在arg1中出现的位置。  
repeat(arg1 ,num_times) -- 返回arg1被重复num_times次的字符串。  
replace(arg1,arg2,arg3) -- 将在arg1中的所有arg2替换成arg3。  
space(arg) -- 返回一个包含arg个空格的字符串。  
substr(arg1,pos,<length>;):返回arg1中pos位置开始的length个字符，如果没指定length，则返回剩余的字符。
initcap(col) -- 每个单词的首字母大写（一个字符串中可以有多个单词）

contains：CONTAINS( 字段1, '"HEIBEI province" OR beijing' )
CONTAINS(*,'beijing')

Abs

ln, log10, log2, log(base,arg)
exp(d), power(a,b)
Power(arg1,arg2) -- 返回arg1的arg2次方。

Mod(arg1,arg2) --返回arg1除以arg2的余数，符号与arg1相同。
Round(arg1,arg2) -- 四舍五入截断处理，arg2是位数，如果arg2为负，则对小数点前的数做四舍五入处理。
Ceil(arg) -- 返回大于或等于arg的最小整数。
Floor(arg) -- 返回小于或等于参数的最小整数。

Rand() -- 返回1到1之间的随机数。
Sign(arg) -- 返回arg的符号指示符。-1,0,1表示。
truncate(arg1,arg2) -- 截断arg1，arg2是位数，如果arg2是负数，则保留arg1小数点前的arg2位。

e(),pi() 常数e和常数pi

factorial(5) -- 阶乘

+
-
*
/
% 余数
A & B 按位与
A|B 按位或
A^B 按位异或
~A 按位反

percentile_approx(col_name,0.25) # 求0.25分位数

function() OVER([partition_clause]  order_by_clause)

-- 拼接行：
CONCAT(col1,col2) -- 拼接每一个字段，按行
CONCAT_WS(",",col1,col2) -- 拼接,并且中间用逗号分隔

-- 把列拼接到一个单元格：（要与group by 配合使用）
-- 注意：新字段是 array 对象
collect_list
collect_set

-- 综合用法：(列拼接成一个单元格，并转为string)
CONCAT_WS(',',COLLECT_LIST(name))
CONCAT_WS(',',COLLECT_LIST(cast (name as string))) -- 非 string 的必须要转格式

SELECT explode(split('a,b,c,d,e',','))

SELECT t1.id,t2.serialno,t2.arr
FROM (SELECT 'xxx' as id,array('a','b','c') as arr) t1
LATERAL VIEW posexplode(t1.arr) t2 AS serialno,arr;

CREATE TABLE tmp_example_json AS
SELECT id,json_str 
FROM(VALUES
    ('0', '[{"name":"王二狗","sex":"男","age":"25"},{"name":"李狗嗨","sex":"男","age":"47"}]'),
    ('1', '[{"name":"王三狗","sex":"女","age":"21"}]')
) AS t(id, json_str)


-- 这种 json 是不带中括号的
CREATE TABLE tmp_example_json2 AS
SELECT id,json_str 
FROM(VALUES
    ('0', '{"name":"王二狗","sex":"男","age":"25"}'),
    ('1', '{"name":"王三狗","sex":"女","age":"21"}')
) AS t(id, json_str)

SELECT id
,GET_JSON_OBJECT(json_str,'$.[0]') -- 提取第0个
,GET_JSON_OBJECT(json_str,'$.[0].name') -- 提取第0个的name属性
FROM tmp_example_json;

-- 对于不带中括号的
SELECT GET_JSON_OBJECT(json_str,'$.name')
FROM tmp_example_json2;

SELECT t1.id, t2.name, t2.age
FROM tmp_example_json2 t1
LATERAL VIEW JSON_TUPLE(json_str, 'name', 'age') t2 AS name, age

SELECT  id
        ,GET_JSON_OBJECT(json_str_split,'$.name') AS name
FROM    (
            SELECT  t1.id
                    ,t2.json_str_split
            FROM    (
                        SELECT  id
                                ,
                                    REGEXP_REPLACE(
                                        REGEXP_EXTRACT(json_str,'^\\[(.+)\\]$',1)    --把前后中括号去掉
                                        ,'\}\,\{\"name"'
                                        ,'\}\|\|\{\"name\"'    --把两个name之间的逗号换成||
                                    )
                                 AS json_str_tmp
                        FROM    tmp_example_json
                    ) t1
            LATERAL VIEW EXPLODE(SPLIT(t1.json_str_tmp,'\\|\\|')) t2 AS json_str_split
        )
;

-- 原始数据
CREATE TABLE tmp_example_json3 AS
SELECT name, sex, age FROM 
(
    VALUES
    ('王二狗','男',23),
    ('李狗嗨','男',29),
    ('王三狗','女',26),
    ('李三刀','女',31)
) AS t(name, sex, age);



-- 生成 JSON
SELECT  TO_JSON(MAP("name", name,"sex", sex,"age", age))
FROM tmp_example_json3;


-- 另外，MAP 结构是这样取数的
SELECT  MAP("name", name,"sex", sex,"age", age)['name']
FROM tmp_example_json3;

-- 或者生成列表嵌套的 JSON
SELECT
  sex
  ,TO_JSON(COLLECT_LIST()(MAP('name', name, 'sex', sex, 'age', age))) AS json_arr
FROM tmp_example_json3
GROUP BY sex;

WITH
tmp_table_01 AS (SELECT 1 AS col1)
-- 前面定义的表，后面可以直接用
,tmp_table_02 AS (SELECT  col1+9 AS col1 FROM    tmp_table_01)
insert overwrite table table_name partition (dt='dt')
SELECT  * FROM    tmp_table_01 UNION ALL SELECT  * FROM    tmp_table_02;

SELECT id,COUNT(id)
FROM table1
WHERE dt = '2018-02-16'
GROUP BY id HAVING COUNT(id) > 1;

select * from
(
select *
,row_number() over(partition by class order by grade_math,grade_english) as rownum
from table
) as temp
where temp.rownum <= 5;

-- 20% 抽样
SELECT * FROM tablename
WHERE rand() < 0.2;

-- 每个类别随机抽 5 个
-- 类似前面，不过 ORDER BY 后面接 rand()


-- 随机抽样，但要求每次运行结果都一样
-- 思路：先做 md5，然后 md5_val % 10 = 1