sql to create one record for a ip

user9068199 0
Viewed 27

I have a dataset:

IP,web,rule
12.54.5435,web1,rule1
12.54.5435,web1,rule1
12.54.5435,web2,rule1
12.54.5435,web1,rule2
13.54.5435,web1,rule1
13.54.5435,web1,rule1
13.54.5435,web1,rule1
13.54.5435,web1,rule2

For every ip, i need to create a single record that looks like

total_count,ip, webrulecountlist
4,12.54.5435, ['web1,rule1, 2', 'web2,rule1,1', 'web1,rule2,1']
4,13.54.5435, ['web1, rule1, 3', 'web1, rule2, 1']

My inner query looks like this:

select count(ip) as c, ip, webacl, rule from t1 group by ip, webacl, rule

above query output is:

2,12.54.5435,web1,rulw1
1,12.54.5435,web1,rulw2
1,12.54.5435,web2,rulw1
3,13.54.5435,web1,rulw1
1,13.54.5435,web1,rulw2

but how can i now group by ip combining column values

flink-sql sql
1 Answers

Using https://nightlies.apache.org/flink/flink-docs-master/docs/dev/table/functions/systemfunctions/ for a list of functions:

  • || concatenates.
  • CHR(##) gives us a text character for ascii equilivant
  • LISTAGG() lets us combine multiple rows into 1.

So...Maybe... [UNTESTED / no means to test]

SELECT sum(CNT) as c,             --- Note Sum() not count
       IP, 
       LISTAGG(Chr(39) ||         --- add first apostrophe
               WEB || ', ' ||     --- Add WebName & ,
              Rule || ', ' ||     --- Add Rule & ,
               CNT || Chr(39) ',')--- Add Cnt and apostrophe & Separate sets by , though 
                                  --- technically we don't need the ',' as , is the default
                                  --- syntax might be [,] instead of ',' too
 FROM (SELECT COUNT(*) as CNT, IP, Web, Rule 
      FROM t1
      GROUP BY IP, Web, Rule) Sub  -- Create derived table (Sub) getting counts for 
                                   -- duplicates by IP, Web, Rule
GROUP BY IP
xQbert0
Related