Extract all sublists with size n of list created by GROUPCONCAT

Follow

Tim

• June 22, 2016 14:24

Hello,

if I have session data for example (time with t0 < t1 < t2 ... < t8):

 

userX | a | t0

userX | b | t1

userX | c | t2

userX | d | t3

userY | a | t4

userY | b | t5

userY | g | t6

userY | c | t7

userY | f | t8

 

and group this data by user with GROUPCONCAT I'll get 

 

userX | [a,b,c,d]

userY | [a,b,g,c,f]

 

Now I want to extract all sublists of length n. For example n = 3 should create:

 

userX | [a,b,c]

userX | [b,c,d]

userY | [a,b,g]

userY | [b,g,c]

userY | [g,c,f]

 

How can I achieve that?

Thanks and best regards,

Tim

0

• 

  Pablo Redondo

  • June 22, 2016 15:02
  • Edited

  HI Tim,

  I would use the ngram function. It takes in a list value (the result of your concat) and the n value (3).  ngram(<your concat list>;3).

  Hope this helps.

  Pablo

  0

  Comment actions Permalink
• 

  Tim

  • June 24, 2016 08:13

  Hi Pablo,

  thanks for the suggestions, but the NGRAM function doesn't return a list but the elements of the input list concatenated.

  What I need is all subLISTS with size n so that I can process them further. The problem is, that my input list elements contain text with spaces so that I can't split them after the NGRAM function concatenated them.

  Best regards,

  Tim

  0

  Comment actions Permalink
• 

  Tim

  • June 24, 2016 08:42

  I solved it now by embedding a delimiter character (with CONCAT) in the list elements before GROUPCONCATing the elements. After that I can go for NGRAM and apply TOKENIZELIST on the resulting column using my delimiter I've set before and then get my desired list with size n.

  Best regards,

  Tim

  0

  Comment actions Permalink

Please sign in to leave a comment.