Calcular o consumo de água com base em uma base de leituras

[flaviokowalske]

Pessoal tenho uma tabela com a seguinte estrutura:

DROP TABLE IF EXISTS `leituras`;
CREATE TABLE IF NOT EXISTS `leituras` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `horario` datetime DEFAULT CURRENT_TIMESTAMP,
  `leitura` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Nessa tabela são inseridos valores lidos em um medidor de consumo de água. São 4 leituras por dia. Preciso fazer o cálculo do consumo diário, na matemática basta subtrair a primeira leitura da última que terei quantos litros foram gastos no dia, mas como implemento isso no MYSQL?

[Denis Courcy]

A primeira coisa a fazer é trocar o tipo DATETIME por TIMESTAMP.

A segunda coisa é criar um campo dtLeitura do tipo DATE e criar um índice por ele para acelerar o processo.

O código pode ser como o abaixo ou melhorar conforme sua vontade.
Usei a função DATE para obter a data sem a hora e assim, facilitar a pesquisa. Porém isto causará TABLE SCAN. Ou seja, varrerá toda a tabela.

A primeira leitura multiplica por -1 para inverter o sinal e obter a subtração dos dois elementos de leitura.

SELECT SUM(leitura) AS consumo
  FROM (
     SELECT (leitura * -1) AS leitura, MIN(horario) AS horario
     FROM leituras
     WHERE DATE(horario) = '2020-11-05'
     GROUP BY DATE(horario)
     UNION
     SELECT leitura, MAX(horario) AS horario 
     FROM leituras
     WHERE DATE(horario) = '2020-11-05'
     GROUP BY DATE(horario)
     ) a;

 

[flaviokowalske]

@Denis Courcy efetuei da forma que você indicou mas o resultado veio zerado.

Consegui fazer de outra forma, cheguei a quase 80% da expectativa em termo de solução.

Tenho os seguintes registros na tabela:

Quando executo a query abaixo:

SELECT
	DATE(horario) AS data,
    MIN(leitura) AS primeira_leitura,
    MIN(TIME(horario)) AS primeiro_horario,
    MAX(leitura) AS segunda_leitura,
    MAX(TIME(horario)) AS segundo_horario,
    MAX(leitura) - MIN(leitura) AS consumo_dia
FROM
	leituras
GROUP BY
	DATE(horario)
ORDER BY
	horario DESC

Obtenho o seguinte resultado:

O resultado é simples, busca de campos específicos e um simples cálculo, segunda_leitura - primeira_leitura = consumo-dia.

A dificuldade agora é: saber qual o consumo_noite que será obtido pelo cálculo da primeira-leitura (dia seguinte) - segunda_leitura (dia anterior).

Não consegui uma forma de buscar valores de campos específicos em dias diferentes.

Editado Novembro 9, 2020 por flaviokowalske

[Denis Courcy]

Corrigi o SELECT que enviei anteriormente

SELECT dtLeitura, SUM(leitura) AS TT_Leitura
FROM (  
     SELECT DATE(L1.horario) AS dtLeitura, (L1.leitura * -1) AS leitura
     FROM leituras L1
     INNER JOIN (
        SELECT MIN(L2.horario) AS hora2
        FROM leituras L2
        WHERE DATE(L2.horario) = '2020-11-05'
        ) Minimo ON Minimo.hora2 = L1.horario
     UNION
     SELECT DATE(L3.horario) AS dtLeitura, L3.leitura AS leitura
     FROM leituras L3
     INNER JOIN (
        SELECT MAX(L4.horario) AS hora4
        FROM leituras L4
        WHERE DATE(L4.horario) = '2020-11-05'
        ) Maximo ON Maximo.hora4 = L3.horario
     ) a;   

 

Em 09/11/2020 em 11:54, flaviokowalske disse:

Não consegui uma forma de buscar valores de campos específicos em dias diferentes.

É uma repetição do exercício anterior onde a menor leitura (aquele multiplicado por -1) será o maior valor do dia anterior e a menor leitura será o menor do dia atual.

Tente assim:

SELECT dtLeitura, SUM(leitura) AS TT_Leitura
FROM (  
     SELECT DATE(L1.horario) AS dtLeitura, (L1.leitura * -1) AS leitura
     FROM leituras L1
     INNER JOIN (
        SELECT MAX(L2.horario) AS hora2
        FROM leituras L2
        WHERE DATE(L2.horario) = '2020-11-05'
        ) Minimo ON Minimo.hora2 = L1.horario
     UNION
     SELECT DATE(L3.horario) AS dtLeitura, L3.leitura AS leitura
     FROM leituras L3
     INNER JOIN (
        SELECT MIN(L4.horario) AS hora4
        FROM leituras L4
        WHERE DATE(L4.horario) = '2020-11-06'
        ) Maximo ON Maximo.hora4 = L3.horario
     ) b;