mysql - 我可以简化这个 MySQL 查询吗？

Question

我有一个表analyticsrecords:

-------------------------------------------------------------------------------
recordDataId | analyticsReferenceDataId | analyticsDataKey | analyticsDataValue
--------------------------------------------------------------------------------
16 | 114621208-20161122174835   | recordtype   |    Course ID
17 | 114621208-20161122174835   | recordtypeid |    78
18 | 114621208-20161122174835   | pageStart    | Tue Nov 22 2016 17:48:13 
19 | 114621208-20161122174835   |   pageEnd    | Tue Nov 22 2016 17:48:34 

我想通过计算与列值“datavalue”相关的列的时间差来计算在类(class)上花费的时间，我的查询是:

SELECT
    IFNULL((SELECT 
                TIMESTAMPDIFF(SECOND, 
                              STR_TO_DATE((SELECT analyticsDataValue 
                                       FROM analyticsrecorddata  
                                       WHERE analyticsDataKey = "pageStart"
                                         AND analyticsReferenceDataId ='114621208-20161122174835'), '%a %b %d %Y %T'),
                              STR_TO_DATE((SELECT analyticsDataValue 
                                       FROM analyticsrecorddata  
                                       WHERE analyticsDataKey = "pageEnd"  
                                         AND analyticsReferenceDataId ='114621208-20161122174835'), '%a %b %d %Y %T')
              )), 0) AS Time_spent,
    (SELECT analyticsDataValue 
     FROM analyticsrecorddata  
     WHERE analyticsDataKey = "recordtypeid" 
       AND analyticsReferenceDataId ='114621208-20161122174835') AS Course_id
FROM 
    `analyticsrecords`
GROUP BY 
    analyticsReferenceDataId

我能否简化此查询并使其更高效？计算 17000 行需要 14 秒。

Answer 1

select  courseid,recordtypeid,
            str_to_date(pagestart ,'%a %b %d %Y %T') pagestart,
            str_to_date(pageend ,'%a %b %d %Y %T') pageend,
            timediff(str_to_date(pageend ,'%a %b %d %Y %T'), str_to_date(pagestart ,'%a %b %d %Y %T')) time_on_course
from
(
select  analyticsReferenceDataId,
            max(case when ltrim(rtrim(analyticsDataKey)) = 'recordtype' then analyticsDatavalue end) courseid,
            max(case when ltrim(rtrim(analyticsDataKey)) = 'recordtypeid' then analyticsDatavalue end) recordtypeid,
            max(case when ltrim(rtrim(analyticsDataKey)) = 'pageStart' then analyticsDatavalue end) pagestart,
            max(case when ltrim(rtrim(analyticsDataKey)) = 'pageEnd' then analyticsDatavalue end) pageend
from analyticsrecords
group by  analyticsReferenceDataId
) s