CODEWITHSUNDEEP
mysqlsql
TheBassMan
TheBassMan

Reputation: 115

Group_Concat internal Group by order

i have following question regarding GROUP_CONCAT():

My table has simplified following format:

| userId | visitTime | position |
     1       TIME1         A
     1       TIME2         B
     1       TIME3         B
     1       TIME4         B
     1       TIME5         A
     1       TIME6         C

With my current sql Statement: 

Select group_concat(position) from Table where userId=1

I receive

A,B,B,B,A,C

How can I group the group_concat so i get a result which looks like:

  A,B,A,C

Thanks in advance!

EDIT:

I like to have the real consecutive sequence of positions, where only multiple occurrences of the same position from the next visitTime should be grouped.

EDIT2:

My expected output is A,B,A,C

For instance: A user 1 moves from A to B, There he stayed at B for more than 1 entry: B, B, than he moves back to A and after that he goes to C.

I like only to get the path he used:

From A to B to A to C

So if a user moved to another position it should be recogniced, but he can move back again.

Upvotes: 1

Views: 223

Answers (4)

Mehran
Mehran

Reputation: 16851

First of all, to implement this you will need a unique id to show you the order of records (otherwise your request is impossible to implement). So I'm going to add an id column to your table like this:

| id | userId | visitTime | position |
  1      1       TIME1         A
  2      1       TIME2         B
  3      1       TIME3         B
  4      1       TIME4         B
  5      1       TIME5         A
  6      1       TIME6         C

Now the query to extract your final string:

SELECT GROUP_CONCAT(t3.position ORDER BY t3.id)
FROM (
    SELECT t1.*, ((
        SELECT position
        FROM Table
        WHERE
            id > t1.id
        AND
            userId = 1
        ORDER BY id
        LIMIT 1
        )) AS following_position
    FROM Table t1
    WHERE
        t1.userId = 1
) t3
WHERE
    t3.position <> t3.following_position OR t3.following_position IS NULL

Here's the same query without using sub-queries (I expect better performance this way but I'm not sure as there are too many NULLs in it):

SELECT GROUP_CONCAT(t3.position ORDER BY t3.id)
FROM (
    SELECT t1.*, MIN(t2.id) AS following_id
    FROM Table t1
    LEFT JOIN Table t2 ON (t1.id < t2.id)
    WHERE
        t1.userId = 1
    AND
        (t2.userId = 1 OR t2.userId IS NULL)
    GROUP BY t1.id
) t3
LEFT JOIN Table t4 ON (t3.following_id = t4.id)
WHERE
    t3.position <> t4.position OR t4.position IS NULL

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220932

Try this query (which relies on unique visitTime values per user...)

-- get those elements p1 from your table...
select p1.userId, group_concat(p1.position order by p1.visitTime)
from p p1
-- for which there doesn't exist a successor p2 with the same "position"
where not exists (
  select *
  from p p2
  where p1.userId = p2.userId
  and p1.visitTime < p2.visitTime
  and p1.position = p2.position
  -- for which there doesn't exist any record p3 strictly in between p1 and p2
  and not exists (
    select *
    from p p3
    where p2.userId = p3.userId
    and p1.visitTime < p3.visitTime
    and p2.visitTime > p3.visitTime
  )
)
group by p1.userId

See a demo in this SQLFiddle.

Note, in other databases, you would probably use window functions, such as LEAD() OVER() and LAG() OVER() instead of writing the above monster...

Upvotes: 0

Olivier Coilland
Olivier Coilland

Reputation: 3096

I'm pretty sure you can't find a nice MySQL-only solution for this one.

I'd recommend doing it in your application layer.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Here is the syntax:

Select group_concat(distinct position order by position)
from Table
where userId=1

Upvotes: 2

Related Questions