select t1.ip, count(ip) aanum, d15mi
from RPT_BAK_20120512 t1,
where t1.type = '2'
group by ip,d15mi
结果：
ip aanum d15mi
10.138.250.103 18 201205120015
10.136.16.197 8 201205120015
10.255.235.223 1 201205120130
10.179.185.209 3 201205120130
10.137.59.23 10 201205120215
10.137.69.234 7 201205120245
10.136.222.6 3 201205120245
10.136.15.154 2 201205120245
10.139.151.42 52 201205120330
10.136.19.105 2 201205120330
10.4.2.11 2 2 201205120345
10.96.30.36 1 201205120345
10.136.35.8 1 201205120345
10.196.130.136 1 201205120400
10.1236.135.18 4 201205120400
现在想要的结果列是
dday,ip ,aanum ,mi15,mi30,mi45,mi60
dday 为2012051204 这样到小时的时间字段
mi15 时间最后两位是 15,mi30 时间最后两位是 30,
mi45 时间最后两位是 45,mi60 时间最后两位是 00
对于同一个ip在同一个小时里面，mi15,mi30,mi45,mi60 都是有值的
求这sql如何写？
谢谢。
[code="sql"]
select substr(t1.d15mi, 0, 10) dtime,
ip,
sum(case
when substr(t1.d15mi, -2) = '15' then
1
end) mi15,
sum(case
when substr(t1.d15mi, -2) = '30' then
1
end) mi30,
sum(case
when substr(t1.d15mi, -2) = '45' then
1
end) mi45,
sum(case
when substr(t1.d15mi, -2) = '00' then
1
end) mi60
from RPT_BAK_20120512 t1
group by ip, substr(t1.d15mi, 0, 10);
[/code]
用sql取不出来的。。。
你可以对现在已有的数据用JAVA或者c代码进行分类啊，根据201205120015字符串(或者是Date)，分成2012051200和15，放到MAP里,Map>
MYSQL 简单的例子
ORACLE 不知道支不支持正则
[code="java"]
SELECT t15.mi15,t30.mi30 FROM (SELECT create_date AS mi15 FROM t_test WHERE create_date REGEXP "15$" ) t15,
(SELECT create_date AS mi30 FROM t_test WHERE create_date REGEXP "30$" ) t30
[/code]
查询结果如下
[code="java"]
+--------------+--------------+
| mi15 | mi30 |
+--------------+--------------+
| 201205120015 | 201205120130 |
| 201205120015 | 201205120130 |
| 201205120215 | 201205120130 |
| 201205120015 | 201205120330 |
| 201205120015 | 201205120330 |
| 201205120215 | 201205120330 |
| 201205120015 | 201205120330 |
| 201205120015 | 201205120330 |
| 201205120215 | 201205120330 |
+--------------+--------------+
[/code]
只能分别统计出来再拼接到一起了：
[code="sql"]
select substr(t.d15min, 0, 10) dtime,
t.ip,
(select count(1)
from RPT_BAK_20120512 mi15
where mi15.ip = t.ip
and mi15.d15min = t.d15min
and substr(mi15.d15min, 11, 2) = '15') MI15,
(select count(1)
from RPT_BAK_20120512 mi30
where mi30.ip = t.ip
and mi30.d15min = t.d15min
and substr(mi30.d15min, 11, 2) = '30') MI30,
(select count(1)
from RPT_BAK_20120512 mi45
where mi45.ip = t.ip
and mi45.d15min = t.d15min
and substr(mi45.d15min, 11, 2) = '45') MI45,
(select count(1)
from RPT_BAK_20120512 mi60
where mi60.ip = t.ip
and mi60.d15min = t.d15min
and substr(mi60.d15min, 11, 2) = '60') MI60
from RPT_BAK_20120512 t
[/code]
用你上面的数据执行的结果：
DTIME IP MI15 MI30 MI45 MI60
1 2012051201 10.255.235.223 0 1 0 0
2 2012051201 10.179.185.209 0 1 0 0
3 2012051202 10.137.59.23 1 0 0 0
4 2012051202 10.137.69.234 0 0 1 0
5 2012051202 10.136.222.6 0 0 1 0
6 2012051202 10.136.15.154 0 0 1 0
7 2012051203 10.139.151.42 0 1 0 0
8 2012051203 10.136.19.105 0 1 0 0
9 2012051203 10.4.2.11 0 0 1 0
10 2012051203 10.96.30.36 0 0 1 0
11 2012051203 10.136.35.8 0 0 1 0
12 2012051204 10.196.130.136 0 0 0 0
13 2012051204 10.1236.135.18 0 0 0 0
14 2012051200 10.138.250.103 1 0 0 0
15 2012051200 10.136.16.197 1 0 0 0
用我上面的例子，在进行联合查询m15 是一个表 a，m30是一个表 b
a.ip=b.ip
[code="java"]
SELECT t15.m15_ip,t15.mi15,t30.mi30 FROM (SELECT m15_ip, count(create_date) AS mi15 FROM t_test WHERE create_date REGEXP "15$" ) t15,
(SELECT m30_ip,count(create_date) AS mi30 FROM t_test WHERE create_date REGEXP "30$" ) t30 where t15.m15_ip = t30.m30_ip
[/code]
时间也是类似，大概就是这么写法，这个语句我没有测试，可能有些细节问题，你自己需修改下 ， t15.m15_ip 这个也可以换成t30 的，因为去相同的部分，所以无所谓用那个，当然语句你可以换成inner join
-- sql 很强大，你这个既不用procedure，也不用function即可完成
-- 你是要行转列 你的需求建议用sum 和 decode 来完成，以下是个参考
select ip,
substr(t1.d15mi, 0, 10) dtime,
sum(case when substr(t1.d15mi, 11, 2) = '15' then 1 end) mi15,
sum(case when substr(t1.d15mi, 11, 2) = '30' then 1 end) mi15,
sum(case when substr(t1.d15mi, 11, 2) = '45' then 1 end) mi15,
sum(case when substr(t1.d15mi, 11, 2) = '60' then 1 end) mi15
from RPT_BAK_20120512 t1
group by ip, substr(t1.d15mi, 0, 10);