Pourquoi utiliser la clause WITH dans les requêtes Bigquery ?

Avec le langage SQL standard de BigQuery, il est possible d'utiliser la clause WITH.

Ceci présente de nombreux avantages :

• Lecture du code SQL facilitée
• Process de Debugging plus facile et rapide (moins de code à tester/retravailler)
• Allegement du code SQL (pour les sous-requetes utilisées à de multiples reprises)

La clause WITH permet simplement de donner un nom à une sous-requête pour y faire reference (comme les alias pour les noms de champs ou de tables).
Ainsi, si une sous-requête est utilisée plusieurs fois et qu'on souhaite la modifier, le changement dans la clause WITH appliquera les modifications partout.

Par exemple, si on prend le code SQL ci-dessous, il peut être ecrit avec une clause WITH.

#standardSQL
SELECT DISTINCT date, visitor_id, session_id, ClientId
FROM (
  SELECT
  date,
  fullVisitorId AS visitor_id,
  CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id,
  (SELECT value FROM UNNEST(hits.customDimensions) WHERE index=2 GROUP BY value) AS ClientId
  FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
  WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231' 
  )
WHERE session_id IN (
  SELECT
  CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id
  FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
  WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231'
  AND hits.type = 'PAGE'
  AND hits.page.pagePath = '/Resiliation/Infos/'
  )
AND ClientId != ''
ORDER BY visitor_id

Code SQL réécrit avec la clause WITH :

#standardSQL
WITH
raw_data as (
SELECT
date,
fullVisitorId AS visitor_id,
CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id,
(SELECT value FROM UNNEST(hits.customDimensions) WHERE index=2 GROUP BY value) AS ClientId
FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231' 
),
session_id_resiliation as (
SELECT
CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS session_id
FROM `mon-compte-big-query.xxx.ga_sessions_*`, UNNEST(hits) as hits
WHERE _TABLE_SUFFIX BETWEEN '20190101' AND '20191231'
AND hits.type = 'PAGE'
AND hits.page.pagePath = '/Resiliation/Infos/'
)

SELECT DISTINCT date, visitor_id, session_id, ClientId
FROM raw_data
WHERE session_id IN session_id_resiliation
AND ClientId != ''
ORDER BY visitor_id

Pour plus d'information :

- Interet d'utiliser WITH : https://stackoverflow.com/questions/12552288/sql-with-clause-example