当前位置：Gxlcms > 数据库问题 > 【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

时间：2021-07-01 10:21:17 帮助过：9人阅读

@table1 table(name nvarchar,createdate smalldatetime)
  • insert into @table1
  • select ‘a‘,‘2011-03-01 10:00:00‘
  • union all select ‘a‘,‘2011-03-01 11:00:00‘
  • union all select ‘a‘,‘2011-03-01 14:00:00‘
  • union all select ‘b‘,‘2011-03-01 13:00:00‘
  • union all select ‘b‘,‘2011-03-01 13:20:00‘
  • union all select ‘b‘,‘2011-03-01 14:00:00‘
  • select name,
  • createdate,
  • (select count(createdate)
  • from @table1 b
  • where a.name=b.name and
  • a.createdate<=b.createdate and
  • dateadd(hh,2,a.createdate) >= b.createdate
  • ) as count
  • from @table1 a
  • where not exists
  • (select 1 from
  • @table1 b
  • where a.name=b.name and
  • a.createdate>b.createdate and
  • a.createdate<dateadd(hh,2,b.createdate))
  • group by name,createdate


    • 但是这个解答其实是有问题的，当把临时表中的第3条数据的createdate改为‘2011-03-01 12:00:00‘，那么显示的结果是：

    name        createdate                           count
    a                2011-03-01 10:00:00        3
    b                2011-03-01 13:00:00        3

    在其中没有包括createdate为‘2011-03-01 12:00:00‘的记录，因为这个时间到为‘2011-03-01 10:00:00‘是超过2个小时了，也就是说为‘2011-03-01 10:00:00‘是第一个出现时间，到为‘2011-03-01 11:59:59‘为止，接下来应该是从‘2011-03-01 12:00:00‘开始的下个区间了，而这里显然是有问题的。

    以下是我写的解法，虽然效率不是太高，但是能解决这个问题：

    1. declare @table1 table(name nvarchar,createdate smalldatetime)
    2.  
    3. insert into @table1
    4. select ‘a‘,‘2011-03-01 10:00:00‘
    5. union all select ‘a‘,‘2011-03-01 11:00:00‘
    6. union all select ‘a‘,‘2011-03-01 12:00:00‘
    7. union all select ‘b‘,‘2011-03-01 13:10:00‘
    8. union all select ‘b‘,‘2011-03-01 13:20:00‘
    9. union all select ‘b‘,‘2011-03-01 14:30:00‘
    10. union all select ‘b‘,‘2011-03-01 15:15:00‘
    11. union all select ‘b‘,‘2011-03-01 16:00:00‘
    12. union all select ‘b‘,‘2011-03-01 17:00:00‘
    13.  
    14.  
    15.   
    16. ;with aa  --按照name分区，同时按照createdate排序编号
    17. as
    18. (
    19. select name,
    20.        createdate,
    21.        ROW_NUMBER() over(partition by name
    22.                              order by createdate) as k1
    23. from @table1 
    24. ),
    25.  
    26. r
    27. as
    28. (
    29. select v.name,
    30.        starts=v.k1,  --区间开始的编号
    31.        
    32.        ends=isnull(
    33.                min(case when v.k1<a.k1 
    34.                              and DATEADD(hour,2,v.createdate) <= a.createdate 
    35.                              then a.k1 
    36.                          else null 
    37.                    end)-1,
    38.  
    39.                max(case when v.k1<a.k1 
    40.                              then a.k1 
    41.                         else v.k1 
    42.                    end)
    43.              ),     --区间结尾的编号      
    44.                              
    45.            isnull(
    46.                min(case when v.k1<a.k1 
    47.                              and DATEADD(hour,2,v.createdate) <= a.createdate 
    48.                              then a.k1 
    49.                          else null 
    50.                    end)-1,
    51.  
    52.                max(case when v.k1<a.k1 
    53.                              then a.k1 
    54.                         else v.k1 
    55.                    end)
    56.              ) - v.k1 as diff     --区间结尾编号与区间开始编号之间的差值
    57.                               
    58. from aa v  
    59. inner join aa a 
    60.         on v.name = a.name  --只关联name相等的
    61. group by v.name,
    62.          v.k1
    63. having isnull(
    64.                min(case when v.k1<a.k1 
    65.                              and DATEADD(hour,2,v.createdate) <= a.createdate 
    66.                              then a.k1 
    67.                          else null 
    68.                    end)-1,
    69.  
    70.                max(case when v.k1<a.k1 
    71.                              then a.k1 
    72.                         else v.k1 
    73.                    end)
    74.              )  >=v.k1   
    75.         and            
    76.         isnull(
    77.                max(case when v.k1>a.k1 and 
    78.                              DATEADD(hour,-2,v.createdate) >= a.createdate
    79.                              then v.k1 - 1 
    80.                         else null 
    81.                    end) + 1,
    82.  
    83.                min(case when v.k1>a.k1 
    84.                              then a.k1 
    85.                         else v.k1 
    86.                    end)
    87.               ) = v.k1
    88. )
    89.  
    90. --select * from r
    91.  
    92.  
    93. select aa.name,
    94.        aa.createdate,
    95.        diff + 1
    96. from r
    97. inner join aa 
    98.         on aa.name = r.name
    99.            and aa.k1 =r.starts
    100. where not exists
    101. 		(select 1
    102. 		 from r rr
    103. 		 where rr.name = r.name and
    104. 		       rr.starts <> r.starts and
    105. 		       rr.starts < r.starts and
    106. 		       (rr.ends = r.ends or
    107. 		        rr.ends = r.starts)
    108. 		)

     不过发现我的这个解法也是有问题的，最大的问题在与不能准确的确定上限在那里，还得继续考虑问题的解法。

    下面这个也是有问题的：

    1. declare @table1 table(name nvarchar,createdate smalldatetime)
    2.  
    3. insert into @table1
    4. select ‘a‘,‘2011-03-01 10:00:00‘
    5. union all select ‘a‘,‘2011-03-01 11:00:00‘
    6. union all select ‘a‘,‘2011-03-01 12:00:00‘
    7.  
    8. --union all select ‘b‘,‘2011-03-01 13:10:00‘
    9. --union all select ‘b‘,‘2011-03-01 13:20:00‘
    10. --union all select ‘b‘,‘2011-03-01 14:30:00‘
    11. --union all select ‘b‘,‘2011-03-01 15:15:00‘
    12. --union all select ‘b‘,‘2011-03-01 16:00:00‘
    13. --union all select ‘b‘,‘2011-03-01 17:15:00‘
    14.  
    15.  
    16.   
    17. ;with a  --按照name分区，同时按照createdate排序编号
    18. as
    19. (
    20. select name,
    21.        createdate,
    22.        ROW_NUMBER() over(partition by name
    23.                              order by createdate) as k1
    24. from @table1 
    25. ),
    26.  
    27. c
    28. as
    29. (
    30. select a1.name,
    31.        a1.createdate,
    32.        a1.k1,
    33.        MIN(a2.createdate) as nextCreatedate,
    34.        MIN(a2.k1) as nextK1
    35. from a a1
    36. inner join a a2
    37.         on a1.name = a2.name
    38.            and a2.createdate < DATEADD(hour,2,a1.createdate)
    39.            and a2.createdate >= a1.createdate
    40.            and a1.k1 <= a2.k1
    41. group by a1.name,
    42.          a1.createdate,
    43.          a1.k1
    44. ),
    45.  
    46. w
    47. as
    48. (
    49. select name,
    50.        createdate,
    51.        k1
    52.        
    53.        --null,
    54.        --null,
    55.        --null
    56. from a
    57. where k1 = 1
    58.  
    59. union all
    60.  
    61. select c.name,
    62.        c.nextCreatedate,
    63.        c.nextK1
    64.        
    65. from W
    66. inner join a
    67.         on a.name = w.name
    68.            and dateadd(hour,2,w.createdate) <= a.createdate
    69.            and w.k1 <= a.k1
    70.            and w.createdate <> ‘2011-03-01 12:00:00‘ 
    71. inner join c
    72.         on w.name = c.name 
    73.            and w.createdate = c.createdate
    74.            and w.k1 = c.k1
    75. where w.k1 <=3
    76. )
    77.  
    78. SELECT * 
    79. FROM w


    下面的解法是正确的，不过用的是T-SQL，不是纯sql了：

    1. declare @table1 table(name nvarchar(100),createdate smalldatetime)
    2.  
    3. declare @table2 table(name nvarchar(100),createdate smalldatetime,rnum bigint)
    4.  
    5. declare @temp table(name nvarchar(100),createdate smalldatetime,rnum bigint)
    6.  
    7. declare @i int = 1;
    8.  
    9. insert into @table1
    10. select ‘a‘,‘2011-03-01 10:00:00‘
    11. union all select ‘a‘,‘2011-03-01 11:00:00‘
    12. union all select ‘a‘,‘2011-03-01 12:00:00‘
    13.  
    14. union all select ‘b‘,‘2011-03-01 13:10:00‘
    15. union all select ‘b‘,‘2011-03-01 13:20:00‘
    16. union all select ‘b‘,‘2011-03-01 14:30:00‘
    17. union all select ‘b‘,‘2011-03-01 15:15:00‘
    18. union all select ‘b‘,‘2011-03-01 16:00:00‘
    19. union all select ‘b‘,‘2011-03-01 17:16:00‘
    20. union all select ‘b‘,‘2011-03-01 17:15:00‘
    21.  
    22.   
    23. ;with a  --按照name分区，同时按照createdate排序编号
    24. as
    25. (
    26. select name,
    27.        createdate,
    28.        ROW_NUMBER() over(partition by name
    29.                              order by createdate) as k1
    30. from @table1 
    31. )
    32.  
    33. insert into @table2
    34. select * from a
    35.  
    36. insert into @temp
    37. select name,
    38.        createdate,
    39.        rnum
    40. from @table2
    41. where rnum = 1
    42.  
    43. --select * from @temp
    44.  
    45.  
    46. while @i <= (select MAX(rnum) from @table2)
    47. begin
    48.     insert into @temp
    49.     
    50. 	select t2.name,
    51. 	       min(t2.createdate),
    52. 	       @i +1	
    53. 	from @temp t1
    54. 	inner join @table2 t2        
    55. 	        on t1.name = t2.name
    56. 	           and t2.createdate >= dateadd(hour,2,t1.createdate)	           
    57. 	where t1.rnum = @i     
    58. 	group by t2.name
    59. 	            
    60. 	set @i = @i + 1	
    61. end
    62.  
    63. ;with r
    64. as
    65. (
    66. select name,
    67.        createdate
    68. from @temp
    69. group by name,
    70.          createdate
    71. )
    72.  
    73. select r.name,
    74.        r.createdate,
    75.        COUNT(1)
    76. from r
    77. inner join @table1 t
    78.         on t.name = r.name
    79.            and t.createdate >= r.createdate
    80.            and t.createdate <DATEADD(HOUR,2,r.createdate)
    81. group by r.name,
    82.          r.createdate

     

    其实这个问题是个递归问题，由上一个找到下一个，但是得构造一下：

    1. declare @table1 table(name nvarchar,createdate smalldatetime)
    2.  
    3. insert into @table1
    4. select ‘a‘,‘2011-03-01 10:00:00‘
    5. union all select ‘a‘,‘2011-03-01 11:00:00‘
    6. union all select ‘a‘,‘2011-03-01 12:00:00‘
    7. union all select ‘a‘,‘2011-03-01 12:20:00‘
    8.  
    9. union all select ‘b‘,‘2011-03-01 13:10:00‘
    10. union all select ‘b‘,‘2011-03-01 13:20:00‘
    11. union all select ‘b‘,‘2011-03-01 14:30:00‘
    12. union all select ‘b‘,‘2011-03-01 15:15:00‘
    13. union all select ‘b‘,‘2011-03-01 16:00:00‘
    14. union all select ‘b‘,‘2011-03-01 17:20:00‘
    15. union all select ‘b‘,‘2011-03-01 17:15:00‘
    16. union all select ‘b‘,‘2011-03-01 19:16:00‘
    17. union all select ‘b‘,‘2011-03-01 17:15:00‘
    18.  
    19.   
    20. ;with a  --按照name分区，同时按照createdate排序编号
    21. as
    22. (
    23. select name,
    24.        createdate,
    25.        ROW_NUMBER() over(partition by name
    26.                              order by createdate) as k1
    27. from @table1 
    28. ),
    29.  
    30. c   --对于每个时间,找到大于这个时间2小时的时间中最小那个时间
    31. as
    32. (
    33. select a1.name,
    34.        a1.createdate,
    35.        a1.k1,
    36.        MIN(a2.createdate) as nextCreatedate,
    37.        MIN(a2.k1) as nextK1
    38. from a a1
    39. inner join a a2
    40.         on a1.name = a2.name
    41.            and a2.createdate >= DATEADD(hour,2,a1.createdate)
    42.            
    43. group by a1.name,
    44.          a1.createdate,
    45.          a1.k1
    46.  
    47. union all
    48.  
    49. select a.name,null,null,a.createdate,1  --构造递归运行时需要的层级
    50. from a
    51. where k1 = 1
    52. ),
    53.  
    54. w   --递归查询
    55. as
    56. (
    57. select c.name,
    58.        c.createdate,
    59.        c.k1,
    60.        c.nextCreatedate,
    61.        c.nextK1,
    62.        1 as lev
    63. from c
    64. where createdate is null
    65.       and k1 is null
    66.  
    67. union all
    68.  
    69. select c.name,
    70.        c.createdate,
    71.        c.k1,
    72.        c.nextCreatedate,
    73.        c.nextK1,
    74.        lev + 1
    75. from W
    76. inner join c
    77.         on w.name = c.name
    78.            and w.nextCreatedate = c.createdate
    79.  
    80. )
    81.  
    82. SELECT distinct name,
    83.        nextCreatedate,
    84.        nextK1 
    85. FROM w



     

     

     

    • 点赞
    • 收藏
    • 分享
      • 文章举报
    技术图片 技术图片 不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万+ 他的留言板 关注

    【Transact-SQL】统计某字段中的值第一次出现后的2小时内出现的次数

    标签：col   关注   相等   from   访问   line   vat   temp   select   

    人气教程排行

    本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！