创建数据库demo,并创建相应的表:
CREATE DATABASE demo;
USE demo;
CREATE TABLE `allprovinces` (
`name` varchar(255) NOT NULL,
`abbr` varchar(255) NOT NULL
);
CREATE TABLE `products` (
`name` varchar(255) NOT NULL,
`price` float NOT NULL,
`craw_time` datetime NOT NULL,
`market` varchar(255) NOT NULL,
`province` varchar(255) not NULL,
`city` varchar(255) not NULL
);
load data local infile '/root/ml-1m/allprovinces.dat' into table allprovinces
CHARACTER SET utf8
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
load data local infile '/root/ml-1m/products.dat' into table products
CHARACTER SET utf8
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';
1.统计每个省份的农产品市场总数
SELECT province,COUNT(DISTINCT market)
FROM products
GROUP BY province;
2.根据农产品类型数量,统计排名前3名的省份
SELECT province,COUNT(DISTINCT NAME) num
FROM products
GROUP BY province ORDER BY num DESC LIMIT 3 ;
3.根据农产品类型数量,统计每个省份排名前3名的农产品市场
with t as (
select count(distinct name) product_count,market,province
from products
group by province,market
),
tt as(
select row_number()over(partition by province order by product_count desc) row_num,market,province
from t
group by province,market
)
select province,market,row_num
from tt
where row_num<=3;
4.计算山西省每种农产品的价格波动趋势,即计算每天价格均值,并将结果输出到控制台上。
某种农产品的价格均值计算公式:
PAVG = (PM1+PM2+…+PMn-max§-min§)/(N-2)
其中,P 表示价格,Mn 表示 market,即农产品市场。PM1 表示 M1 农产品市场的该产品价格,
max§表示价格最大值,min§价格最小值。
with t as (
select name,market,price
from products
where province='山西'
)
select name,
case when count(price)<=2 then round(sum(price)/count(price),3) else round((sum(price)-max(price)-min(price))/(count(price)-2),3) end as pavg
from t
group by name;