Исправление Darth_Revan, (текущая версия) :
Если всегда есть start перед stop, то можно как-то так:
WITH t1 AS (
SELECT action, created_at,
LAG(created_at) OVER (ORDER BY created_at) AS prev_created_at
FROM events
) SELECT SUM(timestampdiff(SECOND, prev_created_at, created_at))
AS total FROM t1 WHERE action = 'stop';
или так:
WITH t1 AS (
SELECT action, created_at,
ROW_NUMBER() OVER (PARTITION BY action ORDER BY created_at) AS num
FROM events
) SELECT SUM(timestampdiff(SECOND, t2.created_at, t3.created_at))
AS total
FROM (SELECT created_at, num FROM t1 WHERE action = 'start') AS t2
INNER JOIN (SELECT created_at, num FROM t1 WHERE action = 'stop') AS t3
USING (num);
Исправление Darth_Revan, :
Если всегда есть start перед stop, то можно как-то так:
WITH t1 AS (
SELECT action, created_at,
LAG(created_at) OVER (ORDER BY created_at) AS prev_created_at
FROM events
) SELECT SUM(timestampdiff(SECOND, prev_created_at, created_at))
AS total FROM t1 WHERE action = 'stop';
или так:
WITH t1 AS (
SELECT action, created_at,
ROW_NUMBER() OVER (PARTITION BY action ORDER BY created_at) AS num
FROM events
) SELECT SUM(timestampdiff(SECOND, t2.created_at, t3.created_at))
AS total
FROM (SELECT created_at FROM t1 WHERE action = 'start') AS t2
INNER JOIN (SELECT created_at FROM t1 WHERE action = 'stop') AS t3
USING (num);
Исправление Darth_Revan, :
Если всегда есть start перед stop, то можно как-то так:
WITH t1 AS (
SELECT action, created_at,
LAG(created_at) OVER (ORDER BY created_at) AS prev_created_at
FROM events
) SELECT SUM(timestampdiff(SECOND, prev_created_at, created_at))
AS total FROM t1 WHERE action = 'stop';
или так:
WITH t1 AS (
SELECT action, created_at,
ROW_NUMBER() OVER (PARTITION BY action ORDER BY created_at) AS num
FROM events
) SELECT SUM(timestampdiff(SECOND, t2.created_at, t3.created_at))
AS total
FROM (SELECT * FROM t1 WHERE action = 'start') AS t2
INNER JOIN (SELECT * FROM t1 WHERE action = 'stop') AS t3
USING (num);
Исходная версия Darth_Revan, :
Если всегда есть start перед stop, то можно как-то так:
WITH t1 AS (
SELECT action, created_at,
LAG(created_at) OVER (ORDER BY created_at) AS prev_created_at
FROM events
) SELECT SUM(timestampdiff(SECOND, prev_created_at, created_at))
AS total FROM t1 WHERE action = 'stop';
или так:
WITH t1 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY action ORDER BY created_at) AS num
FROM events
) SELECT SUM(timestampdiff(SECOND, t2.created_at, t3.created_at))
AS total
FROM (SELECT * FROM t1 WHERE action = 'start') AS t2
INNER JOIN (SELECT * FROM t1 WHERE action = 'stop') AS t3
USING (num);