查看: 706|回复: 4

[SQL] 连续日期合并问题

[复制链接]
论坛徽章:
2
2016猴年福章
日期:2016-02-18 09:31:30秀才
日期:2017-03-20 13:42:20
发表于 2018-5-14 13:56 | 显示全部楼层 |阅读模式
1、测试和语句数据如下:
with test_data as (
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-10' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000365' as team_code,  date'2016-5-19' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000377' as team_code,  date'2016-5-23' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000383' as team_code,  date'2016-5-26' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000384' as team_code,  date'2016-5-27' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-30' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-31' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000508' as team_code,  date'2018-3-8' as insert_date, 1 as valid_flag from dual
)
select user_id,team_code,begin_date,valid_flag,
       lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1,
case when valid_flag=0 and (lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1) is not null
     then lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1
     when valid_flag=0 and (lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1) is null
     then lead(begin_date, 1, begin_date) over (partition by user_id order  by begin_date)
     else lead(begin_date, 1, trunc(sysdate)) over (partition by user_id order  by begin_date)  
end as end_date
from (
select * from (
select o.user_id,o.team_code,trunc(o.insert_date) begin_date,o.valid_flag,
       row_number() over(partition by o.user_id,trunc(o.insert_date) order by o.valid_flag desc) nu
from test_data o
) where nu=1
) ;
2、要求:
通过 user_id,team_code分组,按时间insert_date升序排列,如果相同user_id的下一条记录的team_code相同则为日期连续,将记录合并。
如果相同user_id的下一条记录的team_code不相同则为日期为截断,下一条记录的日期减1作为上一条记录的end_date。
如果当天有相同的记录(user_id,team_code,insert_date)全部一致则取valid_flag为1 的记录,连valid_flag也相同就随意取一条(valid_flag只有0和1,valid_flag=1只会在最后一天的记录中)
如上列中user_id='0901248'  and team_code='T000000221' and insert_date=date'2016-5-30'到insert_date=date'2016-6-8'这6条记录应该要合并成一条记录,
呈现结果是begin_date=date'2016-5-30'  and end_date=date'2016-6-8'的一条记录,而不是上面写法中的6、7、8三条记录。



论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2018-5-14 14:49 | 显示全部楼层


代码如下,由于你没有该处最终结果,  因此如果如下的语句在实际中运行后, 结果不一致的问地方, 详细说明, 并用数据举例



代码测试如下:



SQL> with test_data as (
  2  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-10' as insert_date, 0 as valid_flag from dual
  3  union all
  4  select '0901248' as user_id, 'T000000365' as team_code,  date'2016-5-19' as insert_date, 0 as valid_flag from dual
  5  union all
  6  select '0901248' as user_id, 'T000000377' as team_code,  date'2016-5-23' as insert_date, 0 as valid_flag from dual
  7  union all
  8  select '0901248' as user_id, 'T000000383' as team_code,  date'2016-5-26' as insert_date, 0 as valid_flag from dual
  9  union all
10  select '0901248' as user_id, 'T000000384' as team_code,  date'2016-5-27' as insert_date, 0 as valid_flag from dual
11  union all
12  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-30' as insert_date, 0 as valid_flag from dual
13  union all
14  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-31' as insert_date, 0 as valid_flag from dual
15  union all
16  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
17  union all
18  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
19  union all
20  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
21  union all
22  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
23  union all
24  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
25  union all
26  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
27  union all
28  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
29  union all
30  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
31  union all
32  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
33  union all
34  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
35  union all
36  select '9917013' as user_id, 'T000000508' as team_code,  date'2018-3-8' as insert_date, 1 as valid_flag from dual
37  )
38  select  user_id,
39          max(team_code) team_code,
40          max(insert_date) start_date,
41          lead( max(insert_date)) over(partition by user_id order by max(insert_date)) - 1 end_date,
42          max(valid_flag) valid_flag
43  from
44  (select user_id,
45          team_code,
46          insert_date,
47          valid_flag,
48          max(rn) over(partition by user_id order by insert_date) rn
49  from
50  (select user_id,
51          team_code,
52          insert_date,
53          valid_flag,
54          case
55           when team_code = lag(team_code) over(partition by user_id order by insert_date) then
56                0
57           else
58                row_number() over(partition by user_id order by insert_date)
59          end rn
60     from test_data))
61  group by user_id, rn
62  order by user_id, max(insert_date);

