IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Requête : Trouver la période la plus courte où un objectif de % de chiffre d'affaires a été atteint.


Sujet :

Développement SQL Server

  1. #1
    FMJ
    FMJ est déconnecté
    Membre averti
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    416
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 416
    Points : 363
    Points
    363
    Par défaut Requête : Trouver la période la plus courte où un objectif de % de chiffre d'affaires a été atteint.
    Bonjour,
    Le définition du problème est simplissime. Mais pour la résolution, c'est autre chose.
    Je subodore qu'il faut utiliser du CTE et du récursif à gogo, mais pour l'instant je n'ai pas trouvé la bonne recette (sans faire 53 UNION pour autant de colonnes).

    Donc, pour une année donnée, prenons le cas d'une table avec deux colonnes : Sem (=numéro de semaine) et CAweek (CA de la semaine).
    Le but par exemple serait de trouver quelle est la plus petite période où la somme des CA par semaine approche au plus près 50% du CA total. Réponse par exemple : Sem 23 --> sem 41 = 48.7 % du CA total.
    Sachant qu'il peut y avoir des semaines sans CA (mais ce n'est pas vraiment un problème pour le requêtage).

    C'est facile si l'on prend la période qui commence par la 1ere semaine, mais moins si le début de la période est aléatoire....

    Si vous avez des idée ?
    Merci d'avance.

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 377
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 377
    Points : 39 852
    Points
    39 852
    Billets dans le blog
    9
    Par défaut
    C'est un problème intéressant, la difficulté c'est qu'on ne sait pas a priori s'il faudra 1, 5 ou 30 semaines pour atteindre les 50 %
    Une approche possible est donc de calculer le % sur plusieurs périodes, puis de récupérer celui le plus proche des 50%

    Voici un exemple avec un calcul sur 3 semaines (courante, précédente et suivante), 5 semaines (courante, 2 précédentes, 2 suivantes) et 7 semaines
    Ça a le mérite de fonctionner et d'être simple sur le principe, mais lourd à mettre en œuvre si on veut combiner toutes les périodes possibles.
    J'ai laissé dans la requête quelques valeurs intermédiaires pour mieux comprendre le fonctionnement si on affiche le contenu des CTE intermédiaires.

    Code SQL : 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
    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
    with T1 (t1sem, t1ca) as
        (select 202301, 100 union all
         select 202302, 110 union all
         select 202303, 040 union all
         select 202304, 080 union all
         select 202305, 125 union all
         select 202306, 090 union all
         select 202307, 100 union all  
         select 202308, 130 union all
         select 202309, 070 union all
         select 202310, 065 union all
         select 202311, 100 union all  
         select 202312, 110
        )
       , TX (xsem, xtot, xcum3, xcum5, xcum7) as 
        (select t1sem             
              , sum(t1ca) over()  
              , sum(t1ca) over(order by t1sem 
                               rows between 1 preceding and 1 following) 
              , sum(t1ca) over(order by t1sem 
                               rows between 2 preceding and 2 following) 
              , sum(t1ca) over(order by t1sem 
                               rows between 3 preceding and 3 following) 
          from T1  
        )
       , TY (ysem, ypct3, yecar3, ypct5, yecar5, ypct7, yecar7) as
        (select xsem
              , (xcum3 * 100) / xtot
              , abs(50-(xcum3 * 100) / xtot)
              , (xcum5 * 100) / xtot  
              , abs(50-(xcum5 * 100) / xtot)
              , (xcum5 * 100) / xtot    
              , abs(50-(xcum7 * 100) / xtot)
         from TX 
        )
     
    select * 
    from (select ysem
               , least(yecar3, yecar5, yecar7) as EC0
               , case when least (yecar3, yecar5, yecar7) = yecar3 then '3 sem'
                      when least (yecar3, yecar5, yecar7) = yecar5 then '5 sem'
                      else '7 sem'
                 end as duree
          from TY main
         ) Z
    order by Z.EC0 asc
    offset 0 rows  
    fetch next 1 rows only

    Résultat :

    Nom : Sans titre.png
