Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2004
    Posts
    17

    Unanswered: problem with group by

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table ranking1
    ( rank1 integer not null primary key auto_increment
    , ip varchar(15)
    , hit integer
    );
    insert into ranking1 (ip, hit) 
    select ip, sum(hit) from table1
    group by ip
    order by sum(hit) desc
    ;
    create table ranking2
    ( rank2 integer not null primary key auto_increment
    , service varchar(3)
    , hit integer
    );
    insert into ranking2 (service, hit) 
    select service, sum(hit) from table1
    group by service
    order by sum(hit) desc
    ;
    insert into table2
    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
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    17

    please guide me to proceed further

    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.

  5. #5
    Join Date
    Nov 2004
    Posts
    17

    not getting performance with temp tables.

    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    yes, index your tables on any columns that are used in a join.

  7. #7
    Join Date
    Nov 2004
    Posts
    17

    rank2 problem ....

    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    create table ranking2
    ( ip varchar(15)
    , rank2 integer not null auto_increment
    , primary key (ip,rank2)
    , service varchar(3)
    , hit integer
    );
    insert into ranking2 (ip,service,hit) 
    select ip, service, hit from table1
    order by 1p, hit desc
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Posts
    17

    generating duplicate records in table 2.

    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    inner
    join ranking2
    on table1.ip
    = ranking2.ip
    and table1.service
    = ranking2.service
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Posts
    17

    duplicate records

    Hi,

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

    Thanks.

  12. #12
    Join Date
    Nov 2004
    Posts
    17
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show some rows from your ranking2 table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2004
    Posts
    17
    Quote Originally Posted by maven
    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.
    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.

  15. #15
    Join Date
    Nov 2004
    Posts
    17

    table2 data

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •