SQL 编写一表结构定义基于 MySQL 8.01. 产品信息表product_info- 存储银行理财 / 商品产品基础信息字段名数据类型约束说明字段含义product_idINTPRIMARY KEY AUTO_INCREMENT产品唯一 IDproduct_nameVARCHAR(50)NOT NULL UNIQUE产品名称如半年期理财、定期存款、智能手机product_typeVARCHAR(20)NOT NULL产品类型理财 / 存款 / 实物商品 / 虚拟商品unit_priceDECIMAL(10,2)NOT NULL CHECK (unit_price 0)单位价格理财 / 存款为起购金额商品为单价statusTINYINTDEFAULT 1 CHECK (status IN (0,1))产品状态0下架1在售create_timeDATETIMEDEFAULT CURRENT_TIMESTAMP产品创建时间2. 购买时间表purchase_time- 存储交易时间维度信息字段名数据类型约束说明字段含义time_idINTPRIMARY KEY AUTO_INCREMENT时间记录唯一 IDpurchase_dateDATENOT NULL交易日期purchase_hourTINYINTNOT NULL CHECK (purchase_hour BETWEEN 0 AND 23)交易小时24 小时制week_dayTINYINTNOT NULL CHECK (week_day BETWEEN 1 AND 7)星期1周一7周日quarterTINYINTNOT NULL CHECK (quarter BETWEEN 1 AND 4)季度yearINTNOT NULL交易年份3. 购买数量表purchase_quantity- 存储交易数量及关联关系字段名数据类型约束说明字段含义quantity_idINTPRIMARY KEY AUTO_INCREMENT数量记录唯一 IDuser_idINTNOT NULL购买用户 IDproduct_idINTNOT NULL产品 ID关联 product_info.product_idtime_idINTNOT NULL时间 ID关联 purchase_time.time_idpurchase_quantityINTNOT NULL CHECK (purchase_quantity 0)购买数量理财 / 存款为 1商品为实际购买件数order_idVARCHAR(30)NOT NULL UNIQUE订单 / 交易流水号FOREIGN KEY (product_id) REFERENCES product_info(product_id) ON UPDATE CASCADE ON DELETE RESTRICTFOREIGN KEY (time_id) REFERENCES purchase_time(time_id) ON UPDATE CASCADE ON DELETE RESTRICT4. 金额表amount_detail- 存储交易金额明细字段名数据类型约束说明字段含义amount_idINTPRIMARY KEY AUTO_INCREMENT金额记录唯一 IDorder_idVARCHAR(30)NOT NULL订单 / 交易流水号关联 purchase_quantity.order_idtotal_amountDECIMAL(12,2)NOT NULL CHECK (total_amount 0)交易总金额unit_price × purchase_quantitydiscount_amountDECIMAL(12,2)DEFAULT 0 CHECK (discount_amount 0)优惠金额actual_payDECIMAL(12,2)NOT NULL CHECK (actual_pay 0)实际支付金额total_amount - discount_amountpayment_methodVARCHAR(20)NOT NULL支付方式银行卡 / 微信 / 支付宝 / 现金FOREIGN KEY (order_id) REFERENCES purchase_quantity(order_id) ON UPDATE CASCADE ON DELETE CASCADE二测试数据INSERT 语句-- 1. 产品信息表数据 INSERT INTO product_info (product_name, product_type, unit_price, status, create_time) VALUES (半年期稳健理财, 理财, 5000.00, 1, 2023-01-10 09:00:00), (一年期定期存款, 存款, 1000.00, 1, 2023-01-01 00:00:00), (高端智能手机, 实物商品, 6999.00, 1, 2023-02-15 14:30:00), (三个月短期理财, 理财, 10000.00, 1, 2023-03-05 10:00:00), (无线蓝牙耳机, 实物商品, 399.00, 1, 2023-02-20 11:00:00), (虚拟会员年卡, 虚拟商品, 199.00, 1, 2023-04-01 00:00:00), (二年期定期存款, 存款, 2000.00, 1, 2023-01-01 00:00:00), (智能手表, 实物商品, 1599.00, 1, 2023-03-10 15:00:00), (结构性存款A款, 存款, 50000.00, 1, 2023-02-01 09:00:00), (云存储套餐, 虚拟商品, 99.00, 1, 2023-04-15 16:00:00); -- 2. 购买时间表数据 INSERT INTO purchase_time (purchase_date, purchase_hour, week_day, quarter, year) VALUES (2023-05-10, 9, 3, 2, 2023), (2023-06-15, 14, 5, 2, 2023), (2023-07-20, 10, 4, 3, 2023), (2023-08-05, 16, 7, 3, 2023), (2023-09-12, 11, 2, 3, 2023), (2023-10-18, 15, 3, 4, 2023), (2023-11-22, 13, 4, 4, 2023), (2023-12-03, 10, 7, 4, 2023), (2024-01-15, 9, 2, 1, 2024), (2024-02-20, 14, 3, 1, 2024); -- 3. 购买数量表数据 INSERT INTO purchase_quantity (user_id, product_id, time_id, purchase_quantity, order_id) VALUES (1001, 1, 1, 1, TR20230510090001), (1002, 3, 2, 1, TR20230615140002), (1003, 2, 3, 1, TR20230720100003), (1004, 5, 4, 2, TR20230805160004), (1005, 4, 5, 1, TR20230912110005), (1006, 6, 6, 3, TR20231018150006), (1001, 7, 7, 1, TR20231122130007), (1002, 8, 8, 1, TR20231203100008), (1003, 9, 9, 1, TR20240115090009), (1004, 10, 10, 2, TR20240220140010); -- 4. 金额表数据 INSERT INTO amount_detail (order_id, total_amount, discount_amount, actual_pay, payment_method) VALUES (TR20230510090001, 5000.00, 0.00, 5000.00, 银行卡), (TR20230615140002, 6999.00, 200.00, 6799.00, 微信), (TR20230720100003, 1000.00, 0.00, 1000.00, 银行卡), (TR20230805160004, 798.00, 50.00, 748.00, 支付宝), (TR20230912110005, 10000.00, 0.00, 10000.00, 银行卡), (TR20231018150006, 597.00, 30.00, 567.00, 微信), (TR20231122130007, 2000.00, 0.00, 2000.00, 银行卡), (TR20231203100008, 1599.00, 100.00, 1499.00, 支付宝), (TR20240115090009, 50000.00, 0.00, 50000.00, 银行卡), (TR20240220140010, 198.00, 0.00, 198.00, 微信);三SQL 编写查询 2023 年各季度的交易总金额、平均单笔交易金额、交易笔数按季度升序排序。SELECT pt.quarter, SUM(ad.actual_pay) AS total_trans_amount, ROUND(AVG(ad.actual_pay), 2) AS avg_single_amount, COUNT(DISTINCT pq.order_id) AS transaction_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.quarter ORDER BY pt.quarter;找出购买过 “理财” 类产品且交易金额超过 8000 元的用户 ID、产品名称、实际支付金额及交易日期按实际支付金额降序排序。SELECT pq.user_id, pi.product_name, ad.actual_pay, pt.purchase_date FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN amount_detail ad ON pq.order_id ad.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pi.product_type 理财 AND ad.actual_pay 8000 ORDER BY ad.actual_pay DESC;统计每种支付方式的交易总笔数、总优惠金额、总实际支付金额筛选出总实际支付金额超过 5000 元的支付方式按总实际支付金额降序排序。SELECT ad.payment_method, COUNT(DISTINCT ad.order_id) AS transaction_count, SUM(ad.discount_amount) AS total_discount, SUM(ad.actual_pay) AS total_actual_pay FROM amount_detail ad GROUP BY ad.payment_method HAVING SUM(ad.actual_pay) 5000 ORDER BY total_actual_pay DESC;查询每周各时段小时的交易笔数分布显示星期、交易小时、交易笔数按星期升序、交易小时升序排序。SELECT pt.week_day, pt.purchase_hour, COUNT(DISTINCT pq.order_id) AS transaction_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id GROUP BY pt.week_day, pt.purchase_hour ORDER BY pt.week_day, pt.purchase_hour;找出 2023 年购买商品数量最多的用户按购买总件数统计显示用户 ID、总购买件数、总实际支付金额若有并列则全部列出。WITH user_total_quantity AS ( SELECT user_id, SUM(purchase_quantity) AS total_quantity, SUM(ad.actual_pay) AS total_actual_pay FROM purchase_quantity pq JOIN amount_detail ad ON pq.order_id ad.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 GROUP BY user_id ), max_quantity AS ( SELECT MAX(total_quantity) AS max_qty FROM user_total_quantity ) SELECT utq.user_id, utq.total_quantity, utq.total_actual_pay FROM user_total_quantity utq JOIN max_quantity mq ON utq.total_quantity mq.max_qty ORDER BY utq.user_id;查询每种产品的销售情况产品名称、产品类型、销售总件数、总实际收入按总实际收入降序排序仅显示在售产品status1。SELECT pi.product_name, pi.product_type, SUM(pq.purchase_quantity) AS total_sales_quantity, SUM(ad.actual_pay) AS total_actual_income FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pi.status 1 GROUP BY pi.product_id, pi.product_name, pi.product_type ORDER BY total_actual_income DESC;编写 SQL 语句计算 2023 年每个月的交易增长率环比显示月份、当月交易总金额、上月交易总金额、增长率保留 1 位小数增长率公式(当月 - 上月)/ 上月 ×100%。WITH monthly_trans AS ( SELECT pt.year, MONTH(pt.purchase_date) AS month, SUM(ad.actual_pay) AS monthly_total FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.year, MONTH(pt.purchase_date) ) SELECT current.month, current.monthly_total AS current_month_total, COALESCE(prev.monthly_total, 0) AS prev_month_total, ROUND( CASE WHEN COALESCE(prev.monthly_total, 0) 0 THEN 0 ELSE (current.monthly_total - prev.monthly_total)/prev.monthly_total * 100 END, 1 ) AS growth_rate FROM monthly_trans current LEFT JOIN monthly_trans prev ON current.month prev.month 1 ORDER BY current.month;查询同时购买了 “实物商品” 和 “虚拟商品” 的用户 ID以及两类商品的交易总金额分别显示按用户 ID 升序排序。WITH physical_user AS ( SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_type 实物商品 ), virtual_user AS ( SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_type 虚拟商品 ) SELECT pu.user_id, COALESCE(SUM(CASE WHEN pi.product_type 实物商品 THEN ad.actual_pay ELSE 0 END), 0) AS physical_total, COALESCE(SUM(CASE WHEN pi.product_type 虚拟商品 THEN ad.actual_pay ELSE 0 END), 0) AS virtual_total FROM physical_user pu JOIN virtual_user vu ON pu.user_id vu.user_id JOIN purchase_quantity pq ON pu.user_id pq.user_id JOIN product_info pi ON pq.product_id pi.product_id JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pu.user_id ORDER BY pu.user_id;统计 2023 年各星期的平均每笔交易金额筛选出平均金额高于全年平均每笔交易金额的星期按平均金额降序排序。WITH week_avg_amount AS ( SELECT pt.week_day, ROUND(AVG(ad.actual_pay), 2) AS week_avg_pay FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.week_day ), year_avg_amount AS ( SELECT ROUND(AVG(actual_pay), 2) AS year_avg_pay FROM amount_detail ad JOIN purchase_quantity pq ON ad.order_id pq.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 ) SELECT wa.week_day, wa.week_avg_pay FROM week_avg_amount wa JOIN year_avg_amount ya ON wa.week_avg_pay ya.year_avg_pay ORDER BY wa.week_avg_pay DESC;创建一个视图v_high_value_transaction包含订单号、用户 ID、产品名称、交易日期、实际支付金额、支付方式要求实际支付金额≥5000 元、交易年份为 2023 年、产品类型为 “理财” 或 “存款”视图数据按交易日期降序排序。CREATE VIEW v_high_value_transaction AS SELECT pq.order_id, pq.user_id, pi.product_name, pt.purchase_date, ad.actual_pay, ad.payment_method FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE ad.actual_pay 5000 AND pt.year 2023 AND pi.product_type IN (理财, 存款) ORDER BY pt.purchase_date DESC;查询 2023 年第三季度7-9 月各产品类型的交易笔数、总金额按产品类型升序排序。SELECT pi.product_type, COUNT(DISTINCT pq.order_id) AS transaction_count, SUM(ad.actual_pay) AS total_amount FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 AND pt.quarter 3 GROUP BY pi.product_type ORDER BY pi.product_type;找出实际支付金额排名前 3 的订单对应的用户 ID、产品名称、交易时间日期 小时、支付方式若有并列则顺延。WITH order_rank AS ( SELECT pq.user_id, pi.product_name, CONCAT(pt.purchase_date, , pt.purchase_hour, :00) AS transaction_time, ad.payment_method, ad.actual_pay, DENSE_RANK() OVER (ORDER BY ad.actual_pay DESC) AS pay_rank FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id ) SELECT user_id, product_name, transaction_time, payment_method, actual_pay FROM order_rank WHERE pay_rank 3 ORDER BY actual_pay DESC, user_id;统计每个用户的购买频次交易笔数和平均每笔交易金额筛选出购买频次≥2 次的用户按平均每笔金额降序排序。SELECT pq.user_id, COUNT(DISTINCT pq.order_id) AS purchase_frequency, ROUND(AVG(ad.actual_pay), 2) AS avg_single_pay FROM purchase_quantity pq JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pq.user_id HAVING COUNT(DISTINCT pq.order_id) 2 ORDER BY avg_single_pay DESC;查询没有享受优惠discount_amount0的交易中各产品类型的交易总金额和占比占无优惠交易总金额的百分比保留 1 位小数。WITH no_discount_total AS ( SELECT SUM(actual_pay) AS total_no_discount FROM amount_detail WHERE discount_amount 0 ) SELECT pi.product_type, SUM(ad.actual_pay) AS type_total, ROUND((SUM(ad.actual_pay)/ndt.total_no_discount)*100, 1) AS type_ratio FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id JOIN no_discount_total ndt WHERE ad.discount_amount 0 GROUP BY pi.product_type, ndt.total_no_discount ORDER BY type_ratio DESC;编写 SQL 语句更新 “高端智能手机”product_name的优惠规则后续交易的优惠金额调整为单位价格的 5%需先查询单位价格再更新金额表中未发生的交易此处假设模拟更新现有符合条件的订单。-- 1. 查询高端智能手机的单位价格 SELECT unit_price FROM product_info WHERE product_name 高端智能手机; -- 2. 模拟更新现有符合条件的订单实际场景为后续交易此处更新现有订单示例 UPDATE amount_detail ad JOIN purchase_quantity pq ON ad.order_id pq.order_id JOIN product_info pi ON pq.product_id pi.product_id SET ad.discount_amount pi.unit_price * 0.05, ad.actual_pay ad.total_amount - (pi.unit_price * 0.05) WHERE pi.product_name 高端智能手机;查询 2023 年各月份的交易高峰时段交易笔数最多的小时显示月份、高峰小时、交易笔数若有多个高峰时段则全部列出。WITH monthly_hour_count AS ( SELECT pt.year, MONTH(pt.purchase_date) AS month, pt.purchase_hour, COUNT(DISTINCT pq.order_id) AS hour_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 GROUP BY pt.year, MONTH(pt.purchase_date), pt.purchase_hour ), monthly_max_count AS ( SELECT month, MAX(hour_count) AS max_hour_count FROM monthly_hour_count GROUP BY month ) SELECT mhc.month, mhc.purchase_hour AS peak_hour, mhc.hour_count FROM monthly_hour_count mhc JOIN monthly_max_count mmc ON mhc.month mmc.month AND mhc.hour_count mmc.max_hour_count ORDER BY mhc.month, mhc.purchase_hour;统计每种产品的复购率购买该产品的用户中有多次购买记录的用户占比保留 1 位小数复购定义为同一用户对同一产品购买≥2 次基于现有数据若无复购则显示 0%。WITH product_user_purchase AS ( SELECT pi.product_id, pi.product_name, pq.user_id, COUNT(DISTINCT pq.order_id) AS purchase_times FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id GROUP BY pi.product_id, pi.product_name, pq.user_id ), product_user_stats AS ( SELECT product_id, product_name, COUNT(DISTINCT user_id) AS total_user, COUNT(DISTINCT CASE WHEN purchase_times 2 THEN user_id ELSE NULL END) AS repeat_user FROM product_user_purchase GROUP BY product_id, product_name ) SELECT product_name, ROUND(CASE WHEN total_user 0 THEN 0 ELSE (repeat_user/total_user)*100 END, 1) AS repurchase_rate FROM product_user_stats ORDER BY repurchase_rate DESC;查询 2023 年使用微信支付的交易中各季度的总金额和平均优惠金额按季度升序排序。SELECT pt.quarter, SUM(ad.actual_pay) AS total_wechat_pay, ROUND(AVG(ad.discount_amount), 2) AS avg_wechat_discount FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 AND ad.payment_method 微信 GROUP BY pt.quarter ORDER BY pt.quarter;找出购买了 “一年期定期存款” 但未购买任何 “理财” 类产品的用户 ID按用户 ID 升序排序。SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_name 一年期定期存款 AND pq.user_id NOT IN ( SELECT DISTINCT pq2.user_id FROM purchase_quantity pq2 JOIN product_info pi2 ON pq2.product_id pi2.product_id WHERE pi2.product_type 理财 ) ORDER BY pq.user_id;编写 SQL 语句计算各产品类型的客单价总实际收入 / 购买用户数保留 2 位小数按客单价降序排序。SELECT pi.product_type, ROUND(SUM(ad.actual_pay)/COUNT(DISTINCT pq.user_id), 2) AS customer_unit_price FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pi.product_type ORDER BY customer_unit_price DESC; 
长河君 · 2026/10/24 1:51:35 · 阅读 1.2k MySQL8.0电商金融交易系统SQL实战
SQL 编写一表结构定义基于 MySQL 8.01. 产品信息表product_info- 存储银行理财 / 商品产品基础信息字段名数据类型约束说明字段含义product_idINTPRIMARY KEY AUTO_INCREMENT产品唯一 IDproduct_nameVARCHAR(50)NOT NULL UNIQUE产品名称如半年期理财、定期存款、智能手机product_typeVARCHAR(20)NOT NULL产品类型理财 / 存款 / 实物商品 / 虚拟商品unit_priceDECIMAL(10,2)NOT NULL CHECK (unit_price 0)单位价格理财 / 存款为起购金额商品为单价statusTINYINTDEFAULT 1 CHECK (status IN (0,1))产品状态0下架1在售create_timeDATETIMEDEFAULT CURRENT_TIMESTAMP产品创建时间2. 购买时间表purchase_time- 存储交易时间维度信息字段名数据类型约束说明字段含义time_idINTPRIMARY KEY AUTO_INCREMENT时间记录唯一 IDpurchase_dateDATENOT NULL交易日期purchase_hourTINYINTNOT NULL CHECK (purchase_hour BETWEEN 0 AND 23)交易小时24 小时制week_dayTINYINTNOT NULL CHECK (week_day BETWEEN 1 AND 7)星期1周一7周日quarterTINYINTNOT NULL CHECK (quarter BETWEEN 1 AND 4)季度yearINTNOT NULL交易年份3. 购买数量表purchase_quantity- 存储交易数量及关联关系字段名数据类型约束说明字段含义quantity_idINTPRIMARY KEY AUTO_INCREMENT数量记录唯一 IDuser_idINTNOT NULL购买用户 IDproduct_idINTNOT NULL产品 ID关联 product_info.product_idtime_idINTNOT NULL时间 ID关联 purchase_time.time_idpurchase_quantityINTNOT NULL CHECK (purchase_quantity 0)购买数量理财 / 存款为 1商品为实际购买件数order_idVARCHAR(30)NOT NULL UNIQUE订单 / 交易流水号FOREIGN KEY (product_id) REFERENCES product_info(product_id) ON UPDATE CASCADE ON DELETE RESTRICTFOREIGN KEY (time_id) REFERENCES purchase_time(time_id) ON UPDATE CASCADE ON DELETE RESTRICT4. 金额表amount_detail- 存储交易金额明细字段名数据类型约束说明字段含义amount_idINTPRIMARY KEY AUTO_INCREMENT金额记录唯一 IDorder_idVARCHAR(30)NOT NULL订单 / 交易流水号关联 purchase_quantity.order_idtotal_amountDECIMAL(12,2)NOT NULL CHECK (total_amount 0)交易总金额unit_price × purchase_quantitydiscount_amountDECIMAL(12,2)DEFAULT 0 CHECK (discount_amount 0)优惠金额actual_payDECIMAL(12,2)NOT NULL CHECK (actual_pay 0)实际支付金额total_amount - discount_amountpayment_methodVARCHAR(20)NOT NULL支付方式银行卡 / 微信 / 支付宝 / 现金FOREIGN KEY (order_id) REFERENCES purchase_quantity(order_id) ON UPDATE CASCADE ON DELETE CASCADE二测试数据INSERT 语句-- 1. 产品信息表数据 INSERT INTO product_info (product_name, product_type, unit_price, status, create_time) VALUES (半年期稳健理财, 理财, 5000.00, 1, 2023-01-10 09:00:00), (一年期定期存款, 存款, 1000.00, 1, 2023-01-01 00:00:00), (高端智能手机, 实物商品, 6999.00, 1, 2023-02-15 14:30:00), (三个月短期理财, 理财, 10000.00, 1, 2023-03-05 10:00:00), (无线蓝牙耳机, 实物商品, 399.00, 1, 2023-02-20 11:00:00), (虚拟会员年卡, 虚拟商品, 199.00, 1, 2023-04-01 00:00:00), (二年期定期存款, 存款, 2000.00, 1, 2023-01-01 00:00:00), (智能手表, 实物商品, 1599.00, 1, 2023-03-10 15:00:00), (结构性存款A款, 存款, 50000.00, 1, 2023-02-01 09:00:00), (云存储套餐, 虚拟商品, 99.00, 1, 2023-04-15 16:00:00); -- 2. 购买时间表数据 INSERT INTO purchase_time (purchase_date, purchase_hour, week_day, quarter, year) VALUES (2023-05-10, 9, 3, 2, 2023), (2023-06-15, 14, 5, 2, 2023), (2023-07-20, 10, 4, 3, 2023), (2023-08-05, 16, 7, 3, 2023), (2023-09-12, 11, 2, 3, 2023), (2023-10-18, 15, 3, 4, 2023), (2023-11-22, 13, 4, 4, 2023), (2023-12-03, 10, 7, 4, 2023), (2024-01-15, 9, 2, 1, 2024), (2024-02-20, 14, 3, 1, 2024); -- 3. 购买数量表数据 INSERT INTO purchase_quantity (user_id, product_id, time_id, purchase_quantity, order_id) VALUES (1001, 1, 1, 1, TR20230510090001), (1002, 3, 2, 1, TR20230615140002), (1003, 2, 3, 1, TR20230720100003), (1004, 5, 4, 2, TR20230805160004), (1005, 4, 5, 1, TR20230912110005), (1006, 6, 6, 3, TR20231018150006), (1001, 7, 7, 1, TR20231122130007), (1002, 8, 8, 1, TR20231203100008), (1003, 9, 9, 1, TR20240115090009), (1004, 10, 10, 2, TR20240220140010); -- 4. 金额表数据 INSERT INTO amount_detail (order_id, total_amount, discount_amount, actual_pay, payment_method) VALUES (TR20230510090001, 5000.00, 0.00, 5000.00, 银行卡), (TR20230615140002, 6999.00, 200.00, 6799.00, 微信), (TR20230720100003, 1000.00, 0.00, 1000.00, 银行卡), (TR20230805160004, 798.00, 50.00, 748.00, 支付宝), (TR20230912110005, 10000.00, 0.00, 10000.00, 银行卡), (TR20231018150006, 597.00, 30.00, 567.00, 微信), (TR20231122130007, 2000.00, 0.00, 2000.00, 银行卡), (TR20231203100008, 1599.00, 100.00, 1499.00, 支付宝), (TR20240115090009, 50000.00, 0.00, 50000.00, 银行卡), (TR20240220140010, 198.00, 0.00, 198.00, 微信);三SQL 编写查询 2023 年各季度的交易总金额、平均单笔交易金额、交易笔数按季度升序排序。SELECT pt.quarter, SUM(ad.actual_pay) AS total_trans_amount, ROUND(AVG(ad.actual_pay), 2) AS avg_single_amount, COUNT(DISTINCT pq.order_id) AS transaction_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.quarter ORDER BY pt.quarter;找出购买过 “理财” 类产品且交易金额超过 8000 元的用户 ID、产品名称、实际支付金额及交易日期按实际支付金额降序排序。SELECT pq.user_id, pi.product_name, ad.actual_pay, pt.purchase_date FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN amount_detail ad ON pq.order_id ad.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pi.product_type 理财 AND ad.actual_pay 8000 ORDER BY ad.actual_pay DESC;统计每种支付方式的交易总笔数、总优惠金额、总实际支付金额筛选出总实际支付金额超过 5000 元的支付方式按总实际支付金额降序排序。SELECT ad.payment_method, COUNT(DISTINCT ad.order_id) AS transaction_count, SUM(ad.discount_amount) AS total_discount, SUM(ad.actual_pay) AS total_actual_pay FROM amount_detail ad GROUP BY ad.payment_method HAVING SUM(ad.actual_pay) 5000 ORDER BY total_actual_pay DESC;查询每周各时段小时的交易笔数分布显示星期、交易小时、交易笔数按星期升序、交易小时升序排序。SELECT pt.week_day, pt.purchase_hour, COUNT(DISTINCT pq.order_id) AS transaction_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id GROUP BY pt.week_day, pt.purchase_hour ORDER BY pt.week_day, pt.purchase_hour;找出 2023 年购买商品数量最多的用户按购买总件数统计显示用户 ID、总购买件数、总实际支付金额若有并列则全部列出。WITH user_total_quantity AS ( SELECT user_id, SUM(purchase_quantity) AS total_quantity, SUM(ad.actual_pay) AS total_actual_pay FROM purchase_quantity pq JOIN amount_detail ad ON pq.order_id ad.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 GROUP BY user_id ), max_quantity AS ( SELECT MAX(total_quantity) AS max_qty FROM user_total_quantity ) SELECT utq.user_id, utq.total_quantity, utq.total_actual_pay FROM user_total_quantity utq JOIN max_quantity mq ON utq.total_quantity mq.max_qty ORDER BY utq.user_id;查询每种产品的销售情况产品名称、产品类型、销售总件数、总实际收入按总实际收入降序排序仅显示在售产品status1。SELECT pi.product_name, pi.product_type, SUM(pq.purchase_quantity) AS total_sales_quantity, SUM(ad.actual_pay) AS total_actual_income FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pi.status 1 GROUP BY pi.product_id, pi.product_name, pi.product_type ORDER BY total_actual_income DESC;编写 SQL 语句计算 2023 年每个月的交易增长率环比显示月份、当月交易总金额、上月交易总金额、增长率保留 1 位小数增长率公式(当月 - 上月)/ 上月 ×100%。WITH monthly_trans AS ( SELECT pt.year, MONTH(pt.purchase_date) AS month, SUM(ad.actual_pay) AS monthly_total FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.year, MONTH(pt.purchase_date) ) SELECT current.month, current.monthly_total AS current_month_total, COALESCE(prev.monthly_total, 0) AS prev_month_total, ROUND( CASE WHEN COALESCE(prev.monthly_total, 0) 0 THEN 0 ELSE (current.monthly_total - prev.monthly_total)/prev.monthly_total * 100 END, 1 ) AS growth_rate FROM monthly_trans current LEFT JOIN monthly_trans prev ON current.month prev.month 1 ORDER BY current.month;查询同时购买了 “实物商品” 和 “虚拟商品” 的用户 ID以及两类商品的交易总金额分别显示按用户 ID 升序排序。WITH physical_user AS ( SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_type 实物商品 ), virtual_user AS ( SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_type 虚拟商品 ) SELECT pu.user_id, COALESCE(SUM(CASE WHEN pi.product_type 实物商品 THEN ad.actual_pay ELSE 0 END), 0) AS physical_total, COALESCE(SUM(CASE WHEN pi.product_type 虚拟商品 THEN ad.actual_pay ELSE 0 END), 0) AS virtual_total FROM physical_user pu JOIN virtual_user vu ON pu.user_id vu.user_id JOIN purchase_quantity pq ON pu.user_id pq.user_id JOIN product_info pi ON pq.product_id pi.product_id JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pu.user_id ORDER BY pu.user_id;统计 2023 年各星期的平均每笔交易金额筛选出平均金额高于全年平均每笔交易金额的星期按平均金额降序排序。WITH week_avg_amount AS ( SELECT pt.week_day, ROUND(AVG(ad.actual_pay), 2) AS week_avg_pay FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 GROUP BY pt.week_day ), year_avg_amount AS ( SELECT ROUND(AVG(actual_pay), 2) AS year_avg_pay FROM amount_detail ad JOIN purchase_quantity pq ON ad.order_id pq.order_id JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 ) SELECT wa.week_day, wa.week_avg_pay FROM week_avg_amount wa JOIN year_avg_amount ya ON wa.week_avg_pay ya.year_avg_pay ORDER BY wa.week_avg_pay DESC;创建一个视图v_high_value_transaction包含订单号、用户 ID、产品名称、交易日期、实际支付金额、支付方式要求实际支付金额≥5000 元、交易年份为 2023 年、产品类型为 “理财” 或 “存款”视图数据按交易日期降序排序。CREATE VIEW v_high_value_transaction AS SELECT pq.order_id, pq.user_id, pi.product_name, pt.purchase_date, ad.actual_pay, ad.payment_method FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE ad.actual_pay 5000 AND pt.year 2023 AND pi.product_type IN (理财, 存款) ORDER BY pt.purchase_date DESC;查询 2023 年第三季度7-9 月各产品类型的交易笔数、总金额按产品类型升序排序。SELECT pi.product_type, COUNT(DISTINCT pq.order_id) AS transaction_count, SUM(ad.actual_pay) AS total_amount FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 AND pt.quarter 3 GROUP BY pi.product_type ORDER BY pi.product_type;找出实际支付金额排名前 3 的订单对应的用户 ID、产品名称、交易时间日期 小时、支付方式若有并列则顺延。WITH order_rank AS ( SELECT pq.user_id, pi.product_name, CONCAT(pt.purchase_date, , pt.purchase_hour, :00) AS transaction_time, ad.payment_method, ad.actual_pay, DENSE_RANK() OVER (ORDER BY ad.actual_pay DESC) AS pay_rank FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id ) SELECT user_id, product_name, transaction_time, payment_method, actual_pay FROM order_rank WHERE pay_rank 3 ORDER BY actual_pay DESC, user_id;统计每个用户的购买频次交易笔数和平均每笔交易金额筛选出购买频次≥2 次的用户按平均每笔金额降序排序。SELECT pq.user_id, COUNT(DISTINCT pq.order_id) AS purchase_frequency, ROUND(AVG(ad.actual_pay), 2) AS avg_single_pay FROM purchase_quantity pq JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pq.user_id HAVING COUNT(DISTINCT pq.order_id) 2 ORDER BY avg_single_pay DESC;查询没有享受优惠discount_amount0的交易中各产品类型的交易总金额和占比占无优惠交易总金额的百分比保留 1 位小数。WITH no_discount_total AS ( SELECT SUM(actual_pay) AS total_no_discount FROM amount_detail WHERE discount_amount 0 ) SELECT pi.product_type, SUM(ad.actual_pay) AS type_total, ROUND((SUM(ad.actual_pay)/ndt.total_no_discount)*100, 1) AS type_ratio FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id JOIN no_discount_total ndt WHERE ad.discount_amount 0 GROUP BY pi.product_type, ndt.total_no_discount ORDER BY type_ratio DESC;编写 SQL 语句更新 “高端智能手机”product_name的优惠规则后续交易的优惠金额调整为单位价格的 5%需先查询单位价格再更新金额表中未发生的交易此处假设模拟更新现有符合条件的订单。-- 1. 查询高端智能手机的单位价格 SELECT unit_price FROM product_info WHERE product_name 高端智能手机; -- 2. 模拟更新现有符合条件的订单实际场景为后续交易此处更新现有订单示例 UPDATE amount_detail ad JOIN purchase_quantity pq ON ad.order_id pq.order_id JOIN product_info pi ON pq.product_id pi.product_id SET ad.discount_amount pi.unit_price * 0.05, ad.actual_pay ad.total_amount - (pi.unit_price * 0.05) WHERE pi.product_name 高端智能手机;查询 2023 年各月份的交易高峰时段交易笔数最多的小时显示月份、高峰小时、交易笔数若有多个高峰时段则全部列出。WITH monthly_hour_count AS ( SELECT pt.year, MONTH(pt.purchase_date) AS month, pt.purchase_hour, COUNT(DISTINCT pq.order_id) AS hour_count FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id WHERE pt.year 2023 GROUP BY pt.year, MONTH(pt.purchase_date), pt.purchase_hour ), monthly_max_count AS ( SELECT month, MAX(hour_count) AS max_hour_count FROM monthly_hour_count GROUP BY month ) SELECT mhc.month, mhc.purchase_hour AS peak_hour, mhc.hour_count FROM monthly_hour_count mhc JOIN monthly_max_count mmc ON mhc.month mmc.month AND mhc.hour_count mmc.max_hour_count ORDER BY mhc.month, mhc.purchase_hour;统计每种产品的复购率购买该产品的用户中有多次购买记录的用户占比保留 1 位小数复购定义为同一用户对同一产品购买≥2 次基于现有数据若无复购则显示 0%。WITH product_user_purchase AS ( SELECT pi.product_id, pi.product_name, pq.user_id, COUNT(DISTINCT pq.order_id) AS purchase_times FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id GROUP BY pi.product_id, pi.product_name, pq.user_id ), product_user_stats AS ( SELECT product_id, product_name, COUNT(DISTINCT user_id) AS total_user, COUNT(DISTINCT CASE WHEN purchase_times 2 THEN user_id ELSE NULL END) AS repeat_user FROM product_user_purchase GROUP BY product_id, product_name ) SELECT product_name, ROUND(CASE WHEN total_user 0 THEN 0 ELSE (repeat_user/total_user)*100 END, 1) AS repurchase_rate FROM product_user_stats ORDER BY repurchase_rate DESC;查询 2023 年使用微信支付的交易中各季度的总金额和平均优惠金额按季度升序排序。SELECT pt.quarter, SUM(ad.actual_pay) AS total_wechat_pay, ROUND(AVG(ad.discount_amount), 2) AS avg_wechat_discount FROM purchase_quantity pq JOIN purchase_time pt ON pq.time_id pt.time_id JOIN amount_detail ad ON pq.order_id ad.order_id WHERE pt.year 2023 AND ad.payment_method 微信 GROUP BY pt.quarter ORDER BY pt.quarter;找出购买了 “一年期定期存款” 但未购买任何 “理财” 类产品的用户 ID按用户 ID 升序排序。SELECT DISTINCT pq.user_id FROM purchase_quantity pq JOIN product_info pi ON pq.product_id pi.product_id WHERE pi.product_name 一年期定期存款 AND pq.user_id NOT IN ( SELECT DISTINCT pq2.user_id FROM purchase_quantity pq2 JOIN product_info pi2 ON pq2.product_id pi2.product_id WHERE pi2.product_type 理财 ) ORDER BY pq.user_id;编写 SQL 语句计算各产品类型的客单价总实际收入 / 购买用户数保留 2 位小数按客单价降序排序。SELECT pi.product_type, ROUND(SUM(ad.actual_pay)/COUNT(DISTINCT pq.user_id), 2) AS customer_unit_price FROM product_info pi JOIN purchase_quantity pq ON pi.product_id pq.product_id JOIN amount_detail ad ON pq.order_id ad.order_id GROUP BY pi.product_type ORDER BY customer_unit_price DESC;