sql - 输出连续大于等于三天销售量大于100的日期

问题描述

问 题

CREATE TABLE t_log(f_time DATE, f_value int)
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-01', 100);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-02', 101);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-03', 102);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-04', 99);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-05', 105);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-06', 108);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-07', 105);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-08', 199);
insert into T_LOG (F_TIME, F_VALUE)values ('2016-10-09', 108);
with a as(
select f_group,lead(f_group,1,'9999-01-01') over (order by f_group) as f_grouptwo from
(select F_TIME, F_VALUE,case when lag(f_value,1,0)over (order by f_time)<100 then f_time else null end f_group from t_log)t
where f_group is not null)
, b as(
select * from a
where datediff(dd,f_group,f_grouptwo)>=3)
select f_group,max(f_time) as maxtime
from b
inner join t_log c
on b.f_group<=c.f_time and b.f_grouptwo>c.f_time
where c.f_value>=100
group by f_group

现在的输出：
f_group maxtime
2016-10-01 2016-10-03
2016-10-05 2016-10-09

目标输出
2016-10-01
2016-10-02
2016-10-03
2016-10-05
2016-10-06
2016-10-07
2016-10-08
2016-10-09

解决方案

思路1

某日期（*）要想入选，则其前后的日期（-）也需按如下情况入选：

1. * - -

2. - * -

3. - - *

因此有（MSSQL）：

with t as (select f_time from t_log where f_value > 100)
select * from t
where 
    (dateadd(day, 1, f_time) in (select * from t) and
    dateadd(day, 2, f_time) in (select * from t))
    or
    (dateadd(day, 1, f_time) in (select * from t) and
    dateadd(day, -1, f_time) in (select * from t))
    or
    (dateadd(day, -1, f_time) in (select * from t) and
    dateadd(day, -2, f_time) in (select * from t))
order by f_time

思路2

找出所有满足条件的(d, d+1, d+2)三元组，然后求union：

with
    log as (
        select * from t_log where f_value > 100),
    dates as (
        select g1.f_time date1, g2.f_time date2, g3.f_time date3 from
            log g1 inner join log g2 on dateadd(day, 1, g1.f_time) = g2.f_time
            inner join log g3 on dateadd(day, 1, g2.f_time) = g3.f_time)
select * from
    (select date1 from dates union select date2 from dates union select date3 from dates) foo
order by 1

这篇关于sql - 输出连续大于等于三天销售量大于100的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！