Discussion :

[Tigrounet]

Bonjour à tous,

Je suis novice en SQL et je rencontre quelques difficultés pour effectuer un total du temps sur une colonne ayant pour format TIME. Lorsque je fais SUM(temps), ma base de données me revoie que je ne peux pas utiliser des données TIME pour l'opérateur SUM. J'ai essayé de convertir le type TIME en INT avec les fonctions CAST() et CONVERT(), mais la conversion est refusée.
J'aimerais bien le faire avec le langage SQL standard, si possible…

Merci d'avance pour votre aide !

Au cas où, ma requête complète est…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select sum(temps) from (
	select top 3000 /* <- Je sais, c'est affreux ce TOP 3000 mais je ne sais pas comment le faire avec du SQL standard ! */ diff_nums from (
		select distinct numero_sortant as diff_nums from Appels$
		)
		as numeros
	)
as premiers_differents_numeros, Appels$ where not (numero_sortant = diff_nums);

Elle ne porte que sur une table Appels qui contient les colonnes date_et_heure (format datetime), temps (format time(0)) et numero_sortant (format varchar(20)).

[Waldar]

Effectivement, on ne peut pas sommer simplement le type TIME.
Il faut tout convertir en secondes, sommer les secondes puis reconvertir dans le premier type.

En SQL standard, ça donne quelque chose de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select extract(hour   from temps) * 60 * 60
     + extract(minute from temps) * 60
     + extract(second from temps) as mon_time_second
...

[SQLpro]

En principe une données de type TIME ne peut pas aller au delà de 24h. Donc toute sommation et conversion vers TIME une fois sommée n'a aucun sens !
Pouvez vous sommer des melons ?

Si vous voulez conserver des durées alors il faut les exprimer primalement sous la forme d'une quantité de temps. par exemple en heures décimales, en minutes ou en secondes. Après, comme ce sont des données numériques, vous pouvez faire tous les calculs possibles, y compris sommations !

A +

[Tigrounet]

Effectivement, on ne peut pas sommer simplement le type TIME.
Il faut tout convertir en secondes, sommer les secondes puis reconvertir dans le premier type.

En SQL standard, ça donne quelque chose de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select extract(hour   from temps) * 60 * 60
     + extract(minute from temps) * 60
     + extract(second from temps) as mon_time_second
...

Je ne sais pas pourquoi mais ma BDD ne semble pas reconnaître la fonction extract() alors qu'en regardant sur Google, je me rends compte que c'est bien comme ça qu'il faut l'utiliser.

SQLpro, merci pour l'idée ! Je suis stupide, je n'y ai pas pensé plus tôt à séparer tout bêtement les valeurs secondes minutes et heures.
Mais juste pour répondre, pour moi l'intérêt était d'additionner le temps, tout comme lorsque je vois deux melons, ça peut être 1 melon + 1 melon = 2 melons. Du coup, je pensais que la valeur TIME pouvait stocker des valeurs de temps telles que 1j 2h 3m 5s (à prendre au sens durée, pas au sens date et/ou heure du coup). Ça me paraissait logique de pouvoir faire 15h + 12h = 1j 3h.

Merci pour votre aide.

[SQLpro]

SQLpro, merci pour l'idée ! Je suis stupide, je n'y ai pas pensé plus tôt à séparer tout bêtement les valeurs secondes minutes et heures.

Je n'ai pas dit cela, j'ai dit que vous deviez quantifier vos durées avec une unité temporelle queconque. Par exemple en cinéma on compte la durée d'un film en minutes. En publicité audiovisuelle c'est en secondes. Si l'on veut une plus grande précision, on utlisera un type de numérique à virgule...

Mais juste pour répondre, pour moi l'intérêt était d'additionner le temps, tout comme lorsque je vois deux melons, ça peut être 1 melon + 1 melon = 2 melons. Du coup, je pensais que la valeur TIME pouvait stocker des valeurs de temps telles que 1j 2h 3m 5s (à prendre au sens durée, pas au sens date et/ou heure du coup). Ça me paraissait logique de pouvoir faire 15h + 12h = 1j 3h.

NON, NON et re NON ! Vous n'aditionnez pas des "melon"s mais des quantité de melon. La quantité étant ici unitaire à défaut... C'est cela que vous n'avez pas compris... Dissocier la désignation (melon, poire, ananas....) et la "métrique" (1, 2, 3....)

A +

[Tigrounet]

Merci pour votre réponse. En fait, j'ai choisi de quantifier le temps en le mettant en secondes plutôt que de conserver le format TIME.

