3214. 同比增长率 🔒

题目描述

表：user_transactions

+------------------+----------+
| Column Name      | Type     | 
+------------------+----------+
| transaction_id   | integer  |
| product_id       | integer  |
| spend            | decimal  |
| transaction_date | datetime |
+------------------+----------+
transaction_id 列唯一标识了表中的每一列。
这张表的每一行含有交易 ID，产品 ID，总花费以及交易日期。

编写一个解决方案来计算 每个产品 总支出的 同比增长率。

结果表应该包含以下列：

year：交易的年份。
product_id：产品的 ID。
curr_year_spend：当年的总支出。
prev_year_spend：上一年的总支出。
yoy_rate：同比增速百分比，四舍五入至小数点后 2 位。

返回结果表以 product_id，year 升序排序。

结果格式如下所示。

示例：

输入：

user_transactions 表：

+----------------+------------+---------+---------------------+
| transaction_id | product_id | spend   | transaction_date    |
+----------------+------------+---------+---------------------+
| 1341           | 123424     | 1500.60 | 2019-12-31 12:00:00 |
| 1423           | 123424     | 1000.20 | 2020-12-31 12:00:00 |
| 1623           | 123424     | 1246.44 | 2021-12-31 12:00:00 |
| 1322           | 123424     | 2145.32 | 2022-12-31 12:00:00 |
+----------------+------------+---------+---------------------+

输出：

+------+------------+----------------+----------------+----------+
| year | product_id | curr_year_spend| prev_year_spend| yoy_rate |
+------+------------+----------------+----------------+----------+
| 2019 | 123424     | 1500.60        | NULL           | NULL     |
| 2020 | 123424     | 1000.20        | 1500.60        | -33.35   |
| 2021 | 123424     | 1246.44        | 1000.20        | 24.62    |
| 2022 | 123424     | 2145.32        | 1246.44        | 72.12    |
+------+------------+----------------+----------------+----------+

解释：

对于产品 ID 123424:
- 在 2019：
  - 当年的支出是 1500.60
  - 没有上一年支出的记录
  - 同比增长率：NULL
- 在 2020：
  - 当年的支出是 1000.20
  - 上一年的支出是 1500.60
  - 同比增长率：((1000.20 - 1500.60) / 1500.60) * 100 = -33.35%
- 在 2021：
  - 当年的支出是 1246.44
  - 上一年的支出是 1000.20
  - 同比增长率：((1246.44 - 1000.20) / 1000.20) * 100 = 24.62%
- 在 2022：
  - 当年的支出是 2145.32
  - 上一年的支出是 1246.44
  - 同比增长率：((2145.32 - 1246.44) / 1246.44) * 100 = 72.12%

注意：输出表以 product_id 和 year 升序排序。

解法

方法一：分组统计 + 左连接

我们可以先按照 product_id 和 year(transaction_date) 进行分组统计，然后使用左连接将当前年份的统计结果与上一年份的统计结果进行关联，最后计算年同比增长率。

MySQL

# Write your MySQL query statement below
WITH
    T AS (
        SELECT product_id, YEAR(transaction_date) year, SUM(spend) curr_year_spend
        FROM user_transactions
        GROUP BY 1, 2
    ),
    S AS (
        SELECT t1.year, t1.product_id, t1.curr_year_spend, t2.curr_year_spend prev_year_spend
        FROM
            T t1
            LEFT JOIN T t2 ON t1.product_id = t2.product_id AND t1.year = t2.year + 1
    )
SELECT
    *,
    ROUND((curr_year_spend - prev_year_spend) / prev_year_spend * 100, 2) yoy_rate
FROM S
ORDER BY 2, 1;

3214. 同比增长率 🔒

题目描述

解法

方法一：分组统计 + 左连接

评论