dúvida em select: resultado zero

cjp · Mensagem por **cjp** » 05 Mai 2021 00:53

Pessoal, alguém poderia me tirar uma dúvida sobre MySQL?

Preciso obter da tabela ativ uma lista de todas as datas em que o tempo total seja inferior a 90 minutos.

Fiz este select:

Código: Selecionar todos

SELECT DATA, SUM(tempousado) AS tempo
FROM ativ
WHERE usuario='G' AND DATA>='2021-03-25' AND DATA<'2021-05-03' AND acao='Responde'
GROUP BY DATA
HAVING tempo<90
ORDER BY DATA

Funciona. Mas ele tem um defeito: quando não há nenhum registro na data, ele não coloca na lista. Eu preciso que coloque zero.

Alguém saberia me dizer como fazer para incluir a data com zero nesse caso?

Grato

Inacio

Mensagem por **JoséQuintas** » 05 Mai 2021 09:08

Tempo usado NULL ?

Talvez SUM( COALESCE( tempousado, 0 ) )

Mas com zero se encaixa nesses parâmetros de filtro?

cjp · Mensagem por **cjp** » 05 Mai 2021 10:19

Não, não tem tempousado=NULL.
A questão é não haver mesmo registro na data para certo usuário.

Mensagem por **alxsts** » 06 Mai 2021 01:06

Olá!

cjp escreveu:A questão é não haver mesmo registro na data para certo usuário.

Se não existe a data na tabela, não tem como o SQL retornar a data. Pelo menos usando uma consulta simples, como esta postada.

Se não estou enganado, você utiliza uma versão atual do MariaDB (versão 10 ou superior). Sendo assim, sugiro que crie uma CTE que gere uma série de datas contendo cada dia do período desejado. Feito isto, faça um LEFT JOIN da CTE com esta sua consulta. Montei o exemplo abaixo mas não tenho como testar. Faça os testes e dê um retorno.

Código: Selecionar todos

WITH recursive Datas AS (
      -- "Membro Âncora"
      SELECT
        Cast( '2021-03-25' As Date) as Data
      UNION ALL
        -- "Membro Recursivo"
      SELECT
         Date_Add( Data, INTERVAL 1 DAY)
      FROM Datas  /* Resultado da última iteração */
      WHERE
        Data < Cast( '2021-05-03' as Date)
)
SELECT a.data,
       t.tempo
  FROM datas d
LEFT JOIN 
   ( SELECT a.DATA, 
            Coalsce( SUM( a.tempousado), 0 ) AS tempo
       FROM ativ a
      WHERE a.usuario='G' 
        AND a.data >= '2021-03-25' 
        AND a.data < '2021-05-03' 
        AND a.acao='Responde'
   GROUP BY a.DATA
   HAVING tempo < 90 ) t
ON d.data = t.data
ORDER BY DATA

Por falar em retorno, conseguiu resolver o caso do tópico Mysql: consulta lenta?
?

cjp · Mensagem por **cjp** » 07 Mai 2021 01:05

De fato, uso o Maria DB 10.4.
Mas não entendi o que vc quis dizer com "crie uma CTE". O que seria uma CTE?
Até entendi a ideia, mas não entendi como fazer isso.
Até tentei executar o teu exemplo no Heidi, mas dá erro no "recursive".
Pode me explicar como fazer isso?

Mensagem por **JoséQuintas** » 07 Mai 2021 01:56

CTE é recurso de MySQL mais novo e MariaDB.
É bom, mas só vai servir se TODAS as suas bases forem MariaDB, ou MySQL a partir do 8.0.

cjp · Mensagem por **cjp** » 07 Mai 2021 12:33

Não sei se entendi bem.
Quando vc diz "se todas as suas bases forem MariaDB", quer dizer que eu tenho que usar o Maria DB pra todas?
Uso o Maria DB 10.4 pra tudo.
E como faço para executar essa CTE?

Mensagem por **JoséQuintas** » 07 Mai 2021 14:41

Se o recurso é do MariaDB 10, ou MySQL 8, não vai funcionar em outras versões.
Se sua base MySQL na internet for 5.7, não tem o recurso.

