mysql

create database jingdong;
use jingdong;

create table tbl_clue(
clue_id varchar(10),
city_id varchar(10),
price float,
created_at datetime
);

insert into tbl_clue values('1','10',100,'2016-6-7 15:33'),
						   ('2','12',123.35,'2016-6-7 16:32'),
                           ('3','10',100,'2016-6-8 8:20'),
                           ('4','14',30.25,'2016-6-7 13:00');
                           
create table tbl_city(
city_id varchar(10),
city_name varchar(10)
);

insert into tbl_city values('10','北京'),
						   ('11','上海'),
                           ('12','天津');
                           
create table tbl_contract(
id varchar(10),
clue_id varchar(10),
deal_price float,
created_at datetime
);

insert into tbl_contract values('1','3',100.08,'2016-7-1 0:00'),
							   ('2','2',80.32,'2016-7-1 8:23'),
                               ('3','4',70.11,'2016-7-2 13:22');
                               
select * from tbl_clue;
select * from tbl_city;
select * from tbl_contract;

-- 1、查询6月7日当天各城市的线索量、平均价格，并按照线索量降序排列
select clue_id,avg(price),count(clue_id)
from tbl_clue
where date(created_at)='2016-06-07'
group by clue_id
order by count(clue_id) desc;






-- 2、实现题1的功能前提下，用城市名称替换城市id

select city_name,avg(price),count(clue_id)
from tbl_clue left join tbl_city on tbl_clue.city_id=tbl_city.city_id
where date(created_at)='2016-06-07'
group by tbl_clue.city_id
order by count(clue_id) desc;







-- 3、查询6月7日创建的线索在7月1日成交的合同总量

select count(id) 合同总量
from tbl_contract a 
left join tbl_clue b on a.clue_id=b.clue_id
where date(b.created_at)='2016-06-07' 
	and date(a.created_at)='2016-07-01';





/*
4、hive中sort by,order by,cluster by,distrybute by代表什么意思
order by按照某一列或多列进行排序，与数据库中order by的区别在于hive.mapred.mode = strict模式下必须指定 limit 否则执行会报错。原因： 在order by状态下所有数据会到一台服务器进行reduce操作也即只有一个reduce，如果在数据量大的情况下会出现无法输出结果的情况，如果进行 limit n，那只有n * map number条记录，只有一个reduce也可以处理过来。
sort by不受hive.mapred.mode是否为strict ,nostrict的影响。sort by的数据只能保证在同一reduce中的数据可以按指定字段排序。使用sort by可以指定执行的reduce个数（set mapred.reduce.tasks=<number>）这样可以输出更多的数据。对输出的数据再执行归并排序，即可以得到全部结果。
distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作。distribute by经常和sort by配合使用。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒叙排序，不能指定排序规则为ASC或者DESC。
*/

-- 5、一维表变二维表
create table s(
year varchar(10),
month varchar(10),
amount float
);

insert into s values('1991','1',1.1),
					('1991','2',1.2),
                    ('1991','3',1.3),
                    ('1991','4',1.4),
                    ('1992','1',2.1),
                    ('1992','2',2.2),
                    ('1992','3',2.3),
                    ('1992','4',2.4);

select * from s;
                    







-- 6、表中有ABC三列，当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
create table abc(
	a int,
	b int,
	c int
);

insert into abc values(22,24,23);

select * from abc;


create database gk;
use gk;

create table gaokao
(
kh int(8) ,
km   char(3),
cj  int
);

insert into gaokao
values(2006001,'语文',119),
(2006001,'数学',108),
(2006002, '物理',142),
(2006001, '化学',136),
(2006001,	'物理',127),
(2006002,	'数学',149),
(2006002, '英语'	,110),
(2006002, '语文'	,105),
(2006001, '英语'	,98),
(2006002, '化学'	,129);

select * from gaokao ;

-- 输出高考理科综合总分在300以上且所有科目成绩在600以上的考生的准考证号，并依据总成绩从高到低排序。