Je suis désolé de ne pas avoir saisi la nuance de suite, mais maintenant j'ai compris ! (Quelle honte.)

Cependant, je n'arrive toujours pas à exécuter correctement ma requête. Le but est de rechercher le temps total d'appel vers tous les numéros, excepté les 3000 premiers numéros différents. Exemple : si 05 fait parti des 3000 premiers numéros différents, je ne comptabilisera plus les appels qu'il a passés. Ma requête est celle-ci, je vais tenter de la commenter au mieux. Je suis sûr qu'elle est très simple et j'ai honte de ne pas arriver au résultat souhaité.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select sum(temps_en_sec) /* Donc je récupère le total du temps d'appel. */ from (
	select top 3000 numeros.diff_nums as numeros_differents /* Je ne sélectionne que les 3000 premiers numéros différents. */ from (
		select distinct numero_sortant as diff_nums /* Ici je prends tous les numéros différents pour les trier (au-dessus). */ from Appels$
		)
		as numeros
	)
as premiers_differents_numeros, Appels$ where Appels$.numero_sortant != premiers_differents_numeros.numeros_differents /* Je vire les 3000 premiers numéros différents. Je pense que le problème vient d'ici… */;

Je pense que le problème vient de la dernière ligne parce que je compare une entrée avec un groupe de valeurs… Et là, si j'exécute ma requête, j'obtiens un message d'erreur (Une erreur de dépassement arithmétique s'est produite lors de la conversion de expression en type de données int.) après environ 20 secondes d'attente. Mais comment faire cette requête ?…

[escartefigue]

Bonjour,

Vos 2 requêtes imbriquées successives ne ramènent qu'une colonne, à savoir des numéros de téléphone, du coup, vous ne pouvez pas sommer les temps dans la requete principale, puisque votre table résultante n'a pas l'information !
De plus vous pouvez simplifier
Quel est votre SGBD ?

[Tigrounet]

Bonjour,

Mais je ne peux pas récupérer en même temps les 3000 numéros de téléphone puis la somme des temps d'appel, si ? Et comment simplifier ?
J'utilise SQL Server, bien que j'aimerais me passer de toutes fonctions SQL propriétaires…

Merci pour votre aide !

[SQLpro]

3000 premiers par rapport à quoi ?
Lorsque l'on veut classer des informations il faut un critère...

Faire cette requête avec du SQL normatif n'est pas un problème. Il faut juste utiliser les fonctions de fenêtrage.

A +

[Invité]

Bonjour,

Sur Sql Server pourquoi ne pas utiliser une procédures stockées, pour obtenir de temps cumulable de manière à pas avoir mal aux yeux à chaque lecture de requête .

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create function fct_sec(@temps_int int) 
returns datetime
as
BEGIN
declare  @heures int, @minutes int, @secondes int
 
if @temps_int > 86400
set @heures=@temps_int /(3600)
set @temps_int=@temps_int-(@heures*3600)
set @minutes=@temps_int/(60)
set @secondes=@temps_int-(@minutes*60)
 
return (convert(varchar(2),@heures)+':'+ convert(varchar(2), @minutes) +':'+ convert(varchar(2), @secondes))
END

Pour le reste, comme le disait escartefigue et comme je te disais sur le chat dvp, tu ne peux sélectionner que les champs connus dans ta sous requête.

Il faut trouver une solution pour ajouter le champ "temps_en_sec" dans ta sous requête.

[SQLpro]

Bonjour,

Sur Sql Server pourquoi ne pas utiliser une procédures stockées, pour obtenir de temps cumulable de manière à pas avoir mal aux yeux à chaque lecture de requête . ...

Stupide !

Une seule requête suffit....

Voulez-vous lui pourrit les performances ?

A +

[Tigrounet]

3000 premiers par rapport à quoi ?
Lorsque l'on veut classer des informations il faut un critère...

Faire cette requête avec du SQL normatif n'est pas un problème. Il faut juste utiliser les fonctions de fenêtrage.

A +

Les informations sont triées par date ! Donc les 3000 premiers numéros du mois. J'ai bêtement omis cette précision…

Bonjour,

Sur Sql Server pourquoi ne pas utiliser une procédures stockées, pour obtenir de temps cumulable de manière à pas avoir mal aux yeux à chaque lecture de requête .

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create function fct_sec(@temps_int int) 
returns datetime
as
BEGIN
declare  @heures int, @minutes int, @secondes int
 
if @temps_int > 86400
set @heures=@temps_int /(3600)
set @temps_int=@temps_int-(@heures*3600)
set @minutes=@temps_int/(60)
set @secondes=@temps_int-(@minutes*60)
 
return (convert(varchar(2),@heures)+':'+ convert(varchar(2), @minutes) +':'+ convert(varchar(2), @secondes))
END

Pour le reste, comme le disait escartefigue et comme je te disais sur le chat dvp, tu ne peux sélectionner que les champs connus dans ta sous requête.

Il faut trouver une solution pour ajouter le champ "temps_en_sec" dans ta sous requête.

Merci, c'est noté ! Là j'ai résolu le problème du champ en TIME en le mettant en INT (en sous-entendant que l'unité est les secondes), mais la fonction pourra toujours être utile si jamais je tombe sur un cas similaire.

Stupide !

Une seule requête suffit....

C'est pas très sympa, il a pris de son temps pour tenter de m'aider… Bien sûr que vous aussi, mais le but n'est pas que vous vous insultiez.

[Tigrounet]

Je crois que j'ai un peu avancé !! Finalement, plutôt que de chercher des valeurs, je les ai mises directement en conditions.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select sum(temps_en_sec) from Appels$ where (select top 3000 numeros.diff_nums from
		(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure
	) != numero_sortant;

Le problème est maintenant que mon SGBD me retourne cette erreur : La sous-requête a retourné plusieurs valeurs. Cela n'est pas autorisé quand la sous-requête suit =, !=, <, <= , >, >= ou quand elle est utilisée en tant qu'expression.. C'est bien ce que je pressentais : je compare une seule valeur (par ligne) avec une liste de valeurs. Si j'ai bien compris. Mais alors comment comparer une valeur avec une liste de valeurs ?

[Invité]

SqlPro, merci pour le fond, c'est vrai qu'une procstock est gourmande ; pour la forme, j' espère que ça vous a fait le plus grand bien


C'est un peu confus ; pourquoi ajouter, différent de numero_sortant (comparé à 3000 résultats). J'aurais compris d'utiliser IN par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT sum(temps_en_sec)
FROM Appels$
WHERE numero_sortant IN 
    (SELECT top 3000 numeros.diff_nums
     FROM
       (SELECT DISTINCT numero_sortant AS diff_nums
        FROM Appels$) AS numeros
     ORDER BY date_et_heure );

[Tigrounet]

Merci !!!! En fait, j'ai trouvé la solution, la voici !!!! C'est quasiment la même chose !!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select sum(temps_en_sec) from Appels$ where not (numero_sortant in (select top 3000 numeros.diff_nums from
	(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure));

Je suis bien entendu ouvert à toute critique de votre part parce que je suis conscient que je n'ai pas forcément des requêtes optimisées.

Maintenant, je dois trier par numéros qui commencent par 05, 06, etc.… J'ai donc essayé de faire comme ça, et ça semble fonctionner !! (Ma table ne contient pas le premier 0 des numéros de téléphone.)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select sum(temps_en_sec) from Appels$ where not (numero_sortant in (select top 3000 numeros.diff_nums from
	(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure)) and (numero_sortant like '1%' or numero_sortant like '2%' or numero_sortant like '3%' or numero_sortant like '4%' or numero_sortant like '5%' or numero_sortant like '9%');

Et la même chose pour les téléphones portables…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select sum(temps_en_sec) from Appels$ where not (numero_sortant in (select top 3000 numeros.diff_nums from
	(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure)) and (numero_sortant like '6%' or numero_sortant like '7%');

Franchement, merci beaucoup à tous pour votre aide !!

[Tigrounet]

J'ai une dernière question à poser avant de clore ce sujet, en fait. Ou plutôt deux.

1) Comment se fait-il que mon SQL Server mette plus de 30 secondes (genre 40 secondes ou plus) à exécuter la première requête (ci-dessous) ? C'est mon moteur qui est pourri ou c'est ma requête qui n'est pas optimisée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select sum(temps_en_sec) from Appels$ where not (numero_sortant in (select top 3000 numeros.diff_nums from
	(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure));

En sachant que j'ai un i3 2,9GHz avec SSD, et le SQL Server Enterprise 2014 (64 bits, évidemment) tourne en installation minimale (pas d'interface graphique sur mon Windows Server) sur une machine virtuelle avec 1 cœur et 1 Go de RAM alloués ! L'hyperviseur est Hyper-V sur un Windows 10. Pourtant, l'interface de la machine virtuelle semble très bien répondre, elle m'ouvre très vite une session, par exemple. J'ai 18974 entrées dans ma table, ce qui n'est quand même pas énorme pour une base de données, je suppose.

2) Comment virer ce TOP 3000 de ma requête, et le remplacer par quelque chose de standard ? Je crois que c'est le seul élément qui ne fait pas parti du standard SQL.

