mysql - 找出每年每个学期按美元计销量最差和最好的产品

Question

我将表 sales_by_year_semester 定义为

CREATE TABLE `sales_by_year_semester` (
    `order_year` INT(4) NOT NULL,
    `semester` TINYINT(1),
    `year_semester` VARCHAR(6),
    `productCode` VARCHAR(10),
    `total_sales_in_usd` FLOAT
) ENGINE=InnoDB;

它有以下数据:

| order_year | semester | year_semester | productCode | total_sales_in_usd |
|------------|----------|---------------|-------------|--------------------|
| 2019       | 1        | 2019-1        | S32_2206    | 2025.16            |
| 2019       | 1        | 2019-1        | S24_2972    | 2285.76            |
| 2019       | 1        | 2019-1        | S18_3232    | 26052.88           |
| 2019       | 2        | 2019-2        | S24_1937    | 6770.34            |
| 2019       | 2        | 2019-2        | S24_2840    | 7159.86            |
| 2019       | 2        | 2019-2        | S12_1099    | 44466.99           |
| 2019       | 2        | 2019-2        | S10_1949    | 48878.16           |
| 2019       | 2        | 2019-2        | S18_3232    | 77427.42           |
| 2020       | 1        | 2020-1        | S24_1937    | 3903.19            |
| 2020       | 1        | 2020-1        | S24_2840    | 4470.39            |
| 2020       | 1        | 2020-1        | S10_4698    | 37297.31           |
| 2020       | 1        | 2020-1        | S12_1108    | 39203.54           |
| 2020       | 1        | 2020-1        | S18_3232    | 43418.87           |
| 2020       | 2        | 2020-2        | S24_3969    | 7907.94            |
| 2020       | 2        | 2020-2        | S24_1937    | 8303.47            |
| 2020       | 2        | 2020-2        | S12_1108    | 66283.92           |
| 2020       | 2        | 2020-2        | S18_3232    | 76962.53           |
| 2021       | 1        | 2021-1        | S24_3969    | 4235.98            |
| 2021       | 1        | 2021-1        | S24_1937    | 5660.64            |
| 2021       | 1        | 2021-1        | S10_4698    | 34856.12           |
| 2021       | 1        | 2021-1        | S18_3232    | 52978.28           |

如何获取美元销售额最少/最多的产品，它是每个 year_semester 的 productCode，格式如下:

| order_year | semester | year_semester | productCode_least_sales | least_sales_in_usd | productCode_most_sales | most_sales_in_usd |
|------------|----------|---------------|-------------------------|--------------------|------------------------|-------------------|
| 2019       | 1        | 2019-1        | S32_2206                | 2025.16            | S18_3232               | 26052.88          |
| 2019       | 2        | 2019-2        | S24_1937                | 6770.34            | S18_3232               | 77427.42          |
| 2020       | 1        | 2020-1        | S24_1937                | 3903.19            | S18_3232               | 43418.87          |
| 2020       | 2        | 2020-2        | S24_3969                | 7907.94            | S18_3232               | 76962.53          |
| 2021       | 1        | 2021-1        | S24_3969                | 4235.98            | S18_3232               | 52978.28          |

我尝试做的是使用 CASE WHEN 来选择 MIN(total_sales_in_usd), MAX(total_sales_in_usd) 但我无法获得每个值对应的 productCode。

SELECT 
    order_year,
    semester,
    year_semester, 
    CASE WHEN 
        MIN(DISTINCT total_sales_in_usd) THEN productCode END 
        as productCode_least_sales,
    MIN(total_sales_in_usd),
    CASE WHEN 
        MAX(DISTINCT total_sales_in_usd) THEN productCode END as productCode_most_sales,
    MAX(total_sales_in_usd)
    FROM
        sales_by_year_semester 
    GROUP BY 
        year_semester ;

Answer 1

我喜欢使用 ROW_NUMBER 来解决这类问题。假设您使用的是 MySQL 8+，我们可以尝试:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY year_semester ORDER BY total_sales_in_usd) rn_min,
        ROW_NUMBER() OVER (PARTITION BY year_semester ORDER BY total_sales_in_usd DESC) rn_max
    FROM sales_by_year_semester
)

SELECT
    order_year,
    semester,
    year_semester,
    MAX(CASE WHEN rn_min = 1 THEN productCode END) AS productCode_least_sales,
    MAX(CASE WHEN rn_min = 1 THEN total_sales_in_usd END) AS least_sales_in_usd,
    MAX(CASE WHEN rn_max = 1 THEN productCode END) AS productCode_most_sales,
    MAX(CASE WHEN rn_max = 1 THEN total_sales_in_usd END) AS most_sales_in_usd
FROM cte
GROUP BY
    order_year,
    semester,
    year_semester
ORDER BY
    order_year,
    semester,
    year_semester;