2324. 产品销售分析 IV（难度：中等）

题目描述

（通过次数465 | 提交次数609，通过率76.35%）

表: Sales
+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| user_id     | int   |
| quantity    | int   |
+-------------+-------+
sale_id 是该表的主键。
product_id 是 product 表的外键。
该表的每一行都显示了产品的 ID 和用户购买的数量。
表: Product
+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id 是该表的主键
该表的每一行都表示每种产品的价格。

编写一个 SQL 查询，为每个用户获取其消费最多的产品 id。如果同一用户在两个或多个产品上花费了最多的钱，请获取所有花费了最多的钱的产品。
以 任意顺序 返回结果表。

查询结果格式如下所示。
示例 1:
输入: 
Sales 表:
+---------+------------+---------+----------+
| sale_id | product_id | user_id | quantity |
+---------+------------+---------+----------+
| 1       | 1          | 101     | 10       |
| 2       | 3          | 101     | 7        |
| 3       | 1          | 102     | 9        |
| 4       | 2          | 102     | 6        |
| 5       | 3          | 102     | 10       |
| 6       | 1          | 102     | 6        |
+---------+------------+---------+----------+
Product 表:
+------------+-------+
| product_id | price |
+------------+-------+
| 1          | 10    |
| 2          | 25    |
| 3          | 15    |
+------------+-------+
输出: 
+---------+------------+
| user_id | product_id |
+---------+------------+
| 101     | 3          |
| 102     | 1          |
| 102     | 2          |
| 102     | 3          |
+---------+------------+ 
解释: 
用户 101:
    - 在产品 1 上花费 10 * 10 = 100。
    - 在产品 3 上花费 7 * 15 = 105。
用户101在产品3上花的钱最多。
用户 102:
    - 在产品 1 上花费 (9 + 7)* 10 = 150
    - 在产品 2 上花费 6 * 25 = 150
    - 在产品 3 上花费 10 * 15 = 150。
用户 102 在产品 1、2、3 上花的钱最多。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/product-sales-analysis-iv

//测试数据
Create table If Not Exists Sales (sale_id int, product_id int, user_id int, quantity int);
Create table If Not Exists Product (product_id int, price int);

insert into Sales (sale_id, product_id, user_id, quantity) values ('1', '1', '101', '10');
insert into Sales (sale_id, product_id, user_id, quantity) values ('2', '3', '101', '7');
insert into Sales (sale_id, product_id, user_id, quantity) values ('3', '1', '102', '9');
insert into Sales (sale_id, product_id, user_id, quantity) values ('4', '2', '102', '6');
insert into Sales (sale_id, product_id, user_id, quantity) values ('5', '3', '102', '10');
insert into Sales (sale_id, product_id, user_id, quantity) values ('6', '1', '102', '6');

insert into Product (product_id, price) values ('1', '10');
insert into Product (product_id, price) values ('2', '25');
insert into Product (product_id, price) values ('3', '15');

解题思路

Product表保存了所有的产品价格。
Sales表保存了所有的销售记录。包括购买用户、购买产品、购买数量。
题目要求：计算出每个用户购买金额最多的产品。（购买金额 = 购买数量 * 产品价格）
那么，我们可以通过以下步骤来完成：
第一步：计算出每笔销售记录的购买金额；
因为购买金额 = 购买数量 * 产品价格，而购买数量保存在Sales表、产品价格保存在Product表，所以需要将两张表关联起来，然后计算出购买金额。
第二步：计算出每个用户、每个产品总的购买金额；
直接使用GROUP BY+SUM对用户ID+产品ID分组汇总即可。
第三步：计算出每个用户总购买金额最多的产品。
因为是要求计算每个用户的最多，所以可以通过分组开窗排序实现。如果购买金额最多的产品有多个，都需要返回，那么使用row_number开窗排序就不合适了，可以使用rank或dense_rank实现。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp as (
    select
        a.user_id,
        a.product_id,
        sum(a.quantity*coalesce(b.price,0)) amt
    from Sales a
    left join Product b
    on a.product_id = b.product_id 
    group by a.user_id,
        a.product_id
)
select
    b.user_id,
    b.product_id
from (
    select
        user_id,
        product_id,
        rank() over(partition by user_id order by amt desc) rk
    from tmp
)b
where b.rk = 1;