Récupération d'un ensemble depuis une date

Version imprimable

09/01/2015, 15h13
Marty000

Récupération d'un ensemble depuis une date

Bonjour,

le titre n'est pas très explicite, mais je rencontre un problème de performance sur une requête.

En fait, j'ai une table avec un id, une date, et un type (qui peut être 0 ou 1).

Je cherche à récupérer de manière très rapide le nombre d'éléments de type 1, depuis l'avant dernier élément de type 0.

Voici un exemple de ma table (je ne mets pas les heures pour simplifier)
ID, Date, Type
1, 01/01/2015, 1
2, 01/01/2015, 0
3, 03/01/2015, 1
4, 05/01/2015, 1
5, 07/01/2015, 1
6, 07/01/2015, 0

J'aimerais récupérer les informations de cette manière avec un select (si possible) :
ID, Date, Type, Nb type 1 precedent
1, 01/01/2015, 1, NULL (ou 0 ce n'est pas grave)
2, 01/01/2015, 0, 1
3, 03/01/2015, 1, NULL (ou 0)
4, 05/01/2015, 1, NULL (ou 0)
5, 07/01/2015, 1, NULL (ou 0)
6, 07/01/2015, 0, 3

Là où je bloque c'est que le tout doit être dynamique, si jamais dans mon exemple, l'élément d'ID 5 devient un élément de type 0, alors :
1, 01/01/2015, 1, NULL (ou 0 ce n'est pas grave)
2, 01/01/2015, 0, 1
3, 03/01/2015, 1, NULL (ou 0)
4, 05/01/2015, 1, NULL (ou 0)
5, 07/01/2015, 0, 2
6, 07/01/2015, 0, 0

Je suis donc passé par une requête "classique", avec l'ajout une colonne qui est une sous-requête et qui me récupère ce que je veux, mais cela prend énormément de temps. J'ai essayé de créer une vue indexée, mais il y a tellement de contraintes, que je n'ai jamais pu réussir à placer l'index. :)

Auriez-vous une idée, une autre façon de faire pour améliorer les performances ?

Je vous remercie.
09/01/2015, 15h39
al1_24

ça doit pouvoir se résoudre avec une fonction de regroupement fenêtrée...
09/01/2015, 16h01
Marty000

Bonjour,

auriez-vous une idée sur le type de fonction ?
09/01/2015, 16h33
al1_24

Une différence de sommes ?
Si TYPE = 1 Alors 0 Sinon Somme(TYPE) pour toutes les lignes précédentes - Somme(Col calculée) pour toutes les lignes précédentes

Merci de votre aide,

pourriez-vous expliquer un peu plus votre solution ?

Voici la requête que j'ai :
Code:

1 2 3 select ID, Date, Type , (select count(ID) as nb from maTable m2 where Type = 1 m2.Date <= maTable.Date and m2.Date >= (select top 1 Date from (select top 1 Date from maTable m3 where m3.Date < maTable.Date order by Date desc union all select \'01/01/2010\' as "Date") as "temp" order by Date desc)) from maTable
Je ne suis pas très fier de cette dernière colonne... Et bien sûr, c'est celle-ci qui prend énormément de temps.

09/01/2015, 17h38
al1_24

Regarde les fonctions SUM..OVER.
12/01/2015, 14h02
Marty000
Je reviens sur le forum, car je ne vois pas comment le SUM pourrait m'être utile.

J'ai pensé plutôt utilisé le COUNT, mais au final, je n'arrive pas à faire une requête rapide pour ne récupérer que les dates les plus proches.

Par exemple :
Id, Date, Type, Nb
1, 01/01/2015, 1, NULL (ou 0 ce n'est pas grave)
2, 01/01/2015, 0, 1
3, 03/01/2015, 1, NULL (ou 0)
4, 05/01/2015, 1, NULL (ou 0)
5, 07/01/2015, 1, NULL (ou 0)
6, 07/01/2015, 0, 3

pour l'ID 6, il ne doit se baser que sur la date s'ID 2. Et ainsi de suite.

Pour l'instant, je suis parti aussi avec un DENSE_RANK.

Du type :
Code:

1 2 3 4 5 6 select * from ( select ID, Date, Type, DENSE_RANK over partition by (t.Date) order by prefv.Date) as line from maTable t inner join maTable prev on prev.Date < t.date ) tmp where tmp.line = 1
Avec cette requête je récupère tous les enregistrements précédents, et grâce à line = 1, je récupère que le dernier (tri descendant dans la fenêtre). Malgré tout, je ne suis pas complètement satisfait. ça prend encore pas mal de temps.

Comment pourrai je utiliser le SUM OVER dans mon cas ?

A partir de ton exemple, je voyais quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH    tmp
    AS  (   SELECT  id
                ,   date
                ,   type
                ,   CASE type WHEN 0 THEN SUM(type) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ELSE 0 END AS cnt
            FROM    matable
        )
SELECT  id
    ,   date
    ,   type
    ,   CASE type WHEN 0 THEN cnt - SUM(cnt) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END as nb
FROM    tmp
ORDER BY id
    ,   date
;

12/01/2015, 17h35
Marty000

Merci pour cet exemple complet.

Cependant, quand j'essaie de l'implémenter dans ma base, je récupère via les SUM le total complet. Il faudrait pouvoir "remettre à 0" le compte quand on arrive sur un type 0.

Me serais-je trompé dans ma logique ?
Qu'affiche cette requête ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH    tmp
    AS  (   SELECT  id
                ,   date
                ,   type
                ,   CASE type WHEN 0 THEN SUM(type) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ELSE 0 END AS cnt
            FROM    matable
        )
SELECT  id
    ,   date
    ,   type
    ,   cnt
    ,   CASE type WHEN 0 THEN cnt - MAX(cnt) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END as nb
FROM    tmp
ORDER BY id

12/01/2015, 18h26
Marty000

Un grand merci !
C'est bon j'ai réussi avec avec ta nouvelle requête. :)
La modification du COUNT en MAX fait le job, j'ai modifié ma requête pour qu'elle fonctionne mieux.

Si je peux me permettre d'abuser de ton temps, si je dois rajouter une colonne du genre "famille", as tu une idée pour que j'obtienne quelque chose du genre :
Id, Famille, Date, Type, Nb
1, 1, 01/01/2015, 1, 0
2, 2, 01/01/2015, 0, 0
3, 2, 03/01/2015, 1, 0
4, 2, 05/01/2015, 1, 1
5, 3, 07/01/2015, 1, 0
6, 2, 07/01/2015, 0, 2

Le cas intéressant est pour la famille 2 : on incrémente la colonne "Nb" du nombre d'éléments de Type 1 depuis le dernier élément de type 0 de la même famille. Si tu as une idée je suis preneur.

En tout cas, tu m'as déjà bien aidé, encore merci.
12/01/2015, 18h52
al1_24

Je n'ai pas bien compris ton explication... mais est-ce qu'un simple PARTITION BY famille dans les clauses OVER ne répondrait pas à ton besoin ?

Je vais essayer de mieux expliquer :
en fait, je cherche à compter le nombre d'éléments (d'une même famille) de type 1 qu'il y a entre le dernier élément 0 (de la même famille) et la date de mon élément cible.

J'ai déjà essayé de mettre en place les

Code:

PARTITION BY

mais le résultat n'est pas fameux.

Avec ce genre de requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 WITH tmp AS ( SELECT id , date , type , CASE type WHEN 0 THEN SUM(type) OVER (PARTITION BY famille, date ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ELSE 0 END AS cnt FROM matable ) SELECT id , date , type , cnt , CASE type WHEN 0 THEN cnt - MAX(cnt) OVER (PARTITION BY famille, date ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END as nb FROM tmp ORDER BY id
J'obtiens quelque chose du genre :
Id, Famille, Date, Type, Nb
1, 1, 01/01/2015, 1, 0
2, 2, 01/01/2015, 0, 0
3, 2, 03/01/2015, 1, NULL
4, 2, 05/01/2015, 1, NULL
5, 3, 07/01/2015, 1, 0
6, 2, 07/01/2015, 0, 2

Je pensais que c'était juste un problème sur la condition CASE WHEN, mais en fait j'ai aussi un problème au niveau de ma partition car si je ne mets pas le dernier tri par l'identifiant, je récupère quelque chose de ce genre :
Id, Famille, Date, Type, Nb
1, 1, 01/01/2015, 1, 0
2, 2, 01/01/2015, 0, 0
3, 2, 03/01/2015, 1, NULL
4, 2, 05/01/2015, 1, NULL
6, 2, 07/01/2015, 0, 2
5, 3, 07/01/2015, 1, 0
7, 3, 07/01/2015, 1, NULL
8, 4, 07/01/2015, 1, NULL
9, 4, 07/01/2015, 0, 3
J'aimerais qu'il y ait une "remise à 0" du compteur entre le passage de l'item 7 à l'item 8, car la famille est différente.

Voici ce que j'aimerais obtenir au final (j'enlève le dernier tri sur l'identifiant) :

Alors qu'il me faudrait :
Id, Famille, Date, Type, Nb
1, 1, 01/01/2015, 1, 0
2, 2, 01/01/2015, 0, 0
3, 2, 03/01/2015, 1, 0
4, 2, 05/01/2015, 1, 1
6, 2, 07/01/2015, 0, 2
5, 3, 07/01/2015, 1, 0
7, 3, 07/01/2015, 1, 1
8, 4, 07/01/2015, 1, 0
9, 4, 07/01/2015, 0, 1

Je ne sais pas si je suis plus clair.

Toutes mes excuses !
En fait, j'avais mal placé mes PARTITION dans ma "vraie" requête. Et j'ai réussi à obtenir ce que je voulais grâce à toi.

Voici ma manière de faire (qui peut encore être améliorée, mais je pense que c'est déjà pas mal niveau performance -> 1sec pour 30000 données) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH    tmp
    AS  (   SELECT  id
                ,   date
                ,   type
                ,   CASE type WHEN 0 THEN SUM(type) OVER (PARTITION BY famille ORDER BY famille, date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ELSE 0 END AS cnt
                ,   CASE type WHEN 1 THEN SUM(type) OVER (PARTITION BY famille ORDER BY famille, date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ELSE 0 END AS cnt2
            FROM    matable
        )
SELECT  id
    ,   date
    ,   type
    ,   cnt
    ,   cnt2
    ,   CASE type WHEN 0 THEN cnt - isnull(MAX(cnt) OVER (PARTITION BY famille ORDER BY famille, date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) else  cnt2 - isnull(MAX(cnt) OVER (PARTITION BY famille ORDER BY famille, date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) END as nb
FROM    tmp
ORDER BY id

Encore merci pour ton aide.

D'abord il serait temps de respecter la charte de postage afin d'éviter d'emmerder tout le monde !

http://www.developpez.net/forums/d96...vement-poster/

Donc, par exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
USE tempdb;
GO
 
CREATE TABLE T_TABLE_TBL
(TBL_ID            INT IDENTITY PRIMARY KEY,
 TBL_DATE          DATE,
 TBL_TYPE          BIT);
GO
INSERT INTO T_TABLE_TBL
SELECT DATEADD(day, ABS(CHECKSUM(NEWID()) / 400000), '20000101'), 
       CASE WHEN CHECKSUM(NEWID()) > 1500000000 THEN 0 ELSE 1 END
GO 10000

Voici maintenant un embryon de réponse :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
;
WITH 
T1 AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY TBL_DATE) AS RN
FROM   T_TABLE_TBL 
WHERE  TBL_TYPE = 0
),
T2 AS
(
SELECT *, LAG(RN) OVER(ORDER BY RN) AS RN_PRED
FROM   T1
UNION ALL
SELECT *, NULL, NULL
FROM   T_TABLE_TBL 
WHERE  TBL_TYPE = 1
)
SELECT TBL_ID, TBL_DATE, TBL_TYPE,
       (SELECT COUNT(*)
        FROM   T2 AS Tin
		WHERE  Tin.TBL_DATE BETWEEN (SELECT TBL_DATE FROM T2 WHERE RN = Tout.RN_PRED)
		                        AND Tout.TBL_DATE
          AND  TBL_TYPE = 1)
	   , RN, RN_PRED
FROM   T2 AS Tout
WHERE  COALESCE(RN, 9999999) > 1
ORDER  BY TBL_DATE, TBL_TYPE;

A +

13/01/2015, 14h18
Marty000

Désolé, pour le non respect des règles.

En tout cas, merci d'avoir proposé ta solution.