根据时间分组查询求每天的差值

班长老六哥 2017-03-06 09:16:16
表格如下 我想取每天的pv最大和最小值差值，差值大于24差值等于24，根据time字段分组查询，我通过
CONVERT(varchar(10),t1.Time1,120) 函数把小时分秒去掉就分组 ，但是一天的差值要每天的两个0点相减才准，
但是我这样分组的话就把0点分到前一天去了 算的就不准了 求高手帮忙
time pv
2017/2/19 23:30 536
2017/2/19 23:00 535
2017/2/19 22:30 534
2017/2/19 22:00 533
2017/2/19 21:30 532
2017/2/19 21:00 531
2017/2/19 20:30 530
2017/2/19 20:00 529
2017/2/19 19:30 529
2017/2/19 19:00 528
2017/2/19 18:30 527
2017/2/19 18:00 526
2017/2/19 17:30 525
2017/2/19 17:00 524
2017/2/19 16:30 523
2017/2/19 16:00 522
2017/2/19 15:30 521
2017/2/19 15:00 520
2017/2/19 14:30 519
2017/2/19 14:00 518
2017/2/19 13:30 517
2017/2/19 13:00 516
2017/2/19 12:30 515
2017/2/19 12:00 514
2017/2/19 11:30 513
2017/2/19 11:00 512
2017/2/19 10:30 511
2017/2/19 10:00 510
2017/2/19 9:30 509
2017/2/19 9:00 508
2017/2/19 8:30 507
2017/2/19 8:00 506
2017/2/19 7:30 505
2017/2/19 7:00 504
2017/2/19 6:30 503
2017/2/19 6:00 502
2017/2/19 5:30 501
2017/2/19 5:00 500
2017/2/19 4:30 499
2017/2/19 4:00 498
2017/2/19 3:30 497
2017/2/19 3:00 497
2017/2/19 2:30 496
2017/2/19 2:00 495
2017/2/19 1:30 494
2017/2/19 1:00 493
2017/2/19 0:30 492
2017/2/19 0:00 491
2017/2/18 23:30 490
2017/2/18 23:00 489
2017/2/18 22:30 488
2017/2/18 22:00 486
2017/2/18 21:30 485
2017/2/18 21:00 484
2017/2/18 20:30 483
2017/2/18 20:00 482
2017/2/18 19:30 481
2017/2/18 19:00 480
2017/2/18 18:30 479
2017/2/18 18:00 478
2017/2/18 17:30 477
2017/2/18 17:00 476
2017/2/18 16:30 475
2017/2/18 16:00 474
2017/2/18 15:30 473
2017/2/18 15:00 472
2017/2/18 14:30 471
2017/2/18 14:00 470
2017/2/18 13:30 470
2017/2/18 13:00 469
2017/2/18 12:30 468
2017/2/18 12:00 468
2017/2/18 11:30 467
2017/2/18 11:00 466
2017/2/18 10:30 466
2017/2/18 10:00 465
2017/2/18 9:30 465
2017/2/18 9:00 465
2017/2/18 8:30 465
2017/2/18 8:00 465
2017/2/18 7:30 465
2017/2/18 7:00 465
2017/2/18 6:30 465
2017/2/18 6:00 465
2017/2/18 5:30 465
2017/2/18 5:00 465
2017/2/18 4:30 465
2017/2/18 4:00 465
2017/2/18 3:30 465
2017/2/18 3:00 465
2017/2/18 2:30 465
2017/2/18 2:00 465
2017/2/18 1:30 465
2017/2/18 1:00 465
2017/2/18 0:30 465
2017/2/18 0:00 465
...全文
387 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
二月十六 2017-03-08
  • 打赏
  • 举报
 回复
有一天没有明天零点的数据怎么算？
语句：


;WITH 

--测试数据

tab([time],pv)AS(

select '2017/2/20 0:00',537  union all 

select '2017/2/19 2:00',510  union all 

select '2017/2/19 1:00',500  union all 

select '2017/2/19 0:00',491  union all 

select '2017/2/18 0:00',465    

),

--测试数据结束

tempa AS (

SELECT  CONVERT(NVARCHAR(10), [time], 120) AS [date] ,

        MIN([time]) AS [time] ,

        ROW_NUMBER() OVER ( ORDER BY MIN([time]) ) AS num

FROM    tab

GROUP BY CONVERT(NVARCHAR(10), [time], 120)

)

