ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 895|回复: 5

[SQL] 最大连续时间段问题

[复制链接]
认证徽章
论坛徽章:
0
发表于 2017-11-1 09:33 | 显示全部楼层 |阅读模式
本帖最后由 long_ooops 于 2017-11-1 10:22 编辑

--假设下表是某客户购买我司保险的起保时间,和结束时间(同一客户可能买不同的保险,时间有重叠),现在要根据他购买所有产品最大连续时间来给优惠,中间有断档就不算。
with t as(
select sysdate 起保时间,sysdate +3 结束时间 from dual union
select  sysdate +3,sysdate +5 from dual union
select sysdate+1,sysdate +3 from dual union
select sysdate-1,sysdate +5 from dual union
select sysdate +8,sysdate +16 from dual union
select sysdate +6,sysdate +7 from dual )
select * from t
--最后得到的结果是
2017-10-31 ~ 2017-11-6  
2017-11-7  ~ 2017-11-8
2017-11-9  ~ 2017-11-17

论坛徽章:
4
秀才
日期:2017-08-11 15:38:46秀才
日期:2017-09-08 11:09:50秀才
日期:2017-09-25 15:10:28秀才
日期:2017-09-25 15:10:28
发表于 2017-11-1 11:51 | 显示全部楼层
本帖最后由 却早已分离 于 2017-11-1 12:52 编辑

http://www.itpub.net/thread-2089702-1-1.html  看下这个帖子

使用道具 举报

回复
论坛徽章:
4
秀才
日期:2017-08-11 15:38:46秀才
日期:2017-09-08 11:09:50秀才
日期:2017-09-25 15:10:28秀才
日期:2017-09-25 15:10:28
发表于 2017-11-1 12:58 | 显示全部楼层
本帖最后由 却早已分离 于 2017-11-1 13:14 编辑

with t as(
select sysdate stadt,sysdate +3 enddt from dual union
select  sysdate +3,sysdate +5 from dual union
select sysdate+1,sysdate +3 from dual union
select sysdate-1,sysdate +5 from dual union
select sysdate +6,sysdate +7 from dual union
select sysdate +8,sysdate +16 from dual/* UNION
SELECT SYSDATE,SYSDATE+100 FROM dual*/ )
SELECT MIN(stadt),MAX(enddt) FROM(
select * from t
MODEL
DIMENSION BY (ROW_number()OVER (ORDER BY stadt) rn)
MEASURES (stadt,enddt,0 x)
RULES ITERATE(100) UNTIL  stadt[iteration_number+1] IS NULL
(x[1]=1,
x[iteration_number+1]=CASE WHEN stadt[CV()]>max(enddt)[rn<CV()] THEN x[CV()-1]+1 ELSE NVL(x[iteration_number],x[iteration_number+1]) END ))
GROUP BY x;

使用道具 举报

回复
论坛徽章:
0
发表于 2017-11-1 17:35 | 显示全部楼层
with t as(
select sysdate 起保时间,sysdate +3 结束时间 from dual union all
select sysdate+3,sysdate +5 from dual union all
select sysdate+1,sysdate +3 from dual union all
select sysdate-1,sysdate +5 from dual union all
select sysdate +8,sysdate +16 from dual union all
select sysdate +6,sysdate +7 from dual )
select distinct (select min(t2.起保时间) from t t2  
where t2.结束时间>t.起保时间 )   
      , (select max(t3.结束时间) from t t3
where t3.起保时间<t.结束时间 )
from t

使用道具 举报

回复
论坛徽章:
4
秀才
日期:2017-08-11 15:38:46秀才
日期:2017-09-08 11:09:50秀才
日期:2017-09-25 15:10:28秀才
日期:2017-09-25 15:10:28
发表于 2017-11-3 11:53 | 显示全部楼层

with t as(
select sysdate stadt,sysdate +3 enddt from dual union
select  sysdate +3,sysdate +5 from dual union
select sysdate+1,sysdate +3 from dual union
select sysdate-1,sysdate +5 from dual union
select sysdate +6,sysdate +7 from dual union
select sysdate +8,sysdate +16 from dual /*UNION
SELECT SYSDATE,SYSDATE+100 FROM dual*/ )
SELECT MIN(stadt), MAX(enddt)
  FROM (SELECT stadt, enddt, SUM(mk) over(ORDER BY stadt, enddt) mk1
          FROM (SELECT stadt,
                       enddt,
                       CASE
                         WHEN stadt > MAX(enddt)
                          over(ORDER BY stadt,
                                   enddt rows BETWEEN unbounded preceding AND 1
                                   preceding) THEN
                          1
                         ELSE
                          0
                       END mk
                  FROM t))
GROUP BY mk1;

使用道具 举报

回复
论坛徽章:
4
秀才
日期:2017-08-11 15:38:46秀才
日期:2017-09-08 11:09:50秀才
日期:2017-09-25 15:10:28秀才
日期:2017-09-25 15:10:28
发表于 2017-11-3 12:54 | 显示全部楼层
WITH t AS
(SELECT SYSDATE stadt, SYSDATE + 3 enddt
    FROM dual
  UNION
  SELECT SYSDATE + 3, SYSDATE + 5
    FROM dual
  UNION
  SELECT SYSDATE + 1, SYSDATE + 3
    FROM dual
  UNION
  SELECT SYSDATE - 1, SYSDATE + 5
    FROM dual
  UNION
  SELECT SYSDATE + 6, SYSDATE + 7
    FROM dual
  UNION
  SELECT SYSDATE + 8, SYSDATE + 16
    FROM dual /* UNION
    SELECT SYSDATE,SYSDATE+100 FROM dual*/
  ),
t1 AS
(SELECT t.*,
         MAX(enddt) over(ORDER BY stadt, enddt) maxendt,
         row_number() over(ORDER BY stadt, enddt) rn
    FROM t
   ORDER BY 1, 2), /*不知道为什么这个max(enddt)over(ORDER BY stadt,enddt)不能在CTE里面计算*/
x(stadt,
enddt,
maxendt,
rn,
mark) AS
(SELECT stadt, enddt, maxendt, rn, 0
    FROM t1
   WHERE rn = 1
  UNION ALL
  SELECT t1.stadt,
         t1.enddt,
         t1.maxendt,
         t1.rn,
         CASE
           WHEN t1.stadt > x.maxendt THEN
            mark + 1
           ELSE
            mark
         END
    FROM t1, x
   WHERE t1.rn = x.rn + 1)
SELECT MIN(stadt), MAX(enddt) FROM x GROUP BY mark;

这个和START WITH CONNECT BY 是一样的

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表