Affichages : 104
Taille : 1,2 Ko

    On voit que c'est la semaine 10 sur une période de 7 semaines (3 avant et 3 après) qui est la plus proche des 50% ave un écart de 1%

    Il y a sans doute plus sioux

  3. #3
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 960
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 960
    Points : 4 389
    Points
    4 389
    Par défaut
    Vérifiez si MSSQLServer supporte que l'expression "ROW BETWEEN n PRECEDING" supporte que n soit une colonne,
    alors vous pourriez générer les nombres de 1 à 53 dans une CTE (rng dans le code ci-dessous) et calculer le MIN de l'expression

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    ABS(
        SUM(t1ca) OVER(PARTITION BY rng ORDER BY t1sem ROWS BETWEEN rng PRECEDING AND CURRENT ROW) 
        -
        (select sum(t1ca) / 2.0 from t1)
    )
    AS delta
    les lignes de la solution sont celles où le MIN(delta) = delta, de celles-là il faudra garder celles où le rng est le minimum.

    NB
    Vérification faite sur https://dbfiddle.uk/OXi-Hk5s MSSQLServer ne supporte pas... alors que Oracle : https://dbfiddle.uk/rewGZgdK...
    pas de chance... faudra trouver une autre voie en générant toutes les combinaisons... : https://dbfiddle.uk/zVKtUjgQ

  4. #4
    FMJ
    FMJ est déconnecté
    Membre averti
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    416
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 416
    Points : 363
    Points
    363
    Par défaut
    Bonjour et merci à vous deux.

    Alors concernant vos propositions, LEAST() n'est pas supporté par SQL Server 2016 mais oui ce dernier intègre bien la fonction SUM() OVER(PARTITION BY ORDER BY ROWS BETWEEN PRECEDING AND CURRENT ROW) que je ne connaissais pas d'ailleurs (je veux parler de BETWEEN ...)

    J'ai continué à réfléchir sur le problème et suis arrivé à cette solution très simple et qui marche bien. pour n'importe quelle plage de semaines et n'importe quel objectif (vive les jointures !).
    L'idée, c'est d'abord de calculer pour chaque semaine le CA total jusqu'à la fin de l'année. Puis par une jointure sur elle même, de calculer le delta de CA total entre une semaine et toutes celles qui suivent (mais en limitant au delta inférieur à l'objectif visé) --> quand on lui ajoute le CA de la 1ere semaine du delta, ceci correspond au CA cumulé de la 1ere semaine à la dernière de la comparaison. Dans le même CTE on calcule aussi le delta entre les numéros de semaines correspondant (=durée de la période en nombre de semaines -1).
    Il ne reste plus qu'à faire un TOP 1 sur ce résultat trié par le CA cumulé.
    Plus facile à comprendre en regardant le code.

    Par contre 3 remarques :
    • Le code suivant suit une recherche par pourcentage inférieur : il pourra par exemple trouver 43% quand on cherche un objectif de 50%. Mais c'est pas forcément le plus pertinent car par exemple s'il pourrait exister un résultat à 50.7%.
      Mais ce n'est pas très compliqué d'intégrer la logique de recherche inverse et de renvoyer le plus proche de l'objectif après avoir comparé les résultats de la recherche par le bas et celle par le haut.
    • Ensuite la solution ne répond pas complètement à l'énoncé du problème : "quelle est la période la plus courte pour ....". Car elle se base sur le CA cumulé le plus proche de l'objectif.
      Or par exemple elle pourrait renvoyer une solution sem 8 + 24 avec CA=48.9% alors qu'il existe une solution sem 19 + 15 avec CA = 47.3%
      Donc pour être vraiment pertinent, il faudrait ajouter une pondération. Je vais y réfléchir .....
    • Par contre j'ai testé sur une base avec de vraie données. Pas énorme : presque 300 000 tickets. Par rapport au code ci-dessous il faut donc ajouter une première agrégation pour obtenir la forme [Sem - CA/sem]. De façon atomique, cette agrégation sur 52 semaines ne prend "que" 3s. Mais une fois intégrer au CTE ci-dessous, la requête met alors plus de 4min ... Alors que si j'intègre l'agrégation dans une table temporaire et puis exécute le CTE --> la requête totale ne prend alors que 3s.....
      Je ne suis pas très bon en analyse de plan d'exécution mais je ne comprends pas trop cet énorme écart ?!



    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
    19
    20
    21
    22
    23
    24
    25
    26
    27
    declare @CAPourCible as dec
    set @CAPourCible = 50
     
    ;with CTE_T1 (t1sem, t1ca) as
        (select 202301, 10 union all
         select 202302, 110 union all
         select 202303, 040 union all
         select 202304, 080 union all
         select 202305, 125 union all
         select 202306, 090 union all
         select 202307, 100 union all  
         select 202308, 130 union all
         select 202309, 070 union all
         select 202310, 065 union all
         select 202311, 100 union all  
         select 202312, 110
        )
    	,CTE_CA_WktoEnd (T1sem, CAtot, CAwk, CAWktoEnd) as (select T1sem, sum(CAST(T1ca as dec)) OVER (), 
    		sum(t1ca) OVER (PARTITION BY T1sem) ,
    		sum(t1ca) over (order by t1sem rows between 0 preceding and 12 following)
    	from CTE_T1 )
    	,CTE_CA_Cross (Sem, Semdelta, CAtot, CAdelta) as (select T1.T1sem , T2.T1sem - T1.T1sem, T1.CAtot, T1.CAwk + T1.CAWktoEnd - T2.CAWktoEnd
    	from CTE_CA_WktoEnd T1 
    		inner join CTE_CA_WktoEnd T2 on T2.T1sem > t1.T1sem  
    		where ((T1.CAwk + T1.CAwktoEnd - T2.CAwktoEnd))/T1.CAtot <= @CAPourCible/100)
     
    select top 1 Sem as SemDebut, Semdelta + 1 As SemNb, CAdelta , CAtot, (CAdelta/CAtot)*100 as CAPercent from CTE_CA_Cross order by CAdelta  desc

  5. #5
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 960
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 960
    Points : 4 389
    Points
    4 389
    Par défaut
    Exemple avec toutes les semaines d'une année :

    https://dbfiddle.uk/wTNnWllf



    Si vous modifiez votre étape d'aggrégation pour avoir (annee, sem, ca), vous gagnez une étape de CTE, mais de toute façon la réponse est quasi instantanée
    bien que l'on calcule ici toutes les possibilités: 52417
    ).

    Attention que l'exemple considère que l'on travaille bien par année "fiscale" et non par année "espace de 52 semaines à partir d'une semaine quelconque donnée",
    dans ce dernier cas il faudra partitioner par cet espace en les numérotant dans votre étape d'agrégation
    (et dans ce cas vous n'économiserez plus le CTE qui ajoute le row_number() car vous pourriez avoir un espace sur plusieurs années).

    Notez que dans votre query, on ne voit pas trop que ce vient faire cette limitation à "12 following" dans la logique, les nombres en paramètre de "preceding" et "following" pour "rows"
    sont ... des nombres de lignes. (on peut mettre "range" pour limiter sur l'espace de la colonne agrégée, donc ici cela serait un montant - sans intérêt pour ce problème)
    En mettant 12 vous vous limitez donc à 12 lignes (soit 3 mois s'il n'y a pas de trous...), quel est le but ?

  6. #6
    FMJ
    FMJ est déconnecté
    Membre averti
    Profil pro
    tutu
    Inscrit en
    Octobre 2003
    Messages
    416
    Détails du profil
    Informations personnelles :
    Localisation : France, Aveyron (Midi Pyrénées)

    Informations professionnelles :
    Activité : tutu

    Informations forums :
    Inscription : Octobre 2003
    Messages : 416
    Points : 363
    Points
    363
    Par défaut
    Bonjour Jeite et merci pour ton exemple.
    Oui c'est globalement la même logique que mon code ci-dessus.
    Et oui au temps pour moi j'avais oublié de virer cet between 0 preceding and 12 following qui ne sert à rien.

  7. #7
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 960
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 960
    Points : 4 389
    Points
    4 389
    Par défaut
    si c'était la même logique cela donnerait le même résultat...

Discussions similaires

  1. Trouver l'arborescence des plus courts chemins (algorithme de Bellman-Ford)
    Par geforce dans le forum Algorithmes et structures de données
    Réponses: 12
    Dernier message: 11/03/2015, 06h39
  2. JAVA - Trouver l'arborescence des plus courts chemins
    Par geforce dans le forum Débuter avec Java
    Réponses: 4
    Dernier message: 24/02/2015, 17h11
  3. Trouver les chemins les plus courts avec l'algorithme de Bellman-Ford
    Par geforce dans le forum Algorithmes et structures de données
    Réponses: 3
    Dernier message: 06/02/2015, 17h28
  4. Trouver le k-ème plus court chemin
    Par zamato dans le forum Algorithmes et structures de données
    Réponses: 5
    Dernier message: 31/08/2011, 22h56
  5. Trouver le chemin le plus court
    Par poly128 dans le forum Langage
    Réponses: 8
    Dernier message: 24/04/2006, 09h28

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo