mfy学习笔记

创建表并导入数据

CREATE TABLE sales (
    id INT,
    salesperson STRING,
    region STRING,
    sales_amount INT,
    sale_date DATE
);

INSERT INTO sales (id, salesperson, region, sales_amount, sale_date)
VALUES
(1, 'Alice', 'North', 1000, '2023-01-01'),
(2, 'Bob', 'South', 1500, '2023-01-02'),
(3, 'Alice', 'North', 2000, '2023-01-03'),
(4, 'Charlie', 'East', 1200, '2023-01-04'),
(5, 'Bob', 'South', 1800, '2023-01-05'),
(6, 'Alice', 'North', 2500, '2023-01-06'),
(7, 'Charlie', 'East', 1300, '2023-01-07'),
(8, 'Bob', 'South', 2200, '2023-01-08'),
(9, 'Alice', 'North', 3000, '2023-01-09'),
(10, 'Charlie', 'East', 1400, '2023-01-10');

示例数据表:sales

idsalespersonregionsales_amountsale_date
1AliceNorth10002023-01-01
2BobSouth15002023-01-02
3AliceNorth20002023-01-03
4CharlieEast12002023-01-04
5BobSouth18002023-01-05
6AliceNorth25002023-01-06
7CharlieEast13002023-01-07
8BobSouth22002023-01-08
9AliceNorth30002023-01-09
10CharlieEast14002023-01-10
---

### 1. `CASE WHEN` 示例
根据销售额给销售人员分类:

```sql
SELECT 
    salesperson,
    sales_amount,
    CASE 
        WHEN sales_amount < 1500 THEN 'Low'
        WHEN sales_amount BETWEEN 1500 AND 2500 THEN 'Medium'
        ELSE 'High'
    END AS sales_category
FROM 
    sales;
运行结果:
salespersonsales_amountsales_category
Alice1000Low
Bob1500Medium
Alice2000Medium
Charlie1200Low
Bob1800Medium
Alice2500Medium
Charlie1300Low
Bob2200Medium
Alice3000High
Charlie1400Low

2. SUM(CASE WHEN) 示例

计算每个区域的销售总额:

SELECT 
    region,
    SUM(CASE WHEN salesperson = 'Alice' THEN sales_amount ELSE 0 END) AS alice_sales,
    SUM(CASE WHEN salesperson = 'Bob' THEN sales_amount ELSE 0 END) AS bob_sales,
    SUM(CASE WHEN salesperson = 'Charlie' THEN sales_amount ELSE 0 END) AS charlie_sales
FROM 
    sales
GROUP BY 
    region;
运行结果:
regionalice_salesbob_salescharlie_sales
North850000
South055000
East003900

3. RANK() 示例

根据销售额对销售人员进行排名:

SELECT 
    salesperson,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;
运行结果:
salespersonsales_amountsales_rank
Alice30001
Bob22002
Alice25003
Bob18004
Alice20005
Bob15006
Charlie14007
Charlie13008
Alice10009
Charlie120010

4. ROW_NUMBER() 示例

为每个销售人员的销售额分配一个唯一的行号:

SELECT 
    salesperson,
    sales_amount,
    ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY sales_amount DESC) AS row_num
FROM 
    sales;
运行结果:
salespersonsales_amountrow_num
Alice30001
Alice25002
Alice20003
Alice10004
Bob22001
Bob18002
Bob15003
Charlie14001
Charlie13002
Charlie12003

5. DENSE_RANK() 示例

根据销售额对销售人员进行密集排名(不会跳过排名):

SELECT 
    salesperson,
    sales_amount,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank
FROM 
    sales;
运行结果:
salespersonsales_amountdense_rank
Alice30001
Bob22002
Alice25003
Bob18004
Alice20005
Bob15006
Charlie14007
Charlie13008
Alice10009
Charlie120010

总结

  • CASE WHEN:用于条件判断,生成新的列。
  • SUM(CASE WHEN):用于按条件汇总数据。
  • RANK():用于排名,允许并列排名并跳过后续名次。
  • ROW_NUMBER():用于生成唯一的行号,即使数据相同也会分配不同行号。
  • DENSE_RANK():用于密集排名,允许并列排名但不跳过后续名次。

通过这些示例和运行结果,可以清晰地展示每个函数的作用和用法!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值