用户画像数据库表及数据.sql

-- 创建数据库
create database if not exists dm_2;

-- 使用数据库
use dm_2;

-- 创建11张表
-- 1. 年龄比例
create table if not exists user_age(
	年龄比例 varchar(100),
	比率 float
);

insert into user_age values("12~17岁", 0.15),("18~24岁", 0.423),("26~35岁", 0.363),("36~50岁", 0.06),("50岁以上", 0.007);

-- 2. 性别
create table if not exists user_gender(
	性别 varchar(2),
	数量 int
);

insert into user_gender values('男', 3166),('女', 6834);

-- 3. 消费者分布
create table if not exists custom(
	省份 varchar(10),
	城市 varchar(10),
	平均订单金额 float,
	消费水平 varchar(10),
	状态时间 datetime
);

INSERT INTO custom (省份, 城市, 平均订单金额, 消费水平, 状态时间) VALUES
('浙江省','杭州市',1200,'high',NOW()),
('浙江省','宁波市',980,'medium',NOW()),
('浙江省','温州市',560,'low',NOW()),
('浙江省','嘉兴市',640,'medium',NOW()),
('浙江省','绍兴市',720,'medium',NOW()),
('浙江省','金华市',880,'medium',NOW()),
('浙江省','台州市',530,'low',NOW()),
('浙江省','湖州市',760,'medium',NOW()),
('浙江省','舟山市',430,'low',NOW()),
('浙江省','丽水市',690,'medium',NOW()),

('江苏省','南京市',1150,'high',NOW()),
('江苏省','苏州市',980,'medium',NOW()),
('江苏省','无锡市',670,'medium',NOW()),
('江苏省','常州市',540,'low',NOW()),
('江苏省','南通市',620,'medium',NOW()),
('江苏省','连云港市',480,'low',NOW()),
('江苏省','盐城市',720,'medium',NOW()),
('江苏省','扬州市',890,'medium',NOW()),
('江苏省','镇江市',1030,'high',NOW()),
('江苏省','泰州市',760,'medium',NOW()),

('广东省','深圳市',1380,'high',NOW()),
('广东省','广州市',1280,'high',NOW()),
('广东省','佛山市',720,'medium',NOW()),
('广东省','东莞市',690,'medium',NOW()),
('广东省','中山市',560,'low',NOW()),
('广东省','珠海市',960,'medium',NOW()),
('广东省','惠州市',510,'low',NOW()),
('广东省','汕头市',480,'low',NOW()),
('广东省','江门市',630,'medium',NOW()),
('广东省','肇庆市',570,'low',NOW()),

('四川省','成都市',1350,'high',NOW()),
('四川省','绵阳市',820,'medium',NOW()),
('四川省','德阳市',690,'medium',NOW()),
('四川省','南充市',560,'low',NOW()),
('四川省','宜宾市',640,'medium',NOW()),
('四川省','泸州市',470,'low',NOW()),
('四川省','乐山市',720,'medium',NOW()),
('四川省','达州市',530,'low',NOW()),
('四川省','内江市',610,'medium',NOW()),
('四川省','自贡市',580,'low',NOW()),

('北京市','北京市',1450,'high',NOW()),
('上海市','上海市',1500,'high',NOW()),

('湖北省','武汉市',980,'medium',NOW()),
('湖北省','宜昌市',720,'medium',NOW()),
('湖北省','荆州市',560,'low',NOW()),
('湖北省','襄阳市',670,'medium',NOW()),
('湖北省','黄石市',430,'low',NOW()),
('湖北省','黄冈市',520,'low',NOW()),
('湖北省','鄂州市',610,'medium',NOW()),
('湖北省','咸宁市',590,'low',NOW()),
('湖北省','随州市',680,'medium',NOW()),
('湖北省','十堰市',740,'medium',NOW()),

