瓜子二手车实习笔试SQL

1.有如下线索表tbl_clue

clue_id	city_id	price	created_at
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
....	...	...	...

 

请用一条SQL语句查询出6月7日当天分城市的线索量(clue_id)、平均价格，并按照线索量降序排列。

SELECT city_id, COUNT(clue_id) AS count_clue_id, AVG(price) ASavg_price

FROM tbl_clue

WHERE DATE(created_at) = '2016/6/7'

GROUP BY city_id

ORDER BY count_clue_id  DESC;

 

2.有如下的城市表 tbl_city

 

city_id	city_name
10	北京
11	上海
12	天津
...

 

请用一条SQL语句在实现题1的功能前提下，用城市名称(city_name)替换城市id(city_id)。

SELECT ci.city_name, COUNT(cl.clue_id) AS count_clue_id,AVG(cl.price) AS avg_price

FROM tbl_clue cl JOIN tbl_city ci ON cl.city_id = ci.city_id

WHERE DATE(cl.created_at) = '2016/6/7'

GROUP BY ci.city_name

ORDER BY count_clue_id DESC;

 

 

 

3. 有如下的合同表tbl_contract

id	clue_id	deal_price	created_at
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
...	...	...	...

 

此表的clue_id关联tbl_clue表的clue_id请用一条语句查询出6月7日创建的线索在7月1日的成交合同总量。

SELECT COUNT(id) AS count_id

FROM tbl_contract

WHERE DATE(creat_at) = '2016/7/1'

AND clue_id IN (SELECT clue_id

                FROM tbl_clue

                WHERE DATE(created_at) = '2016/6/7');

 

4.请说明hive中 sort by ，order by ，clusterby ，distribute by各代表什么意思。

 

答：SORT BY：在每个reducer中对数据进行排序，即执行一个局部排序过程；

ORDER BY：对查询结果集执行全局排序，即所有数据都通过一个reducer进行处理；

CLUSTER BY：是DISTRIBUTE BY …SORT BY的简化版；

DISTRIBUTE BY: 控制map的输出在reducer中是怎么划分的。

 

5.怎么把这样一个表儿
 

year	month	amount
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

查成这样一个结果

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

 

SELECT year,

SUM(CASE WHEN month = 1 THENamount ELSE 0 END) AS m1,

SUM(CASE WHEN month = 2 THENamount ELSE 0 END) AS m2,

SUM(CASE WHEN month = 3 THENamount ELSE 0 END) AS m3,

SUM(CASE WHEN month = 4 THENamount ELSE 0 END) AS m4

FROM tb1

GROUP BY year;

 

 

 

6.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。

SELECT (CASE

WHEN A>B THEN A

WHEN B>C THEN B

ELSE C

END) AS line

FROM tb2;