Discussion :

[Sayrus]

Hello,

Dans une db, j'ai un champ "dateCreation" (DATETIME), date à la quelle l'enregistrement a été créé.

J'aimerais extraire des infos de ces éléments (COUNT, SUM, etc.) en les regroupant via GROUP BY par tranches d'ancienneté.

Ex:

0 <= 30 jours ("dateCreation" est inférieur ou égal à 30 jours d'ancienneté par rapport à aujourd'hui)
31 <= 60 jours ("dateCreation" est supérieur à 30 jours d'ancienneté et est inférieur ou égal à 60 jours d'ancienneté par rapport à aujourd'hui)

91 <= 180 etc.

Comment effectuer cette requête?

Un grand merci.

[CinePhil]

Donne la structure exacte de ta table et précise ce que tu veux compter ou additionner.

Un petit jeu de données et le résultat attendu aiderait à mieux cerner le besoin.

[skuatamad]

Il faut créer une table ou une vue de paramétrage qui contiendra les tranches (ou directement dans une sous-requête) :
param (tranche, borne_sup, borne_inf)
'0 <= 30', 0, 30
'31 <= 60, 31, 60
etc
Ensuite, joindre avec un between, quelque chose comme ça :

Code :

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

1
2
3
4
5
select tranche, count(...), sum(...)
  from latable t
  join param p on date(t.datecreation) between date_sub(current_date, INTERVAL p.borne_inf DAY) 
                                           and date_sub(current_date, INTERVAL p.borne_sup DAY)
 group by p.tranche

[Sayrus]

Bonjour,

Voici ma structure:

Code :

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

1
2
3
4
CREATE TABLE IF NOT EXISTS `tableZ` (
  `id` int(11) NOT NULL auto_increment,
  `datec` datetime NOT NULL,
  `prixClient` int(11) NOT NULL)

Ce que j'aimerais bien faire c'est sortir , par tranche (0<=30, 31<=60, 61<=90, 91<=180, >180 jours) par rapport à "datec" et la date du jour, le total d'enregistrements contenu dans chaque tranche, la somme du champ "prixClient" par tranche, etc.

Si il faut effectuer plusieurs requêtes SQL ça ne me dérange pas même en stockant partiellement des infos dans des variables PHP.

Merci à vous deux.

[CinePhil]

La suggestion de Skuatamad me semble la plus simple.
Si tes tranches risquent d'être variables, tu peux aussi utiliser une table temporaire au lieu d'une table fixe.
Tu peux essayer ce genre de truc :

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
18
CREATE TEMPORARY TABLE periode
SELECT 'de 0 à 30 jours' AS nom_periode,
	0 AS mini,
	30 AS maxi
UNION
SELECT 'de 31 à 60 jours', 31, 60
UNION
SELECT 'de 61 à 90 jours', 61, 90
-- etc
;
 
SELECT p.nom_periode,
	COUNT(*) AS nombre,
	SUM(prixClient) AS total_prix
FROM periode p
LEFT OUTER JOIN tableZ z
	ON DATEDIFF(CURRENT_DATE, z.datec) BETWEEN p.mini AND p.maxi
GROUP BY p.nom_periode

Nota : En PHP avec l'API mysql_, il faut passer les requêtes une à une :

Code PHP :

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
18
19
20
21
22
23
$sql = "
	CREATE TEMPORARY TABLE periode
	SELECT 'de 0 à 30 jours' AS nom_periode,
		0 AS mini,
		30 AS maxi
	UNION
	SELECT 'de 31 à 60 jours', 31, 60
	UNION
	SELECT 'de 61 à 90 jours', 61, 90
	-- etc
";
mysql_query($sql);
 
$sql = "
	SELECT p.nom_periode,
		COUNT(*) AS nombre,
		SUM(prixClient) AS total_prix
	FROM periode p
	LEFT OUTER JOIN tableZ z
		ON DATEDIFF(CURRENT_DATE, z.datec) BETWEEN p.mini AND p.maxi
	GROUP BY p.nom_periode
";
$result = mysql_query($sql);

[Sayrus]

Woaw, on ne peut plus clair comme ça!

Par contre je pense que je vais devoir mettre datec comme suit "DATE(datec)" afin de virer le "time" dans la requête.

C'est la première fois que je vais utiliser des tables temporaires. Mais je pense que j'ai bridé mon "user" mysql pour SELECT, UPDATE, DELETE et non CREATE.

Est-ce un souci pour la création de table temporaire?
Au pire je peux créer une table fixe, ce ne sont pas des données qui vont évoluer.

En tout cas... UN GRAND MERCI A VOUS!

[CinePhil]

Tu peux créer une table fixe ou, si le user de l'appli n'a pas le droit CREATE, tu peux transformer la table temporaire de mon code en sous-requête à bâtir par l'application.

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
SELECT p.nom_periode,
	COUNT(*) AS nombre,
	SUM(prixClient) AS total_prix
FROM
(
	SELECT 'de 0 à 30 jours' AS nom_periode,
		0 AS mini,
		30 AS maxi
	UNION
	SELECT 'de 31 à 60 jours', 31, 60
	UNION
	SELECT 'de 61 à 90 jours', 61, 90
	-- etc
) AS periode p
LEFT OUTER JOIN tableZ z
	ON DATEDIFF(CURRENT_DATE, z.datec) BETWEEN p.mini AND p.maxi
GROUP BY p.nom_periode

Mais si tes périodes sont fixes, il vaut mieux créer une table fixe car tu pourras en plus indexer les colonnes mini et maxi, ce qui accélérera la jointure.

[Sayrus]

J'en prends bonne note! Super!

Encore merci!