sql练习

1.求单月访问次数和总访问次数

CREATE EXTERNAL TABLE IF NOT EXISTS t_access(
uname STRING COMMENT '用户名',
umonth STRING COMMENT '月份',
ucount int COMMENT '访问次数')
COMMENT '用户访问表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

insert into t_access values
('A','2015-01',5),
('A','2015-01',15),
('B','2015-01',5),
('A','2015-01',8),
('B','2015-01',25),
('A','2015-01',5),
('A','2015-02',4),
('A','2015-02',6),
('B','2015-02',10),
('B','2015-02',5),
('A','2015-03',16),
('A','2015-03',22),
('B','2015-03',23),
('B','2015-03',10),
('B','2015-03',11);

每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数，结果数据格式如下
输出：用户、月份、最大访问次数、总访问次数、当月访问次数

select
uname,umonth,
max(a.sum_c) over(partition by uname order by umonth),
sum(a.sum_c) over(partition by uname order by umonth),
a.sum_c
from
(
select uname,umonth,sum(ucount) sum_c
from t_access
group by umonth,uname
) a

2.学生课程成绩

CREATE TABLE IF NOT EXISTS courses_scores
(id INT,
sid int,
course STRING,
score INT)
row format delimited fields terminated by ",";

INSERT INTO courses_scores VALUES 
(1, 1, 'yuwen', 43),
(2, 1, 'shuxue', 55),
(3, 2, 'yuwen', 77),
(4, 2, 'shuxue', 88),
(5, 3, 'yuwen', 98),
 (6, 3, 'shuxue', 65);

字段：序号、学号、课程、分数
所有数学课程成绩 大于 语文课程成绩的学生的学号
输出：
学号 数学成绩 语文成绩

select
a.sid,b.score,a.score
from
(select
*
from
courses_scores
where course="yuwen") a
join
(select
*
from
courses_scores
where course="shuxue") b
on a.sid=b.sid
where b.score>a.score;

3.求每一年最大气温的那一天 + 温度

数据格式
2010012325
表示2010年1月23日的气温是25度

CREATE table IF NOT EXISTS temperature
(timetemp STRING)
row format delimited fields terminated by ",";

INSERT into temperature VALUES
('2014010114'),
('2014010216'),
('2014010317'),
('2014010410'),
('2014010506'),
('2012010609'),
('2012010732'),
('2012010812'),
('2012010919'),
('2012011023'),
('2001010116'),
('2001010212'),
('2001010310'),
('2001010411'),
('2001010529'),
('2013010619'),
('2013010722'),
('2013010812'),
('2013010929'),
('2013011023'),
('2008010105'),
('2008010216'),
('2008010337'),
('2008010414'),
('2008010516'),
('2007010619'),
('2007010712'),
('2007010812'),
('2007010999'),
('2007011023'),
('2010010114'),
('2010010216'),
('2010010317'),
('2010010410'),
('2010010506'),
('2015010649'),
('2015010722'),
('2015010812'),
('2015010999'),
('2015011023');

select
bb.yy,bb.mm,bb.dd,c.max_t
from
(
select
yy,max(temper) max_t
from
(
select
substr(ttdate,1,4) yy,substr(ttdate,5,2) mm,substr(ttdate,7,2) dd,temper
from
(
select
substr(timetemp,1,8) ttdate,
substr(timetemp,9) temper
from
temperature
) a
) b
group by b.yy
) c
join (
select
substr(ttdate,1,4) yy,substr(ttdate,5,2) mm,substr(ttdate,7,2) dd,temper
from
(
select
substr(timetemp,1,8) ttdate,
substr(timetemp,9) temper
from
temperature
) a
) bb
on c.yy=bb.yy and c.max_t=bb.temper;

5、求月销售额和总销售额

字段：店铺，月份，金额
求出每个店铺的当月销售额和累计到当月的总销售额

create table t_store(
name string,
months int,
money int
)
row format delimited fields terminated by ",";

INSERT into  t_store VALUES
('a',01,150),
('a',01,200),
('b',01,1000),
('b',01,800),
('c',01,250),
('c',01,220),
('b',01,6000),
('a',02,2000),
('a',02,3000),
('b',02,1000),
('b',02,1500),
('c',02,350),
('c',02,280),
('a',03,350),
('a',03,250)

select
a.name,a.months,a.sum_mon,sum(sum_mon) over(partition by name order by months)
from
(
select
name,months,sum(money) sum_mon
from
t_store
group by name,months
) a