Hi I would really appreciate some help onundefined this one as I've tried various methods and can't get this to

work on

TABLE 1 (LOGDATA)

ip service hit

1.1.1.1 UDP 200
1.1.1.1 UDP 100
1.1.1.1 TCP 300
1.1.1.1 HTTP 100
2.2.2.2 UDP 200
2.2.2.2 TCP 400

I GOT AN REQUIREMENT TO COPY DATA FROM ABOVE TABLE1(LOGDATA) TO TABLE2 (RANK).
AND I AM LOOKING OUTPUT LIKE BELOW.

TABLE 2 (RANK)

ip service hit RANK1 RANK2

1.1.1.1 UDP 300 1 1
1.1.1.1 TCP 200 1 2
1.1.1.1 HTTP 100 1 3
2.2.2.2 UDP 200 2 2
2.2.2.2 TCP 400 2 1

IT SHOULD UPDATE RANK1 LIKE GROUP BY IP AND ORDER HIT, HIGEST HITS IP WILL BE RANK1.
AND
RANK2 SHOULD BE LIKE WHERE IP=IP GROUP BY SERVICE ORDER BY HIT.

Maybe it can be done with joins but I can't get my head around it.

I would greatly appreciate it if someone could point me in the right direction.


THANKS IN ADVANCE.

MAVEN

the easiest way to obtain the rankings is by creating a temp table with an auto_increment column and then inserting the results from a query which has an ORDER BY

do that twice, to 2 different temp tables

then do your insert into the final table by inserting the results of a join

Hi,

I have tried with below query. I am not sure how to get help of temp tables in below query. Please guide me how to proceed further. Tell me, the way i am doing is correct way or not.

insert into rank1 (ip,service,hit)
SELECT
Logdata.ip as ip,
Logdata.service,
sum(Logdata.hit) as hit,
from
(
SELECT
logdateid,
ip,
service,
sum(hit) as hit
from
Logdata
group by ip
order by hit
desc limit 100
) as aac
RIGHT JOIN
Logdata
ON aac.ip=Logdata.ip
group by
Logdata.ip
order by aac.hit desc

Thanks for your help.

Hi,

Thanks for your response. I have tried with creating 2 temp tables (ranking1, ranking2). But its taking toomuch time to execute query, when there are more records in DB. At present I have tested with around 637434 records in table1. Please let me know is there any other way to get better performance?

Thanks.

yes, index your tables on any columns that are used in a join.

hi,

In above query rank2 should be given for one particuar IP.

Result should be like below.

IP - SERVICE - HIT - RANK1 - RANK2

1.1.1.1 - TCP - 300 - 1 - 2
1.1.1.1 - UDP - 400 - 1 - 1
2.2.2.2 - TCP - 150 - 2 - 1
2.2.2.2 - UDP - 100 - 2 - 2

Hits IP 1.1.1.1 using UDP are 400 so rank2 should be 1
and TCP 300, rank2 2.
Query which you gave is grouping on total records.

Please guide how to change this, to get better performance.

Thanks.

ah, i see, rank2 has to start over at 1 for each new ip

okay, where i defined and populate table ranking2, try replacing that part of the code with this --
and have a look at those results to make sure they're working before proceeding to adjust the join that brings them together with the ranking1 results

Hi,

Thanks for your help. Below query is generating duplicate records in table2 (I think we have to do group by serive). Just I want to make sure, I am following correctly. Please check this once and correct me if I am wrong.

insert into table2(ip,service,hit,rank1,rank2)
select table1.ip
, table1.service
, table1.hit
, ranking1.rank1
, ranking2.rank2
from table1
inner
join ranking1
on table1.ip
= ranking1.ip
inner
join ranking2
on table1.service
= ranking2.service
order
by ranking1.rank1
, ranking2.rank2;


And I want to take only top 100(group by ip) records from table1. Where can I include this requirement in this.
Sorry to giving you trouble, by asking again.

Thanks for your help.

inner
join ranking2
on table1.ip
= ranking2.ip
and table1.service
= ranking2.service

Hi,

I have tried before by adding above condition, but its still giving duplicate records in table2.

Thanks.

hi,

And also it has to group services, if particular ip has same service then it should sum like below

Table 1 :

ip - service - hit
1.1.1.1-UDP-200
1.1.1.1-UDP-100
1.1.1.1-TCP-200
2.2.2.2-UDP-200


Table 2 will be like :

ip - service - hit - rank1 - rank2
1.1.1.1-UDP-300 - 1 - 1
1.1.1.1-TCP-200 - 1 - 2
2.2.2.2-UDP-200 - 2 - 1

sorry, I think I have not given inputs properly in my initial mail.

Thanks.

please show some rows from your ranking2 table

Hi,

TABLE 1
ip-service-hit
1.1.1.1-UDP-300
1.1.1.1-UDP-100
1.1.1.1-TCP-200
1.1.1.1-HTTP-100
2.2.2.2-UDP-200
2.2.2.2-TCP-400


TABLE 2

IP-SERVICE-HIT-RANK1-RANK2
2.2.2.2-TCP-400-1-1
2.2.2.2-UDP-200-1-2
1.1.1.1-UDP-200-2-1
1.1.1.1-UDP-100-2-1
1.1.1.1-TCP-200-2-2
1.1.1.1-UDP-200-2-3
1.1.1.1-UDP-100-2-3

EXPECTED RESULT :

IP-SERVICE-HIT-RANK1-RANK2
1.1.1.1-UDP-400-1-1
1.1.1.1-TCP-200-1-2
1.1.1.1-HTTP-100-1-3
2.2.2.2-TCP-400-2-1
2.2.2.2-UDP-200-2-2

Thanks.

Hi,

TABLE 1
ip-service-hit
1.1.1.1-UDP-300
1.1.1.1-UDP-100
1.1.1.1-TCP-200
1.1.1.1-HTTP-100
2.2.2.2-UDP-200
2.2.2.2-TCP-400


TABLE 2 (present query result)

IP-SERVICE-HIT-RANK1-RANK2
2.2.2.2-TCP-400-1-1
2.2.2.2-UDP-200-1-2
1.1.1.1-UDP-200-2-1
1.1.1.1-UDP-100-2-1
1.1.1.1-TCP-200-2-2
1.1.1.1-UDP-200-2-3
1.1.1.1-UDP-100-2-3

table2 EXPECTED RESULT :

IP-SERVICE-HIT-RANK1-RANK2
1.1.1.1-UDP-400-1-1
1.1.1.1-TCP-200-1-2
1.1.1.1-HTTP-100-1-3
2.2.2.2-TCP-400-2-1
2.2.2.2-UDP-200-2-2

Thanks.