时间戳

首页 > 解决方案 > 按分区标记前面的行

sql - 按分区标记前面的行

问题描述

我需要标记值不同并按日期排序的前行。我曾尝试将 row_number() 与分区一起使用，但我没有返回我的预期结果。

桌子：

Product ID  Price   validfrom   validto
123         0.9    27/07/2016  10/08/2016
123         0.9    11/08/2016  24/04/2017
123         0.75   25/04/2017  23/10/2017
123         0.75   24/10/2017  24/10/2017
123         0.8    25/10/2017  02/01/2018
123         0.9    03/01/2018  21/03/2019
123         0.9    22/03/2019  17/10/2019
123         0.9    18/10/2019  11/11/2019

预期成绩：

Product ID  Price   validfrom   validto    rownum
123         0.9    27/07/2016  10/08/2016   4
123         0.9    11/08/2016  24/04/2017   4
123         0.75   25/04/2017  23/10/2017   3
123         0.75   24/10/2017  24/10/2017   3
123         0.8    25/10/2017  02/01/2018   2
123         0.9    03/01/2018  21/03/2019   1
123         0.9    22/03/2019  17/10/2019   1
123         0.9    18/10/2019  11/11/2019   1

当前查询：

select *,row_number() over (partition by Product ID,Price order by validfrom desc) as rownum
from table

当前查询结果（错误）：

Product ID  Price   validfrom   validto    rownum
123         0.9    27/07/2016  10/08/2016   5
123         0.9    11/08/2016  24/04/2017   4
123         0.75   25/04/2017  23/10/2017   1
123         0.75   24/10/2017  24/10/2017   1
123         0.8    25/10/2017  02/01/2018   1
123         0.9    03/01/2018  21/03/2019   3
123         0.9    22/03/2019  17/10/2019   2
123         0.9    18/10/2019  11/11/2019   1

标签： sqlimpala

解决方案

看看哪里有变化，总结一下

select t.*,
       sum(case when next_price is null or next_price <> price then 1 else 0 end) over (partition by product_id order by validfrom desc) as rownum
from (select t.*,
             lead(price) over (partition by product_id order by validfrom) as next_price
      from t
     ) t;

推荐阅读