Simplification requête SQL GROUPBY / MAX / MIN

Version imprimable

Bonjour à tous,

J'ai la requête ci-dessous et j'aimerai savoir s'il est possible de l'améliorer notamment au niveau des sous requêtes qui me permettent de ramener la date associée à la valeur min (PMin) et la valeur max (PMax).
En fait j'aimerai bien ne pas avoir à faire des sous requêtes et donc parcourir 2X ma table.

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
   WITH
      CRB_DAILY
      AS
         (SELECT compteur_id,
                 i1.[date] AS datepoint,
                 type_pt_id,
                 (sum (puissance) / 6) AS volume,
                 max (puissance) AS pmax,
                 min (puissance) AS pmin,
                 (SELECT TOP 1 datepoint
                  FROM codap.gtm_crb10mn AS a                 
                       JOIN
                       (SELECT MIN (puissance) AS PMin
                        FROM codap.gtm_crb10mn crb                             
                        WHERE     crb.compteur_id = c1.compteur_id
                              AND crb.type_pt_id = c1.type_pt_id
                              AND cast
                                  (dateadd (minute, -10, datepoint) AS DATE) =
                                  i1.[date] and crb.lastversion = 1
                        GROUP BY crb.compteur_id) AS b
                          ON (    a.puissance = b.PMin
                              AND a.compteur_id = c1.compteur_id
                              AND a.type_pt_id = c1.type_pt_id
                              AND cast
                                  (dateadd (minute, -10, datepoint) AS DATE) =
                                  i1.[date])) AS dtmin,
                 (SELECT TOP 1 datepoint
                  FROM codap.gtm_crb10mn AS a
                       JOIN
                       (SELECT MAX (puissance) AS PMax
                        FROM codap.gtm_crb10mn crb
                        WHERE     crb.compteur_id = c1.compteur_id
                              AND crb.type_pt_id = c1.type_pt_id
                              AND cast
                                  (dateadd (minute, -10, datepoint) AS DATE) =
                                  i1.[date] and crb.lastversion = 1
                        GROUP BY crb.compteur_id) AS b
                          ON (    a.puissance = b.PMax
                              AND a.compteur_id = c1.compteur_id
                              AND a.type_pt_id = c1.type_pt_id
                              AND cast
                                  (dateadd (minute, -10, datepoint) AS DATE) =
                                  i1.[date])) AS dtmax,
                 count (*) AS iCount
          FROM codap.gtm_crb10mn c1
               INNER JOIN codap.gtm_interval_10mn i1
                  ON i1.datepoint = c1.datepoint
          WHERE c1.lastversion = 1
          GROUP BY compteur_id, type_pt_id, i1.[date])
   SELECT compteur_id,
          datepoint,
          type_pt_id,
          volume,
          pmax,
          pmin,
          dtmin,
          dtmax,
          iCount
   FROM CRB_DAILY

Citation:

Envoyé par Pfeffer

Avez vous regardé le plan de requête avant d'affirmer que SQL Server parcoure deux fois la tables ?
1) s'il y a un index adéquat il n'y a pas de parcours de table mais une recherche d'index
2) SQL Server factorise ce qu'il est possible de factoriser, ici les 2 sous requêtes devraient être fusionnée en une seule...

En revanche votre requête contient beaucoup de choses qui la rende potentiellement contre-performante :
1) dans le prédicat suivant :
cast(dateadd (minute, -10, datepoint) AS DATE) = i1.[date]
si ce sont des colonnes de type DATE, alors le CAST est inutile...

2) vous faites des jointures triangulaire dans vos sous requêtes... Dès lors aucun index n'est utilisable.... Il est probablement possible d'éviter cela.
Expliquez nous ce que vous voulez faire, Donnez nous un jeu d'essai et le DDL des tables en jeu.

