跳转至

2292. 连续两年有 3 个及以上订单的产品 🔒

题目描述

表: Orders

+---------------+------+
| Column Name   | Type |
+---------------+------+
| order_id      | int  |
| product_id    | int  |
| quantity      | int  |
| purchase_date | date |
+---------------+------+
order_id 包含唯一值。
该表中的每一行都包含订单 ID、购买的产品 ID、数量和购买日期。

 

编写解决方案，获取连续两年订购三次或三次以上的所有产品的 id。

以 任意顺序 返回结果表。

结果格式示例如下。

 

示例 1:

输入: 
Orders 表:
+----------+------------+----------+---------------+
| order_id | product_id | quantity | purchase_date |
+----------+------------+----------+---------------+
| 1        | 1          | 7        | 2020-03-16    |
| 2        | 1          | 4        | 2020-12-02    |
| 3        | 1          | 7        | 2020-05-10    |
| 4        | 1          | 6        | 2021-12-23    |
| 5        | 1          | 5        | 2021-05-21    |
| 6        | 1          | 6        | 2021-10-11    |
| 7        | 2          | 6        | 2022-10-11    |
+----------+------------+----------+---------------+
输出: 
+------------+
| product_id |
+------------+
| 1          |
+------------+
解释: 
产品 1 在 2020 年和 2021 年都分别订购了三次。由于连续两年订购了三次，所以我们将其包含在答案中。
产品 2 在 2022 年订购了一次。我们不把它包括在答案中。

解法

方法一

思考

要找出连续两年都至少有三笔订单的商品。按商品和年份分组后，需要判断相邻两年是否都达标。自连接比窗口函数更直观：先打上「该年是否 \(\ge 3\) 笔」的标记，再把年份相差 \(1\) 且两边标记都为真的行连起来。

CTE 按 \((\textit{product\_id}, \textit{YEAR})\) 聚合得到 \(\textit{mark}\)，然后 \(p_1.y = p_2.y-1\) 且同一商品、两行 \(\textit{mark}\) 均为真，最后对商品去重。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Write your MySQL query statement below
WITH
    P AS (
        SELECT product_id, YEAR(purchase_date) AS y, COUNT(1) >= 3 AS mark
        FROM Orders
        GROUP BY 1, 2
    )
SELECT DISTINCT p1.product_id
FROM
    P AS p1
    JOIN P AS p2 ON p1.y = p2.y - 1 AND p1.product_id = p2.product_id
WHERE p1.mark AND p2.mark;

方法二

思考

方法一把所有年份都留在 CTE 里再用 \(\textit{mark}\) 过滤。也可以在分组时用 \(\textit{HAVING COUNT}(1)\ge 3\) 只保留达标年份，自连接条件更短，语义相同。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Write your MySQL query statement below
WITH
    P AS (
        SELECT product_id, YEAR(purchase_date) AS y
        FROM Orders
        GROUP BY 1, 2
        HAVING COUNT(1) >= 3
    )
SELECT DISTINCT p1.product_id
FROM
    P AS p1
    JOIN P AS p2 ON p1.y = p2.y - 1 AND p1.product_id = p2.product_id;

评论