Afin de travailler avec des exemples concrets que vous pouvez suivre, nous allons d'abord créer deux tables temporaires nommées "personnes" et "salaire", puis ces deux tables temporaires seront utilisées pour le reste de l'exercice. L'avantage de cette approche est que vous pouvez copier et coller la partie de création de tableau, puis vous entraîner le long des étapes suivantes pour maximiser l'opportunité d'apprentissage. Veuillez noter que vous n'avez pas besoin de comprendre la partie création de table du travail à ce stade. La partie que vous devez comprendre commence après cela.
Alors, allez-y, copiez et collez ce qui suit dans votre outil SQL préféré et appuyez sur Exécuter.
DROP TABLE IF EXISTS salary;
CREATE TEMPORARY TABLE salary (city VARCHAR(30), average_salary int);
INSERT INTO
salary
VALUES
('san_francisco', '54500'),
('seattle', '54100'),
('new_york', '34400'),
('phoenix', '31800');
DROP TABLE IF EXISTS people;
CREATE TEMPORARY TABLE people (
person_id int,
name VARCHAR(30),
gender VARCHAR(30),
location VARCHAR(30),
birth_year int,
birth_month VARCHAR(30),
birth_day int,
job_title VARCHAR(30),
salary int
);
INSERT INTO
people
VALUES
(
'1',
'james',
'male',
'seattle',
'1984',
'9',
'15',
'software_developer',
'115000'
),
(
'2',
'mary',
'female',
'new_york',
'1992',
'1',
'13',
'financial_analyst',
'183000'
),
(
'3',
'john',
'male',
'san_francisco',
'1971',
'4',
'22',
'data_scientist',
'165000'
),
(
'4',
'patricia',
'female',
'phoenix',
'1971',
'8',
'15',
'physician',
'215000'
),
(
'5',
'michael',
'male',
'new_york',
'1966',
'1',
'13',
'retired',
'25000'
),
(
'6',
'jennifer',
'female',
'phoenix',
'1994',
'12',
'12',
'data_scientist',
'165000'
);
1. Bases
Voyons maintenant ce que nous avons créé dans les deux tables temporaires ci-dessus. Je vais les présenter sous forme de questions et de réponses afin que vous puissiez également essayer de résoudre chaque question en premier, avant de regarder la réponse que j'ai fournie. Ce que j'ai fourni n'est qu'une façon de résoudre ces questions - généralement, il y a plus d'une bonne façon d'arriver à la même réponse.
Q1 : Récupérez toutes les données disponibles dans le tableau people
.
A1 :
En général, je vais d'abord fournir les réponses, puis expliquer ce qui s'est passé et comment fonctionnent différents concepts SQL, soit sous la même question, soit sous la suivante. Par exemple, Q2 expliquera ce qui s'est passé en Q1.
SELECT
*
FROM
personnes ;
Résultats:
![](https://miro.medium.com/max/700/1*g0aVFrqYqH1-Qg90GmmQ_g.png)
salary
Et pour référence future, voici à quoi ressemble le tableau , en utilisant ce qui suit :
SELECT
*
FROM
salaire ;
![](https://miro.medium.com/max/340/1*cP7aGHQBVT001QdUqp1oMg.png)
Conseil de pro : à ce stade, je vous suggère de prendre un instantané de ces deux tableaux. J'ai ajouté des instructions ci-dessous sur la façon de prendre un instantané. Vous voudrez vous référer aux tableaux pour écrire des requêtes à l'avenir et faire défiler de haut en bas dans cet article pour trouver que ces deux tableaux ne seront pas amusants. Pour plus de commodité, j'ai inclus des instructions sur la façon de prendre un instantané sur Mac et Windows :
- Utilisateurs Mac — Appuyez sur ces trois touches et maintenez-les enfoncées : Maj, Commande et 4, puis sélectionnez la zone pour l'instantané. Si vous ne souhaitez pas sélectionner manuellement la zone d'instantané, appuyez simultanément sur Maj, Commande et 3 pour créer un écran d'impression.
- Utilisateurs Windows - Appuyez sur ces trois touches ensemble : Touche Windows + Maj + S, puis créez un instantané à l'aide de l'outil Snip and Sketch (je suis sur un Mac, donc cela est basé sur une recherche Google - j'espère que cela fonctionnera).
Q2 : Expliquez ce qui s'est passé dans la requête précédente.
A2 : SELECT
l'instruction est utilisée pour sélectionner des données à partir d'une base de données donnée (c'est-à-dire une table). FROM
est utilisé pour indiquer dans quelle base de données les données doivent être sélectionnées ("personnes" dans cet exemple). Et enfin *
dit simplement tout sélectionner dans cette base de données.
Q3 : Afficher uniquement les données des colonnes suivantes : ['name', 'gender', 'job_title']
.
A3 :
SELECT
nom,
sexe,
job_title
FROM
personnes ;
Résultats:
![](https://miro.medium.com/max/526/1*U2beV7DhnpyhRZ9rrb2Jlg.png)
Celui-ci était simple - nous pouvons indiquer explicitement si les colonnes doivent être sélectionnées.
Q4 : Quelles personnes travaillent à New York ?
A4 :
SELECT
nom
FROM
personnes
WHERE
emplacement = 'new_york';
Résultats:
![](https://miro.medium.com/max/186/1*_CO-YT4eUhNZ48pYePE78A.png)
Comme le montre cet exemple, nous pouvons filtrer les résultats à l'aide de WHERE
.
3. Agrégations
Les agrégations nous aident à effectuer des analyses de base sur les données. Les exemples incluent COUNT()
, MAX()
, MIN()
, AVG()
et SUM()
.
Q5 : Combien de personnes vivent à Seattle ou à Phonenix ?
A5 :
SELECT
count(person_id)
FROM
people
WHERE
location IN ('seattle', 'phoenix');
Résultats:
![](https://miro.medium.com/max/184/1*BgQU5BSuUGpIM7iAGvi10Q.png)
Q6 : Combien d'individus vivent dans chacune des villes ?
A6 :
SELECT
location,
count(person_id)
FROM
personnes
GROUP BY
location ;
Résultats:
![](https://miro.medium.com/max/314/1*m3Q3nZeSwLhsW2YYIYMZWQ.png)
Nous savions déjà ce que COUNT()
c'était à partir de la question précédente, mais maintenant il y a aussi un nouveau concept à la fin de la requête. GROUP BY
L'instruction regroupe des lignes de données avec les mêmes valeurs et est utilisée avec des fonctions d'agrégation, telles que COUNT()
, MAX()
, MIN()
, AVG()
et SUM()
.
Q7 : Quel est le total des salaires à Phoenix et à New York ?
A7 :
SELECT
location,
SUM(salary) AS total_salary
FROM
people
WHERE
location = 'phoenix'
OR location = 'new_york'
GROUP BY
location;
Résultats:
![](https://miro.medium.com/max/312/1*sLafeN0A0Kt6K50yUNdaCQ.png)
Il y a deux nouveaux apprentissages dans cet exemple. La première est que dans cet exemple, nous avons attribué un alias à la SUM()
colonne et la colonne résultante est maintenant nommée total_salary
. Si nous ne l'avions pas fait, la colonne aurait ressemblé à ceci :
![](https://miro.medium.com/max/312/1*2rvn4U3IUpp0T0bsV_F55w.png)
Le deuxième apprentissage est une approche alternative. Notez que dans le WHERE
, nous utilisons OR
, qui détermine location
peut être phoenix
ou new_york
. Alternativement, nous aurions pu utiliser location IN ('phoenix', 'new_york')
, similaire à l'approche que nous avons utilisée dans Q5. Cela ne change pas les résultats et est simplement une approche alternative.
4. Jointures
C'est l'une des fonctionnalités les plus utiles. Ce. aide à visualiser les données lorsque vous essayez de comprendre les requêtes. Regardons d'abord un exemple, puis j'expliquerai ce qui se passe.
Q8 : Montrez-moi les noms des personnes, leurs emplacements et leurs salaires, ainsi que le salaire moyen dans chaque emplacement.
R8 : Notez qu'il s'agit d'un nouveau type de question que nous n'avions pas abordé auparavant. Les noms individuels, les emplacements et leurs salaires sont disponibles dans le tableau people
tandis que le salaire moyen par emplacement est disponible dans le tableau salary
. Ce dont nous avons besoin, c'est de JOIN
ces deux tables ensemble, ce qui est possible car les deux tables ont le nom des villes en commun (bien que les colonnes soient nommées location
dans people
et city
dans salary
). La façon dont nous structurons cela est la suivante :
SELECT
p.name,
p.location,
p.salary,
s.average_salary
FROM
personnes p
LEFT JOIN salaire s ON p.location = s.city ;
Résultats:
![](https://miro.medium.com/max/606/1*puYpAYQ-9-MpPHkUjdLkRg.png)
Alors que s'est-il passé exactement ? Regardons la jointure de plus près :
FROM
personnes p
LEFT JOIN salaire s ON p.lieu = s.ville
Ce qui précède dit que regarder table people
puis le joindre à table salary
. Ensuite, nous devons indiquer les colonnes de chaque table qui partagent les mêmes valeurs (imaginez assembler ces deux tables uniquement là où il y a des valeurs communes dans ces deux colonnes particulières). Les colonnes avec des valeurs mutuelles sont location
from people
et city
from salary
. Enfin, nous utilisons ici le concept d'alias. Par exemple, table people
a maintenant un alias de p
, tandis que table salary
a un alias de s
. Lorsque nous voulons indiquer la colonne location
de people
, nous pouvons soit indiquer cela comme people.location
ou p.location
. Il en va de même pour la façon dont nous indiquons la colonne city
à partir salary
de s.city
. Notez que la structure est toujours aussitable_name.column_name
. Enfin, nous avons utilisé a LEFT JOIN
dans cet exemple. Ci-dessous, je vais discuter de ce que cela signifie et des autres moyens de rejoindre des tables.
Comme mentionné, nous avons utilisé a LEFT JOIN
dans cet exemple. Il est utile d'avoir l'image ci-dessous à l'esprit lorsque l'on réfléchit à son JOIN
fonctionnement.
![](https://miro.medium.com/max/700/1*CMDOMT7ZRuHwFOndd6sXrQ.jpeg)
Voici quelques-uns des JOIN
types les plus courants :
JOIN
orINNER JOIN
: Renvoie les données qui ont des valeurs correspondantes dans les tables de gauche et de droite.LEFT JOIN
orLEFT OUTER JOIN
: Renvoie les données de la table de gauche et toutes les données correspondantes de la table de droite.RIGHT JOIN
ouRIGHT OUTER JOIN
: C'est le revers duLEFT JOIN
. Il renvoie les données de la table de droite et toutes les données correspondantes de la table de gauche.FULL JOIN
orFULL OUTER JOIN
: Renvoie toutes les données de la table de gauche ou de droite lorsqu'il y a une correspondance entre les deux
5. Conditionnels
Q9 : Créez une nouvelle colonne dans people
qui décompose les titres de poste en technologies et non technologies. Renvoie uniquement les noms, les intitulés de poste, la nouvelle catégorie nommée as job_group
et les salaires.
A9 : Afin de répondre à cette question, nous devons d'abord voir quels titres de poste uniques sont disponibles en people
utilisant la requête suivante :
SELECT
DISTINCT job_title
FROM
personnes ;
Notez qu'il DISTINCT
s'agit de l'instruction qui a été ajoutée pour SELECT
ne renvoyer que des valeurs uniques (ou distinctes) dans cette colonne, comme le montrent les résultats ci-dessous :
![](https://miro.medium.com/max/262/1*mwefjcKPOgWGrTxHTCs16Q.png)
Maintenant que nous savons quels titres de poste existent, nous pouvons les décomposer en technologies et non technologies, comme indiqué. Pour cet exercice, utilisez votre meilleur jugement pour déterminer quel rôle est technique et ce qui n'est pas technique - une partie importante est d'apprendre à implémenter cette logique dans la requête. Par exemple, financial_analyst
est un rôle non technique, tandis que data_scientist
est un rôle technique. La requête suivante fait exactement cela :
SELECT
name,
job_title,
CASE
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech'
ELSE job_title
END AS job_group,
salaire
FROM
people ;
Résultats:
![](https://miro.medium.com/max/654/1*KbbS7FLsiCzRNZFMo1dYaQ.png)
Parlons du fonctionnement de l' CASE
expression en regardant de plus près ce que nous avons fait pour cette question :
CASE
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech'
ELSE job_title
END AS job_group,
Afin de mettre en œuvre cette logique, nous commençons d'abord par l' CASE
expression puis identifions les conditions, en utilisant WHEN
, telles que :
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
Le script ci-dessus examine d'abord la colonne job_title
, et si la valeur de cette colonne pour une ligne est soit software_developer
ou data_scientist
, il affiche ensuite tech
. La même logique s'applique pour la non-tech
catégorie.
Alors il y a ELSE job_title
. Cette clause indique que si une valeur est rencontrée dans la colonne qui n'était pas couverte par les WHEN
conditions ci-dessus, renvoie la valeur qui existe dans la colonne job_title
. Par exemple, si nous avions une ligne dans la colonne job_title
avec la valeur de chef
, car chef
ne faisait pas partie de celles que nous avions incluses dans les WHEN
conditions (c'est-à-dire software_developer
, data_scientist
, financial_analyst
et physician
), alors cette clause renverrait la valeur d'origine dans la colonne job_title
, qui était chef
.
La dernière partie du script est END AS job_group
. CASE
L' expression se termine par un END
et AS job_group
est l'alias donné à la colonne résultante. C'est pourquoi la colonne est nommée job_group
dans le tableau des résultats.
Conseil de pro : l'ordre des instructions conditionnelles, en commençant parWHEN
, est important. Une fois qu'une condition est vraie, il arrête de lire le reste des conditions et renvoie le résultat. Si aucune des conditions n'est remplie, elle renvoie la valeur de laELSE
clause.
Q10 : Qu'est-ce qui job_group
rapporte le plus d'argent en moyenne ? Ordonnez les résultats du plus élevé au plus bas job_group
.
A10 : J'inclurai deux approches pour répondre à cette question, chacune introduira de nouveaux concepts. Je vais d'abord couvrir une approche pour montrer comment utiliser FROM
directement sur une sortie d'une requête, puis je couvrirai une approche différente pour démontrer l'application de WITH
la clause.
Approche 1 :
SELECT
job_group,
AVG(salary) AS average_salary
FROM
(
SELECT
person_id,
CASE
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech'
ELSE titre_emploi
END AS groupe_emploi,
salaire
FROM
personnes
)
GROUP BY
groupe_emploi
ORDER BY
salaire_moyen DESC ;
Résultats:
![](https://miro.medium.com/max/342/1*_qQLwtCbEtUG_bqS1iDoTA.png)
Alors que s'est-il passé exactement ? Dans le passé, nous incluions toujours le nom d'une table après FROM
, mais le nouveau concept ici est que nous pouvons également inclure une requête interne au lieu du nom de la table. Ce qui se passe, c'est que la requête interne après FROM
est d'abord exécutée, puis les résultats sont utilisés comme table temporaire pour sélectionner des données dans la requête externe.
Approche 2 :
Je commencerai par inclure la requête utilisée dans cette approche, puis j'expliquerai chacune des étapes de la requête. Commençons d'abord par la requête :
WITH count_table AS(
SELECT
CASE
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech'
ELSE job_title
END AS job_group,
count(person_id) as total_count
FROM
people
GROUP BY
1
),
total_salary_table AS(
SELECT
CASE
WHEN job_title IN ('software_developer', 'data_scientist') THEN 'tech'
WHEN job_title IN ('financial_analyst', 'physician') THEN 'non-tech'
ELSE job_title
END AS job_group,
SUM(salaire) as total_salary
FROM
people
GROUP BY
1
)
SELECT
ct.job_group,
tst.total_salary / ct.total_count as average_salary
FROM
count_table ct
INNER JOIN total_salary_table tst ON ct.job_group = tst.job_group
ORDER BY
average_salary DESC ;
Regardons maintenant de plus près ce qui s'est passé.
Nous allons d'abord créer une table temporaire à l'aide de la WITH
clause, nommée count_table
. Ce tableau temporaire indique le nombre d'individus dans chaque job_group
, comme indiqué ci-dessous :
![](https://miro.medium.com/max/310/1*J-ca3lub61mfURZlhxTBkA.png)
Ensuite, nous allons créer une deuxième table temporaire nommée total_salary_table
qui affiche le salaire total de chaque job_group
, comme indiqué ci-dessous :
![](https://miro.medium.com/max/306/1*q45dHgNJIK-74dHaC__l-A.png)
Maintenant que nous avons ces deux tableaux, nous pouvons les joindre pour trouver le salaire moyen de chacun job_group
, comme suit :
![](https://miro.medium.com/max/342/1*_qQLwtCbEtUG_bqS1iDoTA.png)
Les résultats ici correspondent aux résultats de l'approche 1 comme prévu.
6. Fonctions de la fenêtre
Ces fonctions sont plus difficiles à visualiser au départ, alors ne soyez pas déçu si vous ne l'obtenez pas tout de suite. Passez en revue quelques exemples et vous commencerez à mieux les comprendre. Nous les incluons généralement dans nos entretiens, je vous recommande donc de les comprendre et de les mettre en pratique.
Les fonctions de fenêtre effectuent généralement un calcul sur un ensemble de lignes de table qui sont liées d'une manière ou d'une autre. En d'autres termes, ils ressemblent en quelque sorte à des agrégations avec quelques mises en garde. Contrairement aux agrégations, les fonctions de fenêtre n'entraînent pas le regroupement des lignes. Cela deviendra plus clair avec des exemples.
Voici quelques exemples de fonctions de fenêtre : COUNT()
, AVG()
, SUM()
, ROW_NUMBER()
, RANK()
, DENSE_RANK()
, LAG
, et LEAD
et elles sont structurées comme suit :
fonction_fenêtre(nom_colonne) OVER (
PARTITION BY
nom_colonne
ORDER BY
nom_colonne ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS output_alias;
L'exemple ci-dessus montre les éléments les plus courants de la structure des fonctions de fenêtre, mais ils ne sont pas tous nécessaires. Je sais que jusqu'à présent, cela n'a pas de sens, alors examinons certains d'entre eux dans la pratique pour mieux les comprendre.
Q11 : Créez un classement des salaires dans l'ensemble et également par sexe, du salaire le plus élevé au salaire le plus bas.
A11 :
SELECT
nom,
sexe,
salaire,
RANK() OVER(
ORDER BY
salaire DESC
) AS salaire_rang_overall,
RANK() OVER(
PARTITION BY sexe
ORDER BY
salaire DESC
) AS salaire_rang_by_sexe
FROM
personnes
ORDER BY
salaire_rang_overall,
salaire_rang_by_sexe ;
Résultats:
![](https://miro.medium.com/max/700/1*SoCMCOu8F0e2HRT3zKTAxw.png)
Comme vous pouvez le voir dans les résultats, en utilisant RANK()
la fonction de fenêtre, nous avons pu classer les salaires à la fois dans une perspective globale et également dans chacune des catégories masculines ou féminines. À l'intérieur du OVER()
, nous avons indiqué que nous voulions que le classement soit décomposé (ou «partitionné») par gender
et également trié par salaire du plus élevé au plus bas en ajoutant ORDER BY salary DESC
.
Q12 : Créer une somme cumulée de chaque salaire à la fois globalement (c'est-à-dire que la somme à la ligne 2 doit être la ligne 1 + la ligne 2, la somme à la ligne 3 doit être la somme de la ligne 1 + la ligne 2 + la ligne 3, et ainsi de suite) et aussi par sexe , classés par âge (du plus vieux au plus jeune). Incluez également une colonne indiquant le salaire total à chaque ligne.
A12 :
SELECT
nom,
sexe,
année_de_naissance,
salaire,
SUM(salaire) OVER(
ORDER BY
rows_année_de_naissance entre UNBOUNDED PRECEDING
And CURRENT ROW
) AS running_total_salary_overall,
SUM(salary) OVER(
PARTITION BY gender
ORDER BY
birth_year ROWS between UNBOUNDED PRECEDING And CURRENT
ROW
) AS running_total_salary_by_gender,
SUM(salary) OVER(
ORDER BY
birth_year ROWS between UNBOUNDED PRECEDING
And UNBOUNDED FOLLOWING
) AS total_salary
FROM
people
ORDER BY
running_total_salary_overall,
running_total_salary_by_gender ;
Résultats:
![](https://miro.medium.com/max/700/1*tt3I39irQ7TJEJjMn6uv4w.png)
Une fois que vous aurez parcouru les explications ci-dessous et que vous les aurez comparées aux résultats, je suis sûr que vous apprécierez également la puissance des fonctions de fenêtre.
Celui-ci a utilisé de nouvelles déclarations, alors regardons-les de plus près. Regardons d'abord ce qui suit :
SUM(salary) OVER(
ORDER BY
birth_year ROWS between UNBOUNDED PRECEDING
And CURRENT ROW
) AS running_total_salary_overall,
SUM(salary)
additionne simplement les salaires, mais la façon dont les salaires doivent être additionnés est la partie la plus intéressante et est indiquée dans la OVER()
déclaration. Tout d'abord, les salaires sont triés par année de naissance des individus du plus bas au plus élevé, comme indiqué dans ORDER BY birth_year
, (l'absence de DESC
nous indique que c'est dans un ordre croissant). Indique ensuite ROWS
comment le SUM()
doit être appliqué à ces lignes de données. Dans ce cas, la sommation est appliquée à toutes les lignes avant chaque ligne ( UNBOUNDED PRECEDING
) jusqu'à et y compris la ligne elle-même ( CURRENT ROW
).
Et si nous voulions que la somme de chaque ligne inclue tout ce qui précède et aussi tout ce qui suit cette ligne spécifique ? Cela peut être accompli par ce qui suit :
LIGNES entre UNBOUNDED PRECEDING
et UNBOUNDED FOLLOWING
Cette structure a été utilisée pour calculer le total_salary
dans la partie suivante de la requête et vous pouvez voir dans la section des résultats :
SUM(salaire) OVER(
ORDER BY
birth_year ROWS between UNBOUNDED PRECEDING
And UNBOUNDED FOLLOWING
) AS total_salary
Étant donné que pour chaque ligne, nous voulions avoir la somme de toutes les lignes avant et après une ligne donnée, nous avons essentiellement demandé à voir la somme totale de cette colonne affichée à chaque ligne sous total_salary
, qui était la même valeur de 868,000
pour chaque ligne.
8. Divers - Opérateur UNION, gestion des valeurs nulles et gestion des dates
Jusqu'à présent, nous avons couvert certains des concepts les plus courants qui peuvent vous aider à rédiger vos propres requêtes. Dans cette partie, je vais couvrir quelques sujets supplémentaires qui peuvent également vous aider dans les entretiens.
Comme la dernière fois, créons d'abord deux nouvelles tables nommées misc_part1
, misc_part2
puis passons en revue les concepts. Pour l'instant, copiez, collez et exécutez le script ci-dessous pour créer les tables temporaires pour cette partie de l'exercice.
Point bonus (facultatif) : Maintenant que vous êtes plus familiarisé avec les différents concepts SQL, jetez un œil à ce que vous copiez et collez et voyez si vous pouvez suivre la logique. Vous pouvez voir que nous définissons d'abord une table, puis spécifions les colonnes dans la table et le type de données associé à chaque colonne, puis ajoutons des valeurs pour chaque colonne (sous le format d'une ligne de valeurs). C'est ça! Vous pouvez maintenant créer vos propres tables temporaires et commencer à en récupérer les données !
DROP TABLE IF EXISTS misc_part1;
CREATE TEMPORARY TABLE misc_part1 (
name VARCHAR(30),
last_contacted DATE,
contact_type VARCHAR(30)
);
INSERT INTO
misc_part1
VALUES
('michael', '2022-09-09', 'email'),
('elizabeth', '2022-01-23', 'sms'),
('david', NULL, 'sms'),
('linda', NULL, 'phone_call'),
('william', '2002-01-28', 'postal_mail'),
('barbara', '2019-12-01', 'email'),
('richard', '2022-08-29', NULL),
('susan', '2020-09-20', NULL);
DROP TABLE IF EXISTS misc_part2;
CREATE TEMPORARY TABLE misc_part2 (
name VARCHAR(30),
last_contacted DATE,
contact_type VARCHAR(30)
);
INSERT INTO
misc_part2
VALUES
('joseph', '2016-08-01', 'phone_call'),
('elizabeth', '2022-01-23', 'sms'),
('david', NULL, 'sms'),
('sarah', '2021-09-13', NULL),
('thomas', '2008-01-18', 'sms'),
('jessica', '2013-04-17', 'email'),
('charles', '2022-08-24', NULL),
('lisa', NULL, 'postal_mail');
Voyons maintenant à quoi ressemblent les tables. Je vais utiliser ce qui suit pour regarder mist_part1
:
SELECT
*
FROM
misc_part1 :
Résultats:
![](https://miro.medium.com/max/482/1*gpWdCV3g3T5XymTxLekmHA.png)
puis utilisera ce qui suit pour voir misc_part2
:
SELECT
*
FROM
misc_part2 ;
Résultats:
![](https://miro.medium.com/max/482/1*Y8YyS_3kwV_idOeMqlEBdA.png)
Nous pouvons considérer ces deux tableaux comme des données marketing sur la dernière fois que les clients ont été contactés et également sur la manière dont ils ont été contactés, par exemple par e-mail, appel téléphonique, etc. Il y a trois observations rien qu'en regardant les données :
- Les deux tables ont les mêmes noms de colonne, il est donc peut-être possible de les combiner dans le cadre de notre exercice, que nous explorerons plus en détail sous
UNION
. - Certaines valeurs sont manquantes, ce qui n'était pas le cas dans les tables
people
et .salary
Par exemple, en regardantmisc_part2
, les cellulesB5
etB6
sont vides. Il y a aussi des valeurs manquantes dansmisc_part1
. Nous discuterons de la gestion des nulls. - Les deux tables incluent une colonne de format de date, que nous n'avions pas dans les tables
people
et .salary
Nous utiliserons ces valeurs pour certaines manipulations de date.
Comme la dernière fois, n'hésitez pas à prendre un instantané de ces deux tableaux pour votre référence, puis continuons avec le même format de questions et réponses pour couvrir de nouveaux concepts.
8.1. Opérateur UNION
Q13 : Je vois que les deux tables incluent les mêmes colonnes. Pouvez-vous les combiner en un seul tableau ? Triez les résultats par nom et identifiez également quelle ligne appartient à quelle table.
R13 : N'oubliez pas que nous avons commencé avec deux tableaux, chacun d'eux comprenant 8 lignes de données (à l'exclusion des en-têtes). Nous nous attendons donc à ce que le tableau combiné inclue 16 lignes de données (à l'exclusion des en-têtes).
Cela peut être fait UNION ALL
en utilisant le format suivant :
SELECT
*,
1 AS misc_table_number
FROM
misc_part1
UNION ALL
SELECT
*,
2 AS misc_table_number
FROM
misc_part2
ORDER BY
nom ;
Résultats:
![](https://miro.medium.com/max/694/1*hjedX0UOJX-5Uoeyn1un5w.png)
Les résultats incluent 16 lignes de données (à l'exclusion des en-têtes) comme prévu. Parlons de ce qui s'est passé.
UNION ALL
L'opérateur place les données de chacune des requêtes et les empile les unes sur les autres. Quelques conditions doivent être remplies pour que cela fonctionne correctement :
- Chaque
SELECT
élément de l'UNION ALL
opérateur doit avoir le même nombre de colonnes. Par exemple, dans notre exercice chaque tableau comprend 4 colonnes. - Les colonnes correspondantes de chaque
SELECT
instruction doivent avoir le même type de données. Par exemple,name
dans les deux tables sont dansVARCHAR(30)
le type de données, oulast_contacted
dans les deux tables sont dansDATE
le type de données. - Les colonnes de chacune des
SELECT
déclarations doivent être dans le même ordre. En d'autres termes, l'ordre dans les deux tables de nos exemples de tables doit êtrename
,last_contacted
,contact_type
,misc_table_number
. Cette condition était également remplie dans notre exemple et c'est pourquoi nous avons pu utiliserUNION ALL
.
Q14 : En examinant les résultats de la Q13, il semble que les deux tableaux contiennent les mêmes données pour david
et elizabeth
. Pouvez-vous créer le même tableau mais n'inclure que des lignes uniques (c'est-à-dire dédoubler les résultats) ? Il n'est pas nécessaire d'indiquer à quelles lignes du tableau appartiennent.
A14 : Cela peut facilement être fait en utilisant UNION
, au lieu de UNION ALL
. En d'autres termes, UNION
ne sélectionne que des valeurs distinctes, tandis que UNION ALL
sélectionne toutes les valeurs. La requête est la suivante :
SELECT
*
FROM
misc_part1
UNION
SELECT
*
FROM
misc_part2
ORDER BY
nom ;
Résultats:
![](https://miro.medium.com/max/482/1*5PJzDefnDXv1oxKw6Mk4Bg.png)
Comme prévu, les résultats n'incluent désormais que des lignes distinctes et le nombre total de lignes est désormais de 14 (hors en-têtes), au lieu de 16 au Q13.
8.2. Traitement nul
Q15 : Créez une table temporaire nommée combined_table
qui inclut des lignes distinctes des tables combinées (similaire à Q14). Lorsque last_contacted
la valeur est manquante, entrez la valeur sous la forme 1901-01-01
. Nous savons aussi que ce contact_type
qui manque est phone_call
donc remplissez-les également.
A15 :
WITH combination_table as (
SELECT
*
FROM
misc_part1
UNION
SELECT
*
FROM
misc_part2
ORDER BY
name
)
SELECT
name,
NVL(last_contacted, '1901-01-01') as last_contacted,
COALESCE(contact_type, 'phone_call') AS contact_type
FROM combination_table
;
Résultats:
![](https://miro.medium.com/max/480/1*tIaGk4EHQ-ofM6Wtqvei7g.png)
Les résultats sont conformes à nos attentes, alors parlons de ce qui s'est passé.
Il y a deux fonctions nulles que nous avons utilisées dans cet exercice et pour nos besoins, elles sont toutes les deux similaires. Ils renvoient tous les deux une valeur alternative lorsqu'une expression est NULL. Je voulais utiliser les deux pour les présenter tous les deux, mais vous pouvez choisir d'utiliser l'un ou l'autre. Regardons-les de plus près :
NVL(last_contacted, '1901-01-01') as last_contacted,
COALESCE(contact_type, 'phone_call') AS contact_type
Le premier dit que lorsque vous rencontrez NULL dans la colonne last_contacted
, remplacez-le par 1901-01-01
. De même, le second dit que lorsque vous rencontrez NULL dans la colonne contact_type
, remplacez-le par phone_call
, comme indiqué dans la question.
Conseil de pro : selon l'environnement SQL, les instructions peuvent légèrement varier, mais les concepts restent les mêmes. Par exemple,COALESCE()
est utilisé sur MySQL, SQL Server, Oracle et MS Access, alors qu'ilNVL()
fonctionne principalement avec Oracle.
8.3. Gestion des dates
Q16 : En commençant par combined_table
, créez des colonnes séparées pour l'année, le trimestre, le mois et la date de la dernière fois que les personnes ont été contactées, lorsqu'une telle date est disponible.
R16 : Examinons d'abord la structure et les résultats, puis discutons-en.
WITH combination_table as (
SELECT
*
FROM
misc_part1
UNION
SELECT
*
FROM
misc_part2
ORDER BY
name
)
SELECT
name,
last_contacted,
DATE_PART(year, last_contacted) AS year_contacted,
DATE_PART(trimestre, last_contacted) AS quarter_contacted,
DATE_PART(month, last_contacted) AS month_contacted,
DATE_PART (jour, dernier_contacté) AS jour_contacté, type_contact
FROM
tableau_combiné
WHERE
dernier_contacté
IS NOT NULL ;
Résultats:
![](https://miro.medium.com/max/700/1*-hZFrLJxbq7dj6be_06Nsg.png)
Il y a deux nouveaux concepts à couvrir dans cet exercice. La première consiste à extraire une partie spécifique (par exemple, année, trimestre, mois ou jour) d'une date, qui peut être implémentée comme suit :
DATE_PART(year, last_contacted) AS year_contacted,
L'idée est simple. year
identifie quelle partie de la date doit être extraite, puis elle est suivie du nom de la colonne où se trouve la date d'origine, qui est last_contacted
dans cet exemple.
Le deuxième concept consiste à filtrer les lignes avec des valeurs NULL, ce qui a été accompli en utilisant ce qui suit :
WHERE
last_contacted IS NOT NULL
Conseil de pro : dans SQL Server, au lieu deDATE_PART()
, on utiliseDATEPART()
.
Section 3 — Aide-mémoire
J'ai inclus la feuille de triche que j'avais développée pour moi-même ici. Avant de commencer à l'utiliser, j'ai deux recommandations à ce sujet et sur d'autres aide-mémoire :
- Dans la mesure du possible, créez votre propre feuille de triche au fil du temps, au lieu de vous fier à une feuille de triche préparée. Vous en apprendrez beaucoup plus lorsque vous le créerez et le mettrez à jour par vous-même, par rapport à celui qui est partagé avec vous.
- Si vous n'avez pas le temps de créer votre propre feuille de triche, prenez une feuille de triche existante et personnalisez-la. Ce que je veux dire, c'est que commencez par la feuille de triche existante, mais ajoutez-y, révisez-la, modifiez-la et à un moment donné, elle deviendra "votre" feuille de triche et vous continuerez à apprendre en cours de route.
-- Introduction
SELECT column_1, column_2… [Returns entries per specified columns]
SELECT * [Returns all entries]
SELECT DISTINCT column_1, column_2… [Returns unique entries per specified columns]
SELECT DISTINCT * [Returns all unique entries]
FROM schema_name.table_name [Specifies table to return entries from]
WHERE column_1 = ‘value’ [Specifies condition per column]
May use =, >, <, >=, <=, <>
WHERE column_1 = ‘value’ AND column_2 = ‘value’ [Specifies both conditions must be met]
WHERE column_1 = ‘value’ OR column_2 = ‘value’ [Specifies one condition must be met]
WHERE column_1 = ‘value’ AND (column_2 = ‘value’ OR column_3 = ‘value’)
-- Aggregations
SUM(column_1) [Returns summation of column]
AVG(column_1) [Returns average of column]
MIN(column_1) [Returns minimum of column]
MAX(column_1) [Returns maximum of column]
COUNT(column_1) [Returns count of entries of column]
COUNT(DISTINCT column_1) [Returns unique count of entries of column]
Aggregations require a GROUP BY clause, i.e. GROUP BY 1, 2 or GROUP BY column_1, column_2
All non-aggregated columns are required to be in the GROUP BY clause
-- Null Handling
IS NULL [Evaluates if an entry is null]
IS NOT NULL [Evaluates if an entry is not null]
NVL(column_1, ‘alternative value’) [Replaces entry with an alternative value if found to be null]
NULLIF(column_1,’value’) [Replaces entry with null if entry meets value]
-- Aliases
column_1 AS ‘value’ [Renames column]
schema_name.table_name AS ‘value’ [Renames table]
-- Joins (added in From and then join on primary keys)
INNER JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains only records with matching values found in both table 1 and table 2]
LEFT JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from table 1]
RIGHT JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from table 2]
FULL OUTER JOIN schema_name.table_name ON table1.value1 = table2.value1 [Merges tables on matching values, contains all records from both table 1 and table 2]
-- UNIONs
UNION [Merges two tables whose structures are the same, removes duplicative records]
UNION ALL [Merges two tables whose structures are the same]
-- Miscellaneous
LIMIT x [Limits returned records to a specified number]
ORDER BY 1, 2 or ORDER BY column_1, column_2 [Orders returned records by specified columns]
LIKE ‘%value%’ [Used to find near matches when evaluating conditions]
IN (‘value 1’,’value 2’) [Used to find matches within a list of values when evaluating conditions]
BETWEEN ‘value 1’ AND ‘value 2’ [Used to find matches where an entry falls between two values when evaluating conditions] (inclusive)
HAVING [Used in place of WHERE for aggregate clauses]
(WHERE filters before data is grouped. HAVING filters after data is grouped.)
CASE WHEN column1 = ‘value’ THEN ‘value’ ELSE ‘value’ END AS ‘value’ [Returns entries by evaluating when/then statements]
CASE WHEN A THEN X
WHEN B THEN Y
WHEN C THEN Z
ELSE W
END
NOT [Used to limit to where a condition is not met]
-- Date Management
CURRENT_DATE [Returns current date]
TO_CHAR (column name, 'date format') [converts the date to a character string in a specified format]
Date formats:
'YYYY/MM/DD'
'YYYY:MM:DD'
'Q' = Quarter number
'MM' = Month number
'Month' = Month name
'W' = Week of the month (1-5; first week starts on the first day of the month)
'WW' = Week number of year (1-53; first week starts on the first day of the year)
'Day' = Day name
'DDD' = Day of year (1-366)
'DD' = Day of month (1-31)
'D' = Day of week (1-7; Sunday is 1)
'YYYY/MM/DD HH24:MI:SS' = Year, month, day, hour, minutes and seconds
TO_DATE ('string', 'date format') [opposite of TO_CHAR]
Wildcard: TO_DATE ('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')
DATE_TRUNC ('datepart', column) [truncates a time stamps expression to a specified date part, such as hour, week or month]
Datepart formats:
'year' = truncates to the first second of the first hour of the first day of the year
'quarter' = truncates to the first second/hour/day/quarter
'month' = truncates to the first second/hour/day/month
'week' = truncates to the first second/hour/day/week (Monday)
'day' = truncates to the first second/hour/day
Source :