Mise en forme plus lisible :

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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
WITH
CRB_DAILY AS
(
SELECT compteur_id,
       i1.[date] AS datepoint,
       type_pt_id,
       (sum (puissance) / 6) AS volume,
       max (puissance) AS pmax,
       min (puissance) AS pmin,
       (SELECT TOP 1 datepoint
        FROM   codap.gtm_crb10mn AS a                 
               JOIN (SELECT MIN (puissance) AS PMin
                     FROM   codap.gtm_crb10mn crb                             
                     WHERE  crb.compteur_id = c1.compteur_id
                       AND  crb.type_pt_id = c1.type_pt_id
                       AND  cast(dateadd (minute, -10, datepoint) AS DATE) = i1.[date] 
                       AND  crb.lastversion = 1
                     GROUP  BY crb.compteur_id) AS b
                  ON a.puissance = b.PMin
                     AND a.compteur_id = c1.compteur_id
                     AND a.type_pt_id = c1.type_pt_id
                     AND cast(dateadd (minute, -10, datepoint) AS DATE) = i1.[date]) AS dtmin,
       (SELECT TOP 1 datepoint
        FROM   codap.gtm_crb10mn AS a
               JOIN (SELECT MAX(puissance) AS PMax
                     FROM   codap.gtm_crb10mn crb
                     WHERE  crb.compteur_id = c1.compteur_id
                       AND  crb.type_pt_id = c1.type_pt_id
                       AND  CAST(DATEADD(minute, -10, datepoint) AS DATE) = i1.[date]
                       AND  crb.lastversion = 1
                     GROUP BY crb.compteur_id) AS b
                  ON a.puissance = b.PMax
                     AND a.compteur_id = c1.compteur_id
                     AND a.type_pt_id = c1.type_pt_id
                     AND cast(dateadd (minute, -10, datepoint) AS DATE) = i1.[date]) AS dtmax,
                 count (*) AS iCount
FROM   codap.gtm_crb10mn c1
       INNER JOIN codap.gtm_interval_10mn i1
          ON i1.datepoint = c1.datepoint
WHERE  c1.lastversion = 1
GROUP  BY compteur_id, type_pt_id, i1.[date]
)
SELECT compteur_id,
       datepoint,
       type_pt_id,
       volume,
       pmax,
       pmin,
       dtmin,
       dtmax,
       iCount
FROM   CRB_DAILY;

A +

15/07/2020, 22h25
Pfeffer

Alors j'ai regardé le plan de maintenance et SQL m'a proposé de créer un index complémentaire.
Ca marche déja un peu mieux ...

Les CAST sont pour moi obligatoires je pense.
D'un côté j'ai un datetime (dd/mm/yyyy hh:mm) et de l'autre une date, l'idée étant de trouver le DATETIME auquel est associé le MIN(PUISSANCE) ou le MAX(puissance) sur une journée particulière ou il y a plusieurs enregistrements.

Comment améliorer mes jointures triangulaires dans ce cas ?
16/07/2020, 16h43
SQLpro

Donnez le code du CREATE des tables.....

A +

Je ne pense pas que first et last existe en version agrégée sur sql server.

Je partirais donc sur du first_value avant de faire l'agrégation, quelque chose comme (requête non testée) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
  with crb_daily as (
select compteur_id
     , i1.[date] as datepoint
     , type_pt_id
     , first_value(c1.datepoint) over (partition by c1.compteur_id, c1.type_pt_id, cast(dateadd (minute, -10, c1.datepoint) as date) order by c1.puissance asc)  as dtmin
     , first_value(c1.datepoint) over (partition by c1.compteur_id, c1.type_pt_id, cast(dateadd (minute, -10, c1.datepoint) as date) order by c1.puissance desc) as dtmax
  from codap.gtm_crb10mn c1
  join codap.gtm_interval_10mn i1
    on i1.datepoint = c1.datepoint
 where c1.lastversion = 1
 group by compteur_id, type_pt_id, i1.[date]
)
select compteur_id
     , datepoint
     , type_pt_id
     , (sum (puissance) / 6) as volume
     , max (puissance) as pmax
     , min (puissance) as pmin
     , dtmin
     , dtmax
     , count (*) as iCount
  from crb_daily
 group by compteur_id, datepoint, type_pt_id, dtmin, dtmax;