,tempb AS (

SELECT a.*,b.num FROM tab a JOIN tempa b ON a.time = b.time

)

SELECT  a.time ,

        ( CASE WHEN ( b.pv - a.pv ) > 24 THEN 24

               ELSE b.pv - a.pv

          END ) AS pv

FROM    tempb a

        LEFT JOIN tempb b ON b.num - a.num = 1

WHERE   b.time IS NOT NULL



结果：




Tiger_Zhao 2017-03-08
  • 打赏
  • 举报
 回复 
WITH

/* 测试数据

t1(time1,pv)AS(

    SELECT '2017/2/19 23:30',536 UNION ALL

    SELECT '2017/2/19 23:00',535 UNION ALL

    SELECT '2017/2/19 22:30',534 UNION ALL

    SELECT '2017/2/19 22:00',533 UNION ALL

    SELECT '2017/2/19 21:30',532 UNION ALL

    SELECT '2017/2/19 21:00',531 UNION ALL

    SELECT '2017/2/19 20:30',530 UNION ALL

    SELECT '2017/2/19 20:00',529 UNION ALL

    SELECT '2017/2/19 19:30',529 UNION ALL

    SELECT '2017/2/19 19:00',528 UNION ALL

    SELECT '2017/2/19 18:30',527 UNION ALL

    SELECT '2017/2/19 18:00',526 UNION ALL

    SELECT '2017/2/19 17:30',525 UNION ALL

    SELECT '2017/2/19 17:00',524 UNION ALL

    SELECT '2017/2/19 16:30',523 UNION ALL

    SELECT '2017/2/19 16:00',522 UNION ALL

    SELECT '2017/2/19 15:30',521 UNION ALL

    SELECT '2017/2/19 15:00',520 UNION ALL

    SELECT '2017/2/19 14:30',519 UNION ALL

    SELECT '2017/2/19 14:00',518 UNION ALL

    SELECT '2017/2/19 13:30',517 UNION ALL

    SELECT '2017/2/19 13:00',516 UNION ALL

    SELECT '2017/2/19 12:30',515 UNION ALL

    SELECT '2017/2/19 12:00',514 UNION ALL

    SELECT '2017/2/19 11:30',513 UNION ALL

    SELECT '2017/2/19 11:00',512 UNION ALL

    SELECT '2017/2/19 10:30',511 UNION ALL

    SELECT '2017/2/19 10:00',510 UNION ALL

    SELECT '2017/2/19 9:30',509 UNION ALL

    SELECT '2017/2/19 9:00',508 UNION ALL

    SELECT '2017/2/19 8:30',507 UNION ALL

    SELECT '2017/2/19 8:00',506 UNION ALL

    SELECT '2017/2/19 7:30',505 UNION ALL

    SELECT '2017/2/19 7:00',504 UNION ALL

    SELECT '2017/2/19 6:30',503 UNION ALL

    SELECT '2017/2/19 6:00',502 UNION ALL

    SELECT '2017/2/19 5:30',501 UNION ALL

    SELECT '2017/2/19 5:00',500 UNION ALL

    SELECT '2017/2/19 4:30',499 UNION ALL

    SELECT '2017/2/19 4:00',498 UNION ALL

    SELECT '2017/2/19 3:30',497 UNION ALL

    SELECT '2017/2/19 3:00',497 UNION ALL

    SELECT '2017/2/19 2:30',496 UNION ALL

    SELECT '2017/2/19 2:00',495 UNION ALL

    SELECT '2017/2/19 1:30',494 UNION ALL

    SELECT '2017/2/19 1:00',493 UNION ALL

    SELECT '2017/2/19 0:30',492 UNION ALL

    SELECT '2017/2/19 0:00',491 UNION ALL

    SELECT '2017/2/18 23:30',490 UNION ALL

    SELECT '2017/2/18 23:00',489 UNION ALL

    SELECT '2017/2/18 22:30',488 UNION ALL

    SELECT '2017/2/18 22:00',486 UNION ALL

    SELECT '2017/2/18 21:30',485 UNION ALL

    SELECT '2017/2/18 21:00',484 UNION ALL

    SELECT '2017/2/18 20:30',483 UNION ALL

    SELECT '2017/2/18 20:00',482 UNION ALL

    SELECT '2017/2/18 19:30',481 UNION ALL

    SELECT '2017/2/18 19:00',480 UNION ALL

    SELECT '2017/2/18 18:30',479 UNION ALL

    SELECT '2017/2/18 18:00',478 UNION ALL

    SELECT '2017/2/18 17:30',477 UNION ALL

    SELECT '2017/2/18 17:00',476 UNION ALL

    SELECT '2017/2/18 16:30',475 UNION ALL

    SELECT '2017/2/18 16:00',474 UNION ALL

    SELECT '2017/2/18 15:30',473 UNION ALL

    SELECT '2017/2/18 15:00',472 UNION ALL

    SELECT '2017/2/18 14:30',471 UNION ALL

    SELECT '2017/2/18 14:00',470 UNION ALL

    SELECT '2017/2/18 13:30',470 UNION ALL

    SELECT '2017/2/18 13:00',469 UNION ALL

    SELECT '2017/2/18 12:30',468 UNION ALL

    SELECT '2017/2/18 12:00',468 UNION ALL

    SELECT '2017/2/18 11:30',467 UNION ALL

    SELECT '2017/2/18 11:00',466 UNION ALL

    SELECT '2017/2/18 10:30',466 UNION ALL

    SELECT '2017/2/18 10:00',465 UNION ALL

    SELECT '2017/2/18 9:30',465 UNION ALL

    SELECT '2017/2/18 9:00',465 UNION ALL

    SELECT '2017/2/18 8:30',465 UNION ALL

    SELECT '2017/2/18 8:00',465 UNION ALL

    SELECT '2017/2/18 7:30',465 UNION ALL

    SELECT '2017/2/18 7:00',465 UNION ALL

    SELECT '2017/2/18 6:30',465 UNION ALL

    SELECT '2017/2/18 6:00',465 UNION ALL

    SELECT '2017/2/18 5:30',465 UNION ALL

    SELECT '2017/2/18 5:00',465 UNION ALL

    SELECT '2017/2/18 4:30',465 UNION ALL

    SELECT '2017/2/18 4:00',465 UNION ALL

    SELECT '2017/2/18 3:30',465 UNION ALL

    SELECT '2017/2/18 3:00',465 UNION ALL

    SELECT '2017/2/18 2:30',465 UNION ALL

    SELECT '2017/2/18 2:00',465 UNION ALL

    SELECT '2017/2/18 1:30',465 UNION ALL

    SELECT '2017/2/18 1:00',465 UNION ALL

    SELECT '2017/2/18 0:30',465 UNION ALL

    SELECT '2017/2/18 0:00',465

),

*/

