sql入门及大数据概念

一、sql基础

1、空值

sum(NULL) -- NULL
count(NULL) -- 0
count(distinct NULL) -- 0
--举例：
select no,count(cnt),sum(cnt),count(distinct cnt) 
from  
(
    select '001'as no,600 as amt,NULL as cnt
    union 
    select '002'as no,100 as amt,NULL as cnt
    union
    select '002'as no,800 as amt,NULL as cnt
    union
    select '003'as no,800 as amt,2 as cnt
)
group by no
--输出
no		_c1		_c2		_c3
001		0		\N		0
002		0		\N		0
003		1		2		1
-- ==========其他
select NULL+1; -- NULL
select concat(null,'1'); -- NULL
select concat_ws(',',null,'1'); -- NULL

2、json

（1）单字段解析 GET_JSON_OBJECT
某json字段如图：

-- ①单层，解析pid
select GET_JSON_OBJECT('{"pid":123456,"aid":"abcdefg","country":"MX","places":{"place":13,"type":"open"}}','$.pid');  
-- 结果：open
-- ②双层，解析第二层的type
select GET_JSON_OBJECT('{"pid":123456,"aid":"abcdefg","country":"MX","places":{"place":13,"type":"open"}}','$.places.type');  
-- 结果：123456

（2）多字段同时解析 lateral view json_tuple()

select date,card,info
from
        (
        	select '{ "date": "20230129", "card": "ABCDE", "info": "CG030#1" }' as col
        )
lateral view json_tuple(col, 'date', 'card','info') tmp as date,card,info
-- 执行结果
date	        card	info
20230129        ABCDE	CG030#1

3、字符拆分 split/split_part

select split('adr_stability_grade:0.0',':')[0]  -- 结果为  ：adr_stability_grade
select split_part('aaa-bbb-ccc','-',1) -- 结果为  ： aaa
select split_part('aaa-bbb-ccc','-',2) -- 结果为  ： bbb
select split_part('aaa-bbb-ccc','-',3) -- 结果为  ： ccc

4、ceil()/floor()

ceil()
向上取整，返回不小于输入值value的最小整数。

--返回2。
select ceil(1.1);
--返回-1。
select ceil