# 173 电商场景

## **SQL168** 计算商城中2021年每月的GMV

## 描述

现有订单总表tb\_order\_overall

| id | order\_id | uid | event\_time         | total\_amount | total\_cnt | status |
| -- | --------- | --- | ------------------- | ------------- | ---------- | ------ |
| 1  | 301001    | 101 | 2021-10-01 10:00:00 | 15900         | 2          | 1      |
| 2  | 301002    | 101 | 2021-10-01 11:00:00 | 15900         | 2          | 1      |
| 3  | 301003    | 102 | 2021-10-02 10:00:00 | 34500         | 8          | 0      |
| 4  | 301004    | 103 | 2021-10-12 10:00:00 | 43500         | 9          | 1      |
| 5  | 301005    | 105 | 2021-11-01 10:00:00 | 31900         | 7          | 1      |
| 6  | 301006    | 102 | 2021-11-02 10:00:00 | 24500         | 6          | 1      |
| 7  | 301007    | 102 | 2021-11-03 10:00:00 | -24500        | 6          | 2      |
| 8  | 301008    | 104 | 2021-11-04 10:00:00 | 55500         | 12         | 0      |

（order\_id-订单号, uid-用户ID, event\_time-下单时间, total\_amount-订单总金额, total\_cnt-订单商品总件数, status-订单状态）

**场景逻辑说明**：

* 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，**status-订单状态**为**0**，表示待付款）；
* 当用户支付完成时，在订单总表修改对应订单记录的**status-订单状态**为**1**，表示已付款；
* 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，**status-订单状态**为2表示已退款）。

**问题**：请计算商城中2021年每月的GMV，输出GMV大于10w的每月GMV，值保留到整数。

**注**：GMV为已付款订单和未付款订单两者之和。结果按GMV升序排序。

**输出示例**：

示例数据输出如下：

| month   | GMV    |
| ------- | ------ |
| 2021-10 | 109800 |
| 2021-11 | 111900 |

解释：

2021年10月有3笔已付款的订单，1笔未付款订单，总交易金额为109800；2021年11月有2笔已付款订单，1笔未付款订单，

总交易金额为111900（还有1笔退款订单由于已计算了付款的订单金额，无需计算在GMV中）。

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 15900, 2, 1),
  (301002, 101, '2021-10-01 11:00:00', 15900, 2, 1),
  (301003, 102, '2021-10-02 10:00:00', 34500, 8, 0),
  (301004, 103, '2021-10-12 10:00:00', 43500, 9, 1),
  (301005, 105, '2021-11-01 10:00:00', 31900, 7, 1),
  (301006, 102, '2021-11-02 10:00:00', 24500, 6, 1),
  (391007, 102, '2021-11-03 10:00:00', -24500, 6, 2),
  (301008, 104, '2021-11-04 10:00:00', 55500, 12, 0);
```

复制

输出：

```
2021-10|109800
2021-11|111900
```

### 题眼：

1.每月的GMV：date\_format(event\_time,'%Y-%m')

2.根据月份分组：group by

3.过滤部分条件：having

### sql

```sql
SELECT
	date_format(event_time, '%Y-%m') `month`,
	sum(total_amount) GMV
from
	tb_order_overall
where
	(status != 2)
	and year(event_time) = 2021
group by
	`month`
having
	GMV > 100000
order by
	GMV
