WITH t_sjd AS (SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1, TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2 FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT, TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT FROM DUAL) T CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT), t_ysj AS( SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual ) SELECT T.SJ1, sum(S.SHULIANG) FROM T_SJD T, T_YSJ S WHERE TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') >= SJ1 AND TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') <= SJ2 GROUP BY T.SJ1 如果想计算10分钟的频率,将sjd修改为10。 即将 * 5 / 1440修改为 * 10/ 1440
WITH t_sjd AS (SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1, TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2 FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT, TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT FROM DUAL) T CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT), t_ysj AS( SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual ) SELECT T.SJ1, sum(S.SHULIANG) FROM T_SJD T, T_YSJ S WHERE TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') >= SJ1 AND TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') <= SJ2 GROUP BY T.SJ1 如果想计算10分钟的频率,将sjd修改为10。 即将 * 5 / 1440修改为 * 10/ 1440 这样做的话 如果我是一张表的数据,时间间隔可能在三天,那么 t_ysj 要写很多SELECT语句?
你把时间段改成你想要的时间段就可以。 t_sjd中设置时间范围。。
t_ysj AS( SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual ) 替换成你的表即可。。 t_sjd中设置你的时间段及频率。
WITH t_sjd AS (SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1, TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2 FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT, TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT FROM DUAL) T CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT), t_ysj AS( select * from T_PERSON_TRAFFICS_TATISTICS ) SELECT T.SJ1, sum(S.GOCOUNT) FROM T_SJD T, T_YSJ S WHERE TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') >= SJ1 AND TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') <= SJ2 GROUP BY T.SJ1 取出来的值不一样啊。
WITH t_sjd AS (SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1, TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2 FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT, TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT FROM DUAL) T CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT), t_ysj AS( select * from T_PERSON_TRAFFICS_TATISTICS ) SELECT T.SJ1, sum(S.GOCOUNT) FROM T_SJD T, T_YSJ S WHERE TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') >= SJ1 AND TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') <= SJ2 GROUP BY T.SJ1 取出来的值不一样啊。
5min:select st_sj, (select sum(shuliang) from t where t.shijian between st_sj and end_sj) from (select min_sj + (level - 1) * 5 / 1440 st_sj, min_sj + level * 5 / 1440 end_sj from (select trunc(min(shijian), 'mi') min_sj, trunc(max(shijian), 'mi') max_sj from t) connect by min_sj + (level - 1) * 5 / 1440 <= max_sj)10 min:select st_sj, (select sum(shuliang) from t where t.shijian between st_sj and end_sj) from (select min_sj + (level - 1) * 10 / 1440 st_sj, min_sj + level * 10 / 1440 end_sj from (select trunc(min(shijian), 'mi') min_sj, trunc(max(shijian), 'mi') max_sj from t) connect by min_sj + (level - 1) * 10 / 1440 <= max_sj)
WITH t_sjd AS
(SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1,
TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2
FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT,
TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT
FROM DUAL) T
CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT),
t_ysj AS(
SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual
)
SELECT T.SJ1, sum(S.SHULIANG)
FROM T_SJD T, T_YSJ S
WHERE TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') >= SJ1
AND TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') <= SJ2
GROUP BY T.SJ1
如果想计算10分钟的频率,将sjd修改为10。
即将 * 5 / 1440修改为 * 10/ 1440
WITH t_sjd AS
(SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1,
TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2
FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT,
TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT
FROM DUAL) T
CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT),
t_ysj AS(
SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual
)
SELECT T.SJ1, sum(S.SHULIANG)
FROM T_SJD T, T_YSJ S
WHERE TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') >= SJ1
AND TO_CHAR(S.SHIJIAN, 'yyyy-mm-dd hh24:mi:ss') <= SJ2
GROUP BY T.SJ1
如果想计算10分钟的频率,将sjd修改为10。
即将 * 5 / 1440修改为 * 10/ 1440
这样做的话 如果我是一张表的数据,时间间隔可能在三天,那么
t_ysj 要写很多SELECT语句?
t_sjd中设置时间范围。。
SELECT to_date('2014-07-03 10:00:04','yyyy-mm-dd hh24:mi:ss') shijian,10 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:00:07','yyyy-mm-dd hh24:mi:ss'),20 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:01:04','yyyy-mm-dd hh24:mi:ss'),30 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:02:04','yyyy-mm-dd hh24:mi:ss'),40 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:07:04','yyyy-mm-dd hh24:mi:ss'),200 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:08:04','yyyy-mm-dd hh24:mi:ss'),300 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:09:04','yyyy-mm-dd hh24:mi:ss'),400 shuliang FROM dual UNION ALL
SELECT to_date('2014-07-03 10:10:04','yyyy-mm-dd hh24:mi:ss'),1000 shuliang FROM dual
)
替换成你的表即可。。
t_sjd中设置你的时间段及频率。
(SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1,
TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2
FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT,
TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT
FROM DUAL) T
CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT),
t_ysj AS(
select * from T_PERSON_TRAFFICS_TATISTICS
)
SELECT T.SJ1, sum(S.GOCOUNT)
FROM T_SJD T, T_YSJ S
WHERE TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') >= SJ1
AND TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') <= SJ2
GROUP BY T.SJ1
取出来的值不一样啊。
(SELECT TO_CHAR(S_DT + (ROWNUM - 1) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj1,
TO_CHAR(S_DT + (ROWNUM ) * 5 / 1440, 'yyyy-mm-dd hh24:mi:ss') sj2
FROM (SELECT TO_DATE('2014-07-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') S_DT,
TO_DATE('2014-07-03 23:59:59', 'yyyy-mm-dd hh24:mi:ss') E_DT
FROM DUAL) T
CONNECT BY S_DT + (ROWNUM - 1) * 5 / 1440 <= E_DT),
t_ysj AS(
select * from T_PERSON_TRAFFICS_TATISTICS
)
SELECT T.SJ1, sum(S.GOCOUNT)
FROM T_SJD T, T_YSJ S
WHERE TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') >= SJ1
AND TO_CHAR(TO_DATE(S.STARTTIME,'yyyy-MM-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') <= SJ2
GROUP BY T.SJ1
取出来的值不一样啊。
5min:select st_sj,
(select sum(shuliang) from t where t.shijian between st_sj and end_sj)
from (select min_sj + (level - 1) * 5 / 1440 st_sj,
min_sj + level * 5 / 1440 end_sj
from (select trunc(min(shijian), 'mi') min_sj,
trunc(max(shijian), 'mi') max_sj
from t)
connect by min_sj + (level - 1) * 5 / 1440 <= max_sj)10 min:select st_sj,
(select sum(shuliang) from t where t.shijian between st_sj and end_sj)
from (select min_sj + (level - 1) * 10 / 1440 st_sj,
min_sj + level * 10 / 1440 end_sj
from (select trunc(min(shijian), 'mi') min_sj,
trunc(max(shijian), 'mi') max_sj
from t)
connect by min_sj + (level - 1) * 10 / 1440 <= max_sj)