MyException - 我的异常网
当前位置:我的异常网» Sql Server » 依照指定的时间段统计

依照指定的时间段统计

www.MyException.Cn  网友分享于:2015-01-22  浏览:0次
按照指定的时间段统计
现有如下表:
ID AREA_CODE STATIONID PVALUE  CLTTM SYSTM
315763 542625 285 0 2014-12-29 14:34:33.000 2014-12-29 14:34:33.000
315764 542625 288 0 2014-12-29 14:34:33.000 2014-12-29 14:34:33.000
315762 542625 297 0 2014-12-29 14:33:43.000 2014-12-29 14:33:43.000
315761 542122 278 0 2014-12-29 14:06:24.000 2014-12-29 14:06:24.000
315760 542626 280 0 2014-12-29 13:41:50.000 2014-12-29 13:41:50.000
315759 542626 280 0 2014-12-29 13:24:40.000 2014-12-29 13:24:40.000
315758 542621 281 0 2014-12-29 10:24:27.000 2014-12-29 10:24:27.000
315757 542626 299 0 2014-12-29 10:17:45.000 2014-12-29 10:17:45.000
315756 542626 299 0 2014-12-29 08:50:19.000 2014-12-29 08:50:19.000
315755 542623 279 0 2014-12-29 08:47:46.000 2014-12-29 08:47:46.000
315754 542625 286 0 2014-12-29 08:44:58.000 2014-12-29 08:44:58.000
315753 542621 281 0 2014-12-29 08:44:54.000 2014-12-29 08:44:54.000
315752 542626 280 0 2014-12-29 08:42:40.000 2014-12-29 08:42:40.000
315751 542625 296 0 2014-12-29 08:40:37.000 2014-12-29 08:40:37.000
315750 542625 290 0 2014-12-29 08:39:55.000 2014-12-29 08:39:55.000
315749 542621 283 0 2014-12-29 08:38:57.000 2014-12-29 08:38:57.000
315747 542625 288 0 2014-12-29 08:34:34.000 2014-12-29 08:34:34.000
315748 542625 285 0 2014-12-29 08:34:34.000 2014-12-29 08:34:34.000
315746 542625 297 0 2014-12-29 08:32:49.000 2014-12-29 08:32:49.000
315745 542122 278 0 2014-12-29 08:06:31.000 2014-12-29 08:06:31.000
315744 542122 278 0 2014-12-29 08:06:28.000 2014-12-29 08:06:28.000
315734 542336 3797 0 2014-12-29 08:00:00.000 2014-12-29 07:47:53.000
315736 542336 3797 0 2014-12-29 08:00:00.000 2014-12-29 07:47:53.000
315738 542336 3797 0 2014-12-29 08:00:00.000 2014-12-29 07:47:53.000
315740 542336 3796 0 2014-12-29 08:00:00.000 2014-12-29 07:53:58.000
315741 542336 3800 0 2014-12-29 07:58:14.000 2014-12-29 07:58:14.000
315742 542336 3800 0 2014-12-29 07:58:14.000 2014-12-29 07:58:14.000
315743 542336 3800 0 2014-12-29 07:58:14.000 2014-12-29 07:58:14.000
315739 542336 3796 0 2014-12-29 07:53:57.000 2014-12-29 07:53:57.000
315733 542336 3797 0 2014-12-29 07:47:53.000 2014-12-29 07:47:53.000
315735 542336 3797 0 2014-12-29 07:47:53.000 2014-12-29 07:47:53.000
315737 542336 3797 0 2014-12-29 07:47:53.000 2014-12-29 07:47:53.000
315732 542626 299 0 2014-12-29 02:50:19.000 2014-12-29 02:50:19.000
315731 542623 279 0 2014-12-29 02:47:47.000 2014-12-29 02:47:47.000
315730 542625 294 0 2014-12-29 02:45:56.000 2014-12-29 02:45:56.000
315729 542625 286 0 2014-12-29 02:44:58.000 2014-12-29 02:44:58.000
315728 542621 281 0 2014-12-29 02:44:55.000 2014-12-29 02:44:55.000
315727 542626 280 0 2014-12-29 02:42:40.000 2014-12-29 02:42:40.000
315726 542625 296 0 2014-12-29 02:40:30.000 2014-12-29 02:40:30.000
315725 542625 290 0 2014-12-29 02:39:55.000 2014-12-29 02:39:55.000
315724 542621 283 0 2014-12-29 02:38:57.000 2014-12-29 02:38:57.000
315723 542625 288 0 2014-12-29 02:34:35.000 2014-12-29 02:34:35.000
315722 542625 285 0 2014-12-29 02:34:34.000 2014-12-29 02:34:34.000
315721 542625 297 0 2014-12-29 02:32:51.000 2014-12-29 02:32:51.000
315720 542122 278 0 2014-12-29 02:06:23.000 2014-12-29 02:06:23.000
315719 542626 299 0 2014-12-28 20:50:18.000 2014-12-28 20:50:18.000
315718 542623 279 0 2014-12-28 20:47:46.000 2014-12-28 20:47:46.000
315717 542625 294 0 2014-12-28 20:45:58.000 2014-12-28 20:45:58.000
315715 542621 281 0 2014-12-28 20:44:57.000 2014-12-28 20:44:57.000
315716 542625 286 0 2014-12-28 20:44:57.000 2014-12-28 20:44:57.000
315714 542626 280 0 2014-12-28 20:42:41.000 2014-12-28 20:42:41.000
315713 542625 296 0 2014-12-28 20:40:36.000 2014-12-28 20:40:36.000
315712 542625 290 0 2014-12-28 20:39:55.000 2014-12-28 20:39:55.000
315711 542621 283 0 2014-12-28 20:38:57.000 2014-12-28 20:38:57.000
315710 542625 285 0 2014-12-28 20:34:34.000 2014-12-28 20:34:34.000
315709 542625 288 0 2014-12-28 20:34:33.000 2014-12-28 20:34:33.000
315708 542122 278 0 2014-12-28 20:06:31.000 2014-12-28 20:06:31.000
315707 542122 278 0 2014-12-28 20:06:28.000 2014-12-28 20:06:28.000
315706 542336 3800 0 2014-12-28 19:58:04.000 2014-12-28 19:58:04.000
315704 542336 3796 0 2014-12-28 19:54:07.000 2014-12-28 19:54:07.000
315702 542336 3797 0 2014-12-28 19:47:38.000 2014-12-28 19:47:38.000
315701 542626 299 0 2014-12-28 14:50:19.000 2014-12-28 14:50:19.000
315700 542623 279 0 2014-12-28 14:47:45.000 2014-12-28 14:47:45.000
315699 542625 294 0 2014-12-28 14:45:55.000 2014-12-28 14:45:55.000
315698 542625 286 0 2014-12-28 14:44:59.000 2014-12-28 14:44:59.000
315697 542621 281 0 2014-12-28 14:44:55.000 2014-12-28 14:44:55.000
315696 542626 280 0 2014-12-28 14:42:44.000 2014-12-28 14:42:44.000
315695 542625 290 0 2014-12-28 14:39:55.000 2014-12-28 14:39:55.000
315694 542621 283 0 2014-12-28 14:38:58.000 2014-12-28 14:38:58.000
315693 542625 288 0 2014-12-28 14:34:35.000 2014-12-28 14:34:35.000
315692 542625 285 0 2014-12-28 14:34:33.000 2014-12-28 14:34:33.000
315691 542122 278 0 2014-12-28 14:06:23.000 2014-12-28 14:06:23.000
315690 542626 299 0 2014-12-28 08:50:19.000 2014-12-28 08:50:19.000
315689 542623 279 0 2014-12-28 08:47:46.000 2014-12-28 08:47:46.000
315688 542625 286 0 2014-12-28 08:44:58.000 2014-12-28 08:44:58.000
315687 542621 281 0 2014-12-28 08:44:56.000 2014-12-28 08:44:56.000
315686 542626 280 0 2014-12-28 08:42:40.000 2014-12-28 08:42:40.000
315685 542625 296 0 2014-12-28 08:40:31.000 2014-12-28 08:40:31.000
315684 542625 290 0 2014-12-28 08:39:55.000 2014-12-28 08:39:55.000
315683 542621 283 0 2014-12-28 08:38:59.000 2014-12-28 08:38:59.000
315681 542625 288 0 2014-12-28 08:34:35.000 2014-12-28 08:34:35.000
315682 542625 285 0 2014-12-28 08:34:35.000 2014-12-28 08:34:35.000
315680 542625 297 0 2014-12-28 08:33:30.000 2014-12-28 08:33:30.000
315679 542122 278 0 2014-12-28 08:06:23.000 2014-12-28 08:06:23.000
315677 542336 3796 0 2014-12-28 08:00:00.000 2014-12-28 07:54:18.000
315703 542336 3797 0 2014-12-28 08:00:00.000 2014-12-28 19:47:38.000
315705 542336 3796 0 2014-12-28 08:00:00.000 2014-12-28 19:54:07.000
315678 542336 3800 0 2014-12-28 07:58:08.000 2014-12-28 07:58:08.000
315676 542336 3796 0 2014-12-28 07:54:18.000 2014-12-28 07:54:18.000
315675 542626 299 0 2014-12-28 02:50:18.000 2014-12-28 02:50:18.000
315674 542623 279 0 2014-12-28 02:47:45.000 2014-12-28 02:47:45.000
315673 542625 294 0 2014-12-28 02:45:55.000 2014-12-28 02:45:55.000
315672 542625 286 0 2014-12-28 02:44:57.000 2014-12-28 02:44:57.000
315671 542621 281 0 2014-12-28 02:44:55.000 2014-12-28 02:44:55.000
315670 542626 280 0 2014-12-28 02:42:40.000 2014-12-28 02:42:40.000
315669 542625 296 0 2014-12-28 02:40:31.000 2014-12-28 02:40:31.000
315668 542625 290 0 2014-12-28 02:39:55.000 2014-12-28 02:39:55.000
315667 542621 283 0 2014-12-28 02:38:57.000 2014-12-28 02:38:57.000
315666 542625 297 0 2014-12-28 02:34:40.000 2014-12-28 02:34:40.000
315665 542625 288 0 2014-12-28 02:34:38.000 2014-12-28 02:34:38.000
求助问题: 我想按照小时统计 30分钟统计 10分钟统计 统计的时间为CLTTM字段
小时统计为: 一天分为24小时,把clttm 属于同一小时下的pvalue 加起来求sum
30分钟统计: 一天分成多个30分钟,把clttm属于同一个 30分钟范围内的pvalue 加起来求sum
10分钟统计: 一天分成多个10分钟,把clttm属于同一个 10分钟范围的pvalue 加起来求sum