```

## **SQL169** **统计2021年10月每个退货率不大于0.5的商品各项指标**

## 描述

现有用户对展示的商品行为表tb\_user\_event

| id | uid | product\_id | event\_time         | if\_click | if\_cart | if\_payment | if\_refund |
| -- | --- | ----------- | ------------------- | --------- | -------- | ----------- | ---------- |
| 1  | 101 | 8001        | 2021-10-01 10:00:00 | 0         | 0        | 0           | 0          |
| 2  | 102 | 8001        | 2021-10-01 10:00:00 | 1         | 0        | 0           | 0          |
| 3  | 103 | 8001        | 2021-10-01 10:00:00 | 1         | 1        | 0           | 0          |
| 4  | 104 | 8001        | 2021-10-02 10:00:00 | 1         | 1        | 1           | 0          |
| 5  | 105 | 8001        | 2021-10-02 10:00:00 | 1         | 1        | 1           | 0          |
| 6  | 101 | 8002        | 2021-10-03 10:00:00 | 1         | 1        | 1           | 0          |
| 7  | 109 | 8001        | 2021-10-04 10:00:00 | 1         | 1        | 1           | 1          |

（uid-用户ID, product\_id-商品ID, event\_time-行为时间, if\_click-是否点击, if\_cart-是否加购物车, if\_payment-是否付款, if\_refund-是否退货退款）

**问题**：请统计2021年10月每个有展示记录的退货率不大于0.5的商品各项指标，

**注**：

* 商品点展比=点击数÷展示数；
* 加购率=加购数÷点击数；
* 成单率=付款数÷加购数；退货率=退款数÷付款数，
* 当分母为0时整体结果记为0，结果中各项指标保留3位小数，并按商品ID升序排序。

**输出示例**：

示例数据的输出结果如下

| product\_id | ctr   | cart\_rate | payment\_rate | refund\_rate |
| ----------- | ----- | ---------- | ------------- | ------------ |
| 8001        | 0.833 | 0.800      | 0.750         | 0.333        |
| 8002        | 1.000 | 1.000      | 1.000         | 0.000        |

解释：

在2021年10月商品8001被展示了6次，点击了5次，加购了4次，付款了3次，退款了1次，因此点击率为5/6=0.833，加购率为4/5=0.800，

成单率为3/4=0.750，退货率为1/3=0.333（保留3位小数）；

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_user_event;
CREATE TABLE tb_user_event (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    product_id INT NOT NULL COMMENT '商品ID',
    event_time datetime COMMENT '行为时间',
    if_click TINYINT COMMENT '是否点击',
    if_cart TINYINT COMMENT '是否加购物车',
    if_payment TINYINT COMMENT '是否付款',
    if_refund TINYINT COMMENT '是否退货退款'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_event(uid, product_id, event_time, if_click, if_cart, if_payment, if_refund) VALUES
  (101, 8001, '2021-10-01 10:00:00', 0, 0, 0, 0),
  (102, 8001, '2021-10-01 10:00:00', 1, 0, 0, 0),
  (103, 8001, '2021-10-01 10:00:00', 1, 1, 0, 0),
  (104, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (105, 8001, '2021-10-02 10:00:00', 1, 1, 1, 0),
  (101, 8002, '2021-10-03 10:00:00', 1, 1, 1, 0),
  (109, 8001, '2021-10-04 10:00:00', 1, 1, 1, 1);
```

复制

输出：

```
8001|0.833|0.800|0.750|0.333
8002|1.000|1.000|1.000|0.000
```

### 题眼：

1.商品点展比=点击数÷展示数：round(sum(if\_click)/ count(1), 3)

2.加购率=加购数÷点击数：round(sum(if\_cart)/ sum(if\_click), 3)

3.成单率=付款数÷加购数：round(sum(if\_payment)/ sum(if\_cart), 3)

4.退货率=退款数÷付款数：round(sum(if\_refund)/ sum(if\_payment), 3)

### sql

```sql
select
	product_id,
	round(sum(if_click)/ count(1), 3) ctr,
	round(sum(if_cart)/ sum(if_click), 3) cart_rate,
	round(sum(if_payment)/ sum(if_cart), 3) payment_rate,
	round(sum(if_refund)/ sum(if_payment), 3) refund_rate
from
	tb_user_event
where
	DATE_FORMAT(event_time, '%Y-%m') = '2021-10'
group by
	product_id
having
	refund_rate <= 0.5
order by
	product_id
```

## SQL170 某店铺的各商品毛利率及店铺整体毛利率

## 描述

商品信息表tb\_product\_info

| id | product\_id | shop\_id | tag  | in\_price | quantity | release\_time       |
| -- | ----------- | -------- | ---- | --------- | -------- | ------------------- |
| 1  | 8001        | 901      | 家电   | 6000      | 100      | 2020-01-01 10:00:00 |
| 2  | 8002        | 902      | 家电   | 12000     | 50       | 2020-01-01 10:00:00 |
| 3  | 8003        | 901      | 3C数码 | 12000     | 50       | 2020-01-01 10:00:00 |

（product\_id-商品ID, shop\_id-店铺ID, tag-商品类别标签, in\_price-进货价格, quantity-进货数量, release\_time-上架时间）

