表 T_table (id int , name varchar(2),Jvalue int , Jdate date )表T_table 中每天产生10万条数据数据格式: 1,J1,100,2008-08-09 00:00:12
2,J1,100,2008-08-09 00:00:43
3,J1,100,2008-08-09 00:00:24 4,J2,100,2008-08-09 01:01:24
5,J2,100,2008-08-09 03:02:24
6,J3,100,2008-08-09 00:00:24
7,J4,100,2008-08-09 22:00:24
........
10000,j4,32,2008-08-09 23:00:24我想按照如下格式统计出来每天0---23 点的值:
j1,jvalue,(超过0点后的第一个值),(超过1后点的第一个值),......,(超过23后点的第一个值)
j2,.........
j3.........
我写了个 sql语句 由于数据量比较大,查询出来那是相当慢,有时卡死
select
(select a.Jvalue from T_table a where a.name=c.name and to_char(a.jdate,'yyyy-mm-dd hh24:mi:ss')=
(select min(to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss')) from T_table b where to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss') >='2008-08-09 00:00:00' and to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss') < '2008-08-09 01:00:00'
and b.name=c.name
) //0点数据一次类推 到23点from T_table c(这样写sql语句比较长,而且速度慢的根本不出来)希望大家能给个建议,怎么样写这个sql语句,是数据查询速度比较快
2,J1,100,2008-08-09 00:00:43
3,J1,100,2008-08-09 00:00:24 4,J2,100,2008-08-09 01:01:24
5,J2,100,2008-08-09 03:02:24
6,J3,100,2008-08-09 00:00:24
7,J4,100,2008-08-09 22:00:24
........
10000,j4,32,2008-08-09 23:00:24我想按照如下格式统计出来每天0---23 点的值:
j1,jvalue,(超过0点后的第一个值),(超过1后点的第一个值),......,(超过23后点的第一个值)
j2,.........
j3.........
我写了个 sql语句 由于数据量比较大,查询出来那是相当慢,有时卡死
select
(select a.Jvalue from T_table a where a.name=c.name and to_char(a.jdate,'yyyy-mm-dd hh24:mi:ss')=
(select min(to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss')) from T_table b where to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss') >='2008-08-09 00:00:00' and to_char(b.jdate,'yyyy-mm-dd hh24:mi:ss') < '2008-08-09 01:00:00'
and b.name=c.name
) //0点数据一次类推 到23点from T_table c(这样写sql语句比较长,而且速度慢的根本不出来)希望大家能给个建议,怎么样写这个sql语句,是数据查询速度比较快
Jvalue ,
max(case when dt = '00' then Jdat end) t01,
max(case when dt = '01' then Jdat end) t02,
...
max(case when dt = '23' then Jdat end) t03
from
(
select name , Jvalue , to_char(Jdate,'HH24') dt, Jdate , row_number() over(partition by name , to_char(Jdate,'YYYY-MM-DD HH24') order by Jdate) px from T_table where substr(to_char(Jdate,'HH24:MI:SS'),4,5) <> '00:00'
) m
where px = 1
group by name , Jvalue
Jvalue ,
max(case when dt = '00' then Jdate end) t01,
max(case when dt = '01' then Jdate end) t02,
...
max(case when dt = '23' then Jdate end) t03
from
(
select name , Jvalue , to_char(Jdate,'HH24') dt, Jdate , row_number() over(partition by name , to_char(Jdate,'YYYY-MM-DD HH24') order by Jdate) px from T_table where substr(to_char(Jdate,'HH24:MI:SS'),4,5) <> '00:00'
) m
where px = 1
group by name , Jvalue
select * from t_table a
where not exists(select 1 from t_table b
where a.name=b.name and trunc(a.Jdate,'HH24')=trunc(b.Jdate,'HH24')
and a.id>b.id
)
已写入 file afiedt.buf 1 with tb as (
2 select 1 as id ,'J1' as name ,100 as Jvalue,to_date('2008-08-09 00:00:12','YYYY-MM-DD HH24:MI
3 UNION ALL SELECT 2,'J1',100,to_date('2008-08-09 00:00:43','YYYY-MM-DD HH24:MI:SS') FROM DUAL
4 UNION ALL SELECT 3,'J1',100,to_date('2008-08-09 00:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
5 UNION ALL SELECT 4,'J2',100,to_date('2008-08-09 01:01:24' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL
6 UNION ALL SELECT 5,'J2',100,to_date('2008-08-09 03:02:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
7 UNION ALL SELECT 6,'J3',100,to_date('2008-08-09 00:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
8 UNION ALL SELECT 7,'J4',100,to_date('2008-08-09 22:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL)
9 select name,jvalue,to_char(jdate,'YYYY-MM-DD HH24:MI:SS') dt
10 from tb a
11 where not exists(select 1 from tb b
12 where a.name=b.name and trunc(a.jdate,'hh')=trunc(b.jdate,'hh')
13* and to_char(a.jdate,'mi:ss')>to_char(b.jdate,'mi:ss'))
SQL> /NA JVALUE DT
-- ---------- -------------------
J3 100 2008-08-09 00:00:24
J2 100 2008-08-09 03:02:24
J1 100 2008-08-09 00:00:12
J2 100 2008-08-09 01:01:24
J4 100 2008-08-09 22:00:24
1 with tb as (
2 select 1 as id ,'J1' as name ,100 as Jvalue,to_date('2008-08-09 00:00:12','YYYY-MM-DD HH24:MI
3 UNION ALL SELECT 2,'J1',100,to_date('2008-08-09 00:00:43','YYYY-MM-DD HH24:MI:SS') FROM DUAL
4 UNION ALL SELECT 3,'J1',100,to_date('2008-08-09 00:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
5 UNION ALL SELECT 4,'J2',100,to_date('2008-08-09 01:01:24' ,'YYYY-MM-DD HH24:MI:SS') FROM DUAL
6 UNION ALL SELECT 5,'J2',100,to_date('2008-08-09 03:02:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
7 UNION ALL SELECT 6,'J3',100,to_date('2008-08-09 00:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL
8 UNION ALL SELECT 7,'J4',100,to_date('2008-08-09 22:00:24','YYYY-MM-DD HH24:MI:SS') FROM DUAL)
9 select name,jvalue,to_char(jdate,'YYYY-MM-DD HH24:MI:SS') dt
10 from tb a
11 where not exists(select 1 from tb b
12 where a.name=b.name and trunc(a.jdate,'hh24')=trunc(b.jdate,'hh24')
13* and to_char(a.jdate,'mi:ss')>to_char(b.jdate,'mi:ss'))
SQL> /NA JVALUE DT
-- ---------- -------------------
J3 100 2008-08-09 00:00:24
J2 100 2008-08-09 03:02:24
J1 100 2008-08-09 00:00:12
J2 100 2008-08-09 01:01:24
J4 100 2008-08-09 22:00:24