柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) 面試經(jīng)典sql題

http://yzkb.51969.com/

假設(shè)有兩張表，表1為份額表，表2為行業(yè)表。份額表與行業(yè)表依靠產(chǎn)品id相關(guān)聯(lián)?，F(xiàn)在需要查詢表2中行業(yè)分類字段為1的所有行業(yè)的占比變動(dòng)情況，占比變動(dòng)計(jì)算公式為：最后一天（假設(shè)為20240531）的行業(yè)占比減去第一天（假設(shè)為20240501）的行業(yè)占比。其中，某一天的行業(yè)占比為：這一天該行業(yè)的所有份額的占比之和。? 理論上所有行業(yè)的行業(yè)占比之和為100%。

利用窗口函數(shù)求解即可，熟能生巧。

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;

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫(kù) 面試經(jīng)典sql題

http://yzkb.51969.com/

精彩鏈接