Sql：计算超过1000的值之间的天数，持续5天 [英] Sql: count days between values over 1000 for 5 days

问题描述

我想选择第一次价值库存中的单元格在-1000以下之前的天数，直到它超过5天：

所以在这个例子中，如果想在值列中获得6，但在value2列中只有2和3

I would like to select the number of days between first time a cell in the "value" coulum is under -1000 until it is above, but only if the number of days is over 5 days:
So in this exaple if would like to get 6 out on the "value colum" but only 2 and 3 in the value2 colum

Date	      Value	             Value2
25-01-2017	600	           600
23-01-2017	-400,00 	-400,00 
20-01-2017	-2300,00 	-2300,00 
19-01-2017	-1200,00 	-1200,00 
18-01-2017	-1600,00 	-1600,00 
17-01-2017	-3400,00 	500,00 
16-01-2017	-1333,00 	-1333,00 
13-01-2017	-4567,00 	-4567,00 
12-01-2017	-453,00 	-453,00 
11-01-2017	300,00 	300,00

表名：余额



我尝试了什么：

Table name: balance

What I have tried:

select (a.date-b.date) as count
from  balance a left join balance b on a.date=b.date
where value>1000 and count>4

推荐答案

create table accountbalance
(
 custid         varchar2(5) not null,
 adate          date not null,
 balance        number not null,
 constraint pk_accountbalance primary key(custid, balance)
);

我已在客户ID（C1）的第一列中插入值，并在客户ID（C2）的第二列中插入值。



查询：

I have inserted the values in the first column for customer id (C1) and the values in the second column for customer id (C2).

The query:

with
a as
(
select custid selid, adate seldate, balance selbal
from accountbalance
),
b as
(
select selid testid, seldate testdate, nvl(max(adate), to_date('01-jan-1900')) prevnormaldate
from a, accountbalance
where custid = selid
and adate < seldate
and balance  > -1000
group by selid, seldate
),
c as
(
select selid, seldate, selbal, prevnormaldate
from a, b
where selid = testid
and seldate = testdate
)
select selid custid, seldate trandate, min(selbal) balance, sum(case when selbal > -1000 then 0 else 1 end) odcount
from c, accountbalance
where selid = custid
and adate between prevnormaldate + 1 and seldate
group by selid, seldate
order by 1, 2
;

这篇关于Sql：计算超过1000的值之间的天数，持续5天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！