日常sql/hql梳理(1)

常用sql

聚合

常用的是group by，一般搭配如下函数一起使用

函数名	函数功能
MAX()	返回所选的最大值
MIN()	返回所选的最小值
COUNT()	返回匹配指定条件的行数
AVG()	返回所选的平均值
SUM()	返回数字列的总和

一般还与having搭配使用，控制查询结果只返回满足having条件的结果。

SELECT AGE,count(1)
FROM persion_table
GROUP BY age
HAVING COUNT(1) >= 2;

并表

并表分为左右连接和上下连接，左右连接用join，上下连接用union
join分为以下几种

连接方式	连接字段	用法
内连接	INNER JOIN	返回两个表都有的行（交集）
外连接	OUTER JOIN	返回两个表有的行（并集）
左连接	LEFT JOIN	返回左表有的行
右连接	INNER JOIN	返回右表有的行

实践中不一定是两个不同的表连接，可以单独使用一个表，自己连接自己去实现相关的功能。
union：两个表的列名要一致

SELECT column_a,column_b,column_c FROM table_a
UNION
SELECT column_a,column_b,column_c FROM table_b;

排序

sql中常用order by，但是hql中可以用sort by、distribute by、cluster by；

抽样

抽样一般用rand()，没有参数的话会产生 0 至 1 之间的随机数，存在参数，会产生0到参数之间的随机数；

-- 会返回一个0到100之间的随机浮点数；
SELECT RAND(100);

与其他函数组合使用。如：

函数名
ROUND(x, y)	对处理的数据进行四舍五入，y为保留的小数位数
CEILING(x)	对于数据x向上取整
FLOOR(x)	对于数据x向下取整
MD5(x)	必须要有参数x，计算x对应的MD5摘要，并返回32位的十六进制的字符串

常用内置函数

nvl()

空置转换函数，如果该列存在值为空，则填充为指定值；

-- 如果score为空，则填充为0；
SELECT name，NVL(score, 0) as score from persion_table;

sql中还有ISNULL()、NVL()、IFNULL() 和 COALESCE()与之等效，但是不同数据库关键词不一样。
ISNULL() 是SQL Server / MS Access 中的用法；
IFNULL() 是MySQL中的用法；
待补充

SQL还有一个类似的函数NVL2()，其语法为：NVL2(expr1, expr2, expr3)。如果expr1为空值，则返回expr3的值，否则返回expr2的值。

case when

条件判断函数。

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END

案例如下：

SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS grade
FROM students;

可以多列处理：

SELECT name,
SUM(CASE WHEN subject = 'math' THEN score ELSE 0 END) AS math_total_score,
SUM(CASE WHEN subject = 'math' THEN score ELSE 0 END) * 1.0 /
SUM(CASE WHEN subject = 'math' THEN 1 ELSE 0 END) AS math_average_score,
MAX(CASE WHEN subject = 'math' THEN score ELSE 0 END) AS math_max_score
FROM scores
GROUP BY name;

数据处理函数

SBUSTR()

用法：SBUSTR(string, pos, len)
string ： 指定的要截取的字符串；
pos：必需，规定在字符串的何处开始；
如果pos为正数 - 在字符串的指定位置开始（pos位置从1开始计数）；
如果pos为负数 - 在从字符串结尾的指定位置开始
length：指定要截取的字符串长度；如果不写的话，则表示从指定位置到最后一个字符。

concat()/ concat_ws()

CONCAT 函数用于将多个字符串连接为一个字符串。
语法：CONCAT(string1, string2, string3)

-- 变更股票代码格式
SELECT CONCAT(substr(symbol,8,2),'.',substr(trade_date,1,6)) as symbol from t1

语法：concat_ws(separator, str1, str2, …)
separator：连接符

-- 变更股票代码格式
SELECT CONCAT('.',substr(symbol,8,2),substr(trade_date,1,6)) as symbol from t1

group_concat()
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

-- 合并个股所属概念
select symbol,group_concat(concept_name order by concept_name ) from t2 group by symbol;

split()

语法：split(original_str,separator)[n];
将字段original_str按照指定分隔符分隔，取n+1部分。

--取值第1部分（年份）
select split(‘1990-09-01’,‘-’)[0]

replace()

语法：replace(original_str，search_str，replace_str)
original_str：需要处理的字段名。
search_str： 需要替换的字符。
replace_str：替换标的。如果 该字段为空，则删除出现的所有 search_str。

TRIM()

去除字段前后的空格；
除此之外还有 RTRIM( ), **LTRIM( )**分别去除字段前空格和字段后空格；

窗口函数

语法：<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

-- 按照收盘价进行排序
select *,
rank() over (partition by symbol order by close_price desc) as rank_num
from daily_price

时间处理函数

date_sub

语法：date_sub(date,INTERVAL expr unit)
date：需要处理的时间
expr：需要处理的时间（数字）
unit：需要处理的时间单位（hour、day等）

select now() as BEIJING,date_sub(now(),interval 13 hour)as US;

datediff

语法：datediff(expr1,expr2)
返回日期expr1与expr2相差的天数

date_format

将日期格式转化

select date_format(now(),'%Y-%m-%d') as format_str

current_date

获取当前时间

select now() as curr_time1, 
current_timestamp() as curr_time12,
localtime() as 当前curr_time3;