Bases de Données / Databases

Site Web de l'équipe BD du LIP6 / LIP6 DB Web Site

Outils pour utilisateurs

Outils du site

Piste: • TME N1QL sous Couchbase

Panneau latéral

English version

site:enseignement:master:mlbda:tmes:couchbase

Table des matières

TME N1QL sous Couchbase

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'

Description des données

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.

Documentation N1QL

N1QL CheatSheet
N1QL Language Reference

Questions

Exercice 1

Projection

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 triés par prix décroissant. 

SELECT  unitPrice
  FROM product
  ORDER BY unitPrice DESC
  LIMIT 10

Sélection

3. Les identifiants des produits (productId) ayant au moins une note (rating) de 5. Les identifiants de produits sont tous distincts dans le reéultat.

Résultat : 782 lignes 

SELECT DISTINCT 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

Aplatissement des listes, parcours des listes

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

Jointure

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

Agrégation

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
    },
    ...
    ]

Solution classique 

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

solution orientée données dénormalisées utilisant la fonction array_sum() 

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

Exercice 2

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. Indice : utiliser LIMIT et OFFSET

Réponses https://query-tutorial.couchbase.com/tutorial/#33 

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.

Réponses

https://query-tutorial.couchbase.com/tutorial/#34 

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).

Réponses https://query-tutorial.couchbase.com/tutorial/#35 

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”.

Réponses https://query-tutorial.couchbase.com/tutorial/#36 

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”.

Réponses https://query-tutorial.couchbase.com/tutorial/#37 

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#38 

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

Résultat : 10 lignes 

{
  "results": [
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Briggs & Riley 13 Inch Slim Clamshell Briefcase",
      "unitPrice": 175.2,
      "unitsSold": 149
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "American Standard 2988.101.020 Concealed Trapway Cadet 3 Right Height Round Front Flowise 1.28 gpf Toilet with Seat, White",
      "unitPrice": 279.95,
      "unitsSold": 146
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "3M Indoor Window Insulator Kit, 1-Window",
      "unitPrice": 18.03,
      "unitsSold": 136
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Ball Cap Buddy",
      "unitPrice": 5.61,
      "unitsSold": 131
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Achim Home Furnishings FTVGM30820 Nexus 12-Inch Vinyl Tile, Geo 16 Square Sandstone, 20-Pack",
      "unitPrice": 14.25,
      "unitsSold": 131
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Moen YB8099CH Mason Paper Holder, Chrome",
      "unitPrice": 1.41,
      "unitsSold": 130
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Darice 4-3/4-Inch Magnetic Manikin",
      "unitPrice": 6.96,
      "unitsSold": 128
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Hardware House H10-6184 Bridgeport Series Single Handle Tub and Shower Mixer, Brushed Nickel",
      "unitPrice": 69.99,
      "unitsSold": 128
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Moonrays 91515 Turtles on a Log Solar-Powered Outdoor LED Light",
      "unitPrice": 16.95,
      "unitsSold": 125
    },
    {
      "dateAdded": "2013-05-11T15:52:18Z",
      "name": "Mr. Beams MB 542 Battery Powered Motion Sensing LED Remote Path Light, 2-Pack",
      "unitPrice": 33.72,
      "unitsSold": 124
    }
  ]
}

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”.

Réponses https://query-tutorial.couchbase.com/tutorial/#39 

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

Résultat : 1 ligne 

{
  "results": [
    {
      "categories": [
        "Appliances"
      ],
      "name": "Ball Cap Buddy",
      "unitPrice": 5.61
    }
  ]
}

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

Réponses https://query-tutorial.couchbase.com/tutorial/#40 

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

Résultat : 10 lignes 