a(dt,min_pv,max_pv)AS(

    SELECT CONVERT(varchar(10),t1.Time1,120),

           MIN(pv),

           MAX(pv)

      FROM t1

  GROUP BY CONVERT(varchar(10),t1.Time1,120)

)

    SELECT a1.dt,

           a1.min_pv, -- DEBUG

           ISNULL(a2.min_pv,a1.max_pv) AS max_pv, -- DEBUG

           ISNULL(a2.min_pv,a1.max_pv) - a1.min_pv AS delta_pv

      FROM a a1

 LEFT JOIN a a2

        ON DATEADD(day,1,a1.dt) = a2.dt

dt              min_pv      max_pv    delta_pv

---------- ----------- ----------- -----------

2017/2/18          465         491          26

2017/2/19          491         536          45
班长老六哥 2017-03-08
  • 打赏
  • 举报
 回复
引用 8 楼 Tiger_Zhao 的回复:
语文不行就请拿数据举例： 19日的最大值是多少？最小值是多少？差值应该是多少？ 18日的最大值是多少？最小值是多少？差值应该是多少？
19日的最大值就是20号0点的，最小值就是19号0点的 18日的最大值就是19号0点的，最小值就是18号0点的
班长老六哥 2017-03-08
  • 打赏
  • 举报
 回复
