LINUX.ORG.RU

История изменений

Исправление 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);