1

I have the following Tables, pk1 is the primary key and unique, t1 is the time of the event, e1 is the event type, ek2 is the event key so for the same ek2, there may be multiple event.

The sample data is here

pk1, t1, e1, ek2
10001, 14/02/2014 01:00:00, banner, a0001
10002, 15/02/2014 02:00:00, search, a0001
10003, 15/02/2014 04:00:00, search, a0001
10004, 14/02/2014 01:00:00, banner, a0002
10005, 15/02/2014 02:00:00, search, a0002

To replicate the table, here is the sql.

CREATE TABLE Table1 (`pk1` int, `t1` datetime, `e1` varchar(6), `ek2` varchar(5));
INSERT INTO Table1 (`pk1`, `t1`, `e1`, `ek2`) 
VALUES (10001, '2015-02-02 09:00:00', 'banner', 'a0001'), 
(10002, '2015-03-02 10:00:00', 'search', 'a0001'), 
(10003, '2015-03-02 12:00:00', 'search', 'a0001'), 
(10004, '2015-02-02 09:00:00', 'banner', 'a0002'), 
(10005, '2015-03-02 10:00:00', 'search', 'a0002');

My desired outcome is as below, in the original DB, there are up to 15 event per single event.

event key, 1st event, 2nd event, 3rd event, ...
a0001, banner, search, search
a0002, banner, search

After hours of trial and error, I come up with the following query, which account for the 1st 4 evet. If it is bug-free, I would extend it to the 15th event (et15).

SELECT ek2,
     MAX(case when pk1 =1 THEN e1 END) as et1,
     MAX(case when pk1 =2 THEN e1 END) as et2,
     MAX(case when pk1 =3 THEN e1 END) as et3,
     MAX(case when pk1 =4 THEN e1 END) as et4
FROM (SELECT t0.pk1, t0.e1, t0.ek2
    FROM Table1 AS t0
    LEFT JOIN Table1 AS t1 ON t0.ek2=t1.ek2 AND t1.pk1>t0.pk1
    where t1.pk1 is null   
    ORDER by t0.t1) as rn 
GROUP by ek2;

The subquery is try to grab the max time stamp for each event (ek2). And I reference to Simple Query to Grab Max Value for each ID but the outcome is this

a0001, null, null, null, null               
a0002, null, null, null, null

What is wrong with my code?

updated 1: (Thanks for Ravinder's input), also I was thinking to add a row number(or kind of index for each sub_event (pk1) for a given event (ei2). Updated code as below, 

SELECT ek2,
     MAX(case when row_n =1 THEN e1 END) as et1,
     MAX(case when row_n =2 THEN e1 END) as et2,
     MAX(case when row_n =3 THEN e1 END) as et3,
     MAX(case when row_n =4 THEN e1 END) as et4
FROM (SELECT t0.pk1, t0.e1, t0.ek2, @curRow := @curRow + 1 AS row_n
    FROM Table1 AS t0
    JOIN (select @curRow :=0) r
    LEFT JOIN Table1 AS t1 ON t0.ek2=t1.ek2 AND t1.pk1>t0.pk1
    where t1.pk1 is NOT null
    ORDER by t0.t1) as rn 
GROUP by ek2;

The outcome of this would be, a bit closer but still need debugging.

a0001, banner, banner, search, (null)
a0002, (null), (null), (null), banner

update 2. With reference to Row number per group in mysql , I managed to add the row no. with this update query.

SELECT ek2,
     MAX(case when row_n =1 THEN e1 END) as et1,
     MAX(case when row_n =2 THEN e1 END) as et2,
     MAX(case when row_n =3 THEN e1 END) as et3,
     MAX(case when row_n =4 THEN e1 END) as et4
FROM (SELECT t0.pk1, t0.e1, t0.ek2, t0.t1, 
        ( CASE t0.ek2 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := t0.ek2 END
          ) + 1 AS row_n
    FROM Table1 AS t0
    LEFT JOIN Table1 AS t1 ON t0.ek2=t1.ek2 AND t1.pk1>t0.pk1
    join (select @curRow :=0, @curType := '') r
    where t1.pk1 is NOT null 
    ORDER by t0.ek2,row_n) as rn 
GROUP by ek2;

The outcome is this 

a0001, banner, banner, search, (null)
a0002, banner, (null), (null), (null)

It is very close to what I want, howerver there is one extra issue, you see the 2nd event is banner for a0001, and null for a0002. The correct one shoud be search for a0001, and also search for a0002 too. Whats is wrong here?

If I remove the where t1.pk1 is NOT null clause 

a0001, banner, banner, search, search
a0002, banner, search, (null), (null)

and it is still not correct.

Further question, if the no. of event is a varialbe, could we make it into a dynamic one? just like MySQL pivot row into dynamic number of columns ?

Thanks so much for your attention first.

Cheers,

rc.

Community
  • 1
  • 1
redcasper
  • 11
  • 3
  • Issue must be at `'where t1.pk1 is null'`. I think it should be `'is NOT null'`. Otherwise, subquery will always returns `'NULL'` values. – Ravinder Reddy Jun 10 '14 at 05:36
  • Thanks Ravinder for your input, actually I also quite confuse here, I have try `'where t1.pk1 is null'`, `'is NOT null'`, or even remove the whole where clause. The outcome is still `'NULL'` anyway. What is the point to put a where clause here actually? Coz I am referencing other's people code so need some guideline here. – redcasper Jun 10 '14 at 05:52
  • @Ravinder, I was thinking to add a row number(or kind of index for each sub_event (pk1) for a given event (ei2). I have just updated the original message. How should I do it? – redcasper Jun 10 '14 at 06:12

1 Answers1

0

With the hints from @Ravinder, I finally comes up with the SQL to do the dirty work.

SELECT ek2,
     MAX(case when row_n =1 THEN e1 END) as et1,
     MAX(case when row_n =2 THEN e1 END) as et2,
     MAX(case when row_n =3 THEN e1 END) as et3,
     MAX(case when row_n =4 THEN e1 END) as et4
FROM (SELECT pk1, e1, ek2, t1, 
        ( CASE ek2 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := ek2 END
          ) + 1 AS row_n
    FROM Table1 
    join (select @curRow :=0, @curType := '') r
    -- where pk1 is NOT null 
    ORDER by ek2,t1,row_n) as rn 
GROUP by ek2;

This will exactly gives the result 

a0001,  banner, search, search, (null)  
a0002,  banner, search, (null), (null)

Thanks all for your input and concern over the subject.

Here is the fiddle http://sqlfiddle.com/#!2/1fb10d/1/0 P.S. I comment out the where pk1 is NOT Null as it is arbitary, adding it or not will not affect the result.

rc.

redcasper
  • 11
  • 3