引用 8 楼 Tiger_Zhao 的回复:
语文不行就请拿数据举例： 19日的最大值是多少？最小值是多少？差值应该是多少？ 18日的最大值是多少？最小值是多少？差值应该是多少？
引用 8 楼 Tiger_Zhao 的回复:
语文不行就请拿数据举例： 19日的最大值是多少？最小值是多少？差值应该是多少？ 18日的最大值是多少？最小值是多少？差值应该是多少？
time pv 2017/2/19 23:30 536 2017/2/19 23:00 535 2017/2/19 22:30 534 2017/2/19 22:00 533 2017/2/19 21:30 532 2017/2/19 21:00 531 2017/2/19 20:30 530 2017/2/19 20:00 529 2017/2/19 19:30 529 2017/2/19 19:00 528 2017/2/19 18:30 527 2017/2/19 18:00 526 2017/2/19 17:30 525 2017/2/19 17:00 524 2017/2/19 16:30 523 2017/2/19 16:00 522 2017/2/19 15:30 521 2017/2/19 15:00 520 2017/2/19 14:30 519 2017/2/19 14:00 518 2017/2/19 13:30 517 2017/2/19 13:00 516 2017/2/19 12:30 515 2017/2/19 12:00 514 2017/2/19 11:30 513 2017/2/19 11:00 512 2017/2/19 10:30 511 2017/2/19 10:00 510 2017/2/19 9:30 509 2017/2/19 9:00 508 2017/2/19 8:30 507 2017/2/19 8:00 506 2017/2/19 7:30 505 2017/2/19 7:00 504 2017/2/19 6:30 503 2017/2/19 6:00 502 2017/2/19 5:30 501 2017/2/19 5:00 500 2017/2/19 4:30 499 2017/2/19 4:00 498 2017/2/19 3:30 497 2017/2/19 3:00 497 2017/2/19 2:30 496 2017/2/19 2:00 495 2017/2/19 1:30 494 2017/2/19 1:00 493 2017/2/19 0:30 492 2017/2/19 0:00 491 max 2017/2/18 23:30 490 2017/2/18 23:00 489 2017/2/18 22:30 488 2017/2/18 22:00 486 2017/2/18 21:30 485 2017/2/18 21:00 484 2017/2/18 20:30 483 2017/2/18 20:00 482 2017/2/18 19:30 481 2017/2/18 19:00 480 2017/2/18 18:30 479 2017/2/18 18:00 478 2017/2/18 17:30 477 2017/2/18 17:00 476 2017/2/18 16:30 475 2017/2/18 16:00 474 2017/2/18 15:30 473 2017/2/18 15:00 472 2017/2/18 14:30 471 2017/2/18 14:00 470 2017/2/18 13:30 470 2017/2/18 13:00 469 2017/2/18 12:30 468 2017/2/18 12:00 468 2017/2/18 11:30 467 2017/2/18 11:00 466 2017/2/18 10:30 466 2017/2/18 10:00 465 2017/2/18 9:30 465 2017/2/18 9:00 465 2017/2/18 8:30 465 2017/2/18 8:00 465 2017/2/18 7:30 465 2017/2/18 7:00 465 2017/2/18 6:30 465 2017/2/18 6:00 465 2017/2/18 5:30 465 2017/2/18 5:00 465 2017/2/18 4:30 465 2017/2/18 4:00 465 2017/2/18 3:30 465 2017/2/18 3:00 465 2017/2/18 2:30 465 2017/2/18 2:00 465 2017/2/18 1:30 465 2017/2/18 1:00 465 2017/2/18 0:30 465 2017/2/18 0:00 465 min 结果491-465 （同时他是17号的最大值）
Tiger_Zhao 2017-03-08
  • 打赏
  • 举报
 回复
语文不行就请拿数据举例：
19日的最大值是多少？最小值是多少？差值应该是多少？
18日的最大值是多少？最小值是多少？差值应该是多少？
班长老六哥 2017-03-08
  • 打赏
  • 举报
 回复
引用 6 楼 sinat_28984567 的回复:
[quote=引用 5 楼 zk12668 的回复:] [quote=引用 3 楼 sinat_28984567 的回复:] 算每天的最大最小差么？那就按日期分组。 SELECT CONVERT(NVARCHAR(10),[time],120), (CASE WHEN (MAX(pv)-MIN(pv))>24 THEN 24 ELSE (MAX(pv)-MIN(pv)) END) AS pv FROM T1 GROUP BY CONVERT(NVARCHAR(10),[time],120)
最大值我想取昨天的零点，有啥办法吗 如果取今天的23:30就不准了[/quote] 最大值是想取明天零点的吧。比如算19号的pv值，是20号零点的减去19号零点的？还是就是想让18号零点的减去19号零点的值？[/quote] 比如算19号的pv值，是20号零点的减去19号零点的。谢谢
二月十六 2017-03-08
  • 打赏
  • 举报
 回复
