Creating a MySQL Procedure

Question

I have an objective to achieve using MySQL Procedures where data will be queried from a table and some other relative data has to be get from the same table as well. I'd be grateful if somebody could help me through.

This is what the table looks like:

+----------+------------+-----------+----------+----------+------+
| id       | src_ip     | dst_ip    | src_port | dst_port | data | 
+----------+------------+-----------+----------+----------+------+
| 12000000 |     796928 | 1911291904|       14 |     2048 |   64 | 
| 12000001 |     796933 |  316866560|       14 |     2048 |  126 | 
| 12000002 |     796946 | 4081388040|    49155 |    45338 |  122 | 
| 12000003 |     796951 | 3085830664|    36108 |    49155 |  146 | 
| 12000004 |     796946 | 4081388040|       14 |     2048 |  150 | 
| 12000005 |     796950 | 3085830664|    45338 |    49155 |  194 | 
+----------+------------+-----------+----------+----------+------+

The data to be extracted here is something as follows:

query_get = "select distinct(src_ip), dst_ip from user_activity_load"

which gives me distinct src_ip communication with dst_ip. Somewhat like:

+--------+------------+
| src_ip | dst_ip     |
+--------+------------+
| 796928 | 1911291904 |
| 796928 | 1911294472 |
| 796933 |  316866560 |
| 796946 | 1925840896 |
| 796946 | 4081385472 |
| 796946 | 4081388040 |
+--------+------------+

Now,each of this row is to be take and queried separately in order to get the data part for all the communications that took place and add them to be displayed in a separate table which would be my end result.

+--------+------------+------+
| src_ip | dst_ip     | data |
+--------+------------+------+
| 796928 | 1911291904 |   64 |
| 796928 | 1911291904 |   64 |
| 796928 | 1911291904 |   64 |
| 796928 | 1911291904 |   64 |
| 796928 | 1911291904 |   64 |
+--------+------------+------+

This'd be my End-result table which I'd like to achieve after all the queries being run and as the Ouput:

+------------+------------+------+
| src_ip     | dst_ip     | data |
+------------+------------+------+
| 796928     | 1911291904 |   320|
| 796933     |  316866560 |  1240|
| 796933     |  316866560 |  1029|
| 27969233   |  312322311 | 11780|
| 316866560  | 1192808377 | 21450|
| 8596933    |  316866560 |  1320|
+------------+------------+------+

Where all the unique communications are visible with all the data used amongst all the communications that ever occured between them. Because I'm not adept at writing MySQL Procedures, the thing I tried did not give me the result I am trying to achieve and because I'm not a database guy, but since I need to get this done, I did try something as it is not plausible to ask for help before trying it ourselves. Here's something I tried:

CREATE procedure getIPActivity(
 )
 begin
  DECLARE src_ip text;
  DECLARE dst_ip text;
  DECLARE x INT;
  DECLARE data INT;
  DECLARE sip varchar(20)
  DECLARE dip varchar(20)
  DECLARE cnt INT;
  SET data = 0;
  select
    distinct(INET_NTOA(ui.src_ip)) into @src, INET_NTOA(ui.dst_ip) into @dst from
    user_activity_load as ui limit 100;
  SET sip = @src;
  SET dip = @dst;


select
    count(*) into @count from user_activity_load where src_ip = sip and dst_ip = dip; 
    SET cnt = @count;
      REPEAT
     SET data = data + (select data from user_activity_load where src_ip = sip and dst_ip = dip);
     SET  x = x + 1;
            UNTIL x  > cnt
            END REPEAT;
      select
        distinct(INET_NTOA(ui.src_ip)) as src_ip, INET_NTOA(ui.dst_ip) as dst_ip, data from
        user_activity_load as ui limit 100;
    end ;;
    delimiter ;

Again, because I'm not somebody who's very good at MySQL procedure, I tried my hand at it and failed. My objective is clear and so is how to do it, but I cannot write a procedure for the same. I'd be extremely grateful if somebody could help me out here. Thanks in advance.

PS: The the queried result I have put are a little different from my sample table data because of Indexing I have put in on src_ip and dst_ip.

It's helpful when including sample data and expected output in the question that the we can get from 1 to the other. In this I cannot. Can you recast your expected output based on the sample source data you have published. Also why a procedure this looks like a simple aggregation grouping by src_isp,dst_isp? — P.Salmon
– P.Salmon, Commented Feb 5, 2019 at 8:26
@P.Salmon I have included the end-objective I'd like to achieve. It's been labelled as the End-Result Table. Please check. Also, since this was the first time me writing a procedure, simple aggregation grouping by src_isp,dst_isp? is not something I actually took care of. Apart from that, the result table is based on the sample input I put in. Thanks again — Devanshu Misra
– Devanshu Misra, Commented Feb 5, 2019 at 8:34
I see src_ip = 796928 in your end result table but not in the source table so in what way is your end result table based on the source table? — P.Salmon
– P.Salmon, Commented Feb 5, 2019 at 8:37
@P.Salmon Please refer to my PS: line trailing the question,. Also, The sample table differs from the end result table and the queried tables because of index being put on the src_ip and dst_ip table — Devanshu Misra
– Devanshu Misra, Commented Feb 5, 2019 at 8:40
You could improve your question by introducing some continuity from source to end result until then I'm out. — P.Salmon
– P.Salmon, Commented Feb 5, 2019 at 8:44

P.Salmon · Accepted Answer · 2019-02-05 12:22:23Z

1

Just a guess:-

 select t.src_ip,t.dst_ip, sum(data) sumdata
    -> from t
    -> group by t.src_ip,t.dst_ip;
+--------+------------+---------+
| src_ip | dst_ip     | sumdata |
+--------+------------+---------+
| 796928 | 1911291904 |      64 |
| 796933 | 316866560  |     126 |
| 796946 | 4081388040 |     272 |
| 796950 | 3085830664 |     194 |
| 796951 | 3085830664 |     146 |
+--------+------------+---------+
5 rows in set (0.00 sec)

answered Feb 5, 2019 at 12:22

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Devanshu Misra Over a year ago

Just what I needed. Thank you so much for your time and effort, and my apologies for a late response. +1

Collectives™ on Stack Overflow

Creating a MySQL Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related