3
votes

DATE_DIFF mais en ne comptant que les jours ouvrés

J'ai une requête assez simple; il ressemble à ceci:

+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 3/29/19    | 4/3/19      |               2 |
| 3/29/19    | 4/2/19      |               3 |
+------------+-------------+-----------------+

Le seul problème est que je dois calculer la différence de date en jours ouvrables, pas tous les jours.

Donc, au lieu de ce qui précède requête renvoyant:

+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 3/29/19    | 4/3/19      |               5 |
| 3/29/19    | 4/2/19      |               4 |
+------------+-------------+-----------------+

Je veux qu'elle renvoie:

SELECT
order_date,
pickup_date,
DATE_DIFF(pickup_date,order_date, day) order_to_pickup
FROM
`orders.table`

sql google-bigquery datediff

3 commentaires

Qu'est-ce qui définit un jour ouvrable? Chaque jour de la semaine est-il un jour ouvrable ou avez-vous un calendrier des jours fériés qui ne compte pas non plus comme des jours ouvrables?

@ElliottBrossard Nous pouvons simplement dire pour cela que les jours ouvrables sont strictement du lundi au vendredi

cela peut vous aider: stackoverflow.com/questions / 252519 /…

3 Réponses :

3
votes

Je pense qu'il y a une solution intelligente à trouver si l'on considère le nombre de semaines entre les deux dates, mais voici une approche par force brute en attendant:

CREATE TEMP FUNCTION BusinessDateDiff(start_date DATE, end_date DATE) AS (
  (SELECT COUNTIF(MOD(EXTRACT(DAYOFWEEK FROM date), 7) > 1)
   FROM UNNEST(GENERATE_DATE_ARRAY(
       start_date, DATE_SUB(end_date, INTERVAL 1 DAY))) AS date)
);

WITH OrdersTable AS (
  SELECT
    DATE '2019-03-29' AS order_date,
    DATE '2019-04-03' AS pickup_date UNION ALL
  SELECT
    '2019-03-29',
    '2019-04-02'
)
SELECT
  order_date,
  pickup_date,
  BusinessDateDiff(order_date, pickup_date) AS order_to_pickup
FROM OrdersTable
ORDER BY pickup_date
+------------+-------------+-----------------+
| order_date | pickup_date | order_to_pickup |
+------------+-------------+-----------------+
| 2019-03-29 |  2019-04-02 |               2 |
| 2019-03-29 |  2019-04-03 |               3 |
+------------+-------------+-----------------+

Pour votre entrée, j'obtiens:

CREATE TEMP FUNCTION BusinessDateDiff(start_date DATE, end_date DATE) AS (
  (SELECT COUNTIF(MOD(EXTRACT(DAYOFWEEK FROM date), 7) > 1)
   FROM UNNEST(GENERATE_DATE_ARRAY(
       start_date, DATE_SUB(end_date, INTERVAL 1 DAY))) AS date)
);

1 commentaires

Oui! Je pense que cela me donne ce dont j'ai besoin! Je vous remercie!

1
votes

Voici une méthodologie de travail pour calculer les jours ouvrables entre les dates en fonction du travail effectué dans la communauté Looker Discourse ici . L'exemple d'origine est pour Redshift, je l'ai donc adapté pour BigQuery ci-dessous.

SELECT
  order_date,
  pickup_date,
  CAST(-1*(DATE_DIFF(order_date, pickup_date, DAY) - ((FLOOR(DATE_DIFF(order_date, pickup_date, DAY) / 7) * 2) +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  order_date) - EXTRACT(DAYOFWEEK  FROM  pickup_date) IN (1,  2,  3,  4,  5) AND EXTRACT(DAYOFWEEK  FROM  pickup_date) != 0 THEN 2
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  order_date') != 0 AND EXTRACT(DAYOFWEEK  FROM  pickup_date) = 0 THEN 1
            ELSE 0
          END +
          CASE
            WHEN EXTRACT(DAYOFWEEK  FROM  order_date) = 0 AND EXTRACT(DAYOFWEEK  FROM  pickup_date) != 0 THEN 1
            ELSE 0 END)) AS int64) AS weekdays
  FROM
    `orders.table`

En appliquant ceci à votre ensemble de données, nous obtenons:

SELECT
  CAST(-1*(DATE_DIFF(DATE '2019-01-01', DATE '2019-01-31', DAY) - ((FLOOR(DATE_DIFF(DATE '2019-01-01', DATE '2019-01-31', DAY) / 7) * 2) +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') - EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') IN (1,  2,  3,  4,  5) AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') != 0 THEN 2
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') != 0 AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') = 0 THEN 1
          ELSE 0
        END +
        CASE
          WHEN EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-01') = 0 AND EXTRACT(DAYOFWEEK  FROM  DATE '2019-01-31') != 0 THEN 1
          ELSE 0 END)) AS int64) AS weekdays

0 commentaires

3
votes

Cela devrait être la solution simplifiée et non bruteforce mentionnée par @Elliott Brossard:

select
  order_date,
  pickup_date,
  case 
    when date_diff(pickup_date, order_date, week) > 0 
      then date_diff(pickup_date, order_date, day) - (date_diff(pickup_date, order_date, week) * 2)
    else
      date_diff(pickup_date, order_date, day) 
  end
from `orders.table`

0 commentaires