CTE acho legal, pra facilitar a montagem dos comandos.
NÃO uso, porque sei que tenho MySQL 5.7 na NET, e em clientes, e neles esse recurso não existe.

Exemplo simbólico, ao invés de

Código: Selecionar todos

SELECT * FROM ( SELECT codigo, nome FROM B ) AS A

faz assim:

Código: Selecionar todos

WITH temp AS ( SELECT codigo, nome FROM clientes )
SELECT * FROM temp

Neste caso cria uma tabela temporária com o nome temp, pra executar o que vém depois.
Convém lembrar que tudo é criado em memória, então é bom usar pra informações reduzidas, não pra SELECT *

As queries complexas, acabam ficando com textos mais simples.

Provavelmente melhor para o interpretador do MySQL também.

Mensagem por **alxsts** » 07 Mai 2021 23:29

Olá!

CJP escreveu:Mas não entendi o que vc quis dizer com "crie uma CTE". O que seria uma CTE?
Quando vc diz "se todas as suas bases forem MariaDB", quer dizer que eu tenho que usar o Maria DB pra todas?

Common Table Expressions (CTE) ou Expressões Comuns de Tabela é um recurso disponível nos principais sistemas gerenciadores de banco de dados relacionais.
Este recurso não nasceu junto com a especificação de SQL. Foi sendo agregado ao longo do tempo aos diversos SGBDR. No caso do MySQL, foi a partir da versão 8 e no MariaDB, a partir da versão 10.
Desta forma, se você deseja usar CTEs em seu sistema, tem que usar uma versão de SGBDR que suporte esta funcionalidade. Se você for instalar o sistema
em um cliente ou provedor de internet que não tenha disponível a versão correta do SGBD, não será possível usar CTE. Exemplo: você desenvolveu para MySQL 8 com CTE e o cliente tem a versão 5... não vai funcionar.

CJP escreveu:Até entendi a ideia, mas não entendi como fazer isso.
E como faço para executar essa CTE?
Pode me explicar como fazer isso?

No exemplo que postei, as linhas de 1 a 12 criam o CTE e as demais o utilizam.
Veja a criação e uso sem incluir a tua query:

Código: Selecionar todos

WITH recursive Datas AS (
      -- "Membro Âncora"
      SELECT
        Cast( '2021-03-25' As Date) as Data
      UNION ALL
        -- "Membro Recursivo"
      SELECT
         Date_Add( Data, INTERVAL 1 DAY)
      FROM Datas  /* Resultado da última iteração */
      WHERE
        Data < Cast( '2021-05-03' as Date)
)
SELECT data from datas;

Resultado

#	data
1	2021-03-25
2	2021-03-26
3	2021-03-27
4	2021-03-28
5	2021-03-29
   ...
32	2021-04-25
33	2021-04-26
34	2021-04-27
35	2021-04-28
36	2021-04-29
37	2021-04-30
38	2021-05-01
39	2021-05-02
40	2021-05-03

CJP escreveu:Até tentei executar o teu exemplo no Heidi, mas dá erro no "recursive".

Como visto acima, o CTE está funcionando e gera a faixa de datas corretamente. Então o problema era no resto do código.
Veja exemplo abaixo com a tabela que criei para testes.

Código: Selecionar todos

CREATE TABLE tbAtiv ( 
   id INT(6) NOT NULL AUTO_INCREMENT,
   tempousado INT(6),
   usuario TEXT(1),
   data DATE,
   acao VARCHAR(20), 
   CONSTRAINT tbAtiv_pk PRIMARY KEY (id)
);

---