订单总表tb\_order\_overall

| id | order\_id | uid | event\_time         | total\_amount | total\_cnt | status |
| -- | --------- | --- | ------------------- | ------------- | ---------- | ------ |
| 1  | 301001    | 101 | 2021-10-01 10:00:00 | 30000         | 3          | 1      |
| 2  | 301002    | 102 | 2021-10-01 11:00:00 | 23900         | 2          | 1      |
| 3  | 301003    | 103 | 2021-10-02 10:00:00 | 31000         | 2          | 1      |

（order\_id-订单号, uid-用户ID, event\_time-下单时间, total\_amount-订单总金额, total\_cnt-订单商品总件数, status-订单状态）

订单明细表tb\_order\_detail

| id | order\_id | product\_id | price | cnt |
| -- | --------- | ----------- | ----- | --- |
| 1  | 301001    | 8001        | 8500  | 2   |
| 2  | 301001    | 8002        | 15000 | 1   |
| 3  | 301002    | 8001        | 8500  | 1   |
| 4  | 301002    | 8002        | 16000 | 1   |
| 5  | 301003    | 8002        | 14000 | 1   |
| 6  | 301003    | 8003        | 18000 | 1   |

（order\_id-订单号, product\_id-商品ID, price-商品单价, cnt-下单数量）

**场景逻辑说明**：

* 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款，**status-订单状态**为**0**表示待付款），在订单明细表生成该订单中每个商品的信息；
* 当用户支付完成时，在订单总表修改对应订单记录的**status-订单状态**为**1**表示已付款；
* 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，**status-订单状态**为2表示已退款）。

**问题**：请计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

**注**：商品毛利率=(1-进价/平均单件售价)\*100%；

店铺毛利率=(1-总进价成本/总销售收入)\*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

**输出示例**：

示例数据的输出结果如下：

| product\_id | profit\_rate |
| ----------- | ------------ |
| 店铺汇总        | 31.0%        |
| 8001        | 29.4%        |
| 8003        | 33.3%        |

解释：

店铺901有两件商品8001和8003；8001售出了3件，销售总额为25500，进价总额为18000，毛利率为1-18000/25500=29.4%，8003售出了1件，售价为18000，进价为12000，毛利率为33.3%；

店铺卖出的这4件商品总销售额为43500，总进价为30000，毛利率为1-30000/43500=31.0%

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 30000, 3, 1),
  (301002, 102, '2021-10-01 11:00:00', 23900, 2, 1),
  (301003, 103, '2021-10-02 10:00:00', 31000, 2, 1);

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '家电', 6000, 100, '2020-01-01 10:00:00'),
  (8002, 902, '家电', 12000, 50, '2020-01-01 10:00:00'),
  (8003, 901, '3C数码', 12000, 50, '2020-01-01 10:00:00');

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8001, 8500, 2),
  (301001, 8002, 15000, 1),
  (301002, 8001, 8500, 1),
  (301002, 8002, 16000, 1),
  (301003, 8002, 14000, 1),
  (301003, 8003, 18000, 1);
