例: 左外部結合の使用

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}}