例: 左外部結合の使用

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の使用例

例6-120 ユースケース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}

例6-121 ユースケース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}}

例6-122 ユースケース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}}