```

复制

输出：

```
店铺汇总|31.0%
8001|29.4%
8003|33.3%
```

### 解答

### 明确题意：

计算2021年10月以来店铺901中商品毛利率大于24.9%的商品信息及店铺整体毛利率。

此处毛利率定义如下：商品毛利率=(1-进价/平均单件售价)\*100%；店铺毛利率=(1-总进价成本/总销售收入)\*100%。

结果先输出店铺毛利率，再按商品ID升序输出各商品毛利率，均保留1位小数。

***

### 问题分解：

* 统计每个被售出的商品的售价进价（生成子表t\_product\_in\_each\_order）：
  * 订单明细表内连接商品信息表：tb\_order\_detail JOIN tb\_product\_info USING(product\_id)
  * 继续内连接订单总表：JOIN tb\_order\_overall USING(order\_id)
  * 筛选店铺和时间窗：WHERE shop\_id = 901 and DATE(event\_time) >= "2021-10-01"
* 按商品分组：GROUP BY product\_id
* 加上汇总结果：WITH ROLLUP
* 商品ID列重整：IFNULL(product\_id, '店铺汇总') as product\_id
* 计算商品利润率：`100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)) as profit_rate`
* 保留1位小数：ROUND(x, 1)
* 筛选满足条件的分组（商品）：HAVING profit\_rate > 24.9 OR product\_id IS NULL
* 格式化毛利率格式：CONCAT(profit\_rate, "%") as profit\_rate

***

### 细节问题：

* 表头重命名：as
* 按商品ID排序：ORDER BY product\_id

```sql
SELECT product_id, CONCAT(profit_rate, "%") as profit_rate
FROM (
    SELECT IFNULL(product_id, '店铺汇总') as product_id,
        ROUND(100 * (1 - SUM(in_price*cnt) / SUM(price*cnt)), 1) as profit_rate
    FROM (
        SELECT product_id, price, cnt, in_price
        FROM tb_order_detail
        JOIN tb_product_info USING(product_id)
        JOIN tb_order_overall USING(order_id)
        WHERE shop_id = 901 and DATE(event_time) >= "2021-10-01"
    ) as t_product_in_each_order
    GROUP BY product_id
    WITH ROLLUP
    HAVING profit_rate > 24.9 OR product_id IS NULL
    ORDER BY product_id
) as t1;
```

## **SQL171** 零食类商品中复购率top3高的商品

## 描述

商品信息表tb\_product\_info

| id | product\_id | shop\_id | tag | int\_ | quantity | release\_time       |
| -- | ----------- | -------- | --- | ----- | -------- | ------------------- |
| 1  | 8001        | 901      | 零食  | 60    | 1000     | 2020-01-01 10:00:00 |
| 2  | 8002        | 901      | 零食  | 140   | 500      | 2020-01-01 10:00:00 |
| 3  | 8003        | 901      | 零食  | 160   | 500      | 2020-01-01 10:00:00 |

（product\_id-商品ID, shop\_id-店铺ID, tag-商品类别标签, in\_price-进货价格, quantity-进货数量, release\_time-上架时间）

订单总表tb\_order\_overall

| id | order\_id | uid | event\_time         | total\_amount | total\_cnt | status |
| -- | --------- | --- | ------------------- | ------------- | ---------- | ------ |
| 1  | 301001    | 101 | 2021-09-30 10:00:00 | 140           | 1          | 1      |
| 2  | 301002    | 102 | 2021-10-01 11:00:00 | 235           | 2          | 1      |
| 3  | 301011    | 102 | 2021-10-31 11:00:00 | 250           | 2          | 1      |
| 4  | 301003    | 101 | 2021-10-02 10:00:00 | 300           | 2          | 1      |
| 5  | 301013    | 105 | 2021-10-02 10:00:00 | 300           | 2          | 1      |
| 6  | 301005    | 104 | 2021-10-03 10:00:00 | 170           | 1          | 1      |

（order\_id-订单号, uid-用户ID, event\_time-下单时间, total\_amount-订单总金额, total\_cnt-订单商品总件数, status-订单状态）

订单明细表tb\_order\_detail

| id | order\_id | product\_id | price | cnt |
| -- | --------- | ----------- | ----- | --- |
| 1  | 301001    | 8002        | 150   | 1   |
| 2  | 301011    | 8003        | 200   | 1   |
| 3  | 301011    | 8001        | 80    | 1   |
| 4  | 301002    | 8001        | 85    | 1   |
| 5  | 301002    | 8003        | 180   | 1   |
| 6  | 301003    | 8002        | 140   | 1   |
| 7  | 301003    | 8003        | 180   | 1   |
| 8  | 301013    | 8002        | 140   | 2   |
| 9  | 301005    | 8003        | 180   | 1   |

（order\_id-订单号, product\_id-商品ID, price-商品单价, cnt-下单数量）

**场景逻辑说明**：

* 用户将购物车中多件商品一起下单时，订单总表会生成一个订单（但此时未付款， **status-订单状态-\*\*\*\*订单状态**为**0**表示待付款），在订单明细表生成该订单中每个商品的信息；
* 当用户支付完成时，在订单总表修改对应订单记录的**status-订单状态-\*\*\*\*订单状态**为**1**表示已付款；
* 若用户退货退款，在订单总表生成一条交易总金额为负值的记录（表示退款金额，订单号为退款单号，订单状态为**2表示**已退款）。

**问题**：请统计零食类商品中复购率top3高的商品。

**注**：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率

此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 ÷ 购买它的总人数

近90天指包含最大日期（记为当天）在内的近90天。结果中复购率保留3位小数，并按复购率倒序、商品ID升序排序

**输出示例**：

示例数据的输出结果如下：

| product\_id | repurchase\_rate |
| ----------- | ---------------- |
| 8001        | 1.000            |
| 8002        | 0.500            |
| 8003        | 0.333            |

解释：

商品8001、8002、8003都是零食类商品，8001只被用户102购买了两次，复购率1.000；

商品8002被101购买了两次，被105购买了1次，复购率0.500；

商品8003被102购买两次，被101和105各购买1次，复购率为0.333。

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '零食', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-09-30 10:00:00', 140, 1, 1),
  (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
  (301011, 102, '2021-10-31 11:00:00', 250, 2, 1),
  (301003, 101, '2021-11-02 10:00:00', 300, 2, 1),
  (301013, 105, '2021-11-02 10:00:00', 300, 2, 1),
  (301005, 104, '2021-11-03 10:00:00', 170, 1, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8002, 150, 1),
  (301011, 8003, 200, 1),
  (301011, 8001, 80, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 140, 1),
  (301003, 8003, 180, 1),
  (301013, 8002, 140, 2),
  (301005, 8003, 180, 1);
```

