面试经典sql题

假设有两张表，表1为份额表，表2为行业表。份额表与行业表依靠产品id相关联。现在需要查询表2中行业分类字段为1的所有行业的占比变动情况，占比变动计算公式为：最后一天（假设为20240531）的行业占比减去第一天（假设为20240501）的行业占比。其中，某一天的行业占比为：这一天该行业的所有份额的占比之和。  理论上所有行业的行业占比之和为100%。

利用窗口函数求解即可，熟能生巧。

SELECT
2  industry_category,
3  SUM(CASE WHEN date = '2024-05-01' THEN share ELSE 0 END) OVER (PARTITION BY industry_category) AS first_day_share,
4  SUM(CASE WHEN date = '2024-05-31' THEN share ELSE 0 END) OVER (PARTITION BY industry_category) AS last_day_share,
5  (
6    SUM(CASE WHEN date = '2024-05-31' THEN share ELSE 0 END) -
7    SUM(CASE WHEN date = '2024-05-01' THEN share ELSE 0 END)
8  ) / NULLIF(SUM(CASE WHEN date = '2024-05-01' THEN share ELSE 0 END), 0) * 100 AS percentage_change
9FROM (
10  SELECT
11    t2.industry_category,
12    t1.date,
13    t1.share
14  FROM
15    table1 t1
16  JOIN
17    table2 t2 ON t1.product_id = t2.product_id
18  WHERE
19    t2.industry_category = 1 AND
20    t1.date IN ('2024-05-01', '2024-05-31')
21) AS filtered_shares
22WHERE
23  industry_category = 1;