{
  "results": [
    {
      "name": "Pyrex Prepware 2-Cup Measuring Cup, Clear with Red Measurements",
      "unitsSold": 182
    },
    {
      "name": "Danze D304055RB Sheridan Two Handle Widespread Lavatory Faucet, Oil Rubbed Bronze",
      "unitsSold": 164
    },
    {
      "name": "Hunter Ceiling Fans 21647 Caribbean Breeze Fan - Weathered Bronze Indoor Fan 54",
      "unitsSold": 156
    },
    {
      "name": "New Super Mario Bros. 2",
      "unitsSold": 156
    },
    {
      "name": "Eagle Claw Youth Fishing Vest (Small)",
      "unitsSold": 154
    },
    {
      "name": "Aroma ARC-914SBD 4-Cup (Uncooked) 8-Cup (Cooked) Digital Rice Cooker and Food Steamer",
      "unitsSold": 152
    },
    {
      "name": "Angels' Eyes Tear-Stain Eliminator for Dogs and Cats, Beef Flavor, 120 Grams Bottle",
      "unitsSold": 151
    },
    {
      "name": "Brother Project Runway CE7070PRW 70-Stitch Computerized Sewing Machine with Wide Table",
      "unitsSold": 150
    },
    {
      "name": "BeginAgain Eco YoYo - Green",
      "unitsSold": 150
    },
    {
      "name": "Zumba Fitness LLC Women's Let Loose Racerback Shirt",
      "unitsSold": 149
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#41 

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

Résultat : 5 lignes 

{
  "results": [
    {
      "avg_rating": 4.5,
      "name": "Waring Pro IC70 Professional Stainless Steel Large-Capacity Ice Crusher"
    },
    {
      "avg_rating": 4.2,
      "name": "Racor PHL-1R Pro HeavyLift 4-by-4-Foot Cable-Lifted Storage Rack"
    },
    {
      "avg_rating": 4.2,
      "name": "Marineland Rite-Size Cartridge E, 4-Pack"
    },
    {
      "avg_rating": 4.1,
      "name": "Midwest 1524 iCrate Single-Door Pet Crate 24-By-18 -By-19-Inch"
    },
    {
      "avg_rating": 4.1,
      "name": "Dorcy 41-1643 30 Lumen 3 Volt LED Replacement Bulb"
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#42 

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

Résultat : 2 lignes 

{
  "results": [
    {
      "customer": {
        "ccInfo": {
          "cardExpiry": "2012-11-12",
          "cardNumber": "1234-2121-1221-1211",
          "cardType": "americanexpress"
        },
        "customerId": "customer515",
        "dateAdded": "2014-01-06T15:52:16Z",
        "dateLastActive": "2014-05-06T15:52:16Z",
        "emailAddress": "valentine_bednar@halvorsongrant.net",
        "firstName": "Dustin",
        "lastName": "Lemke",
        "phoneNumber": "1-151-555-5907 x324",
        "postalCode": "26384",
        "state": "MS",
        "type": "customer"
      },
      "product": {
        "reviewList": [
          "review2505",
          "review4108",
          "review4160",
          "review4866",
          "review4908",
          "review5519",
          "review8101",
          "review8720",
          "review8981",
          "review9116"
        ],
        "type": "product",
        "imageURL": "http://ecx.images-amazon.com/images/I/51BfRrG%2BdoL._SL300_.jpg",
        "dateModified": "2014-05-06T15:52:19Z",
        "productId": "product701",
        "unitPrice": 29,
        "dateAdded": "2014-01-06T15:52:19Z",
        "description": "This product is available on <a target=\"_blank\" href=\"http://www.amazon.com/gp/product/B007KKU8QE/ref=s9_ri_bw_g199_ir015?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=merchandised-search-8&pf_rd_r=D199F6AF45FA49D6845F&pf_rd_t=101&pf_rd_p=1479925062&pf_rd_i=2619533011\">Amazon.com</a>.",
        "name": "Aquarius Aq15005 Aquarius 5 Rounded 5-Gallon Aquarium Kit",
        "categories": [
          "Pet Supplies"
        ],
        "color": "white"
      },
      "purchases": {
        "type": "purchase",
        "purchaseId": "purchase0",
        "purchasedAt": "2014-04-06T15:52:32Z",
        "lineItems": [
          {
            "count": 3,
            "product": "product701"
          },
          {
            "count": 4,
            "product": "product85"
          }
        ],
        "customerId": "customer515"
      }
    },
    {
      "customer": {
        "ccInfo": {
          "cardExpiry": "2012-11-12",
          "cardNumber": "1234-2121-1221-1211",
          "cardType": "americanexpress"
        },
        "customerId": "customer515",
        "dateAdded": "2014-01-06T15:52:16Z",
        "dateLastActive": "2014-05-06T15:52:16Z",
        "emailAddress": "valentine_bednar@halvorsongrant.net",
        "firstName": "Dustin",
        "lastName": "Lemke",
        "phoneNumber": "1-151-555-5907 x324",
        "postalCode": "26384",
        "state": "MS",
        "type": "customer"
      },
      "product": {
        "reviewList": [
          "review681",
          "review3771",
          "review4215",
          "review5685",
          "review5856",
          "review5935",
          "review6789",
          "review7532",
          "review7649",
          "review9011"
        ],
        "type": "product",
        "imageURL": "http://ecx.images-amazon.com/images/I/21xHRhlFslL._SL300_.jpg",
        "dateModified": "2014-05-06T15:52:18Z",
        "productId": "product85",
        "unitPrice": 14.95,
        "dateAdded": "2013-11-07T15:52:18Z",
        "description": "This product is available on <a target=\"_blank\" href=\"http://www.amazon.com/gp/product/B0000DE9B5/ref=s9_ri_bw_g79_ir04?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=merchandised-search-11&pf_rd_r=1C0DBDE1A6234FF8A9DB&pf_rd_t=101&pf_rd_p=1405136162&pf_rd_i=284507\">Amazon.com</a>.",
        "name": "Bormioli Rocco Frigoverre Jug with Hermetic Lid, 2-Liters",
        "categories": [
          "Kitchen & Dining"
        ],
        "color": "white"
      },
      "purchases": {
        "type": "purchase",
        "purchaseId": "purchase0",
        "purchasedAt": "2014-04-06T15:52:32Z",
        "lineItems": [
          {
            "count": 3,
            "product": "product701"
          },
          {
            "count": 4,
            "product": "product85"
          }
        ],
        "customerId": "customer515"
      }
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#43 

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

Résultat : 10 lignes 

{
  "results": [
    {
      "emailAddress": "mabel_oberbrunner@mueller.net",
      "firstName": "Vernon",
      "lastName": "Satterfield",
      "purchaseCount": 140,
      "totalSpent": 27847
    },
    {
      "emailAddress": "opal@gaylordpouros.info",
      "firstName": "Nikolas",
      "lastName": "Zulauf",
      "purchaseCount": 185,
      "totalSpent": 27004
    },
    {
      "emailAddress": "fabian.west@handmoen.org",
      "firstName": "Verlie",
      "lastName": "Fahey",
      "purchaseCount": 112,
      "totalSpent": 26234
    },
    {
      "emailAddress": "helena@waltercummings.org",
      "firstName": "Cary",
      "lastName": "Jerde",
      "purchaseCount": 125,
      "totalSpent": 26035
    },
    {
      "emailAddress": "lia@kohler.biz",
      "firstName": "Carolanne",
      "lastName": "Hegmann",
      "purchaseCount": 133,
      "totalSpent": 24875
    },
    {
      "emailAddress": "benedict@hirtheborer.net",
      "firstName": "Coralie",
      "lastName": "O'Kon",
      "purchaseCount": 125,
      "totalSpent": 23348
    },
    {
      "emailAddress": "jolie@beer.info",
      "firstName": "Madison",
      "lastName": "Klocko",
      "purchaseCount": 151,
      "totalSpent": 21687
    },
    {
      "emailAddress": "tyrese@auersimonis.biz",
      "firstName": "Luna",
      "lastName": "Rodriguez",
      "purchaseCount": 166,
      "totalSpent": 21564
    },
    {
      "emailAddress": "jonatan_armstrong@cruickshank.info",
      "firstName": "Rossie",
      "lastName": "Padberg",
      "purchaseCount": 95,
      "totalSpent": 21318
    },
    {
      "emailAddress": "tess@bergnaum.name",
      "firstName": "Jeremie",
      "lastName": "Runolfsson",
      "purchaseCount": 182,
      "totalSpent": 21221
    }
  ]
}

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

Réponses https://query-tutorial.couchbase.com/tutorial/#44 

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”.

Réponses https://query-tutorial.couchbase.com/tutorial/#45 

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

Résultat : 1 ligne 

{
  "results": [
    {
      "$1": 529
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#46 

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
{
  "results": [
    {
      "avgRating": 0.667,
      "name": "New Commercial Stainless Steel Salt / Pepper / Spice / Sugar Shaker, Shakers, Dredge, Dredges, Set of 2",
      "numReviews": 6,
      "productId": "product96"
    },
    {
      "avgRating": 0.5,
      "name": "Danze D481150 9-Inch Adjustable Shower Arm with High Flow, Chrome",
      "numReviews": 6,
      "productId": "product476"
    },
    {
      "avgRating": 0.889,
      "name": "Tovolo Sphere Ice Molds, Set of 2",
      "numReviews": 9,
      "productId": "product53"
    },
    {
      "avgRating": 0.889,
      "name": "Briggs & Riley Luggage Executive Clamshell Backpack",
      "numReviews": 9,
      "productId": "product10"
    },
    {
      "avgRating": 0.75,
      "name": "Britax B-Agile Stroller Child Tray",
      "numReviews": 4,
      "productId": "product83"
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#47 

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 
{
  "results": [
    {
      "month": "2013-05",
      "revenueMillion": 0.611
    },
    {
      "month": "2013-06",
      "revenueMillion": 0.68
    },
    {
      "month": "2013-07",
      "revenueMillion": 0.601
    },
    {
      "month": "2013-08",
      "revenueMillion": 0.596
    },
    {
      "month": "2013-09",
      "revenueMillion": 0.554
    },
    {
      "month": "2013-10",
      "revenueMillion": 0.617
    },
    {
      "month": "2013-11",
      "revenueMillion": 0.595
    },
    {
      "month": "2013-12",
      "revenueMillion": 0.596
    },
    {
      "month": "2014-01",
      "revenueMillion": 0.613
    },
    {
      "month": "2014-02",
      "revenueMillion": 0.587
    },
    {
      "month": "2014-03",
      "revenueMillion": 0.624
    },
    {
      "month": "2014-04",
      "revenueMillion": 0.602
    },
    {
      "month": "2014-05",
      "revenueMillion": 0.628
    }
  ]
}

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.

Réponses https://query-tutorial.couchbase.com/tutorial/#48 

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
        
{
  "results": [
    {
      "name": "Brother PQ1500S High Speed Quilting and Sewing Machine",
      "product": "product221",
      "purchaseId": "purchase1533",
      "unitPrice": 599
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase170",
      "unitPrice": 700
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product490",
      "purchaseId": "purchase1760",
      "unitPrice": 1094.46
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase1917",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase2195",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase2316",
      "unitPrice": 1499
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase2372",
      "unitPrice": 1094.46
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase2500",
      "unitPrice": 1249.99
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase2891",
      "unitPrice": 599.99
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase2907",
      "unitPrice": 1249.99
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase3010",
      "unitPrice": 1499
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase3278",
      "unitPrice": 700
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase3476",
      "unitPrice": 599.99
    },
    {
      "name": "TOTO MS854114SL-01 Ultramax ADA One Piece Toilet, Cotton White",
      "product": "product506",
      "purchaseId": "purchase3685",
      "unitPrice": 502.37
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football",
      "product": "product833",
      "purchaseId": "purchase3685",
      "unitPrice": 599.99
    },
    {
      "name": "Archie Eli & Peyton Manning NFL Duke Football",
      "product": "product839",
      "purchaseId": "purchase3949",
      "unitPrice": 1249.99
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase4281",
      "unitPrice": 1094.46
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase4660",
      "unitPrice": 700
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football w/ SB XLVI MVP Insc.",
      "product": "product838",
      "purchaseId": "purchase5042",
      "unitPrice": 799.99
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase5321",
      "unitPrice": 524
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase545",
      "unitPrice": 700
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase5483",
      "unitPrice": 524
    },
    {
      "name": "Eli Manning Signed Super Bowl XLVI Football",
      "product": "product833",
      "purchaseId": "purchase568",
      "unitPrice": 599.99
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase6223",
      "unitPrice": 599.99
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase6491",
      "unitPrice": 700
    },
    {
      "name": "Danby DDW1899WP 8 Place Setting Portable Dishwasher - White",
      "product": "product203",
      "purchaseId": "purchase7679",
      "unitPrice": 524
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase8069",
      "unitPrice": 599.99
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase8390",
      "unitPrice": 1094.46
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product293",
      "purchaseId": "purchase8539",
      "unitPrice": 1094.46
    },
    {
      "name": "Oeuf Sparrow Crib, White",
      "product": "product606",
      "purchaseId": "purchase8764",
      "unitPrice": 760
    },
    {
      "name": "LG 3.6 Cu. Ft. White Front Load Steam Washer - WM2650HRA",
      "product": "product167",
      "purchaseId": "purchase8886",
      "unitPrice": 892.49
    },
    {
      "name": "TOTO SW564T695-12 Washlet S400 Elongated Front Toilet Seat for G-Max Toilets with Auto Flush System, Sedona Beige",
      "product": "product490",
      "purchaseId": "purchase9007",
      "unitPrice": 1094.46
    },
    {
      "name": "LG 3.6 CF FRONT LOAD WASHER DRYER COMBO",
      "product": "product160",
      "purchaseId": "purchase9210",
      "unitPrice": 1499
    },
    {
      "name": "LG 3.6 Cu. Ft. White Front Load Steam Washer - WM2650HRA",
      "product": "product167",
      "purchaseId": "purchase9374",
      "unitPrice": 892.49
    },
    {
      "name": "Eli Manning NFL Duke Football",
      "product": "product836",
      "purchaseId": "purchase9591",
      "unitPrice": 599.99
    },
    {
      "name": "EdgeStar 2.0 Cu. Ft. Ventless Combo Washer/Dryer - White",
      "product": "product158",
      "purchaseId": "purchase9598",
      "unitPrice": 830.99
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase9646",
      "unitPrice": 700
    },
    {
      "name": "Diamondback 2013 Recoil 29'er Full Suspension Mountain Bike with 29-Inch Wheels",
      "product": "product788",
      "purchaseId": "purchase9668",
      "unitPrice": 700
    }
  ]
}

Utilisation de Couchbase en local (facultatif)

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/

Depuis machines personnelles

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éation des buckets

  • 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

Importation des données

  • 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;

Données du TD (pour les révisions)

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

site/enseignement/master/mlbda/tmes/couchbase.txt · Dernière modification: 28/11/2022 14:40 par amine

Outils de la page

Sauf mention contraire, le contenu de ce wiki est placé sous les termes de la licence suivante : CC Attribution-Noncommercial-Share Alike 3.0 Unported
CC Attribution-Noncommercial-Share Alike 3.0 Unported Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki