hive如何实现查询n天内销售值是连续增长的

Question

表

code   amount date
1001    100   2023-01-10
1001    101   2023-01-09
1001    99   2023-01-08
1002    110   2023-01-10
1002    109   2023-01-09
1002    102   2023-01-08
.
.
.

通过hive怎么获取到连续n天是增长状态的编码，比如我查询2023-01-10这天的数据 也就是从2023-01-10这天往前数三天，看这三天的数据中amount是否连续增长，表中1001就不是，1002则符合。

如果连续天数是固定的比如三天 我勉强能写出来，但是要是非固定的或者非常多的情况下就不好实现了，各位能否给提供下思路
我现在写了一个三天的，但是如果是30天就显得代码非常臃肿

```bash
select b.code,b.`date`,b.dd
from (select a.id,
             a.`date`,
             a.code,
             a.dd,
             rank() over ( partition by code order by `date` desc ) as rank_alias
      from (select id,
                   code,
                   `date`,
                   amount,
                   3 as                                                                   dd,
                   nvl(lead(amount, 1) over (partition by code order by `date` desc ), 0) l1amount,
                   nvl(lead(amount, 2) over (partition by code order by `date` desc ), 0) l2amount
            from kd_stock_history_info
            where day <= '2023-07-24'
              and day > date_add('2023-07-24', -10)) a
      where a.l1amount > a.amount
        and a.l2amount > a.l1amount
      order by a.code) b
where b.rank_alias = 1;

```

Answer 1

你可以看看这个:

hive面试题--连续性问题_hive求连续两条_Xzchen555的博客-CSDN博客 背景面试和工作中经常遇到，用sql/hive求出连续N天登录或者连续N天销售额破万的记录等。数据准备--新建表与导入数据use test;create table if not exists log_info(uid string,log_date string)row format delimited fields terminated by ',';insert into table log_info values('A','20210901'), _hive求连续两条 https://blog.csdn.net/cc_jjj/article/details/121096571