[SQLpro]

2) Comment virer ce TOP 3000 de ma requête, et le remplacer par quelque chose de standard ? Je crois que c'est le seul élément qui ne fait pas parti du standard SQL.

Comme je vous l'ai dit au départ, utiliser une fonction de fenêtrage comme RANK(), DENSE_RANK() ou ROW_NUMBER().

1) Comment se fait-il que mon SQL Server mette plus de 30 secondes (genre 40 secondes ou plus) à exécuter la première requête (ci-dessous) ? C'est mon moteur qui est pourri ou c'est ma requête qui n'est pas optimisée ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select sum(temps_en_sec) from Appels$ where not (numero_sortant in (select top 3000 numeros.diff_nums from
	(select distinct numero_sortant as diff_nums from Appels$) as numeros order by date_et_heure));

En sachant que j'ai un i3 2,9GHz avec SSD, et le SQL Server Enterprise 2014 (64 bits, évidemment) tourne en installation minimale (pas d'interface graphique sur mon Windows Server) sur une machine virtuelle avec 1 cœur et 1 Go de RAM alloués ! L'hyperviseur est Hyper-V sur un Windows 10. Pourtant, l'interface de la machine virtuelle semble très bien répondre, elle m'ouvre très vite une session, par exemple. J'ai 18974 entrées dans ma table, ce qui n'est quand même pas énorme pour une base de données, je suppose.

De toute façon n'attendez pas d'une VM les performance d'une machine a moins de "fixer" les ressources :
1) dédié les CPU
2) interdire le balooning et fixer la RAM de SQL Server
3) faire du disk pass through et non du disque virtuel
Autrement dit, dévirtualiser !

De plus 1 Gà de RAM pour une VM hébergeant SQL Server c'est trop peu. Il lui faut au moins 4 Go...
Enfin le i3 est le plus pourri au niveau des performances.
Pour ma part et afin de faire fonctionner correctement SQL Server j'utilise toujours un portable avec 8 a 16 Go de RAM et du core i7.
Le SSD ne sert pas à grand chose pour les SGBDR qui font tout en cache et très peu d'écriture... Vous auriez mieux fait d'acheter un portable avec un disque magnétique et plus de RAM et de CPU !!!

Dans votre requête ce qui va pénaliser le plus c'est l'opérateur <> qui n'est jamais "sargable"
En utilisant les fonctions de fenêtrage, vous allez pour faire une requête de type RANG > 3000 ce qui est "sargable"

Reste ensuite à indexer...

A +

[Tigrounet]

Merci pour votre aide. Je vais dans ce cas tenter d'apprendre l'utilisation de ces fameuses fonctions RANK(), DENSE_RANK() et ROW_NUMBER().

En fait, je virtualise sur mon poste de travail, et le i3 me semble amplement suffisant pour de la bureautique ; je m'estime content d'avoir d'ailleurs reçu cette machine pour travailler. Par contre, merci quand même pour vos infos parce que je comptais justement me prendre une machine pour moi avec un i7, je suis content de constater qu'il ne servira pas à rien !! Je comptais dédier des disques durs à des machines virtuelles ; content de constater que ça sera utile pour SQL Server. Donc viser les 32 Go (ou plus) de RAM pour ma machine ne sera visiblement pas du luxe, surtout si mon SQL Server me bouffera déjà 8 Go… Il est vrai que j'avais zappé que les SGBDR ne font pas d'écriture pour ainsi dire, il est complètement ridicule de prendre des SSD pour ça !! Déjà ça en moins à prévoir.
Je me renseignerai sur toutes ces notions qui m'étaient jusqu'alors inconnues (balooning, fonctions de fenêtrage, « sabgrabilité »…).

[aieeeuuuuu]

Bonjour

Que donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH pa AS (
	SELECT 
			temps_en_sec
		,	MIN(date_et_heure) OVER(PARTITION BY numero_sortant) AS premier_appel
	FROM appels
)
, 
rg AS (
	SELECT 
			temps_en_sec
		,	DENSE_RANK() OVER(ORDER BY premier_appel) AS rang
	FROM pa
)
SELECT SUM(temps_en_sec) 
FROM rg
WHERE rang > 3000

[Tigrounet]

Bonjour,

Désolé pour la réponse tardive…
Je suppose que je devais faire un copier coller ? Quand je le fais, mon client m'indique qu'il est impossible de trouver la procédure stockée pa. Même lorsque je force l'exécution de la requête, la base de données me répond en gros la même chose (syntaxe incorrecte vers le premier AS). Désolé…