Ce TME utilise le système Couchbase qui pourra être installé suivant les instructions ci-dessous. Il est recommandé d'utiliser le tutoriel en ligne Accès Couchbase en ligne et de saisir les requêtes dans l'onglet 'Query' et d'observer les résultats dans l'onglet 'Results'

Le TME utilise 4 collections de données décrites ci-dessous :

• product: contient la liste de produits à vendre, les catégories auxquelles ils appartiennent, le prix de chaque produit et autres informations. Il y aura 900 documents dans ce bucket. Schéma

• customer: contient les informations sur le clients, telles que les noms, les adresses, les cartes bancaires. Il y aura 1000 documents dans ce bucket. Schéma

• purchases: contient les achats des clients, chaque document contient une liste de produits achetés et la quantité achetée de chaque produit. Il y aura 10000 documents dans ce bucket. Schéma

• reviews: lcontient iste d'avis donnés par les clients sur les produits, chaque review ayant une note comprise entre 0 et 5. Il y aura 10000 documents dans ce bucket. Schéma

En utilisant tutoriel en ligne il n'est pas utile de télécharger les données qui sont déjà chargées.

A toutes fins utiles, il est possible télécharger les données du TME depuis l'archive.

N1QL CheatSheet
N1QL Language Reference

1. L'ensemble des couleurs des produits (ensemble signifie pas de doublons).

Résultat : 31 lignes

SELECT DISTINCT color
  FROM product

2. La liste des prix des 10 premiers produits.

SELECT  unitPrice
  FROM product
  ORDER BY unitPrice DESC
  LIMIT 10

3. Les identifiants des produits (productId) ayant une note (rating) de 5.

Résultat : 1708 lignes

SELECT productId
    FROM reviews
    WHERE rating = 5

4. Les identifiants des clients (customerId) ainsi que leurs emails (emailAddress) qui se terminant par '@herman.com'.

Résultat : 1 ligne

SELECT customerId, emailAddress
    FROM customer 
        WHERE emailAddress LIKE '%herman.com'

5. Les identifiants des clients (customerId) qui ont acheté exactement 5 produits différents. Les produits achetés sont renseignés par purchases.lineItems. Ne retourner que les customerId uniques. Résultat sans doublons : 869 lignes Résultat avec doublons : 2088 lignes

SELECT DISTINCT customerId
    FROM purchases 
        WHERE ARRAY_LENGTH(lineItems) = 5

6. Les identifiants des clients (customerId) ayant acheté au moins un produit avec une quantité > 4. Ne retourner que les customerId uniques.

Résultat sans doublons : 989 lignes Résultat avec doublons : 4666 lignes

SELECT DISTINCT customerId
    FROM purchases 
        WHERE ANY item IN purchases.lineItems SATISFIES item.count > 4  END

7. Les identifiants des clients (customerId) dont tout les produits achetés le sont avec une quantité > 4.

Résultat : 488 lignes

SELECT customerId
    FROM purchases 
        WHERE EVERY item IN purchases.lineItems SATISFIES item.count > 4  END

8.a) Les paires identifiants de produits (productId) et catégorie. Retourner une paire pour chaque combinaison de productId et de chaine de caractères dans le tableau correspondant à categories.

Résultat est un tableau de 900 objets ayant le schéma

   {  "cat": String, "productId": String }

SELECT cat, product.productId
    FROM product 
    UNNEST product.categories AS cat

8.b) Les identifiants des clients (customerId) et les produits achetés. Chaque client sera retourné autant de fois qu'il a acheté de produits.

Résultat est un tableau de 30254 objets ayant le schéma

   {  "customerId": String,
      "item": {
        "count": Number,
        "product": String
              }
      }

SELECT purchases.customerId, item
  FROM purchases unnest lineItems AS item

9.Les identifiants des clients (customerId) et la liste des produits achetés ; cette liste est obtenue en projetant l'attribut product de lineItems.

Résultat est un tableau d'objets ayant le schéma

    { "customerId": String,     "products": [ String ]   }