结果如:
小时统计:(自制结果非准确数据)
STATIONID   PVALUE     时间
285                0                2014-09-10 12:00:00
285                3                2014-09-10 13:00:00
285               1                 2014-09-10 14:00:00

30分钟统计:
STATIONID   PVALUE     时间
285                0                2014-09-10 12:00:00
285                1                2014-09-10 12:30:00
285               1                 2014-09-10 13:00:00
285               3                 2014-09-10 13:30:00
285               1                 2014-09-10 14:00:00

10分钟统计:
STATIONID   PVALUE     时间
285                0                2014-09-10 12:00:00
285                1                2014-09-10 12:10:00
285               1                 2014-09-10 12:20:00
285               3                 2014-09-10 12:30:00
285               1                 2014-09-10 12:40:00
285                0                2014-09-10 12:50:00
285                1                2014-09-10 13:00:00
285               1                 2014-09-10 13:10:00
285               3                 2014-09-10 13:20:00
285               1                 2014-09-10 13:30:00
------解决思路----------------------
WITH t1 AS (
    SELECT STATIONID,PVALUE,CLTTM,
           Convert(datetime,Convert(varchar(10),CLTTM,120)) dt
      FROM table1
     WHERE CLTTM >= '2014-12-28'
       AND CLTTM <  '2014-12-29'
)
,t2 AS (
    SELECT STATIONID,PVALUE,
           DateAdd(second,
                   (DateDiff(second,dt,CLTTM) / 600) * 600, /* 600秒: 10分钟,其他自己调整 */
                   dt) tm
      FROM t1
)
    SELECT STATIONID,
           SUM(PVALUE) PVALUE,
           tm 时间
      FROM t2
  GROUP BY STATIONID,tm
  ORDER BY STATIONID,tm

