{"id":6953,"date":"2025-06-24T15:53:14","date_gmt":"2025-06-24T13:53:14","guid":{"rendered":"https:\/\/services.lib.uliege.be\/alma\/?p=6953"},"modified":"2025-06-24T15:53:14","modified_gmt":"2025-06-24T13:53:14","slug":"combiner-les-donnees-analytics-de-differents-domaines-via-une-requete-sql-join","status":"publish","type":"post","link":"https:\/\/services.lib.uliege.be\/alma\/combiner-les-donnees-analytics-de-differents-domaines-via-une-requete-sql-join\/","title":{"rendered":"Combiner les donn\u00e9es analytics de diff\u00e9rents domaines via une requ\u00eate SQL \u00ab\u00a0JOIN\u00a0\u00bb"},"content":{"rendered":"\n

La requ\u00eate JOIN permet de combiner des donn\u00e9es de domaines diff\u00e9rents en s’affranchissant des 3 conditions impos\u00e9es par Analytics.<\/p>\n\n\n\n

https:\/\/services.lib.uliege.be\/alma\/comment-combiner-deux-domaines-dans-une-analyse-statistique\/<\/a><\/p>\n\n\n\n

L’unique condition attach\u00e9e \u00e0 la requ\u00eate\u00a0 JOIN est qu’au moins 1 colonne soit identique entre les diff\u00e9rents domaines combin\u00e9s, de pr\u00e9f\u00e9rence une donn\u00e9e de type \u00ab\u00a0identifiant unique\u00a0\u00bb comme le MMS ID.<\/p>\n\n\n\n

Ce point est abord\u00e9 en d\u00e9tail dans le billet suivant : https:\/\/developers.exlibrisgroup.com\/blog\/using-join-in-analytics-to-combine-data-from-two-subject-areas\/<\/p>\n\n\n\n

La requ\u00eate JOIN prend la forme suivante :<\/p>\n\n\n\n

\n
\n
\n

SELECT<\/p>\n\n\n\n

query_fields<\/p>\n\n\n\n

FROM (first_query) A<\/p>\n\n\n\n

LEFT OUTER JOIN (second_query) B<\/p>\n\n\n\n

ON A.linking_field = B.linking_field<\/p>\n<\/div><\/div>\n<\/div><\/div>\n<\/blockquote>\n\n\n\n

Les first et second query peuvent \u00eatre obtenues en cr\u00e9ant d\u2019abord un rapport dans chaque domaine respectif.<\/p>\n\n\n\n

On veillera \u00e0 n’ajouter que les donn\u00e9es et les filtres n\u00e9cessaires. Les donn\u00e9es ne doivent pas se r\u00e9p\u00e9ter entre les domaines sauf celle qui servira pour les linking fields.<\/p>\n\n\n\n

Mettons qu\u2019on souhaite afficher le nombre de pr\u00eats et le nombre de requests pour les collections de G\u00e9o-Sciences.<\/p>\n\n\n\n

Je cr\u00e9\u00e9 un premier rapport dans le domaine \u00ab\u00a0Fulfillment\u00a0\u00bb. Je demande \u00e0 afficher 3 colonnes et j\u2019ajoute 1 filtre.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Je me rends ensuite dans l\u2019onglet \u00ab\u00a0Advanced\u00a0\u00bb et je copie-colle le \u00ab\u00a0SQL Issued\u00a0\u00bb jusqu\u2019\u00e0 la mention ORDER BY (non inclus).<\/p>\n\n\n\n

\n

SELECT<\/p>\n\n\n\n

   0 s_0,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbTitle\u00a0\u00bb s_2,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbLoan\u00a0\u00bb.\u00a0\u00bbLoans (Not In House)\u00a0\u00bb s_3<\/p>\n\n\n\n

FROM \u00ab\u00a0Fulfillment\u00a0\u00bb<\/p>\n\n\n\n

WHERE<\/p>\n\n\n\n

(\u00ab\u00a0Item Location at Time of Loan\u00a0\u00bb.\u00a0\u00bbLibrary Name\u00a0\u00bb = ‘G\u00e9osciences’)<\/p>\n<\/blockquote>\n\n\n\n

Cette requ\u00eate me servira de First query. Je peux fermer le rapport sans l\u2019enregistrer.<\/p>\n\n\n\n

Puis je cr\u00e9\u00e9 un second rapport dans le domaine \u00ab\u00a0Request\u00a0\u00bb. J’ajoute la colonne qui me manquait dans l’autre domaine et je reprend la colonne MMS Id qui me permettra de faire le lien entre les domaines.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Je me rends ensuite dans l\u2019onglet \u00ab\u00a0Advancef\u00a0\u00bb et je copie le \u00ab\u00a0SQL Issued\u00a0\u00bb jusqu\u2019\u00e0 la mention ORDER BY (non inclus).<\/p>\n\n\n\n

\n

SELECT<\/p>\n\n\n\n

   0 s_0,<\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbRequest Measures\u00a0\u00bb.\u00a0\u00bb# of requests\u00a0\u00bb s_2<\/p>\n\n\n\n

FROM \u00ab\u00a0Requests\u00a0\u00bb<\/p>\n<\/blockquote>\n\n\n\n

Cette requ\u00eate me servira de Second query. Je peux d\u00e9sormais remplacer \u00ab\u00a0first_query\u00a0\u00bb et \u00ab\u00a0second_query\u00a0\u00bb dans ma requ\u00eate.<\/p>\n\n\n\n

\n

SELECT<\/p>\n\n\n\n

   query_fields<\/p>\n\n\n\n

FROM (SELECT<\/p>\n\n\n\n

   0 s_0,<\/strong><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/strong><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbTitle\u00a0\u00bb s_2,<\/strong><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbLoan\u00a0\u00bb.\u00a0\u00bbLoans (Not In House)\u00a0\u00bb s_3<\/strong><\/p>\n\n\n\n

FROM \u00ab\u00a0Fulfillment\u00a0\u00bb<\/strong><\/p>\n\n\n\n

WHERE<\/strong><\/p>\n\n\n\n

(\u00ab\u00a0Item Location at Time of Loan\u00a0\u00bb.\u00a0\u00bbLibrary Name\u00a0\u00bb = ‘G\u00e9osciences’)<\/strong><\/p>\n\n\n\n

) A<\/p>\n\n\n\n

LEFT OUTER JOIN (SELECT<\/strong><\/p>\n\n\n\n

   0 s_0,<\/strong><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/strong><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbRequest Measures\u00a0\u00bb.\u00a0\u00bb# of requests\u00a0\u00bb s_2<\/strong><\/p>\n\n\n\n

FROM \u00ab\u00a0Requests\u00a0\u00bb<\/strong><\/p>\n\n\n\n

) B<\/p>\n\n\n\n

ON A.linking_field = B.linking_field<\/p>\n<\/blockquote>\n\n\n\n

Il me reste ensuite \u00e0 pr\u00e9ciser les linking fields, ici le MMS Id donc => \u00ab\u00a0A.s_1\u00a0\u00bb et \u00ab\u00a0B.s_1\u00a0\u00bb.<\/p>\n\n\n\n

\n

SELECT<\/p>\n\n\n\n

   query_fields<\/p>\n\n\n\n

FROM (SELECT<\/p>\n\n\n\n

   0 s_0,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbTitle\u00a0\u00bb s_2,<\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbLoan\u00a0\u00bb.\u00a0\u00bbLoans (Not In House)\u00a0\u00bb s_3<\/p>\n\n\n\n

FROM \u00ab\u00a0Fulfillment\u00a0\u00bb<\/p>\n\n\n\n

WHERE<\/p>\n\n\n\n

(\u00ab\u00a0Item Location at Time of Loan\u00a0\u00bb.\u00a0\u00bbLibrary Name\u00a0\u00bb = ‘G\u00e9osciences’)<\/p>\n\n\n\n

) A<\/p>\n\n\n\n

LEFT OUTER JOIN (SELECT<\/p>\n\n\n\n

   0 s_0,<\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbRequest Measures\u00a0\u00bb.\u00a0\u00bb# of requests\u00a0\u00bb s_2<\/p>\n\n\n\n

FROM \u00ab\u00a0Requests\u00a0\u00bb<\/p>\n\n\n\n

) B<\/p>\n\n\n\n

ON A.s_1 = B.s_1<\/strong><\/p>\n<\/blockquote>\n\n\n\n

Enfin, en d\u00e9but de requ\u00eate, il me reste \u00e0 compl\u00e9ter les \u00ab\u00a0query_fields\u00a0\u00bb\u00a0 soit la liste des champs qu\u2019on souhaite afficher dans notre rapport final parmi ceux ajout\u00e9s dans les deux rapports. Ici nous reprenons tous les champs du premier rapport et seulement le second champ du deuxi\u00e8me rapport (pour ne pas r\u00e9p\u00e9ter le MMS Id \u00e0 l\u2019affichage).<\/p>\n\n\n\n

Cette partie prend la forme suivante :<\/p>\n\n\n\n

A.s_1 intitul\u00e9,
A.s_2 intitul\u00e9,
A.s_3 intitul\u00e9,
B.s_2 intitul\u00e9<\/p>\n\n\n\n

L\u2019intitul\u00e9 peut \u00eatre d\u00e9fini arbitrairement. Les intitul\u00e9s doivent \u00eatre en minuscules et sans espaces. Attention de terminer sans virgule apr\u00e8s le dernier intitul\u00e9. Ex\u00a0:
<\/p>\n\n\n\n

\n

SELECT<\/em><\/p>\n\n\n\n

A.s_1 mms_id,<\/em><\/strong><\/p>\n\n\n\n

A.s_2 title,<\/em><\/strong><\/p>\n\n\n\n

A.s_3 loans,<\/em><\/strong><\/p>\n\n\n\n

B.s_2 requests<\/em><\/strong><\/p>\n\n\n\n

FROM (SELECT<\/em><\/p>\n\n\n\n

   0 s_0,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbTitle\u00a0\u00bb s_2,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbLoan\u00a0\u00bb.\u00a0\u00bbLoans (Not In House)\u00a0\u00bb s_3<\/em><\/p>\n\n\n\n

FROM \u00ab\u00a0Fulfillment\u00a0\u00bb<\/em><\/p>\n\n\n\n

WHERE<\/em><\/p>\n\n\n\n

(\u00ab\u00a0Item Location at Time of Loan\u00a0\u00bb.\u00a0\u00bbLibrary Name\u00a0\u00bb = ‘G\u00e9osciences’)<\/em><\/p>\n\n\n\n

) A<\/em><\/p>\n\n\n\n

LEFT OUTER JOIN (SELECT<\/em><\/p>\n\n\n\n

   0 s_0,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbRequest Measures\u00a0\u00bb.\u00a0\u00bb# of requests\u00a0\u00bb s_2<\/em><\/p>\n\n\n\n

FROM \u00ab\u00a0Requests\u00a0\u00bb<\/em><\/p>\n\n\n\n

) B<\/em><\/p>\n\n\n\n

ON A.s_1 = B.s_1<\/em><\/p>\n<\/blockquote>\n\n\n\n

Ensuite, je cr\u00e9\u00e9 un rapport (peu importe le domaine), je vais dans \u00ab\u00a0Advanced\u00a0\u00bb, sous \u00ab\u00a0SQL Issued\u00a0\u00bb, je clique sur ‘New Analysis\u00a0\u00bb.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Je copie-colle la formule\u00a0:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Je valide avec \u00ab\u00a0OK\u00a0\u00bb et j\u2019obtiens le r\u00e9sultat suivant.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Le rapport est limit\u00e9 au niveau de son \u00e9dition (pas d\u2019ajout de champ, de filtres, etc). On peut toutefois r\u00e9organiser et renommer les diff\u00e9rentes colonnes.<\/p>\n\n\n\n

On peut aussi choisir de renommer les colonnes via la requ\u00eate en rempla\u00e7ant les intitul\u00e9s dans la requ\u00eate par les intitul\u00e9s souhait\u00e9s entre guillemets. Il faut alors penser \u00e0 r\u00e9p\u00e9ter ces m\u00eames intitul\u00e9s dans les first et second query et dans les linking fields.<\/p>\n\n\n\n

\n

SELECT<\/em><\/p>\n\n\n\n

A.\u00a0\u00bbMMS Id\u00a0\u00bb,<\/em><\/strong><\/p>\n\n\n\n

A.\u00a0\u00bbTitle\u00a0\u00bb,<\/em><\/strong><\/p>\n\n\n\n

A.\u00a0\u00bbLoans\u00a0\u00bb,<\/em><\/strong><\/p>\n\n\n\n

B.\u00a0\u00bb# of requests\u00a0\u00bb<\/em><\/strong><\/p>\n\n\n\n

FROM (SELECT<\/em><\/p>\n\n\n\n

   0 s_0,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb \u00ab\u00a0MMS Id\u00a0\u00bb,<\/strong><\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbTitle\u00a0\u00bb \u00ab\u00a0Title\u00a0\u00bb,<\/strong><\/em><\/p>\n\n\n\n

   \u00ab\u00a0Fulfillment\u00a0\u00bb.\u00a0\u00bbLoan\u00a0\u00bb.\u00a0\u00bbLoans (Not In House)\u00a0\u00bb \u00ab\u00a0Loans\u00a0\u00bb<\/strong><\/em><\/p>\n\n\n\n

FROM \u00ab\u00a0Fulfillment\u00a0\u00bb<\/em><\/p>\n\n\n\n

WHERE<\/em><\/p>\n\n\n\n

(\u00ab\u00a0Item Location at Time of Loan\u00a0\u00bb.\u00a0\u00bbLibrary Name\u00a0\u00bb = ‘G\u00e9osciences’)<\/em><\/p>\n\n\n\n

) A<\/em><\/p>\n\n\n\n

LEFT OUTER JOIN (SELECT<\/em><\/p>\n\n\n\n

   0 s_0,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbBibliographic Details\u00a0\u00bb.\u00a0\u00bbMMS Id\u00a0\u00bb s_1,<\/em><\/p>\n\n\n\n

   \u00ab\u00a0Requests\u00a0\u00bb.\u00a0\u00bbRequest Measures\u00a0\u00bb.\u00a0\u00bb# of requests\u00a0\u00bb \u00ab\u00a0# of requests\u00a0\u00bb<\/strong><\/em><\/p>\n\n\n\n

FROM \u00ab\u00a0Requests\u00a0\u00bb<\/em><\/p>\n\n\n\n

) B<\/em><\/p>\n\n\n\n

ON A.\u00ab\u00a0MMS Id\u00a0\u00bb<\/strong> = B.s_1<\/em><\/p>\n<\/blockquote>\n\n\n\n

J’obtiens alors le r\u00e9sultat suivant :<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

La requ\u00eate JOIN permet de combiner des donn\u00e9es de domaines diff\u00e9rents en s’affranchissant des 3 conditions impos\u00e9es par Analytics. https:\/\/services.lib.uliege.be\/alma\/comment-combiner-deux-domaines-dans-une-analyse-statistique\/ L’unique condition attach\u00e9e \u00e0 la requ\u00eate\u00a0 JOIN est qu’au moins 1 colonne soit identique entre les diff\u00e9rents domaines combin\u00e9s, de pr\u00e9f\u00e9rence une donn\u00e9e de type \u00ab\u00a0identifiant unique\u00a0\u00bb comme le MMS… Lire l'int\u00e9gralit\u00e9…Combiner les donn\u00e9es analytics de diff\u00e9rents domaines via une requ\u00eate SQL \u00ab\u00a0JOIN\u00a0\u00bb<\/span><\/span><\/a><\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[76],"class_list":["post-6953","post","type-post","status-publish","format-standard","hentry","category-analytics","tag-combiner-concatener","content-layout-excerpt-thumb"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7rJUv-1O9","_links":{"self":[{"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/posts\/6953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/comments?post=6953"}],"version-history":[{"count":14,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/posts\/6953\/revisions"}],"predecessor-version":[{"id":6973,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/posts\/6953\/revisions\/6973"}],"wp:attachment":[{"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/media?parent=6953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/categories?post=6953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/services.lib.uliege.be\/alma\/wp-json\/wp\/v2\/tags?post=6953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}