检验数据

查看文本 head

head 文件

香菜		2.80	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
大葱		2.80	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
葱头		1.60	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
大蒜		3.60	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
蒜苔		6.20	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
韭菜		5.60	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
青椒		5.20	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
茄子		5.40	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
西红柿	4.80	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳
黄瓜		3.40	2018/1/1	山西汾阳市晋阳农副产品批发市场	山西	汾阳

查看文本尾

tail 文件

查看文件多少行

wc -l 文件

建表

CREATE TABLE products(
name VARCHAR(5),
price float,
times date,
market VARCHAR(25),
province VARCHAR(10),
city VARCHAR(10)
);

上传文件

load data local infile '/root/package/products.txt' --文件位置
into table products 			--表名
CHARACTER SET utf8 				--文本格式
FIELDS TERMINATED BY '\t' 		--列分割符
OPTIONALLY ENCLOSED BY '' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\n' 
(name,price,time,market,province,city);		--列名

查看表中数据

select * from products limit 10; --查看前10条
select count(*) from products;	 --查看数据条数
truncate table products;		 --删除表中数据
drop table products;			 --删除表

1.统计每个省份的农产品市场总数

SELECT province,COUNT(distinct market) number FROM products GROUP BY province;

2.根据农产品类型数量，统计排名前3名的省份

SELECT province,COUNT(DISTINCT name) number  --name 去重并统计次数
FROM products 
GROUP BY province 
ORDER BY number DESC 
limit 3;

3.根据农产品类型数量，统计每个省份排名前3名的农产品市场

WITH temp AS(
SELECT  province,market,ROW_NUMBER() OVER (partition BY province 
ORDER BY COUNT(DISTINCT name)) AS number 
FROM products GROUP BY province,market
)
SELECT province,market,number
FROM temp WHERE number<=3;

4.计算山西省每种农产品的价格波动趋势，即计算每天价格均值，并将结果输出到控制台上。某种农产品的价格均值计算公式：PAVG=(PM1+PM2+…+PMn-max-min)/(N-2)其中，P 表示价格 Mn 表示 market 即农产品市场。PM1 表示 M1 农产品市场的该产品价格，max表示价格最大值，min价格最小值

查看数据有几天

select distinct time from products;

每个市场当天的商品价格是固定的

精简版

WITH temp AS ( 												 --一个临时表
SELECT name,(											     --表的商品列
CASE WHEN COUNT(name) BETWEEN 1 AND 2 THEN AVG(price) 		 --统计价格次数在1和2之间直接取平均数
ELSE (SUM(price)-MAX(price)-MIN(price))/(COUNT(name)-2) END) --反之求和除去max()和min()求平均值
avg_price 													 --别名
FROM products GROUP BY name)								 --对name进行分组
SELECT name,ROUND(avg_price,2) FROM temp;					 --round()对avg_price列取小数点后两位

详细版

与上述思路一致

WITH cte AS(
SELECT name,SUM(price) sums,MAX(price) maxs,MIN(price) mins,count(name) number
FROM products
WHERE province='山西'
GROUP BY name
HAVING number > 2
),
temp AS (
SELECT name,SUM(price) sums,MAX(price) maxs,MIN(price) mins,count(name) number
FROM products
WHERE province='山西'
GROUP BY name
HAVING number <= 2
)
SELECT name,ROUND((sums-maxs-mins)/(number-2),2) avg_price FROM cte
UNION
SELECT name,ROUND(sums/number,2) aver_price FROM temp;