select * from PREDICT_PRESENT_COST t where t.create_date >= TRUNC(sysdate) + 8 / (24) and t.create_date <= TRUNC(sysdate) + 1 + 8 / (24);后面这个可以你自己规定个时间,这次查询是今天八点到明天八点内的数据,不知道这个是不是你想要的,
SELECT STATIONID,COUNT(1)AS PVALUE ,CONVERT(VARCHAR(13),CLTTM,120)+':00:00' AS 时间 FROM TB GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120)SELECT STATIONID,COUNT(1)AS PVALUE ,CONVERT(VARCHAR(13),CLTTM,120)+':'+CAST(RIGHT(CONVERT(varchar(5),CLTTM,24),2)/30*30 AS VARCHAR(10))+':00' AS 时间 FROM TB GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120),RIGHT(CONVERT(varchar(5),CLTTM,24),2)/30*30SELECT STATIONID,COUNT(1)AS PVALUE ,CONVERT(VARCHAR(13),CLTTM,120)+':'+CAST(RIGHT(CONVERT(varchar(5),CLTTM,24),2)/10*10 AS VARCHAR(10))+':00' AS 时间 FROM TB GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120),RIGHT(CONVERT(varchar(5),CLTTM,24),2)/10*10
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
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 1 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))每半小时
24小时/30分钟,等于48(48是这个意思) 24小时/1小时,等于24;30换成60没有错 24小时/10分钟,等144;30换成10SELECT 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))
from PREDICT_PRESENT_COST t
where t.create_date >= TRUNC(sysdate) + 8 / (24)
and t.create_date <= TRUNC(sysdate) + 1 + 8 / (24);后面这个可以你自己规定个时间,这次查询是今天八点到明天八点内的数据,不知道这个是不是你想要的,
,CONVERT(VARCHAR(13),CLTTM,120)+':00:00' AS 时间
FROM TB
GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120)SELECT STATIONID,COUNT(1)AS PVALUE
,CONVERT(VARCHAR(13),CLTTM,120)+':'+CAST(RIGHT(CONVERT(varchar(5),CLTTM,24),2)/30*30 AS VARCHAR(10))+':00' AS 时间
FROM TB
GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120),RIGHT(CONVERT(varchar(5),CLTTM,24),2)/30*30SELECT STATIONID,COUNT(1)AS PVALUE
,CONVERT(VARCHAR(13),CLTTM,120)+':'+CAST(RIGHT(CONVERT(varchar(5),CLTTM,24),2)/10*10 AS VARCHAR(10))+':00' AS 时间
FROM TB
GROUP BY STATIONID,CONVERT(VARCHAR(13),CLTTM,120),RIGHT(CONVERT(varchar(5),CLTTM,24),2)/10*10
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
谢谢你的回答 这个是执行结果 30分钟统计
STATIONID PVALUE 时间
288 1 2014-12-29 14:30:00
288 1 2014-12-29 08:30:00
288 1 2014-12-29 02:30:00
288 1 2014-12-28 20:30:00
288 1 2014-12-28 14:30:00
288 1 2014-12-28 08:30:00
288 2 2014-12-28 02:30:00
288 1 2014-12-27 20:30:00
288 1 2014-12-27 14:30:00
288 1 2014-12-27 08:30:00
288 1 2014-12-27 02:30:00但是这个时间好像没有把一天平均分成30分钟时段 如果那个时段没有数据 pvalue 是0
AND DATEDIFF(MINUTE,DATEADD(MINUTE,30*T1.number,CONVERT(VARCHAR(10),CLTTM,120)),CLTTM)>=0
THEN 1 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))每半小时
STATIONID PVALUE shijian
288 NULL 2014-08-30 07:30:00.000
288 NULL 2014-08-30 07:00:00.000
288 NULL 2014-08-30 06:30:00.000
288 NULL 2014-08-30 06:00:00.000
288 4 2014-08-30 05:30:00.000
288 NULL 2014-08-30 05:00:00.000
288 1 2014-08-30 04:30:00.000
288 1 2014-08-30 04:00:00.000
288 NULL 2014-08-30 03:30:00.000针对这个时间段的 原始数据 如下:
id AREA_CODE STATIONID PVALUE CLTTM
243968 542625 288 0 2014-08-30 05:46:38.000
247460 542625 288 0 2014-08-30 05:46:38.000
247461 542625 288 0 2014-08-30 05:46:38.000
248644 542625 288 0.5 2014-08-30 05:46:38.000
247305 542625 288 0.5 2014-08-30 04:59:48.000
247304 542625 288 0.5 2014-08-30 04:29:43.000
250216 542625 288 0.5 2014-08-30 03:03:16.000
248643 542625 288 0.5 2014-08-30 02:49:59.000
统计的出现问题,这个时间段2014-08-30 05:30:00.000 没有那么大的数据
这个地方换下
24小时/1小时,等于24;30换成60没有错
24小时/10分钟,等144;30换成10SELECT 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))