------解决思路----------------------
24小时/30分钟,等于48(48是这个意思)
24小时/1小时,等于24;30换成60没有错
24小时/10分钟,等144;30换成10
SELECT T2.STATIONID,SUM(CASE WHEN DATEDIFF(MINUTE,DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120)),CLTTM)<30
        AND DATEDIFF(MINUTE,DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120)),CLTTM)>=0
    THEN T2.PVALUE END)PVALUE--就是换这里THEN T2.PVALUE END)PVALUE
    ,DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120))
FROM master..spt_values T1
    LEFT JOIN TB T2 ON DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120))<DATEADD(DAY,1,CONVERT(VARCHAR(10),CLTTM,120))
WHERE T1.type='P'AND T1.number<48
GROUP BY T2.STATIONID,DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120))

文章评论

程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
老程序员的下场
老程序员的下场
一个程序员的时间管理
一个程序员的时间管理
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
编程语言是女人
编程语言是女人
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
 程序员的样子
程序员的样子
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
我是如何打败拖延症的
我是如何打败拖延症的
如何成为一名黑客
如何成为一名黑客
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
Java程序员必看电影
Java程序员必看电影
我的丈夫是个程序员
我的丈夫是个程序员
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
代码女神横空出世
代码女神横空出世
每天工作4小时的程序员
每天工作4小时的程序员
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
漫画:程序员的工作
漫画:程序员的工作
程序员的鄙视链
程序员的鄙视链
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
10个调试和排错的小建议
10个调试和排错的小建议
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员都该阅读的书
程序员都该阅读的书
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
旅行,写作,编程
旅行,写作,编程
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
那些争议最大的编程观点
那些争议最大的编程观点
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
中美印日四国程序员比较
中美印日四国程序员比较
鲜为人知的编程真相
鲜为人知的编程真相
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有