在BigQuery中，我有一个如下所示的表:
| Order | Value |
|---|---|
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 7 | A |
| 8 | A |
| 10 | A |
| 11 | C |
Order列在增加，但并不是在每种情况下都是连续的.Value列可以具有返回值.我需要通过值和值的出现次数的组合为值创建组. 因此，基本上我需要一个新的专栏(组)，如下所示:
| Order | Value | Group |
|---|---|---|
| 1 | A | 1 |
| 2 | A | 1 |
| 3 | A | 1 |
| 4 | B | 2 |
| 5 | B | 2 |
| 7 | A | 3 |
| 8 | A | 3 |
| 10 | A | 3 |
| 11 | C | 4 |
我try 在不同的变体中使用Dense_Rank()，这是我得到的最好的结果，但这不是我所需要的.
| Order | Value | dense_rank() over(order by Value) | Right solution |
|---|---|---|---|
| 1 | A | 1 | 1 |
| 2 | A | 1 | 1 |
| 3 | A | 1 | 1 |
| 4 | B | 2 | 2 |
| 5 | B | 2 | 2 |
| 7 | A | 1 | 3 |
| 8 | A | 1 | 3 |
| 10 | A | 1 | 3 |
| 11 | C | 4 | 4 |
测试数据代码:
with test_data as(
select 1 as _order, 'A' as value
union all
select 2 as _order, 'A' as value
union all
select 3 as _order, 'A' as value
union all
select 4 as _order, 'B' as value
union all
select 5 as _order, 'B' as value
union all
select 7 as _order, 'A' as value
union all
select 8 as _order, 'A' as value
union all
select 10 as _order, 'A' as value
union all
select 11 as _order, 'C' as value
)
select _order,
value,
dense_rank() over(order by value) value_order,
from test_data
order by _order