USER_ID TEAM_CODE  START_DATE  END_DATE    VALID_FLAG
------- ---------- ----------- ----------- ----------
0901248 T000000221 2016/5/10   2016/5/18            0
0901248 T000000365 2016/5/19   2016/5/22            0
0901248 T000000377 2016/5/23   2016/5/25            0
0901248 T000000383 2016/5/26   2016/5/26            0
0901248 T000000384 2016/5/27   2016/6/7             0
0901248 T000000221 2016/6/8                         0
9917013 T000000434 2017/3/14   2017/3/21            0
9917013 T000000453 2017/3/22   2017/3/27            0
9917013 T000000434 2017/3/28   2018/3/7             0
9917013 T000000508 2018/3/8                         1

10 rows selected

SQL>


使用道具 举报

回复
论坛徽章:
2
2016猴年福章
日期:2016-02-18 09:31:30秀才
日期:2017-03-20 13:42:20
 楼主| 发表于 2018-5-14 15:47 | 显示全部楼层
bell6248 发表于 2018-5-14 14:49
代码如下,由于你没有该处最终结果,  因此如果如下的语句在实际中运行后, 结果不一致的问地方, 详细 ...

谢谢版主。
需求就是通过user_id分组insert_date排序,然后判断team_code是否和下一列的team_code相等(直到不相等则重新开始作为一条新记录存储),相等就将记录合并成一条记录,begin_date取第一条记录的时间,end_date则取合并记录最后一条记录下一条记录的日期减1。当user_id分组insert_date排序最后一条记录valid_flag=0时end_date直接截断,valid_flag=0时end_date为当前日期。

根据你的思路改了下,需要的是如下效果。
目前来看需求已经实现,存在的问题就是构造的列有点多,可能性能会有点慢

