根据商品类型按商品价格对商品打标签。标签规则：前20%为高档，20%到80%为中档，后20%为低档。

最新推荐文章于 2024-11-12 10:28:35 发布

vae_musician

最新推荐文章于 2024-11-12 10:28:35 发布

阅读量647

点赞数

分类专栏： mysql 文章标签： mysql sql 数据分析

本文链接：https://blog.csdn.net/vae_musician/article/details/116210561

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

考点：窗口函数与case when

题目：根据商品类型按商品价格对商品打标签。标签规则：前20%为高档，20%到80%为中档，后20%为低档。

1.建表：

CREATE TABLE IF NOT EXISTS `goods_1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `goods_type` VARCHAR(10) NOT NULL,
   `goods_name` VARCHAR(10) NOT NULL,
   `goods_price` INT NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据：

insert into goods_1 (goods_type, goods_name, goods_price) values('手机','华为','5999');
insert into goods_1 (goods_type, goods_name, goods_price) values('手机','苹果','6999');
insert into goods_1 (goods_type, goods_name, goods_price) values('手机','vivo','3999');
insert into goods_1 (goods_type, goods_name, goods_price) values('手机','小米','4999');
insert into goods_1 (goods_type, goods_name, goods_price) values('手机','oppo','3999');
insert into goods_1 (goods_type, goods_name, goods_price) values('食品','好多鱼','5');
insert into goods_1 (goods_type, goods_name, goods_price) values('食品','牛肉干','20');
insert into goods_1 (goods_type, goods_name, goods_price) values('食品','巧克力','15');
insert into goods_1 (goods_type, goods_name, goods_price) values('食品','奥利奥','8');
insert into goods_1 (goods_type, goods_name, goods_price) values('食品','可乐','3');

3.查询数据：

SELECT t1.*, 
			 CASE WHEN t1.row_num/t1.type_num <=0.2 then "高档" 
			 WHEN 0.8 <= t1.row_num/t1.type_num then "低档"
			 else "中档"
			 END as goods_level
FROM(
		SELECT goods_type,
					 goods_name,
					 goods_price,
					 ROW_NUMBER() over(PARTITION BY goods_type ORDER BY goods_price DESC) row_num, 
					 count(*) over(PARTITION BY goods_type) type_num
		FROM goods_1)t1