INSERT INTO tbAtiv (tempousado, usuario, data, acao)
VALUES 
      ( 0, 'G', curdate(), 'Responde' ),
      (60, 'G', curdate(), 'Responde' ),
      (90, 'G', curdate(), 'Responde' ),
      ( 0, 'G', Date_Sub( Current_Date(), INTERVAL 1 DAY), 'Atende' ),
      (60, 'G', Date_Sub( Current_Date(), INTERVAL 1 DAY), 'Responde' ),
      (90, 'G', Date_Sub( Current_Date(), INTERVAL 1 DAY), 'Atende' ),
      (11, 'G', Date_Sub( Current_Date(), INTERVAL 2 DAY), 'Responde' ),
      (60, 'G', Date_Sub( Current_Date(), INTERVAL 2 DAY), 'Responde' ),
      (20, 'G', Date_Sub( Current_Date(), INTERVAL 2 DAY), 'Responde' ),
      (60, 'F', Date_Sub( Current_Date(), INTERVAL 3 DAY), 'Responde' ),
      (20, 'F', Date_Sub( Current_Date(), INTERVAL 3 DAY), 'Responde' ),
      (33, 'G', Date_Sub( Current_Date(), INTERVAL 3 DAY), 'Responde' );

---

SELECT * FROM tbAtiv

--- Resultado

#	id	tempousado	usuario	data	      acao
1	 1	0	            G  	2021-05-07	Responde
2	 2	60	            G  	2021-05-07	Responde
3	 3	90	            G  	2021-05-07	Responde
4	 4	0	            G  	2021-05-06	Atende
5	 5	60	            G  	2021-05-06	Responde
6	 6	90	            G  	2021-05-06	Atende
7	 7	11	            G  	2021-05-05	Responde
8	 8	60	            G  	2021-05-05	Responde
9	 9	20	            G  	2021-05-05	Responde
10	10	60	            F  	2021-05-04	Responde
11	11	20	            F  	2021-05-04	Responde
12	12	33	            G  	2021-05-04	Responde

---

WITH RECURSIVE Datas AS (
      -- "Membro Âncora"
      SELECT
        Cast( '2021-05-01' As Date) as Data
      UNION ALL
        -- "Membro Recursivo"
      SELECT
         Date_Add( Data, INTERVAL 1 DAY)
      FROM Datas  /* Resultado da última iteração */
      WHERE
        Data < Cast( '2021-05-10' as Date)
)
SELECT d.data Data,
       Coalesce( t.tempo, 0) Tempo
  FROM Datas d
LEFT JOIN 
   ( SELECT a.DATA, 
            SUM( a.tempousado) AS tempo
       FROM tbAtiv a
      WHERE a.usuario='G' 
        AND a.data >= '2021-05-01' 
        AND a.data < '2021-05-10' 
        AND a.acao='Responde'
   GROUP BY a.DATA
   HAVING tempo < 90 ) t
ON d.data = t.data
ORDER BY data;

--- Resultado

#	Data	      Tempo
1	2021-05-01	0
2	2021-05-02	0
3	2021-05-03	0
4	2021-05-04	33
5	2021-05-05	0
6	2021-05-06	60
7	2021-05-07	0
8	2021-05-08	0
9	2021-05-09	0
10	2021-05-10	0

---

JoséQuintas escreveu:Convém lembrar que tudo é criado em memória, então é bom usar pra informações reduzidas, não pra SELECT *

Não vejo restrições no uso, independentemente do volume de informações. Creio que, conforme o volume, ele use disco também, além da memória.
Pode ser usado tanto para SELECT como para INSERT, UPDATE, DELETE e CREATE TABLE.
Pode ter vários membros recursivos ou não, separados por vírgula. Quando pelo menos um membro é recursivo, é obrigatório usar a palavra RECURSIVE.

Mensagem por **JoséQuintas** » 08 Mai 2021 10:42

Aproveitando...

NÃO é a mesma coisa, mas serve como ajuda pra entender o CTE.
Já usei assim em alguns fontes.

Código: Selecionar todos

cSQLA := "SELECT CODIGO, NOME, ENDERECO FROM CLIENTES WHERE CIDADE='SAO PAULO'"
cSQL := "SELECT * FROM ( " + cSQLA + " ) AS A"

o CTE é parecido com esse uso acima.
Na ajuda mais simples, deixa o fonte SQL mais legível.
Comandos que seriam complexos acabam ficando mais simples de criar/entender.
Ao mesmo tempo, cria novas possibilidades.
E com certeza, também deixa mais clara a "conversa" entre programador e servidor, evitando confusão.

