sql-window函数的稠密秩()

wvyml7n5 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(288)

我在hive中存储了一个数据集，如下面的数据集，称之为df：

+-----+-----+----------+--------+
| id1 | id2 |   date   | amount |
+-----+-----+----------+--------+
|   1 |   2 | 11-07-17 | 0.93   |
|   2 |   2 | 11-11-17 | 1.94   |
|   2 |   2 | 11-09-17 | 1.90   |
|   1 |   1 | 11-10-17 | 0.33   |
|   2 |   2 | 11-10-17 | 1.93   |
|   1 |   1 | 11-07-17 | 0.25   |
|   1 |   1 | 11-09-17 | 0.33   | 
|   1 |   1 | 11-12-17 | 0.33   |
|   2 |   2 | 11-08-17 | 1.90   |
|   1 |   1 | 11-08-17 | 0.30   |
|   2 |   2 | 11-12-17 | 2.01   |
|   1 |   2 | 11-12-17 | 1.00   |
|   1 |   2 | 11-09-17 | 0.94   |
|   2 |   2 | 11-07-17 | 1.94   |
|   1 |   2 | 11-11-17 | 1.92   |
|   1 |   1 | 11-11-17 | 0.33   |
|   1 |   2 | 11-10-17 | 1.92   |
|   1 |   2 | 11-08-17 | 0.94   |
+-----+-----+----------+--------+

我希望按id1和id2进行分区，然后在id1和id2的每个分组中按日期降序排列，然后在其中排列“amount”，连续几天相同的“amount”将获得相同的排名。我希望看到的排序输出如下所示：

+-----+-----+------------+--------+------+
| id1 | id2 |    date    | amount | rank |
+-----+-----+------------+--------+------+
|   1 |   1 | 2017-11-12 | 0.33   |    1 |
|   1 |   1 | 2017-11-11 | 0.33   |    1 |
|   1 |   1 | 2017-11-10 | 0.33   |    1 |
|   1 |   1 | 2017-11-09 | 0.33   |    1 |
|   1 |   1 | 2017-11-08 | 0.30   |    2 |
|   1 |   1 | 2017-11-07 | 0.25   |    3 |
|   1 |   2 | 2017-11-12 | 1.00   |    1 |
|   1 |   2 | 2017-11-11 | 1.92   |    2 |
|   1 |   2 | 2017-11-10 | 1.92   |    2 |
|   1 |   2 | 2017-11-09 | 0.94   |    3 |
|   1 |   2 | 2017-11-08 | 0.94   |    3 |
|   1 |   2 | 2017-11-07 | 0.93   |    4 |
|   2 |   2 | 2017-11-12 | 2.01   |    1 |
|   2 |   2 | 2017-11-11 | 1.94   |    2 |
|   2 |   2 | 2017-11-10 | 1.93   |    3 |
|   2 |   2 | 2017-11-09 | 1.90   |    4 |
|   2 |   2 | 2017-11-08 | 1.90   |    4 |
|   2 |   2 | 2017-11-07 | 1.94   |    5 |
+-----+-----+------------+--------+------+

我尝试了以下sql查询：

SELECT 
    id1, 
    id2, 
    date, 
    amount,
    dense_rank() OVER (PARTITION BY id1, id2 ORDER BY date DESC) AS rank
FROM
    df
GROUP BY
    id1,
    id2,
    date,
    amount

但是这个查询似乎没有达到我想要的效果，因为我没有收到我想要的输出。
这似乎是一个窗口函数使用密集的排名，分区和秩序是我需要的，但我似乎不能得到它给我的样本输出，我想要的。任何帮助都将不胜感激！谢谢！

sql Hive

来源：https://stackoverflow.com/questions/47277119/sql-window-functions-with-dense-rank

1条答案

按热度按时间

6kkfgxo01#

这是相当棘手的。我觉得你需要 lag() 要查看值的变化位置，然后进行累积求和：

select df.*,
       sum(case when prev_amount = amount then 0 else 1 end) over
           (partition by id1, id2 order by date desc) as rank
from (select df.*,
             lag(amount) over (partition by id1, id2 order by date desc) as prev_amount
      from df
     ) df;

赞(0）回复(0）举报 2021-06-26

我来回答

sql-window函数的稠密秩()

1条答案

相关问题

热门标签

最新问答