Sql-对数据进行特殊分组(HIVE、Oracle)
2017-10-17 本文已影响0人
咖啡不解酒
- 昨天收到一个朋友的求助，一个特殊的分组需求，来源数据和目标结果数据如下(上面是来源数据，下面是展示的数据)。
| name | score | flag |
|---|---|---|
| XX1 | 95 | 0 |
| XX1 | 80 | 0 |
| XX1 | 80 | 1 |
| XX1 | 70 | 0 |
| XX1 | 90 | 1 |
| name | score |
|---|---|
| XX1 | 255 |
| XX1 | 160 |
- 其实就是按照flag列进行分组，不过是按照flag=1来进行区间分组。与我们常见的0为一组，1位一组不一样，从上面的结果可以看到是按flag=1位界限，把数据前三行分为一组、后两行分为一组。例如下表格，flag=1把这列截成了4部分，也就是可以按照flag分为四组：
| flag | groupId |
|---|---|
| 0 | 1 |
| 0 | 1 |
| 0 | 1 |
| 0 | 1 |
| 1 | 1 |
| 1 | 2 |
| 0 | 3 |
| 0 | 3 |
| 1 | 3 |
| 0 | 4 |
| 1 | 4 |
- 乍一看好像还是很简单的，但是仔细一想又不是那么容易。由于当时没有sql环境，就在excel里面画了画，思路如下(sql就不提供了，各位小伙伴有兴趣的话可以去写写看):对over这种不理解的小伙伴们要去补充一下分析函数的知识了。
| flag | lead | sum_1 | sum_2 | groupId |
|---|---|---|---|---|
| 0 | 0 | 0 | 4 | 4 |
| 0 | 0 | 0 | 4 | 4 |
| 0 | 0 | 0 | 4 | 4 |
| 0 | 0 | 0 | 4 | 4 |
| 1 | 0 | 0 | 4 | 4 |
| 1 | 1 | 1 | 4 | 3 |
| 0 | 1 | 2 | 4 | 2 |
| 0 | 0 | 2 | 4 | 2 |
| 1 | 0 | 2 | 4 | 2 |
| 0 | 1 | 3 | 4 | 1 |
| 1 | 0 | 3 | 4 | 1 |
| columns | comments |
|---|---|
| flag | 数据源里面的flag字段 |
| lead | flag字段往下移一位(lead()over()) |
| sum_1 | 对lead的值进行累加(sum()over(order by)) |
| sum_2 | 对flag的值进行sum(sum()over()) |
| groupId | sum_2-sum1 |
