value及其在配置单元中的行

sdnqo3pr 于 2021-06-25 发布在 Hive

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

我必须在2小时的滑动日期窗口中选择minvalue及其相应的日期值。例如

Create table stock(time string, cost float);

Insert into stock values("1990-01-01 8:00 AM",4.5);
Insert into stock values("1990-01-01 9:00 AM",3.2);
Insert into stock values("1990-01-01 10:00 AM",3.1);
Insert into stock values("1990-01-01 11:00 AM",5.5);
Insert into stock values("1990-01-02 8:00 AM",5.1);
Insert into stock values("1990-01-02 9:00 AM",2.2);
Insert into stock values("1990-01-02 10:00 AM",1.5);
Insert into stock values("1990-01-02 11:00 AM",6.5);
Insert into stock values("1990-01-03 8:00 AM",8.1);
Insert into stock values("1990-01-03 9:00 AM",3.2);
Insert into stock values("1990-01-03 10:00 AM",2.5);
Insert into stock values("1990-01-03 11:00 AM",4.5);

为此，我可以编写这样的查询

select min(cost) over(order by unix_timestamp(time) range between current row and 7200 following)
from stock

因此，从当前行向前看2小时（7200秒）并选取最小值，因此对于第一行，最小值将是3.1，位于上午10:00的第三行。通过此查询，我得到了正确的最小值，但我还需要最小值的相应日期值，在这种情况下，我需要“1990-01-01 10:00 am”。我怎么选这个？
谢谢，拉吉

sql Hive hiveql bigdata

来源：https://stackoverflow.com/questions/59111356/picking-minvalue-and-its-row-in-hive

1条答案

按热度按时间

wpcxdonn1#

我认为这是个难题。一种方法是 join 要查找值：

select s.*
from (select s.*,
             min(cost) over (order by unix_timestamp(time) range between current row and 7200 following) as min_cost,
      from stock s
     ) s join
     stock smin
     on smin.cost = min_cost and
        unix_timestamp(smin.time) >= unix_timestamp(s.time) and
        unix_timestamp(smin.time) < unix_timestamp(s.time) + 7200

这种方法的缺点是可能会产生重复。如果这是一个问题：

select s.*
from (select s.*, smin.time as min_time,
             row_number() over (partition by s.time order by smin.time) as seqnum
      from (select s.*,
                   min(cost) over (order by unix_timestamp(time) range between current row and 7200 following) as min_cost,
            from stock s
           ) s join
           stock smin
           on smin.cost = min_cost and
              unix_timestamp(smin.time) >= unix_timestamp(s.time) and
              unix_timestamp(smin.time) < unix_timestamp(s.time) + 7200
       ) s
where seqnum = 1;

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

我来回答

value及其在配置单元中的行

1条答案

相关问题

热门标签

最新问答