复制

输出：

```
8001|1.000
8002|0.500
8003|0.333
```

### 题眼

1.近90天内购买：DATE\_SUB(date,INTERVAL expr type)

DATE\_SUB(MAX(event\_time), INTERVAL 89 DAY)

### sql

```sql
SELECT product_id,
    ROUND(SUM(repurchase) / COUNT(repurchase), 3) as repurchase_rate
FROM (
    SELECT product_id, IF(COUNT(event_time)>1, 1, 0) as repurchase
    FROM tb_order_detail
    JOIN tb_order_overall USING(order_id)
    JOIN tb_product_info USING(product_id)
    WHERE tag='零食' AND event_time >= (
        SELECT DATE_SUB(MAX(event_time), INTERVAL 89 DAY)
        FROM tb_order_overall
    )
    GROUP BY uid, product_id
) as t_uid_product_info
GROUP BY product_id
ORDER BY repurchase_rate DESC, product_id
LIMIT 3;

```

## SQL172 10月的新户客单价和获客成本

## 描述

商品信息表tb\_product\_info

| id | product\_id | shop\_id | tag | int\_ | quantity | release\_time       |
| -- | ----------- | -------- | --- | ----- | -------- | ------------------- |
| 1  | 8001        | 901      | 日用  | 60    | 1000     | 2020-01-01 10:00:00 |
| 2  | 8002        | 901      | 零食  | 140   | 500      | 2020-01-01 10:00:00 |
| 3  | 8003        | 901      | 零食  | 160   | 500      | 2020-01-01 10:00:00 |
| 4  | 8004        | 902      | 零食  | 130   | 500      | 2020-01-01 10:00:00 |

（product\_id-商品ID, shop\_id-店铺ID, tag-商品类别标签, in\_price-进货价格, quantity-进货数量, release\_time-上架时间）

订单总表tb\_order\_overall

| id | order\_id | uid | event\_time         | total\_amount | total\_cnt | status |
| -- | --------- | --- | ------------------- | ------------- | ---------- | ------ |
| 1  | 301002    | 102 | 2021-10-01 11:00:00 | 235           | 2          | 1      |
| 2  | 301003    | 101 | 2021-10-02 10:00:00 | 300           | 2          | 1      |
| 3  | 301005    | 104 | 2021-10-03 10:00:00 | 160           | 1          | 1      |

（order\_id-订单号, uid-用户ID, event\_time-下单时间, total\_amount-订单总金额, total\_cnt-订单商品总件数, status-订单状态）

订单明细表tb\_order\_detail

| id | order\_id | product\_id | price | cnt |
| -- | --------- | ----------- | ----- | --- |
| 1  | 301002    | 8001        | 85    | 1   |
| 2  | 301002    | 8003        | 180   | 1   |
| 3  | 301003    | 8004        | 140   | 1   |
| 4  | 301003    | 8003        | 180   | 1   |
| 5  | 301005    | 8003        | 180   | 1   |

（order\_id-订单号, product\_id-商品ID, price-商品单价, cnt-下单数量）

