例: 左外部結合の使用

Oracle NoSQL Databaseを使用するオンライン・ショッピング・ポータルの例を見てみましょう。このアプリケーションには、オンライン・ショッピングを実行し、ショッピング・カートで購入するものを追加する数百万の顧客が存在します。最後に、オーダーが処理され、請求されます。顧客のショッピング・パターンは、このアプリケーションを使用して分析できます。

ここでは、親子関係に3つの表があります。顧客のすべての個人情報を含むcustomerprofile表は、最上位の表です。顧客が選択した項目、その数量および各項目の個々の価格をリストするShoppingcartは、customerprofileの子孫表です。合計オーダー数量と合計オーダー額を含むOrderは、shoppingcartの子孫表です。

これらの表を作成し、いくつかのデータを移入します。

CREATE TABLE customerprofile (
     customer_id INTEGER,
     customer_name STRING,
     customer_address STRING,
     customer_phone INTEGER,
     PRIMARY KEY(customer_id));
CREATE TABLE customerprofile.shoppingcart (
     cart_id STRING,
     product_name STRING,
     product_quantity INTEGER,
     product_price INTEGER,
     PRIMARY KEY(cart_id));
CREATE TABLE customerprofile.shoppingcart.order (
     order_id INTEGER,
     order_quantity INTEGER,
     order_total INTEGER,
     PRIMARY KEY(order_id));

INSERT INTO customerprofile VALUES (1,"Aana","Blr",111111111);
INSERT INTO customerprofile VALUES (2,"Bobby","Chn",22222222);
INSERT INTO customerprofile VALUES (3,"Celin","Del",33333333);
INSERT INTO customerprofile VALUES (4,"Diana","Blr",44444444);
INSERT INTO customerprofile VALUES (5,"Elizabeth","Mum",55555555);

INSERT INTO customerprofile.shoppingcart VALUES(1,'c1',"Shampoo",2,300);
INSERT INTO customerprofile.shoppingcart VALUES(1,'c2',"Soap",3,80);
INSERT INTO customerprofile.shoppingcart VALUES(2,'c3',"Milk",5,100);
INSERT INTO customerprofile.shoppingcart VALUES(3,'c4',"Chips",2,50);
INSERT INTO customerprofile.shoppingcart VALUES(4,'c5',"Bread",1,40);

INSERT INTO customerprofile.shoppingcart.order VALUES(1,'c1',100,2,600);
INSERT INTO customerprofile.shoppingcart.order VALUES(1,'c2',101,3,240);
INSERT INTO customerprofile.shoppingcart.order VALUES(2,'c3',102,5,500);
INSERT INTO customerprofile.shoppingcart.order VALUES(3,'c4',103,2,100);

LOJの使用例

ユースケース1: すべての顧客詳細とそのオーダー履歴をフェッチします

SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart.order c
ON a.customer_id = c.customer_id ORDER BY a.customer_id;

{"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"c":{"customer_id":1,"cart_id":"c1","order_id":100,"order_quantity":2,"order_total":600}}
{"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"c":{"customer_id":1,"cart_id":"c2","order_id":101,"order_quantity":3,"order_total":240}}
{"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},"c":{"customer_id":2,"cart_id":"c3","order_id":102,"order_quantity":5,"order_total":500}}
{"a":{"customer_id":3,"customer_name":"Celin","customer_address":"Del","customer_phone":33333333},"c":{"customer_id":3,"cart_id":"c4","order_id":103,"order_quantity":2,"order_total":100}}
{"a":{"customer_id":4,"customer_name":"Diana","customer_address":"Blr","customer_phone":44444444},"c":null}
{"a":{"customer_id":5,"customer_name":"Elizabeth","customer_address":"Mum","customer_phone":55555555},"c":null}

ユースケース2: 同じ項目を3つ以上購入したすべての顧客をフェッチします

SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart b
  ON a.customer_id = b.customer_id WHERE product_quantity >2;

{"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"b":{"customer_id":1,"cart_id":"c2","product_name":"Soap","product_quantity":3,"product_price":80}}
{"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},"b":{"customer_id":2,"cart_id":"c3","product_name":"Milk","product_quantity":5,"product_price":100}}

ユースケース3: 500以上の額を購入した顧客のみの詳細をフェッチします

SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart.order b
ON a.customer_id = b.customer_id WHERE order_total >=500 ;

{"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},
 "b":{"customer_id":1,"cart_id":"c1","order_id":100,"order_quantity":2,"order_total":600}}
{"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},
 "b":{"customer_id":2,"cart_id":"c3","order_id":102,"order_quantity":5,"order_total":500}}