SELECT p.customerId, 
array item.product FOR item IN p.lineItems END AS products
  FROM purchases p 

10. L'identifiant des clients (customerId), leur état de résidence (state) et la liste des produits achetés (lineItems). Compléter la requête ci-dessous.

Remarque : la jointure se fait sur la clé primaire de customer à la quelle on accède par l'expression

 Meta(c).id

SELECT 
  FROM ... p INNER JOIN customer c ON KEYS p.customerId

Résultat est un tableau d'objets ayant le schéma

{
      "customerId": String,
      "lineItems": [
        {
          "count": Number,
          "product": String
        }
      ],
      "state": String
    }

  SELECT p.customerId, c.state, p.lineItems
  FROM purchases p INNER JOIN customer c ON (p.customerId = META(c).id)
 
  SELECT p.customerId, c.state, p.lineItems
  FROM purchases p INNER JOIN customer c ON KEYS p.customerId

11. L'identifiant des clients (customerId) ainsi que la quantité totale des produits achetés.

Résultat est un tableau d'objets ayant le schéma

{     "customerId": String, "qteProduits": Number    }

voici un extrait de la réponse :

{
  "results": [
    {
      "customerId": "customer732",
      "qteProduits": 95
    },
    {
      "customerId": "customer204",
      "qteProduits": 135
    },
    ...
    ]

 SELECT purchases.customerId, SUM(item.count) AS qteProduits
  FROM purchases unnest lineItems AS item
  GROUP BY purchases.customerId

SELECT t.customerId, array_sum (t.products) AS qte_produits
FROM(
    SELECT customerId, ARRAY item.count
    FOR item IN purchases.lineItems END
    AS products
    FROM purchases
) AS t

1. Donner la liste de 10 produits qui se trouvent dans la catégorie “golf” (indépendamment de la casse), omettre les 10 premiers produits.

SELECT *
    FROM product 
    UNNEST product.categories AS cat
            WHERE LOWER(cat) = "golf" LIMIT 10 OFFSET 10 

2. Donner la liste des différentes catégories existantes.

SELECT 
    DISTINCT categories
    FROM product
    UNNEST product.categories AS categories

Résultat : 23 lignes

3. Donner les identifiants et les noms des produits dont le nom contient la chaîne “cup” (indépendamment de la casse).

SELECT 
    productId, name
    FROM product
    WHERE LOWER(name) LIKE "%cup%"

Résultat : 14 lignes

4. Donner le nom, le prix unitaire et la couleur des produits dans la catégorie “Appliances”.

SELECT
    product.name, product.unitPrice, product.color
    FROM product UNNEST product.categories AS categories
    WHERE categories = "Appliances"

Résultat : 78 lignes

5. Donner le nom, le nombre total d'avis, la moyenne des avis et la couleur des trois produits les mieux notés (avec la meilleure note moyenne) qui se trouvent dans la catégorie “Appliances”.

SELECT
    product.name, 
    COUNT(reviews) AS reviewCount,
    ROUND(AVG(reviews.rating),1) AS avgRating,
    product.color
    FROM reviews
    JOIN product ON KEYS reviews.productId
    UNNEST product.categories AS category
    WHERE category = "Appliances"
    GROUP BY category, product
    ORDER BY avgRating 
    DESC LIMIT 3 
    

Résultat : 3 lignes

{
  "results": [
    {
      "avgRating": 4.5,
      "color": "azure",
      "name": "Waring Pro IC70 Professional Stainless Steel Large-Capacity Ice Crusher",
      "reviewCount": 2
    },
    {
      "avgRating": 4,
      "color": "green",
      "name": "Air King AILT4 Lint Trap",
      "reviewCount": 6
    },
    {
      "avgRating": 3.4,
      "color": "red",
      "name": "Bosch HEZ1090 LP Conversion Kit for Bosch Free-standing Gas Ranges (300 and 500 Series)",
      "reviewCount": 5
    }
  ]
}

6. Pour chaque produit, retourner son nom, son prix unitaire, la date à laquelle il a été ajouté et le nombre total d'achats de ce produit. Le résultat sera trié par ordre décroissant en fonction de la date d'ajout et du nombre total d'achat. Afficher uniquement les 10 premiers résultats.

SELECT product.name, product.unitPrice, product.dateAdded, SUM(items.count) AS unitsSold 
        FROM purchases UNNEST purchases.lineItems AS items 
        JOIN product ON KEYS items.product 
        GROUP BY product 
        ORDER BY product.dateAdded, unitsSold DESC LIMIT 10
 

7. Afficher les noms et les prix unitaires des produits dont le prix unitaire est inférieur à 6.99 et qui se trouvent dans la catégorie “Appliances”.

SELECT product.name, product.unitPrice, product.categories 
FROM product UNNEST product.categories AS categories 
WHERE categories = "Appliances" AND product.unitPrice < 6.99

8. Afficher les 10 produits les plus vendus. Pour chacun de ces produits afficher son nom et le nombre d'achats de ce produit.

SELECT product.name, SUM(items.count) AS unitsSold 
FROM purchases UNNEST purchases.lineItems AS items 
JOIN product ON KEYS items.product 
GROUP BY product 
ORDER BY unitsSold DESC LIMIT 10

9. Afficher les 5 produits les mieux notés (en fonction de la note moyenne). Pour chaque produit afficher son nom et sa note moyenne.

SELECT product.name, ROUND(AVG(reviews.rating),1) AS avg_rating 
FROM reviews JOIN product ON KEYS reviews.productId 
GROUP BY product 
ORDER BY AVG(reviews.rating) DESC LIMIT 5

10. Pour le document “purchase0” retourner l'achat correspondant, avec les informations sur le client ayant effectué cet achat et sur les produits achetés.

SELECT purchases, product, customer 
FROM purchases USE KEYS "purchase0" UNNEST purchases.lineItems AS items 
JOIN product ON KEYS items.product
JOIN customer ON KEYS purchases.customerId

11. Afficher les 10 clients qui ont dépensé le plus d'argent en achats. Pour chaque client on veut connaître leur nom, leur prénom, leur mail, le nombre total de produits achetés ainsi que le prix total de tous leur achats.

SELECT  customer.firstName, 
        customer.lastName, 
        customer.emailAddress,
        SUM(items.count) purchaseCount, 
        ROUND(SUM(product.unitPrice * items.count))  totalSpent 
FROM purchases UNNEST purchases.lineItems AS items 
JOIN product ON KEYS items.product
JOIN customer ON KEYS purchases.customerId 
GROUP BY customer 
ORDER BY totalSpent DESC LIMIT 10

12. Donner les nombre total de clients par région. Le résultat doit être trié par ordre décroissant du nombre de clients.

SELECT COUNT(customer) AS customerCount, state
FROM customer 
GROUP BY state
ORDER BY customerCount DESC

13. Donner le nombre total de clients différents qui ont effectué des achats entre “2014-03-01” et “2014-03-31”.

SELECT COUNT(DISTINCT purchases.customerId) 
FROM purchases
WHERE purchases.purchasedAt BETWEEN "2014-03-01" AND "2014-03-31"

14. Afficher les produits dont la note moyenne est inférieure à 1. Pour chaque produit on veut connaître son som, son identifiant et la note moyenne.

SELECT product.name, product.productId, ROUND(AVG(reviews.rating), 3) avgRating, COUNT(reviews) numReviews 
FROM product JOIN reviews ON KEYS product.reviewList 
GROUP BY product HAVING AVG(reviews.rating) < 1

15. Pour chaque date d'achat (année et mois) donner le prix de tous les achats effectués à cette date. Le prix sera exprimé en millions de dollars (arrondi à 3 chiffres après la virgule). Ordonner le résultat par ordre croissant de la date.

SELECT SUBSTR(purchases.purchasedAt, 0, 7) as month, 
    ROUND(SUM(product.unitPrice * items.count)/1000000, 3) revenueMillion
FROM purchases UNNEST purchases.lineItems AS items JOIN product ON KEYS items.product 
GROUP BY SUBSTR(purchases.purchasedAt, 0, 7) 
ORDER BY month 

16. Donner les achats au mois d'Avril 2014 pour des produits dont le prix unitaire est supérieur à 500 dollars. Pour chaque achat on veut connaître son identifiant, l'identifiant du produit acheté, le nom du produit et son prix unitaire.

SELECT purchases.purchaseId, l.product, prod.name, prod.unitPrice
    FROM purchases UNNEST purchases.lineItems l
        JOIN product prod ON KEYS l.product
    WHERE DATE_PART_STR(purchases.purchasedAt,"month") = 4
        AND DATE_PART_STR(purchases.purchasedAt,"year") = 2014 
        AND prod.unitPrice > 500
        

Couchbase est pré-installé sur les machines de le PPTI. Il suffit de le lancer depuis un navigateur Web à l'adresse suivante: http://127.0.0.1:8091/

• Télécharger la dernière version 'Community' de Couchbase Server à cette adresse https://www.couchbase.com/downloads
• Instructions d'installation: Pour Linux, Pour MacOSX, Pour Windows

Sous windows désactiver le démarrage automatique du serveur pour éviter que couchbase soit constamment démarré sur votre ordinateur voir Start and Stop Couchbase Server et sélectionner la valeur Manuel pour le type de démarrage:

• Lancer Couchbase et ensuite se rendre dans un navigateur Web à l'adresse suivante: http://127.0.0.1:8091/
• Suivre ces instructions pour créer un nouveau cluster (choisir un nom pour celui-ci, choisir 'Administrator' et 'password' pour username et password). Si la valeur de la partie 'Data' dans 'Service Memory Quotas' est inférieure à 512 MB, choisir 512 MB comme valeur pour cette partie sinon la laisser inchangée. Laisser les autres valeurs inchangées.

• Créer 4 nouveaux buckets avec les noms suivants (voir leur description ci-après): product, customer, purchases et reviews. Voir les instruction de création des buckets à cette adresse. Pour chaque bucket changer la valeur de 'Memory Quota' à 100 MB. Laisser les autres options dans bucket-settings inchangées.

• product: ce bucket contient la liste de produits à vendre, les catégories auxquelles ils appartiennent, le prix de chaque produit et autres informations. Il y aura 900 documents dans ce bucket. Schéma

• customer: ce bucket contient les informations sur le clients, telles que les noms, les adresses, les cartes bancaires. Il y aura 1000 documents dans ce bucket. Schéma

• purchases: ce bucket contient les achats des clients, chaque document contient une liste de produits achetés et la quantité achetée de chaque produit. Il y aura 10000 documents dans ce bucket. Schéma

• reviews: liste d'avis donnés par les clients sur les produits, chaque review ayant une note comprise entre 0 et 5. Il y aura 10000 documents dans ce bucket. Schéma

• Télécharger et décompresser l'archive qui contient les données à importer.
• Ajouter les données de chaque fichier dans le bucket portant le même nom. Pour ajouter les données suivre ces instructions. Dans la liste 'Parse File As' choisir 'JSON List', pour l'option 'Import With Document ID' choisir 'Value of Field', et pour chaque fichier choisir les champs suivants:
  • pour product.json choisir productID
  • pour customer.json, choisir customerID
  • pour purchases.json, choisir purchaseID
  • pour review.json, choisir reviewID

• Créer les index sur ces buckets. Aller dans l'onglet 'Query' de l'interface Web et exécuter chacune des commandes suivantes:

CREATE PRIMARY INDEX ON customer;
CREATE PRIMARY INDEX ON product;
CREATE PRIMARY INDEX ON reviews;
CREATE PRIMARY INDEX ON purchases;

Pour voir si les index ont bien été créés vous pouvez tester en exécutant la commande suivante:

select * from system:indexes;

Dés-archiver l'archive qui contient les fichiers Countries.json, Deserts.json et Mountains.json.