**问题**：请计算2021年10月商城里所有新用户的首单平均交易金额（客单价）和平均获客成本（保留一位小数）。

**注**：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

**输出示例**：

示例数据的输出结果如下

| avg\_amount | avg\_cost |
| ----------- | --------- |
| 231.7       | 23.3      |

解释：

2021年10月有3个新用户，102的首单为301002，订单金额为235，商品总金额为85+180=265，优惠金额为30；

101的首单为301003，订单金额为300，商品总金额为140+180=320，优惠金额为20；

104的首单为301005，订单金额为160，商品总金额为180，优惠金额为20；

平均首单客单价为(235+300+160)/3=231.7，平均获客成本为(30+20+20)/3=23.3

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '日用', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00'),
  (8004, 902, '零食', 130, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
  (301003, 101, '2021-10-02 10:00:00', 300, 2, 1),
  (301005, 104, '2021-10-03 10:00:00', 160, 1, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8004, 140, 1),
  (301003, 8003, 180, 1),
  (301005, 8003, 180, 1);
```

复制

输出：

```
231.7|23.3
```

### 解答

问题：请计算2021年10月商城里所有**新用户**的首\*\*\*均交易金额（客单价）和平均获客成本（保留一位小数）。

注：订单的优惠金额 = 订单明细里的{该订单各商品单价×数量之和} - 订单总表里的{订单总金额} 。

1.题目要求的指标与第一个表无关，因此我们只需要后面两个表即可完成计算。

由于要求新用户的的指标，我们先选出新用户

```
select * from``(select *,rank()over(partition by uid order by event_time ) as rk ``from tb_order_overall ) as u``where rk=``1` `and left(event_time,``7``)=``'2021-10'
```

将上述结果作为表t， 结果如下：

![alt](https://raw.githubusercontent.com/qkd90/figureBed/main/202407111754475.png)

2.将表t和第三个表tb\_order\_detail连接完成我们的计算即可。 先求出total\_amount,以及每个order\_id的订单明细里的总和：

```
select t.order_id,total_amount,``sum(price*cnt) as pc``from t join tb_order_detail as c on t.order_id=c.order_id``group by t.order_id,total_amount
```

结果如下

![alt](https://raw.githubusercontent.com/qkd90/figureBed/main/202407111754472.png)

剩下的按题意进行计算即可。完整代码如下

```sql
select round(sum(total_amount) / count(order_id), 1) avg_amount,
       round(avg(cost), 1) avg_cost
    from (select a.order_id,
                 total_amount,
                 (sum(price * cnt) - total_amount) as cost
              from tb_order_detail a
                       left join tb_order_overall b
                                 on a.order_id = b.order_id
              where date_format(event_time, '%Y-%m') = '2021-10'
                and (uid, event_time) in (select uid, min(event_time) -- 用户和其第一次购买的时间
                                              from tb_order_overall
                                              GROUP BY uid)
              GROUP BY a.order_id) a

```

## **SQL173** **店铺901国庆期间的7日动销率和滞销率**

## 描述

商品信息表tb\_product\_info

| id | product\_id | shop\_id | tag | int\_ | quantity | release\_time       |
| -- | ----------- | -------- | --- | ----- | -------- | ------------------- |
| 1  | 8001        | 901      | 日用  | 60    | 1000     | 2020-01-01 10:00:00 |
| 2  | 8002        | 901      | 零食  | 140   | 500      | 2020-01-01 10:00:00 |
| 3  | 8003        | 901      | 零食  | 160   | 500      | 2020-01-01 10:00:00 |

（product\_id-商品ID, shop\_id-店铺ID, tag-商品类别标签, in\_price-进货价格, quantity-进货数量, release\_time-上架时间）

订单总表tb\_order\_overall

| id | order\_id | uid | event\_time         | total\_amount | total\_cnt | status |
| -- | --------- | --- | ------------------- | ------------- | ---------- | ------ |
| 1  | 301004    | 102 | 2021-09-30 10:00:00 | 170           | 1          | 1      |
| 2  | 301005    | 104 | 2021-10-01 10:00:00 | 160           | 1          | 1      |
| 3  | 301003    | 101 | 2021-10-02 10:00:00 | 300           | 2          | 1      |
| 4  | 301002    | 102 | 2021-10-03 11:00:00 | 235           | 2          | 1      |

（order\_id-订单号, uid-用户ID, event\_time-下单时间, total\_amount-订单总金额, total\_cnt-订单商品总件数, status-订单状态）

订单明细表tb\_order\_detail

| id | order\_id | product\_id | price | cnt |
| -- | --------- | ----------- | ----- | --- |
| 1  | 301004    | 8002        | 180   | 1   |
| 2  | 301005    | 8002        | 170   | 1   |
| 3  | 301002    | 8001        | 85    | 1   |
| 4  | 301002    | 8003        | 180   | 1   |
| 5  | 301003    | 8002        | 150   | 1   |
| 6  | 301003    | 8003        | 180   | 1   |

（order\_id-订单号, product\_id-商品ID, price-商品单价, cnt-下单数量）

**问题**：请计算店铺901在2021年国庆头3天的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

**注**：

* 动销率定义为店铺中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数)。
* 滞销率定义为店铺中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品/已上架总商品数)。
* 只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

**输出示例**：

示例数据的输出结果如下：

| dt         | sale\_rate | unsale\_rate |
| ---------- | ---------- | ------------ |
| 2021-10-01 | 0.333      | 0.667        |
| 2021-10-02 | 0.667      | 0.333        |
| 2021-10-03 | 1.000      | 0.000        |

解释：

10月1日的近7日（9月25日---10月1日）店铺901有销量的商品有8002，截止当天在售商品数为3，动销率为0.333，滞销率为0.667；

10月2日的近7日（9月26日---10月2日）店铺901有销量的商品有8002、8003，截止当天在售商品数为3，动销率为0.667，滞销率为0.333；

10月3日的近7日（9月27日---10月3日）店铺901有销量的商品有8002、8003、8001，截止当天店铺901在售商品数为3，动销率为1.000，

滞销率为0.000；

## 示例1

输入：

```
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '日用', 60, 1000, '2020-01-01 10:00:00'),
  (8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
  (8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301004, 102, '2021-09-30 10:00:00', 170, 1, 1),
  (301005, 104, '2021-10-01 10:00:00', 160, 1, 1),
  (301003, 101, '2021-10-02 10:00:00', 300, 2, 1),
  (301002, 102, '2021-10-03 11:00:00', 235, 2, 1);

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301004, 8002, 180, 1),
  (301005, 8002, 170, 1),
  (301002, 8001, 85, 1),
  (301002, 8003, 180, 1),
  (301003, 8002, 150, 1),
  (301003, 8003, 180, 1);
