MYSQL开窗判断连续月份

hive开窗判断连续月份

select custkey,name,
months,
add_mont,
memory,
memory1
 from 
(
select a1.custkey,a2.name,a1.orderdate as months,sum(a1.totalprice) as memory,
lead(a1.orderdate)over(partition by custkey) as add_mont,
lead(sum(a1.totalprice))over(partition by custkey) as memory1
from orders as a1 join customer as a2
on
a1.custkey = a2.custkey group by a1.custkey,a2.name,substr(a1.orderdate,1,7) order by a1.custkey ,a1.ORDERDATE
) as fbb 
where 
memory1-0>memory-0 
and
(
year(months)=year(add_mont) and month(add_mont)=month(months)+1 
or 
year(add_mont)=year(months)+1 and month(add_mont)=1 and month(months)=12)
UNION all
select custkey,name,
months,
add_mont,
memory,
memory1
 from 
(
select a1.custkey,a2.name,a1.orderdate as months,sum(a1.totalprice) as memory,
lag(a1.orderdate)over(partition by custkey) as add_mont,
lag(sum(a1.totalprice))over(partition by custkey) as memory1
from orders as a1 join customer as a2
on
a1.custkey = a2.custkey group by a1.custkey,a2.name,substr(a1.orderdate,1,7) order by a1.custkey ,a1.ORDERDATE
) as fbb 
where 
memory1-0>memory-0 
and
(
year(months)=year(add_mont) and month(add_mont)=month(months)+1 
or 
year(add_mont)=year(months)+1 and month(add_mont)=1 and month(months)=12)

纯mysql判断连续月份

select DISTINCT fbb1.custkey,
concat(
substr(fbb1.orderdate1,1,7),
'_',
substr(fbb2.orderdate1,1,7)
),
fbb1.memory
from(
select a1.custkey,a1.ORDERDATE as orderdate1 ,sum(a1.totalprice) as memory from orders as a1 join customer as a2 on a1.custkey = a2.custkey
group by a1.CUSTKEY,year(a1.ORDERDATE),month(a1.ORDERDATE)
)as fbb1
 join 
(
select a1.custkey,a1.ORDERDATE as orderdate1 ,sum(a1.totalprice) as memory from orders as a1 join customer as a2 on a1.custkey = a2.custkey
group by a1.CUSTKEY,year(a1.ORDERDATE),month(a1.ORDERDATE)
)as fbb2
on
fbb1.custkey=fbb2.custkey 
and substr(ADDDATE(fbb1.orderdate1,INTERVAL 1 month),1,7) = substr(fbb2.orderdate1,1,7)
and fbb1.memory<fbb2.memory