导入
Question: 窗口函数
学习如何使用 SQL 窗口函数计算相关数据的累计总计和排名。
表
| 字段 | 类型 |
|---|---|
| CustomerID | int |
| CustomerName | varchar(50) |
| varchar(50) | |
| Phone | varchar(15) |
| Address | varchar(100) |
| LastLogin | date |
| CreatedAt | timestamp |
| CustomerTier | enum('New','Regular','VIP') |
| ReferralID | int |
| LoyaltyPoints | int |
| LastPurchaseDate | date |
| IsChurnRisk | tinyint(1) |
表
| 字段 | 类型 |
|---|---|
| OrderID | int |
| CustomerID | int |
| OrderDate | date |
| TotalAmount | decimal(10,2) |
| ShippedDate | date |
| DeliveryStatus | enum('Pending','Shipped','Delivered','Cancelled') |
| EmployeeID | int |
| CreatedAt | timestamp |
| ExpectedDeliveryDate | date |
| ActualDeliveryDate | date |
| LateDelivery | tinyint(1) |
| PaymentMethod | enum('Credit Card','PayPal','Cash on Delivery') |
| ReturnCount | int |
| FraudRisk | tinyint(1) |
表
| CustomerID | CustomerName | OrderID | OrderDate | TotalAmount | RunningTotal | OrderRank |
|---|---|---|---|---|---|---|
| 1 | John Doe | 1 | 2025-03-01 | 150.00 | 150.00 | 1 |
| 1 | John Doe | 2 | 2025-03-05 | 220.00 | 370.00 | 2 |
| 1 | John Doe | 51 | 2025-03-12 | 180.00 | 550.00 | 3 |
| 1 | John Doe | 53 | 2025-03-14 | 45.99 | 595.99 | 4 |
| 1 | John Doe | 56 | 2025-03-15 | 50.00 | 725.99 | 5 |
| 1 | John Doe | 55 | 2025-03-15 | 80.00 | 725.99 | 6 |
| 2 | Jane Smith | 3 | 2025-03-03 | 320.00 | 320.00 | 1 |
| 2 | Jane Smith | 4 | 2025-03-06 | 420.00 | 740.00 | 2 |
| 2 | Jane Smith | 52 | 2025-03-12 | 210.00 | 950.00 | 3 |
| 3 | Alice Johnson | 5 | 2025-02-20 | 80.00 | 80.00 | 1 |
| 3 | Alice Johnson | 6 | 2025-03-01 | 150.00 | 230.00 | 2 |
| 4 | Bob Brown | 7 | 2025-03-05 | 200.00 | 200.00 | 1 |
| 4 | Bob Brown | 8 | 2025-03-10 | 300.00 | 500.00 | 2 |
| 5 | Carol White | 9 | 2025-03-02 | 80.00 | 80.00 | 1 |
| 5 | Carol White | 10 | 2025-03-05 | 95.00 | 175.00 | 2 |
| 6 | David Clark | 11 | 2025-03-06 | 120.00 | 120.00 | 1 |
| 6 | David Clark | 12 | 2025-03-09 | 180.00 | 300.00 | 2 |
| 7 | Emily Davis | 13 | 2025-03-01 | 250.00 | 250.00 | 1 |
| 7 | Emily Davis | 14 | 2025-03-07 | 320.00 | 570.00 | 2 |
| 8 | Frank Green | 15 | 2025-03-02 | 60.00 | 60.00 | 1 |
| 8 | Frank Green | 16 | 2025-03-05 | 70.00 | 130.00 | 2 |
| 9 | Grace Hill | 17 | 2025-03-01 | 190.00 | 190.00 | 1 |
| 9 | Grace Hill | 18 | 2025-03-11 | 220.00 | 410.00 | 2 |
| 10 | Henry Ford | 19 | 2025-03-02 | 350.00 | 350.00 | 1 |
| 10 | Henry Ford | 20 | 2025-03-05 | 280.00 | 630.00 | 2 |
| 11 | Ivy King | 21 | 2025-02-25 | 90.00 | 90.00 | 1 |
| 11 | Ivy King | 22 | 2025-03-01 | 100.00 | 190.00 | 2 |
| 12 | Jake Long | 23 | 2025-03-02 | 140.00 | 140.00 | 1 |
| 12 | Jake Long | 24 | 2025-03-07 | 60.00 | 200.00 | 2 |
| 13 | Kelly Martin | 25 | 2025-03-01 | 420.00 | 420.00 | 1 |
| 13 | Kelly Martin | 26 | 2025-03-10 | 490.00 | 910.00 | 2 |
| 14 | Larry Nelson | 27 | 2025-03-03 | 150.00 | 150.00 | 1 |
| 14 | Larry Nelson | 28 | 2025-03-07 | 230.00 | 380.00 | 2 |
| 15 | Mia Owens | 29 | 2025-03-02 | 60.00 | 60.00 | 1 |
| 15 | Mia Owens | 30 | 2025-03-06 | 130.00 | 190.00 | 2 |
| 16 | Nick Perez | 31 | 2025-03-01 | 75.00 | 75.00 | 1 |
| 16 | Nick Perez | 32 | 2025-03-10 | 120.00 | 195.00 | 2 |
| 17 | Olivia Queen | 33 | 2025-03-02 | 220.00 | 220.00 | 1 |
| 17 | Olivia Queen | 34 | 2025-03-08 | 300.00 | 520.00 | 2 |
| 18 | Peter Roberts | 35 | 2025-03-05 | 180.00 | 180.00 | 1 |
| 18 | Peter Roberts | 36 | 2025-03-07 | 250.00 | 430.00 | 2 |
| 19 | Quincy Stone | 37 | 2025-03-01 | 110.00 | 110.00 | 1 |
| 19 | Quincy Stone | 38 | 2025-03-06 | 190.00 | 300.00 | 2 |
| 20 | Rachel Turner | 39 | 2025-03-02 | 210.00 | 210.00 | 1 |
| 20 | Rachel Turner | 40 | 2025-03-10 | 300.00 | 510.00 | 2 |
| 21 | Sam Underwood | 41 | 2025-02-28 | 120.00 | 120.00 | 1 |
| 21 | Sam Underwood | 42 | 2025-03-03 | 180.00 | 300.00 | 2 |
| 22 | Tina Van | 43 | 2025-03-01 | 400.00 | 400.00 | 1 |
| 22 | Tina Van | 44 | 2025-03-06 | 450.00 | 850.00 | 2 |
| 23 | Uma White | 45 | 2025-03-05 | 100.00 | 100.00 | 1 |
| 23 | Uma White | 46 | 2025-03-07 | 130.00 | 230.00 | 2 |
| 24 | Victor X | 47 | 2025-03-01 | 160.00 | 160.00 | 1 |
| 24 | Victor X | 48 | 2025-03-06 | 230.00 | 390.00 | 2 |
| 25 | Wendy Young | 49 | 2025-03-02 | 280.00 | 280.00 | 1 |
| 25 | Wendy Young | 50 | 2025-03-10 | 350.00 | 630.00 | 2 |
| 30 | Noah Vale | 54 | 2025-03-10 | 100.00 | 100.00 | 1 |
1/* Write your query below */
所需技能如下：
JOIN的概念- 窗口函数的知识
- 使用
OVER()与PARTITION BY - 聚合函数的知识
所需技能如下：
JOIN的概念- 窗口函数的知识
- 使用
OVER()与PARTITION BY - 聚合函数的知识
No configuration found for ID: 26b99e723d38ea14552c370f466ba413344a2cadff1b24e0efea2f79d4ec6a92