Hive sql rank dense_rank row_number具体例子加结果

假设有一个名为`sales`的表，包含以下字段：`region`、`date`、`product`、`sales_amount`。现在需要对每个区域、每个日期、每个产品的销售额进行排名，并使用`rank`、`dense_rank`和`row_number`三种函数进行排名。

以下是具体实现及结果：

1. 使用`rank`函数进行排名：

SELECT region, date, product, sales_amount,
       RANK() OVER (PARTITION BY region, date, product ORDER BY sales_amount DESC) AS rank
FROM sales;

结果：

| region | date | product | sales_amount | rank |
|--------|------------|---------|--------------|------|
| A | 2020-01-01 | apple | 1000 | 1 |
| A | 2020-01-01 | apple | 900 | 2 |
| A | 2020-01-01 | banana | 800 | 1 |
| A | 2020-01-01 | banana | 600 | 2 |
| A | 2020-01-01 | banana | 500 | 3 |
| B | 2020-01-01 | apple | 1200 | 1 |
| B | 2020-01-01 | apple | 1100 | 2 |
| B | 2020-01-01 | banana | 700 | 1 |
| B | 2020-01-01 | banana | 600 | 2 |

2. 使用`dense_rank`函数进行排名：

SELECT region, date, product, sales_amount,
       DENSE_RANK() OVER (PARTITION BY region, date, product ORDER BY sales_amount DESC) AS dense_rank
FROM sales;

结果：

| region | date | product | sales_amount | dense_rank |
|--------|------------|---------|--------------|------------|
| A | 2020-01-01 | apple | 1000 | 1 |
| A | 2020-01-01 | apple | 900 | 2 |
| A | 2020-01-01 | banana | 800 | 1 |
| A | 2020-01-01 | banana | 600 | 2 |
| A | 2020-01-01 | banana | 500 | 3 |
| B | 2020-01-01 | apple | 1200 | 1 |
| B | 2020-01-01 | apple | 1100 | 2 |
| B | 2020-01-01 | banana | 700 | 1 |
| B | 2020-01-01 | banana | 600 | 2 |

3. 使用`row_number`函数进行排名：

SELECT region, date, product, sales_amount,
       ROW_NUMBER() OVER (PARTITION BY region, date, product ORDER BY sales_amount DESC) AS row_number
FROM sales;

结果：

| region | date | product | sales_amount | row_number |
|--------|------------|---------|--------------|------------|
| A | 2020-01-01 | apple | 1000 | 1 |
| A | 2020-01-01 | apple | 900 | 2 |
| A | 2020-01-01 | banana | 800 | 1 |
| A | 2020-01-01 | banana | 600 | 2 |
| A | 2020-01-01 | banana | 500 | 3 |
| B | 2020-01-01 | apple | 1200 | 1 |
| B | 2020-01-01 | apple | 1100 | 2 |
| B | 2020-01-01 | banana | 700 | 1 |
| B | 2020-01-01 | banana | 600 | 2 |

以上三个例子都是按照每个区域、每个日期、每个产品的销售额进行降序排列，并使用不同的函数进行排名。通过这些函数，我们可以根据不同的业务需求，对数据进行不同的排名处理。需要注意的是，要在`OVER`子句中指定`PARTITION BY`子句，以便对每个分区进行排名。