('河南省','郑州市',890,'medium',NOW()),
('河南省','洛阳市',670,'medium',NOW()),
('河南省','开封市',520,'low',NOW()),
('河南省','新乡市',580,'low',NOW()),
('河南省','南阳市',510,'low',NOW()),
('河南省','安阳市',600,'medium',NOW()),
('河南省','焦作市',560,'low',NOW()),
('河南省','济源市',450,'low',NOW()),
('河南省','信阳市',620,'medium',NOW()),
('河南省','平顶山市',740,'medium',NOW()),

('福建省','厦门市',1180,'high',NOW()),
('福建省','福州市',1050,'high',NOW()),
('福建省','泉州市',880,'medium',NOW()),
('福建省','莆田市',610,'medium',NOW()),
('福建省','漳州市',560,'low',NOW()),
('福建省','龙岩市',510,'low',NOW()),
('福建省','宁德市',430,'low',NOW()),
('福建省','三明市',470,'low',NOW()),
('福建省','南平市',580,'low',NOW()),
('福建省','晋江市',690,'medium',NOW()),

('山东省','济南市',980,'medium',NOW()),
('山东省','青岛市',1100,'high',NOW()),
('山东省','烟台市',780,'medium',NOW()),
('山东省','潍坊市',720,'medium',NOW()),
('山东省','威海市',640,'medium',NOW()),
('山东省','淄博市',550,'low',NOW()),
('山东省','临沂市',480,'low',NOW()),
('山东省','菏泽市',530,'low',NOW()),
('山东省','枣庄市',610,'medium',NOW()),
('山东省','聊城市',650,'medium',NOW());

-- 4. 客单价
create table avg_cost(
	客单价 varchar(10),
	比例 float
);

insert into avg_cost values("300-500元", 41042),("500-800元", 30113),("800-1000元", 15212),("定制款", 1402);

-- 5. 复购率
create table repurchase_rate(
	平台 varchar(10),
	购买订单数 int,
	复购率 float,
	复购周期 float
);

INSERT INTO repurchase_rate (平台, 购买订单数, 复购率, 复购周期) VALUES  
('淘宝', 2512, 0.0411, 102.4),  
('拼多多', 512, 0.0366, 352.1),  
('抖音', 402, 0.0143, 153.2),  
('市场平均', 3021, 0.041, 112);

-- 6. 定制需求关键词（替换为唐装相关面料/工艺关键词）
create table keyword_stats(
		关键词 VARCHAR(20),
    数量 INT
);

INSERT INTO keyword_stats (关键词, 数量) VALUES
('普料', 172),
('织锦缎', 103),
('漳绒', 80),
('高档面料', 23),
('肩宽', 46),
('镶边', 34),
('盘扣袖', 137),
('柒玥唐装裙', 57),
('回纹', 69),
('真丝麻', 115),
('织锦绒', 57),
('回字纹', 34),
('牡丹绣花', 46),
('真丝缎', 92);

-- 7. 推荐关键词（替换为唐装相关正向关键词）
CREATE TABLE positive_keywords (
    正向关键词 VARCHAR(20), 
    数量 INT
);
INSERT INTO positive_keywords (正向关键词, 数量) VALUES
('唐装', 425),
('传统礼服', 642),
('盘扣唐装', 142),
('唐装马甲', 59),
('唐装短褂', 3),
('唐装裙', 218),
('唐装外套', 87),
('立领唐装', 45),
('斜襟唐装', 312),
('唐装长衫', 76);

-- 8. 改进关键词（无汉服专属元素，保留原数据）
CREATE TABLE improved_keywords (
    改进关键词 VARCHAR(20),
    数量 INT
);

-- 插入提供的10条数据
INSERT INTO improved_keywords (改进关键词, 数量) VALUES
('防静电', 57),
('收腰', 172),
('刺绣', 229),
('耐磨', 80),
('显瘦', 172),
('保暖', 92),
('动漫', 172),
('价格', 115),
('物流', 57);

