Discussion :

[rguillau]

Bonjour
(Premier post sur le forum, pardonnez moi si je rate qqc)

J'ai un KPI "On time delivery ou OTD" qui se calcule comme suit : Parmi les X orders qui devaient etre livrées à telle date, combien ont été livrées dans les temps (<= ETA delivery)

Probleme: Je sais calculer l'OTD sur une période de temps (disons tous les jours, tous les mois), mais je cherche a obtenir cet OTD sur une période de 30 jours roulants et ce tous jours.

ex (jour au hasard en 2023): 10-06-2023 / OTD = Nb commandes qui devaient etre livrées entre du 11-05-2023 et le 09-06-2023 et bien été livées à temps


--------
La query qui me donne le KPI tous les jours (mais pas roulant sur la somme des 30J dans le passé) que j'essaye d'améliorer
(Il y a aussi une metric d'on time shipment dans la query que je n'ai pas retirée, mais l'idée est la meme)

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 
  SELLER_ID
  ,SELLER_NAME
  ,DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) AS DATE_DELIVERY_ETA
  ,COUNT(DISTINCT(ORDER_ID)) AS NB_ORDERS
  ,COUNT(DISTINCT(CASE WHEN SHIPPED_ON_TIME = 1 THEN ORDER_ID END)) AS NB_ORDERS_OTS
  ,ROUND(SAFE_DIVIDE(COUNT(DISTINCT(CASE WHEN SHIPPED_ON_TIME = 1 THEN ORDER_ID END)),COUNT(DISTINCT(ORDER_ID))), 4) AS OTS
  ,COUNT(DISTINCT(CASE WHEN DELIVERED_ON_TIME = 1 THEN ORDER_ID END)) AS NB_ORDERS_OTD
  ,ROUND(SAFE_DIVIDE(COUNT(DISTINCT(CASE WHEN DELIVERED_ON_TIME = 1 THEN ORDER_ID END)),COUNT(DISTINCT(ORDER_ID))), 4) AS OTD
FROM `universe-prod-20220914.shipping.universe_delivery_forwards`
WHERE 1=1
  AND SELLER_ID = '1EU' -- Filter on specific sellers
  AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) >= CURRENT_DATE()-365 -- Date start
  AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) < CURRENT_DATE() -- Date end - maximum current date (exclude orders to be delivered after today)
GROUP BY 1,2,3
ORDER BY 1,3 DESC

Champs comportés par ma table en pj
DATETIME_CREATION_ORDER
DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME
DATETIME_DELIVERY_ORDER
DATETIME_SHIPPING_ORDER
DATETIME_SHIPPING_ORDER_ESTIMATED_LOCAL TIME
DELIVERED_ON_TIME
SHIPPED

J'utilise Google cloud / Big Query

Merci d'avance

[vanagreg]

Bonjour,

Tu peux utiliser les fonctions analytiques COUNT(...) OVER (...) ou SUM(...) OVER (...) pour faire des comptages glissants. Si tu fournis un petit jeu de données on pourrait illustrer.

Merci

[rguillau]

Merci !
Voici un exemple de data dans la table utilisée Cas data OTD.xlsx

[LEGALLJJA]

bonjour,

il suffit d'encadrer DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME par trunc(sysdate)-30 et sysdate

Code sql :

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

1
2
AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) >= trunc(sysdate)-30 
  AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) <  trunc(sysdate)

escusez moi si la réponse n'est pas juste
cordialement

[al1_24]

@LEGALLJA:
TRUNC pas plus que SYSDATE ne sont des fonctions du langage SQL normalisé.
Sur tout autre SGBD que celui que vous connaissez, cette formulation conduira à une erreur de syntaxe.

[LEGALLJJA]

bonjour,

Avec CURRENT_DATE c'est plus correct

Code sql :

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

1
2
AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) >= CURRENT_DATE -30 
AND DATE(DATETIME_DELIVERY_ORDER_ESTIMATED_LOCAL_TIME) <  CURRENT_DATE

cordialement

[Michel.Priori]

ex (jour au hasard en 2023): 10-06-2023 / OTD = Nb commandes qui devaient etre livrées entre du 11-05-2023 et le 09-06-2023 et bien été livées à temps

Je ne comprends pas pourquoi "les 30 derniers jours glissants" ne prennent pas en compte le jour J.
C'est peut être plus "exact" dans le sens où les valeurs seront les mêmes toute la journée mais, sémantiquement, je m'attends à ce que les résultats du jour J soient comptabilisés.
Donc je suppose que vous avez un compteur à part pour le jour J.

Ensuite, la date de référence est un élément crucial :
Si c'est la même pour tous les livreurs, comme la date du jour (ou un scalaire contenu dans une variable), alors une simple condition WHERE avec un calcul de date pour soustraire les fameux 30 jours (CURRENT_DATE()-30) et l'affaire est réglée

Si la période est variable par livreur, là c'est plus complexe !
Exemple de demande : "Pour chaque livreur sur ces x derniers jours calendaires à partir de sa dernière journée d'activité, sachant que 1<x<30. Afficher le nombre de jours de la période considérée, le nombre de commandes confiées et le nombre de commandes livrées à temps".
Autre exemple : "Pour chaque livreur, sur les 30 derniers jours d'activité effectifs, afficher le nombre de commandes confiées et le nombre de commande livrées à temps. Note : les livreurs n'ayant pas 30 jours d'activité auront null comme décompte".
Je rejoins vanagreg dans le fait qu'on ait a utiliser des fonctions analytiques. https://cloud.google.com/bigquery/do...function-calls


Peut tu expliciter la demande ?