11.25sql测试题目

创建数据库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;