具有时间戳排序的 MySQL 数据透视表查询

Question

我有以下表格，pk1是主键并且是唯一的，t1是事件的时间，e1是事件类型，ek2是事件键所以对于同一个ek2，可能有多个事件。

示例数据在这里

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

要复制表，这里是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');

我想要的结果如下，在原始数据库中，每个事件最多有 15 个事件。

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

经过数小时的反复试验，我提出了以下查询，其中包含第 1 4 个事件。如果没有错误，我会将其扩展到第 15 个事件 (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;

子查询尝试获取每个事件 (ek2) 的最大时间戳。我引用了 Simple Query to Grab Max Value for each ID但结果是这样

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

我的代码有什么问题？

更新 1:(感谢 Ravinder 的输入)，我也在考虑为给定事件 (ei2) 的每个子事件 (pk1) 添加行号(或索引类型)。更新代码如下，

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;

这样做的结果会更接近一些，但仍需要调试。

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

更新 2. 引用 Row number per group in mysql ，我设法添加了行号。使用此更新查询。

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;

结果是这样的

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

它非常接近我想要的，但是还有一个额外的问题，您看到第二个事件是 a0001 的横幅，而 a0002 是空的。正确的应该是搜索a0001，也搜索a0002。这里有什么问题吗？

如果我删除 where t1.pk1 is NOT null 子句

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

它仍然不正确。

进一步的问题，如果没有。 of event 是一个varialbe，我们可以把它变成一个动态的吗？就像MySQL pivot row into dynamic number of columns ？

首先感谢您的关注。

干杯，

回复。

Answer 1

在@Ravinder 的提示下，我终于想出了 SQL 来完成脏活。

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;

这将准确地给出结果

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

感谢大家对这个主题的投入和关注。

这是 fiddle http://sqlfiddle.com/#!2/1fb10d/1/0附言我注释掉 where pk1 is NOT Null 因为它是任意的，添加与否不会影响结果。

回复。