1. 常用函數(shù)
數(shù)據(jù)本體：
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語(yǔ)句
select id, name, score,
if (score<60,"Bujige", "Jige")? as PingDing from student;
1.3 插入數(shù)據(jù)
先要插入數(shù)據(jù)，兩種方法，一種是直接values，一種是select另外一個(gè)子表作為
insert into table student values(5, "laoxing",null);?
insert into table student? ? (select 6, "guyizaodekong", null)；
1.4 nvl函數(shù)：控制轉(zhuǎn)換函數(shù)
select id, score, nvl(score, 0) from student;
1.5 其他常用函數(shù)
https://www.cnblogs.com/MOBIN/p/5618747.html
2. 窗口函數(shù)
2.0 數(shù)據(jù)本體
數(shù)據(jù)內(nèi)容
1,18,a,male
2,19,a,male
3,22,a,female
4,16,b,female
5,30,b,male
6,26,b,female
創(chuàng)建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;
加載數(shù)據(jù)：
load data local inpath "" into table userinfo;
2.1 row_number() over()
得到每個(gè)性別中年齡最大的2條數(shù)據(jù)。
select?
*,
row_number() over(partition by sex order by age desc) rn
from userinfo;
select * from aaaa where rn <3;
可以用來(lái)去重。
2.2 sum() over()
得到每個(gè)月的累加和。
數(shù)據(jù)：
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
建表的語(yǔ)句：
drop table saleinfo;
create table saleinfo ( product_name? string,
month string,?
?money string)
row format delimited fields terminated by ',';
導(dǎo)入語(yǔ)句：
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;