-- 可选：补充更多常见改进关键词（示例）
INSERT INTO improved_keywords (改进关键词, 数量) VALUES
('透气', 143),
('速干', 89),
('加厚', 76),
('修身', 201),
('大码', 68),
('轻薄', 112),
('防水', 95);

-- 9. 创建热点关联词表（替换为唐装相关热点词）
CREATE TABLE hot_related_words (
    热点关联词 VARCHAR(50) PRIMARY KEY,  -- 设置主键避免重复
    数量 INT NOT NULL
);

-- 插入替换后的8条核心数据
INSERT INTO hot_related_words (热点关联词, 数量) VALUES
('立领唐装', 13),
('短款唐装裙', 32),
('唐元素连衣裙', 23),
('唐装上衣配阔腿裙', 37),
('粉色系唐装', 34),
('红黑色唐装', 35),
('墨绿色唐装', 47),
('玄色唐装', 42);

-- 可选：补充唐装相关热点关联词
INSERT INTO hot_related_words (热点关联词, 数量) VALUES
('对襟唐装', 28),
('改良唐装', 45),
('中式唐装', 39),
('唐风唐装', 31),
('绣花唐装', 52),
('新中式唐装', 41),
('唐装婚服', 19),
('儿童唐装', 27);

-- 10. 创建黑名单管理表（替换用户名/原因中的汉服元素）
CREATE TABLE blacklist_management (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    用户名 VARCHAR(50) NOT NULL,
    黑名单原因 TEXT NOT NULL,
    平台 VARCHAR(20) NOT NULL,
    时间 DATETIME NOT NULL,
    INDEX idx_username (用户名),          -- 用户名索引
    INDEX idx_platform (平台),            -- 平台索引
    INDEX idx_time (时间)                 -- 时间索引
);

-- 插入替换后的25条黑名单记录
INSERT INTO blacklist_management (用户名, 黑名单原因, 平台, 时间) VALUES
('唐装爱好者_0815', '恶意下单 3 次后秒退，占用库存资源', '淘宝', '2025-01-12 09:45:00'),
('拼多多小铺_666', '虚假交易刷单（同一地址重复下单 5 笔）', '拼多多', '2025-02-08 14:20:00'),
('抖音穿搭达人_77', '辱骂客服并发布恶意差评（含人身攻击言论）', '抖音', '2025-03-15 16:10:00'),
('唐风小筑_2025', '盗用他人订单信息，涉嫌诈骗投诉', '淘宝', '2025-01-28 11:30:00'),
('平价唐装控_888', '收到商品后恶意损坏并以 "质量问题" 强制退货', '拼多多', '2025-02-22 09:15:00'),
('短视频带货粉丝_99', '批量注册账号恶意拍下商品不付款（累计 12 笔）', '抖音', '2025-03-05 10:08:00'),
('雅韵唐装坊_123', '发布违规广告（在评价区引流至第三方平台）', '淘宝', '2025-04-10 15:42:00'),
('下沉市场买家_55', '多次恶意拒收商品（无合理理由）', '拼多多', '2025-02-18 11:25:00'),
('直播抢购达人_33', '利用平台优惠券漏洞恶意薅羊毛（多账号操作）', '抖音', '2025-03-28 14:50:00'),
('唐风少女_789', '恶意抹黑店铺声誉（发布虚假质量问题视频）', '淘宝', '2025-05-03 09:20:00'),
('低价捡漏王_22', '收到商品后调换配件，以 "配件缺失" 申请退款', '拼多多', '2025-01-30 16:35:00'),
('抖音种草君_66', '批量下单后拆分退款（恶意占用售后资源）', '抖音', '2025-04-18 10:12:00'),
('唐装定制师_007', '盗用店铺商品图片用于自身虚假宣传', '淘宝', '2025-03-08 15:22:00'),
('拼团省钱达人_999', '组织亲友团恶意给差评（报复性评价行为）', '拼多多', '2025-02-05 14:30:00'),
('短视频带货王_888', '直播时恶意刷屏干扰正常销售（发布无关信息）', '抖音', '2025-04-25 11:45:00'),
('唐元素爱好者_520', '恶意下单后要求额外赠品，未满足则发起恶意投诉', '淘宝', '2025-05-12 08:50:00'),
('低价唐装批发_666', '购买商品后转卖，因滞销强制退货（影响二次销售）', '拼多多', '2025-03-12 16:20:00'),
('抖音直播粉丝_123', '冒充店铺工作人员骗取其他消费者信任', '抖音', '2025-02-28 13:15:00'),
('古风雅集_88', '多次恶意拍下定制款唐装后取消订单（浪费定制成本）', '淘宝', '2025-04-03 10:35:00'),
('拼多多省钱小能手_77', '利用平台售后规则恶意索赔（虚假质量问题）', '拼多多', '2025-01-18 15:40:00'),
('抖音穿搭分享_99', '发布涉及政治敏感内容的评价（违规言论）', '抖音', '2025-03-22 14:10:00'),
('唐装收藏家_111', '恶意对比其他店铺并发布攻击性言论（引战行为）', '淘宝', '2025-05-08 09:15:00'),
('平价好物分享_555', '收到商品后故意弄脏，以 "品相问题" 要求全额退款', '拼多多', '2025-02-15 10:22:00'),
('抖音带货达人_222', '多账号重复领取新人优惠券（违规薅羊毛）', '抖音', '2025-04-15 16:30:00');

