恒生电子笔试交流 20240926

9.26 晚的恒生电子笔试，被数据库实操干懵了。

这里记录一下自己的算法题题解和数据库实操的 SQL 语句，SQL 语句是事后在 AI 帮助下写出来的，案例运行结果对了，不知道是否完全正确，希望大家指正。若帖子侵权，可联系删除。

算法题 1（AC）：直接输出即可，注意要用 long，因为题目说数字可以到达 10^9，2 个 10^9 相乘会超过 int 的范围

class Main {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        long c = scanner.nextLong();
        System.out.println((c / 2) * (c - c / 2));
    }
}

算法题 2（AC）：根据题意，S 表示一个连续子数组中所有相邻元素的最大公因数之和，既然是连续子数组，肯定想到滑动窗口，看大家和我一样很多，首次提交时只过 90%，下面 2 个示例可以说明原因（吐槽一下，题目真挺拗口的，难点在于看懂题目而不是写出来，求两数的最大公因数用辗转相除法这一点需要一点数学基础）

输入：3 5
     6 3 7
输出：0
说明：k = 5，比第 2 个元素 3 大，存在子数组 6 [3] 7，但是区间大小为 1，根据定义 S = 0

输入：3 2
     6 3 7
输出：-1
说明：要求的 k 比任何一个数字都小，不存在窗口

class Main {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        int n = scanner.nextInt();
        long k = scanner.nextLong();
        long[] nums = new long[n];
        for (int i = 0; i < n; i++) {
            nums[i] = scanner.nextLong();
        }

        // 提前求出相邻两个数的最大公因数
        long[] gcds = new long[n - 1];
        for (int i = 0; i < n - 1; i++) {
            gcds[i] = gcd(nums[i], nums[i + 1]);
        }

        // 滑动窗口
        long maxS = -1;
        long windowS = 0;
        long windowSum = nums[0];
        for(int leftIdx = 0, rightIdx = 1; rightIdx < nums.length; rightIdx++) {
            windowSum += nums[rightIdx];
            windowS += gcds[rightIdx - 1];

            while (leftIdx < rightIdx && windowSum > k) {
                windowSum -= nums[leftIdx];
                windowS -= gcds[leftIdx];
                leftIdx++;
            }

            // 要区分 “窗口大小为 1 时可以” 和 “窗口大小连为 1” 都不可以的情况
            // 只过 90% 就是因为这一句缺了
            if (leftIdx == rightIdx && nums[leftIdx] > k) {
                continue;
            }

            maxS = Math.max(maxS, windowS);
        }

        System.out.println(maxS);
    }

    // 辗转相除法求最大公因数
    private static long gcd(long num1, long num2) {
        if (num2 == 0) {
            return num1;
        } else {
            return gcd(num2, num1 % num2);
        }
    }
}

数据库实操（事后做出）：

核心思想是，这些不同的字段关系区别太大，将多个子查询的结果按 Product_Category 这一字段 JOIN 起来，难点是 Top_Sales_Region 字段

DROP TABLE IF EXISTS products_info;
DROP TABLE IF EXISTS sales_info;
DROP TABLE IF EXISTS customer_satisfaction_info;

-- 创建 products_info 表
CREATE TABLE products_info (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    product_category VARCHAR(20)
);

-- 创建 sales_info 表
CREATE TABLE sales_info (
    sale_id INT PRIMARY KEY,
    product_id INT,
    region VARCHAR(20),
    sale_amount DECIMAL(10, 2)
);

-- 创建 customer_satisfaction_info 表
CREATE TABLE customer_satisfaction_info (
    satisfaction_id INT PRIMARY KEY,
    product_id INT,
    satisfaction_score INT
);

-- 向 products_info 表插入数据
INSERT INTO products_info (product_id, product_name, product_category)
VALUES (1, '产品 A', '股票'),
       (2, '产品 B', '基金'),
       (3, '产品 C', '债券'),
       (4, '产品 D', '股票'),
       (5, '产品 E', '基金'),
       (6, '产品 F', '债券');

-- 向 sales_info 表插入数据
INSERT INTO sales_info (sale_id, product_id, region, sale_amount)
VALUES (1, 1, '华北', 50000.00),
       (2, 1, '华东', 80000.00),
       (3, 2, '华南', 30000.00),
       (4, 2, '华东', 60000.00),
       (5, 3, '华南', 80000.00),
       (6, 3, '华北', 90000.00),
       (7, 4, '华北', 90000.00),
       (8, 5, '华北', 90000.00),
       (9, 6, '华北', 90000.00);

-- 向 customer_satisfaction_info 表插入数据
INSERT INTO customer_satisfaction_info (satisfaction_id, product_id, satisfaction_score)
VALUES (1, 1, 8),
       (2, 2, 7),
       (3, 3, 9),
       (4, 4, 6),
       (5, 5, 7),
       (6, 6, 8);
     
select * from products_info;
select * from sales_info;
select * from customer_satisfaction_info;

-- 自己的解答, 请各位指正
SELECT
	a.Product_Category,
	a.Total_Sales,
	b.Number_of_Different_Product_IDs,
	a.Average_Satisfaction_Score,
	c.Top_Sales_Region
-- 子表 a: 包含 Product_Category, Total_Sales 和 Average_Satisfaction_Score 共 3 个字段
FROM (
	SELECT
		p.product_category AS "Product_Category",
		ROUND(SUM(s.sale_amount), 2) AS "Total_Sales",
		ROUND(AVG(c.satisfaction_score), 2) AS "Average_Satisfaction_Score"
	FROM products_info AS p
	JOIN sales_info AS s
	ON p.product_id = s.product_id
	JOIN customer_satisfaction_info AS c
	ON p.product_id = c.product_id
	GROUP BY p.product_category
) AS a
-- 子表 b: 包含 Number_of_Different_Product_IDs 字段
JOIN (
	SELECT 
		p.product_category AS "Product_Category",
		COUNT(p.product_id) AS "Number_of_Different_Product_IDs"
	FROM products_info AS p
	GROUP BY p.product_category
) AS b ON a.Product_Category = b.Product_Category
-- 子表 c (最难): 获取 Top_Sales_Region 字段
JOIN (
	SELECT
		product_category AS "Product_Category",
		region AS "Top_Sales_Region"
	FROM (
		SELECT
			p.product_category,
			s.region,
			SUM(s.sale_amount) AS sum_up,
			-- 在根据 p.product_category, s.region 分组后, 按 sum_up 列降序计算排序序号
			RANK() OVER (
				PARTITION BY p.product_category
				ORDER BY SUM(s.sale_amount) DESC
			) as rank_sum_up
		FROM products_info AS p
		JOIN sales_info AS s
		ON p.product_id = s.product_id
		GROUP BY p.product_category, s.region
	) AS c1
	-- 序号为 1 的就是销量最大的记录, 选取地区即为所需 Top_Sales_Region 字段
	where rank_sum_up = 1
) AS c ON a.Product_Category = c.Product_Category;