Início / dba / Perguntas / 334937
Accepted
Shahab Mohd
Asked: 2024-01-17 12:40:13 +0800 CST

Adicione valores e junte linhas sob condição em uma consulta Mysql

  • 772

Eu tenho uma tabela com dados de tempo de entrada/tempo limite em uma estrutura conforme mostrado abaixo.

eu ia data em vez tempo esgotado duração
3 02/01/2024 01:00 02:00 01:00
3 02/01/2024 02:30 07:00 04:30
3 02/01/2024 09:00 09:10 00:10
3 02/01/2024 09:10 09:30 00:20
3 02/01/2024 12:00 12:25 00:25

As posições de tempo com duração inferior a 30 minutos não devem ser consideradas para cálculo. entretanto, se dois intervalos consecutivos em um dia forem maiores ou iguais a 30 minutos, isso deverá ser considerado. como no exemplo acima, o tempo limite da linha 3 é igual ao tempo limite da linha 4 09:00-09:10/09:1--09:30. são considerados como tempo único e devem ser somados se o tempo total for superior a 30 minutos. Eu criei a seguinte consulta para obter a duração total do dia, excluindo durações inferiores a 30 minutos, mas não incluindo a duração do tempo conforme explicado acima.

SELECT
    DATE,  TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(duration))),'%H:%i' ) AS total,
    SUM(TIME_TO_SEC(duration)) AS seconds
FROM
    `timeintable`
WHERE
    id = '3' AND( DATE BETWEEN '2024-01-01' AND '2024-01-10') AND(TIME_TO_SEC(duration) >= 1800)
GROUP BY
    DATE
ORDER BY
    DATE ASC

É possível conseguir o mesmo por meio de consulta SQL em vez de loop de array php? Minha tentativa de usar funções LEAD/LAG não teve êxito.

mysql

1 respostas

  1. Best Answer
    WITH 
cte1 AS (
  SELECT *, CASE WHEN timein = LAG(timeout) OVER (PARTITION BY id, `date` ORDER BY timein)
                 THEN 0 
                 ELSE 1
                 END not_adjacent  
  FROM test
-- WHERE id IN ( {needed ids list} )
--   AND `date` IN ( {needed dates list} )
  ),
cte2 AS (
  SELECT *,
         SUM(not_adjacent) OVER (PARTITION BY id, `date` ORDER BY timein) group_no
  FROM cte1
  ),
cte3 AS (
  SELECT id, `date`, TIMESTAMPDIFF(SECOND, MIN(timein), MAX(timeout)) duration
  FROM cte2
  GROUP BY id, `date`, group_no
  HAVING duration >= TIME_TO_SEC('00:30:00')
  )
SELECT id, `date`, SEC_TO_TIME(SUM(duration)) daily_duration
FROM cte3
GROUP BY id, `date`

    violino passo a passo com alguns comentários.

    • 1

relate perguntas