Em alguns comandos, por falta de versão mais nova do servidor MySQL, optei por isso acima, pra ficar mais fácil de entender cada parte e evitar erros.

Mensagem por **alxsts** » 11 Mai 2021 02:38

Olá!

JoséQuintas escreveu:NÃO uso, porque sei que tenho MySQL 5.7 na NET, e em clientes, e neles esse recurso não existe.

Nos casos onde o recurso CTE não está disponível, independentemente do SGBDR, pode-se utilizar a query abaixo, que encontrei em Generating a series of dates

Código: Selecionar todos

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2017-01-01' and '2017-12-31'

#	gen_date
1	2017-01-01
2	2017-01-02
3	2017-01-03
4	2017-01-04
5	2017-01-05
6	2017-01-06
7	2017-01-07
8	2017-01-08
9	2017-01-09
...
360	2017-12-26
361	2017-12-27
362	2017-12-28
363	2017-12-29
364	2017-12-30
365	2017-12-31

Ainda não parei para analisar a lógica disto mas, apesar de parecer estranho, funciona.

SequenciaDatasDateSequence

cjp · Mensagem por **cjp** » 25 Mai 2021 01:01

Desculpem a demora, mas só agora consegui ver este tópico.
Tentei a opção com With recursive pelo Heidi, mas está dando erro em recursive.
Será que me falta algum requisito? Ou eu preciso fazer algo antes disso?

Mensagem por **alxsts** » 25 Mai 2021 13:14

Olá!

O exemplo que postei acima funciona perfeitamente.

cjp escreveu:Tentei a opção com With recursive pelo Heidi, mas está dando erro em recursive.

Deve ser alguma coisa que faltou no processo copiar/colar. Talvez algum ponto e vírgula. Mas, como saber? Você acusa erros mas não mostra o código... fica difícil. Poste o código. Não o print do código.

cjp · Mensagem por **cjp** » 26 Mai 2021 22:51

Desculpe.

O código está assim:

Código: Selecionar todos

WITH recursive ativ AS (
SELECT CAST('2021-03-25' AS DATE) AS DATA UNION ALL
SELECT DATE_ADD(DATA, INTERVAL 1 DAY)
FROM ativ
WHERE DATA < CAST('2021-05-03' AS DATE))
SELECT DATA
FROM ativ;

Note que alterei datas para ativ porque é o nome da tabela que uso aqui. Mas testei também da forma original, com datas, e também com alguns datas e outros ativ.
A verdade é que não entendi bem o código, e por isso não sei bem como aplicá-lo ao meu caso aqui.
Mas, ao que parece, o erro não é este (ou ainda não é este). Aparentemente, o erro está no "recursive". Vide print do erro em anexo.

Mensagem por **alxsts** » 27 Mai 2021 00:21

Olá!

Simplesmente colei o teu código e executei. Funcionou perfeitamente:

alxsts escreveu:Common Table Expressions (CTE) ou Expressões Comuns de Tabela é um recurso disponível nos principais sistemas gerenciadores de banco de dados relacionais.
Este recurso não nasceu junto com a especificação de SQL. Foi sendo agregado ao longo do tempo aos diversos SGBDR. No caso do MySQL, foi a partir da versão 8 e no MariaDB, a partir da versão 10.
Desta forma, se você deseja usar CTEs em seu sistema, tem que usar uma versão de SGBDR que suporte esta funcionalidade. Se você for instalar o sistema
em um cliente ou provedor de internet que não tenha disponível a versão correta do SGBD, não será possível usar CTE. Exemplo: você desenvolveu para MySQL 8 com CTE e o cliente tem a versão 5... não vai funcionar.

Estou usando a versão 10.5.8 do MariaDB. E você? Está usando um banco de dados conforme a citação acima?

Lembre-se: se algum dos teus clientes usar versão anterior do BD, CTE não vai funcionar neles. Neste caso, troque o CTE pela outra opção que forneci (acima).