Dans celui-ci nous allons voir le plus gros du reste car bien souvent la traduction est très directe.
Les opérateurs numériques
Nous pouvons déjà les éliminer rapidement : pour faire des maths, on utilise exactement les mêmes opérateurs courants ! C’est juste que selon le langage, parfois un opérateur dans l’un est une fonction dans l’autre et vice versa.
L’exemple typique est le modulo (le reste de la division entière) :
SELECT MOD(10,3) AS resultat;
-- donne 1alors qu’en R (comme dans de nombreux langages de programmation1), il existe un opérateur %% :
10 %% 3
1Les fonctions numériques
Les seules fonctions numériques classiques que nous allons voir en SQL sont : CEIL, FLOOR, ROUND et TRUNCATE. La plupart des autres fonctions mathématiques sont directement transposables et pour les retraitements PMSI vous n’aurez pas besoin de beaucoup plus.
| SQL | Fonction | R |
|---|---|---|
CEIL() ou CEILING() | arrondi à la valeur entière supérieure | ceiling() |
FLOOR() | arrondi à la valeur entière inférieure | floor() |
ROUND(x, n) | arrondi à la valeur à n décimales la plus proche si n est positif arrondi à la puissance de 10(-n) la plus proche si n est négatif | round(x, digits = n) |
TRUNCATE(x, n) | raboter le nombre à n décimales | trunc(x, digits = n) |
| (n’existe pas)2 | raboter à n chiffres significatifs (c’est à dire aussi bien avant qu’après la virgule) | signif(x, digits = n) |
Les fonctions de booléens
AND, OR, XOR, NOT sont des opérateurs en SQL (binaires pour les 3 premiers, unaire pour NOT) et s’écrivent en toute lettre.
En R, il y a une feinte car XOR s’écrit sous forme de fonction : xor(), tandis que les autres sont bien des opérateurs mais AND s’écrit &, OR s’écrit | et NOT, !.
# TRUE AND FALSE
TRUE & FALSE
# TRUE OR FALSE
TRUE | FALSE
# TRUE XOR FALSE
xor(TRUE, FALSE) # xor est une fonction
# NOT TRUE
!TRUELes fonctions de dates et date-heures
Les dates (DATE en SQL, Date en R) et date-heures (TIMESTAMP en SQL, POSIXct/lt en R) s’écrivent de la même façon en SQL et en R. Elles respectent le format « AAA-MM-JJ hh:mm:ss FUSEAU » (le format ISO international) avec des tirets « – » comme séparateurs des éléments de date puis des « : » comme séparateurs de l’heure.
Il est ensuite possible de les formater pour affichage.
En SQL, il faut utiliser TO_CHAR() en passant le format en 2ème paramètre. En R, on utilise format() de la même façon, par contre la définition du format change :
| Signification | SQL TO_CHAR(..., "<ICI>") | R format(..., format = "<ICI>") |
|---|---|---|
| Année sur 4 chiffres | YYYY | %Y |
| Année sur 2 chiffres | YY | %y |
| Mois numérique | MM | %m |
| Mois abrégé | MON | %b |
| Mois complet | MONTH | %B |
| Jour du mois | DD | %d |
| Jour de l’année | DDD | %j |
| Jour de semaine abrégé | DY | %a |
| Jour de semaine complet | DAY | %A |
| Heure (00-23) | HH24 | %H |
| Heure (01-12) | HH12 | %I |
| Minutes | MI | %M |
| Secondes | SS | %S |
| AM/PM | AM | %p |
| Timezone | TZR | %z pour le décalage horaire |
D’autres formats sont possibles, j’ai listé les principaux et les plus compatibles.
Inversement, on peut lire une chaine de caractères vers une date avec les mêmes types de format grâce à :
En SQL : TO_DATE() pour obtenir une date, TO_TIMESTAMP() pour obtenir… un TIMESTAMP (c’est à dire un date-heure)
En R : as.Date() pour obtenir une date ; as.POSIXct() ou as.POSIXlt()3 pour obtenir l’équivalent d’un TIMSTAMP. En y passant format="<...>", vous pourrez directement décoder une chaine de caractères grâce au tableau ci-dessus.
Toutes les fonctions d’extractions et de calcul SQL seront possibles avec une librairie R comme lubridate. Je vous renvoie aux pages d’aide pour le moment sous peine de démultiplier inutilement la taille de cet article…
Les fonctions de condition
On appelle fonction de condition une fonction qui retourne une valeur différente selon une valeur discriminée en entrée.
IF()SQL
IF() est un faux ami (car if() en R n’est pas une fonction mais un contrôleur de flux et il n’est pas vectorisé donc pas utilisable dans un mutate()). Cependant IF() fonctionne exactement comme une autre fonction R : ifelse(), elles attendent 3 paramètres :
- Un test
- Le code à exécuter si le test est positif
- Le code à exécuter si le test est négatif
Et la valeur de retour est le résultat du code au final exécuté.
A noter que dplyr fournit une alternative sous la forme de la fonction if_else(). Celle-ci fonctionne pareil mais en permettant de spécifier un 4ème paramètre qui sera appelé si le test renvoie NA (le pendant de NULL).
# SELECT IF(jour="lundi","C'est lundi","revenez plus tard") AS jsem;
jsem <- ifelse(jour == "lundi", "C'est lundi", "revenez plus tard")
# ou avec dplyr
jsem <- if_else(jour == "lundi", "C'est lundi", "revenez plus tard", "Vous avez passé la valeur nulle")CASE … WHEN …THEN … ELSE … END
Il existe 2 façons d’écrire un CASE WHEN... en SQL
avec une paramètre après le CASE
SELECT
CASE a
WHEN 1 THEN "A"
WHEN 2 THEN "B"
ELSE "Z"
END;En R, comme pour if(), l’équivalent direct n’est pas vectorisé : il s’agit de switch(...). Je ne vais même pas vous le montrer 😁.
En pratique pour obtenir le même fonctionnement, on utilisera soit un vecteur nommé si on ne veut pas utiliser dplyr :
a <- c("1", "2", "3", "1")
cases <- c(
"1"="A",
"2"="B"
)
cases[a]
1 2 <NA> 1
"A" "B" NA "A"
# Il faut ensuite mettre la valeur par défaut :
cases[is.na(cases)]<-"Z"Mais en réalité, on utilise la structure dplyr que nous allons voir ci-dessous.
sans paramètre placé après le CASE
-- Si a et b sont définis bien sûr...
SELECT
CASE
WHEN a = 1 THEN "A"
WHEN b = 2 THEN "B"
ELSE "Z"
END;Comme souvent dplyr a la solution simple et lisible : case_when() qui gère chaque cas dans l’ordre de lecture avec un repli sur la valeur .default= si tous les tests sont négatifs. Et cette fonction est vectorisée donc utilisable sur des vecteurs ou au sein d’un mutate() ou d’autres fonctions.
case_when(
a == 1 ~ "A",
b == 2 ~ "B",
.default = "Z")
# Exemple d'application : définition du jour ouvré suivant :
josuivant <- \(DATE){
case_when(wday(DATE) == 0 ~ DATE + 1, # Dimanche
wday(DATE) == 6 ~ DATE + 2, # Samedi
wday(DATE) == 5 ~ DATE + 3, # Vendredi
.default = DATE + 1)
}(La première clause n’est pas strictement nécessaire vu qu’elle est couverte par le .default= c’est juste pour illustrer)
Comme en SQL, rien n’empêche d’avoir des calculs déclenchés par le test (comme ci-dessus) ou des tests sur des paramètres différents (comme ci-après) :
# Exemple : personnalise le libellé en fonction du type de montée.
# DATE est la date de passage aux urgences et DENT la date du séjour
case_when(DENT == DATE ~ "DIRECTE",
DENT == DATE + 1 ~ "LENDEMAIN",
DENT == josuivant(DATE) ~ "JO SUIVANT",
.default = "NON URG"))NULLIF()SQL
En SQL, NULLIF() retourne la valeur NULL si les 2 valeurs qui lui sont passées sont égales. Il n’y a pas d’équivalent strict, il faut utiliser ifelse() ou case_when() pour reconstruire le fonctionnement (et au passage gérer le cas particulier des valeurs NA qui, rappelons-le, sont gérées différemment de NULL en SQL).
Dans les faits NULLIF() n’est en fait qu’un CASE...WHEN...END particulier :
SELECT
CASE WHEN a = b THEN NULL ELSE a END;# version brutale de la définiton d'un nullif() en R
nullif <-\(a,b){ifelse(a == b,NA, a)}Franchement, n’utilisez pas la fonction ci-dessus. R a mieux à proposer au travers d’un case_when() plus lisible qui vous permettra de bien déterminer que faire dans chaque cas particulier (quand a ou b sont NA en particulier) !
case_when(
is.na(a) & is.na(b) ~ <valeur si a et b sont NA>,
is.na(a) ~ <valeur si a est NA seulement>,
is.na(b) ~ <valeur si b est NA seulement>,
a == b ~ NA # cas particulier du NULLIF
.default = a # la valeur par défaut du NULLIF
) Et bien souvent le NULLIF() n’est pas la finalité du traitement et l’usage d’un bon case_when() bien écrit gèrera tout en une fois.
COALESCE()SQL
Cette fonction sert là aussi à remplacer les valeurs NULL qui traineraient dans vos jeux de données.
dplyr la transpose par coalesce() qui accepte un ensemble de vecteurs de taille constante sauf le dernier qui peut être de taille 1 (en fait un intermédiaire peut être de taille 1, mais les suivants seront ignorés vu que les NA auront été remplacés).
Une autre contrainte est que les types doivent être compatibles.
a <- c(1, 2, NA, 4, NA, 6)
b <- c(0, 0, -3, NA, NA, NA)
c <- -5
coalesce(a, b, c)
[1] 1 2 -3 4 -5 6Conclusion
Il existe encore bon nombre de fonctions SQL plus ou moins standardisées (surtout moins que plus désormais…) mais nous avons fait le tour des principales depuis le début de cette série d’articles. Vous devriez donc être en mesure de transposer toute votre logique SQL en pipeline de traitement R.
Si vous séchez sur une transposition que je n’ai pas couverte, n’hésitez pas à demander dans les commentaires.
Il ne manque plus qu’une chose, mais la plus cruciale, l’accès aux données elles-mêmes. C’est ce que nous verrons dans le prochain article ! Bonne entrainement d’ici là.
%en python, C, perl, et la plupart des langages. Que ce soit%%en R est un presque mystère (c’est lié au fait que d’écrire une fonction%...%permet de créer des opérateurs personnalisés donc%a un sens de délimitateur ce qui introduisait une interprétation équivoque) ↩︎- Il faut passer par une infame bidouille :
ROUND(x / POWER(10, FLOOR(LOG10(ABS(x))) - (digits - 1))) * POWER(10, FLOOR(LOG10(ABS(x))) - (digits - 1))↩︎ - POSIXct et POSIXlt sont 2 façons différentes de représenter des dates et heures :
POSIXct correspond à ce qu’on appelle communément l’heure « C » (le langage) c’est à dire le nombre de secondes depuis l’epoch (1er janvier 1970 0h00 GMT).
Tandis que POSIXlt est un format complexe qui précise une date sous forme de ses constituants.
La conversion entre les 2 est grosso-modo transparente, mais POSIXct ne gère pas les timezones nativement,ce qui peut être un mal ou un bien… Quoi qu’il en soit. Si vous devez gérer des dates, renseignez vous sur les librairies lubridate ou clock toutes 2 membres du tidyverse et retenez mon conseil : Gérer les dates et heures et bien plus compliqué qu’il n’y parait ! Laissez cela à ceux qui savent et ne cherchez pas à réinventer la roue, vous feriez une sortie de piste assurée au premier virage ! ↩︎