获取Mysql的IN子句中的最后一个匹配项

xytpbqjk 于 2022-11-28 发布在 Mysql

关注(0)|答案(2)|浏览(140)

我有一个学习者表，名为learner_lesson

learnerlessonid learnerid   lessonid
1                 24          42

另一个表为learner_lesson_log

lessonlogid learnerlessonid progress    maxprogress.    interactionType       createdAt
1                   1           0           15           Start             2022-11-02 07:50:30
1                   1           0           15           Start             2022-11-02 07:51:30
2                   1           4           15          Pause               2022-11-02 07:51:34
3                   1           4           15          Play               2022-11-02 07:52:20
4                   1          14           15           Run               2022-11-02 07:52:30
5                   1          15           15           Stop              2022-11-02 07:52:31

我想得到的是学习者观看的秒数。但是，可能发生的情况是，学习者开始了一节课，但没有完成它或暂停它，稍后回来，然后完成课程。例如，在上面的例子中，学习者开始了一节课，并放弃了它，再次回来，并再次开始课程，然后在4秒后暂停它。我希望结果看起来像

Learner ID  Length of Interaction   Start Timestamp
24               4                2022-11-02 07:51:30
24              11                2022-11-02 07:52:20

但我有一个疑问

Learner ID  Length of Interaction   Start Timestamp
24               64               2022-11-02 07:50:30
24               4                2022-11-02 07:51:30
24              11                2022-11-02 07:52:20

我希望查询只计算Start -> Pause, Start->Stop, Play -> Pause, Play -> Stop组合之间的秒数。如何才能实现此结果？这是我的查询

SELECT 
  c.learnerid AS "Learner ID",
  TIMESTAMPDIFF(SECOND, a.createdAt, 
  (SELECT b.createdAt
                  FROM learner_lesson_log b
            INNER JOIN learner_lessons d 
                    ON b.learnerLessonId = d.learnerLessonId
                 WHERE b.learnerLessonId = a.learnerLessonId
                   AND d.learnerId = c.learnerId
                   AND b.createdAt > a.createdAt
                   AND b.interactionType IN ('Stop', 'Pause')
              ORDER BY b.createdAt ASC LIMIT 1)) AS "Length of Interaction",
  a.createdAt AS "Start Timestamp"
FROM learner_lesson_log a 
INNER JOIN learner_lessons c 
  ON c.learnerLessonId = a.learnerLessonId 
WHERE a.interactionType IN ('Start', 'Play') 
ORDER BY a.createdAt ASC;

这是fiddle

mysql

来源：https://stackoverflow.com/questions/74561289/get-the-last-occurrence-in-in-clause-of-mysql

2条答案

按热度按时间

dgenwo3n1#

但是版本8解决方案也可以

WITH 
cte1 AS (
  SELECT *,
         (interactionType IN ('Start', 'Play')) * 4 +
         (interactionType IN ('Stop', 'Pause')) * 2 +
         (COALESCE(LAG(interactionType) OVER (PARTITION BY learnerlessonid
                                              ORDER BY createdAt), 
                   'Stop') IN ('Stop', 'Pause')) point_mark
  FROM learner_lesson_log
  ),
cte2 AS (
  SELECT *,
         TIMESTAMPDIFF(SECOND, 
                       createdAt,
                       LEAD(createdAt) OVER (PARTITION BY learnerlessonid
                                             ORDER BY createdAt)) delta
  FROM cte1 
  WHERE point_mark IN (2, 5)
  )
SELECT learner_lessons.learnerid `Learner ID`,   
       cte2.delta `Length of Interaction`,
       cte2.createdAt `Start Timestamp`
FROM learner_lessons
JOIN cte2 USING (learnerlessonid)
WHERE point_mark = 5
ORDER BY 1, 3

step-by-step fiddle

赞(0）回复(0）举报 2022-11-28

jjhzyzn02#

我认为这在5. 7上和预期的一样有效-

SELECT
  c.learnerid `Learner ID`,
  TIMESTAMPDIFF(SECOND, l1.createdAt, l2.createdAt) `Length of Interaction`,
  l1.createdAt `Start Timestamp`
FROM learner_lesson_log l1
INNER JOIN learner_lessons c 
  ON l1.learnerLessonId = c.learnerLessonId 
INNER JOIN learner_lesson_log l2 
  ON l1.learnerLessonId = l2.learnerLessonId
  AND l2.interactionType IN ('Pause', 'Stop')
  AND l2.createdAt = (
    SELECT MIN(createdAt)
    FROM learner_lesson_log
    WHERE learnerLessonId = l1.learnerLessonId
    AND interactionType IN ('Start', 'Play', 'Pause', 'Stop')
    AND createdAt > l1.createdAt
  )
WHERE l1.interactionType IN ('Start', 'Play') 
ORDER BY c.learnerid ASC, l1.createdAt ASC;

db<>fiddle

赞(0）回复(0）举报 2022-11-28

我来回答

获取Mysql的IN子句中的最后一个匹配项

2条答案

相关问题

热门标签

最新问答