1. 常用函数
数据本体：
1,zhangsan,56.7
2,lisi,78.9
3,wagnwu,90.8
4,赵六,100
1.1 case when
select id, name, score, case when score < 60 then 'bujige'
when score>60 and score<80 then 'youliang'
else 'youxiu' end as pingding
from student;
1.2 if语句
select id, name, score,
if (score<60,"Bujige", "Jige") as PingDing from student;
1.3 插入数据
先要插入数据，两种方法，一种是直接values，一种是select另外一个子表作为
insert into table student values(5, "laoxing",null);
insert into table student (select 6, "guyizaodekong", null)；
1.4 nvl函数：控制转换函数
select id, score, nvl(score, 0) from student;
1.5 其他常用函数
https://www.cnblogs.com/MOBIN/p/5618747.html
2. 窗口函数
2.0 数据本体
数据内容
1,18,a,male
2,19,a,male
3,22,a,female
4,16,b,female
5,30,b,male
6,26,b,female
创建table
use frog_db;
drop table userinfo;
create table userinfo(
id string,
age int,
title string,
sex string)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
加载数据：
load data local inpath "" into table userinfo;
2.1 row_number() over()
得到每个性别中年龄最大的2条数据。
select
*,
row_number() over(partition by sex order by age desc) rn
from userinfo;
select * from aaaa where rn <3;
可以用来去重。
2.2 sum() over()
得到每个月的累加和。
数据：
A,2012-01,1000
A,2012-02,2030
A,2012-03,3600
A,2012-04,6008
A,2012-05,3000
B,2012-01,2000
B,2012-02,2300
B,2012-03,1800
B,2012-04,2000
B,2012-05,1300
B,2012-06,1600
B,2012-07,5000
C,2012-01,1020
C,2012-02,2000
C,2012-03,3200
C,2012-04,6000
C,2012-05,5300
C,2012-06,8800
C,2012-07,9000
建表的语句：
drop table saleinfo;
create table saleinfo ( product_name string,
month string,
money string)
row format delimited fields terminated by ',';
导入语句：
load data local inpath '/home/froghd/data1.txt' into table saleinfo;
累加怎么算？
select *,
sum(money) over(partition by product_name order by month) as money_cumsum from saleinfo;
分组求和：
select product_name, sum(money) from saleinfo group by product_name;