with test_data as (
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-6' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-10' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000365' as team_code,  date'2016-5-19' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000377' as team_code,  date'2016-5-23' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000383' as team_code,  date'2016-5-26' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000384' as team_code,  date'2016-5-27' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-30' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-31' as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
union all
select '9917013' as user_id, 'T000000508' as team_code,  date'2018-3-8' as insert_date, 1 as valid_flag from dual
)
select user_id,team_code,
min(begin_date) begin_date,valid_flag,
max(end_date) end_date
from (
select user_id,team_code,begin_date,valid_flag,rn,
       --lead(team_code, 1, null) over (partition by user_id order  by begin_date),
case when valid_flag=0 and (lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1) is not null
     then lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1
     when valid_flag=0 and (lead(begin_date, 1, null) over (partition by user_id order  by begin_date)-1) is null
     then lead(begin_date, 1, begin_date) over (partition by user_id order  by begin_date)
     else lead(begin_date, 1, trunc(sysdate)) over (partition by user_id order  by begin_date) end end_date
from (                  
select user_id,
          team_code,
          insert_date as begin_date,
          valid_flag,
          max(rn) over(partition by user_id order by insert_date) rn
  from
  (select user_id,
          team_code,
          insert_date,
          valid_flag,
          case when team_code = lag(team_code) over(partition by user_id order by insert_date) then 0
          else row_number() over(partition by user_id order by insert_date)
          end rn,
          row_number() over(partition by user_id,trunc(insert_date) order by valid_flag desc) nu
     from test_data) where nu=1
) )
group by  user_id,team_code,valid_flag,rn
order by  user_id,begin_date
;

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2018-5-14 16:32 | 显示全部楼层
robot_sql 发表于 2018-5-14 15:47
谢谢版主。
需求就是通过user_id分组insert_date排序,然后判断team_code是否和下一列的team_code相等( ...



你的语句最外层取END_DATE写的太复杂, 完全可以简化, 下次提问的时候就把需求说清楚, 这样就避免大家猜了


测试代码如下



SQL> with test_data as (
  2  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-6' as insert_date, 0 as valid_flag from dual
  3  union all
  4  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-10' as insert_date, 0 as valid_flag from dual
  5  union all
  6  select '0901248' as user_id, 'T000000365' as team_code,  date'2016-5-19' as insert_date, 0 as valid_flag from dual
  7  union all
  8  select '0901248' as user_id, 'T000000377' as team_code,  date'2016-5-23' as insert_date, 0 as valid_flag from dual
  9  union all
10  select '0901248' as user_id, 'T000000383' as team_code,  date'2016-5-26' as insert_date, 0 as valid_flag from dual
11  union all
12  select '0901248' as user_id, 'T000000384' as team_code,  date'2016-5-27' as insert_date, 0 as valid_flag from dual
13  union all
14  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-30' as insert_date, 0 as valid_flag from dual
15  union all
16  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-5-31' as insert_date, 0 as valid_flag from dual
17  union all
18  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
19  union all
20  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
21  union all
22  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
23  union all
24  select '0901248' as user_id, 'T000000221' as team_code,  date'2016-6-8'  as insert_date, 0 as valid_flag from dual
25  union all
26  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
27  union all
28  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-14' as insert_date, 0 as valid_flag from dual
29  union all
30  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
31  union all
32  select '9917013' as user_id, 'T000000453' as team_code,  date'2017-3-22' as insert_date, 0 as valid_flag from dual
33  union all
34  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
35  union all
36  select '9917013' as user_id, 'T000000434' as team_code,  date'2017-3-28' as insert_date, 0 as valid_flag from dual
37  union all
38  select '9917013' as user_id, 'T000000508' as team_code,  date'2018-3-8' as insert_date, 1 as valid_flag from dual
39  )
40  select  user_id,
41          max(team_code) team_code,
42          min(insert_date) start_date,
43          nvl(lead( min(insert_date)) over(partition by user_id order by min(insert_date)) - 1, decode(max(valid_flag), 0, max(insert_date), 1, trunc(sysdate))) end_date,
44          max(valid_flag) valid_flag
45  from
46  (select user_id,
47          team_code,
48          insert_date,
49          valid_flag,
50          max(rn) over(partition by user_id order by insert_date) rn
51  from
52  (select user_id,
53          team_code,
54          insert_date,
55          valid_flag,
56          case
57           when team_code = lag(team_code) over(partition by user_id order by insert_date) then
58                0
59           else
60                row_number() over(partition by user_id order by insert_date)
61          end rn
62     from test_data))
63  group by user_id, rn
64  order by user_id, min(insert_date);

USER_ID TEAM_CODE  START_DATE  END_DATE    VALID_FLAG
------- ---------- ----------- ----------- ----------
0901248 T000000221 2016/5/6    2016/5/18            0
0901248 T000000365 2016/5/19   2016/5/22            0
0901248 T000000377 2016/5/23   2016/5/25            0
0901248 T000000383 2016/5/26   2016/5/26            0
0901248 T000000384 2016/5/27   2016/5/29            0
0901248 T000000221 2016/5/30   2016/6/8             0
9917013 T000000434 2017/3/14   2017/3/21            0
9917013 T000000453 2017/3/22   2017/3/27            0
9917013 T000000434 2017/3/28   2018/3/7             0
9917013 T000000508 2018/3/8    2018/5/14            1

10 rows selected

SQL>


使用道具 举报

回复
论坛徽章:
2
2016猴年福章
日期:2016-02-18 09:31:30秀才
日期:2017-03-20 13:42:20
 楼主| 发表于 2018-5-14 16:54 | 显示全部楼层
bell6248 发表于 2018-5-14 16:32
你的语句最外层取END_DATE写的太复杂, 完全可以简化, 下次提问的时候就把需求说清楚, 这样就避免 ...

好的。谢谢

使用道具 举报

回复

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

本版积分规则 发表回复

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