```

复制

输出：

```
2021-10-01|0.333|0.667
2021-10-02|0.667|0.333
2021-10-03|1.000|0.000
```

### 解答

* 感觉这六道题做了很久，每道题都很折磨。。。。。。
* 这道题我本来思路是想直接用窗口函数统计10-01到10-03每天最近7日有销量的商品数目，然后发现窗口函数不能用DISTINCT，只能用子查询搜索和每天相近七天有销量的商品数目。
* 第一个子查询对应查询每条record相距最近七天的产品，去重后输出
* 第二个子查询对应查询整体有多少个独特的商品（DISTINCT product\_id）
* **欢迎大家指正！**

  ```sql
  SELECT dt, ROUND(cnt / total_cnt, 3) AS sale_rate, ROUND(1 - cnt / total_cnt, 3) AS unsale_rate
      FROM (SELECT DISTINCT DATE(event_time) AS dt,
                            (SELECT COUNT(DISTINCT (IF(shop_id != 901, null, product_id)))
                                 FROM tb_order_overall
                                          JOIN tb_order_detail USING (order_id)
                                          JOIN tb_product_info USING (product_id)
                                 WHERE TIMESTAMPDIFF(DAY, event_time, to1.event_time) BETWEEN 0 AND 6) AS cnt,
                            (SELECT COUNT(DISTINCT product_id)
                                 FROM tb_product_info
                                 WHERE shop_id = 901) AS total_cnt
                FROM tb_order_overall to1
                WHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03') AS t0
      ORDER BY dt;

  ```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qiangrens-organization.gitbook.io/qkd90/shu-ju-ku-he-zhong-jian-jian/sql-yu-ju/168173-dian-shang-chang-jing.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.