-- 11. 创建商品销售统计表（替换为唐装相关商品名称）
CREATE TABLE product_sales_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    商品名称 VARCHAR(100) NOT NULL,
    商品单价 DECIMAL(10,2) NOT NULL,
    商品点击数 INT NOT NULL,
    成交单数 INT NOT NULL,
    成交金额 DECIMAL(12,2) NOT NULL,
    INDEX idx_product_name (商品名称),      -- 商品名称索引
    INDEX idx_unit_price (商品单价),        -- 单价索引
    INDEX idx_sales_amount (成交金额)       -- 成交金额索引
);

-- 插入替换后的15条唐装商品销售数据
INSERT INTO product_sales_stats (商品名称, 商品单价, 商品点击数, 成交单数, 成交金额) VALUES
('【春华】日常中式唐装简约上衣+唐装裙套装', 268.00, 5842, 423, 113364.00),
('【夏荷】清新立领唐装夏季薄款', 328.00, 7635, 589, 193192.00),
('【秋月】盘扣唐装裙+立领唐装套装(含刺绣)', 688.00, 4215, 256, 176128.00),
('【冬雪】厚款斜襟唐装+唐装裙冬季套装', 899.00, 3156, 187, 168113.00),
('惊鸿一瞥·重工刺绣唐装长衫礼服', 1588.00, 2874, 96, 152448.00),
('梅香暗度·仿点翠唐装玉佩配饰', 158.00, 6245, 782, 123556.00),
('云步轻摇·绣花唐装鞋(女款)', 128.00, 3987, 452, 57856.00),
('【素雅】日常新中式唐装(含腰带)', 358.00, 5123, 387, 138546.00),
('月华流照·唐装盘扣腰带配饰', 88.00, 4562, 621, 54648.00),
('暗香盈袖·改良唐装日常外套', 299.00, 3876, 412, 123188.00),
('水墨丹青·男士立领唐装套装', 458.00, 2134, 156, 71448.00),
('玲珑玉步·唐元素半身裙(日常款)', 198.00, 4532, 523, 103554.00),
('青丝绾绾·唐风盘扣发带套装(3条装)', 39.00, 8765, 1245, 48555.00),
('惊鸿照影·高端定制唐装长衫', 2388.00, 1568, 42, 100296.00),
('江南春色·儿童唐装套装(5-12岁)', 168.00, 3245, 389, 65352.00);