引用 5 楼 zk12668 的回复:
[quote=引用 3 楼 sinat_28984567 的回复:] 算每天的最大最小差么？那就按日期分组。 SELECT CONVERT(NVARCHAR(10),[time],120), (CASE WHEN (MAX(pv)-MIN(pv))>24 THEN 24 ELSE (MAX(pv)-MIN(pv)) END) AS pv FROM T1 GROUP BY CONVERT(NVARCHAR(10),[time],120)
最大值我想取昨天的零点，有啥办法吗 如果取今天的23:30就不准了[/quote] 最大值是想取明天零点的吧。比如算19号的pv值，是20号零点的减去19号零点的？还是就是想让18号零点的减去19号零点的值？
班长老六哥 2017-03-08
  • 打赏
  • 举报
 回复
引用 3 楼 sinat_28984567 的回复:
算每天的最大最小差么？那就按日期分组。 SELECT CONVERT(NVARCHAR(10),[time],120), (CASE WHEN (MAX(pv)-MIN(pv))>24 THEN 24 ELSE (MAX(pv)-MIN(pv)) END) AS pv FROM T1 GROUP BY CONVERT(NVARCHAR(10),[time],120)
最大值我想取昨天的零点，有啥办法吗 如果取今天的23:30就不准了
xxfvba 2017-03-07
  • 打赏
  • 举报
 回复
-- select case when (b.pv-a.pv)>24 then 24 else b.pv-a.pv end from T a,T b where DATEADD(dd,1,a.[time])=b.time and right(CONVERT(varchar,a.[time]),4)='0:00')
二月十六 2017-03-07
  • 打赏
  • 举报
 回复
算每天的最大最小差么？那就按日期分组。 SELECT CONVERT(NVARCHAR(10),[time],120), (CASE WHEN (MAX(pv)-MIN(pv))>24 THEN 24 ELSE (MAX(pv)-MIN(pv)) END) AS pv FROM T1 GROUP BY CONVERT(NVARCHAR(10),[time],120)
班长老六哥 2017-03-06
  • 打赏
  • 举报
 回复
引用 1 楼 dr592112441 的回复:
没理解 ，具体点 例如。。。
就是通过time字段分组查询，分组后求每天pv的差值，但是分组后把0点的值分组到2017/2/19 0:00 491了 ，20号的差值就不准了
Mirror然 2017-03-06
  • 打赏
  • 举报
 回复
没理解 ，具体点 例如。。。
oracle查询天分组,oracle日期分组查询的完整实例
，但是现在有种情况，时间有可能不是整点开始，会有半点的情况，9点30，需要按照9点30-10点30,10点30-11点30分组统计实现以时间范围起始时间作为参照时间，计算与其差值(分)/所需时间间隔(1小时)得出值作为分组依据...
MySQL计算结束时间和开始时间的差值，并统计差值
--方法一：将查询的结果作为一个新的表 --注意：要给新的表取一个别名，不然会报 Every derived table must have its own alias 错误 select SUM(time) as result FROM((select TIMESTAMPDIFF(SECOND,t.start_time,t...
mysql分组函数按月份差_mysql 日期函数 按天,按周,按月,按年分组 统计数据
内置日期函数now(): 返回当前的日期和时间unix_timestamp(): 返回自1970-01-01 00:00:00到当前时间的秒数差(时间戳)unix_timestamp(指定时间): 返回自1970-01-01 00:00:00到指定时间的秒数差(时间戳)from_unixtim...
SQL查找连续日期并分组
实现分组具体步骤 1）先对date1进行排序，见第1列 2）使用row_number()添加序号，见第2列 3）用lag() over() 函数取上一条日期记录，见第3列 4）用date_diff函数计算当前日期与上一条记录日期的差值，见第4列 5）用...
使用EXCEL进行数据分组分析法
分组分析是将总体数据按照某一特征性质划分成不同的部分和类型进行研究，从而深入分析其内在规律。 一、为什么要分组？ 对于大量的数据，如果不进行分组是很难发现其中规律，找到不同组别之间的关系，从而更好地...
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章