描述

【背景】：电商平台需要对商家的销售业绩、退款情况和客户满意度进行综合评估，以确定优秀商家和需要改进的商家。
【原始表】：

merchants_underline （商家）表:

• merchant_id (商家 ID): 商家的唯一标识符，INT
• merchant_name (商家名称): 商家的名称，VARCHAR(50)
• industry (行业): 商家所属的行业，VARCHAR(20)

sales_underline （销售）表:

• sale_id (销售 ID): 销售记录的唯一标识符，INT
• merchant_id (商家 ID): 关联商家表的商家 ID，INT
• sale_amount (销售金额): 销售的金额，DECIMAL(10, 2)

refunds_underline （退款）表:

• refund_id (退款 ID): 退款记录的唯一标识符，INT
• merchant_id (商家 ID): 关联商家表的商家 ID，INT
• refund_amount (退款金额): 退款的金额，DECIMAL(10, 2)

satisfaction_underline （满意度）表:

• satisfaction_id (满意度 ID): 满意度记录的唯一标识符，INT
• merchant_id (商家 ID): 关联商家表的商家 ID，INT
• satisfaction_score (满意度得分): 客户对商家的满意度得分，INT（1 - 100 分）

【要求】：根据上述表格，查询出商家的总销售金额、总退款金额、平均满意度得分。查询结果按照商家ID升序排列。要求查询出来的表格的字段如下：

• merchant_id: 商家 ID。
• merchant_name ：商家名称。
• total_sales_amount: 总销售金额。
• total_refund_amount: 总退款金额。
• average_satisfaction_score: 平均满意度得分。（round保留2位小数）

【示例】

merchants_underline （商家）表:

sales_underline （销售）表:

refunds_underline （退款）表:

satisfaction_underline （满意度）表:

【按要求查出来的表】

【解释】

上述示例中merchant_id是1的商家名称是商家A，总销售额是5000+4000 = 9000；退款总金额是1000，满意度得分是（80+70）/2 = 75

示例1

输入：

DROP TABLE IF EXISTS merchants_underline;
DROP TABLE IF EXISTS sales_underline;
DROP TABLE IF EXISTS refunds_underline;
DROP TABLE IF EXISTS satisfaction_underline;
-- 创建表
CREATE TABLE merchants_underline (
    merchant_id INT PRIMARY KEY,
    merchant_name VARCHAR(50),
    industry VARCHAR(20)
);

CREATE TABLE sales_underline (
    sale_id INT PRIMARY KEY,
    merchant_id INT,
    sale_amount DECIMAL(10, 2)
);

CREATE TABLE refunds_underline (
    refund_id INT PRIMARY KEY,
    merchant_id INT,
    refund_amount DECIMAL(10, 2)
);

CREATE TABLE satisfaction_underline (
    satisfaction_id INT PRIMARY KEY,
    merchant_id INT,
    satisfaction_score INT
);

-- 插入数据
INSERT INTO merchants_underline (merchant_id, merchant_name, industry)
VALUES (1, '商家 A', '服装'),
       (2, '商家 B', '电子产品');

INSERT INTO sales_underline (sale_id, merchant_id, sale_amount)
VALUES (1, 1, 5000.00),
       (2, 2, 8000.00),
       (3, 1, 4000.00),
       (4, 2, 6000.00);

INSERT INTO refunds_underline (refund_id, merchant_id, refund_amount)
VALUES (1, 1, 1000.00),
       (2, 2, 1500.00);

INSERT INTO satisfaction_underline (satisfaction_id, merchant_id, satisfaction_score)
VALUES (1, 1, 80),
       (2, 2, 90),
       (3, 1, 70),
       (4, 2, 60);


select * from merchants_underline;
select * from sales_underline;
select * from refunds_underline;
select * from satisfaction_underline;

复制输出：

merchant_id|merchant_name|total_sales_amount|total_refund_amount|average_satisfaction_score
1|商家 A|9000.00|1000.00|75.00
2|商家 B|14000.00|1500.00|75.00

with  t1 as (
 
    select distinct merchant_id ,sum(sale_amount) over (partition by merchant_id) as total_sales_amount
    from sales_underline
),
       t2 as (
            select distinct  merchant_id,sum(refund_amount) over (partition by merchant_id) as total_refund_amount
            from refunds_underline
        ),
    t3 as (
        select distinct  merchant_id ,round((avg(satisfaction_score) over (partition by merchant_id) ),2) as average_satisfaction_score
 
        from satisfaction_underline
    )
 
select merchants_underline.merchant_id,merchant_name,t1.total_sales_amount,t2.total_refund_amount,t3.average_satisfaction_score
from merchants_underline
 join t1 on t1.merchant_id=merchants_underline.merchant_id
 join t2 on t2.merchant_id=merchants_underline.merchant_id
join t3 on t3.merchant_id=merchants_underline.merchant_id