例: GROUP BY句でのネスト解除の使用

TVストリーミング・アプリケーションについて検討します。世界中の顧客が視聴する様々な番組をストリーミングしています。すべての番組には数多くのシーズンがあり、各シーズンには複数のエピソードがあります。TVストリーミング・アプリケーションを使用して顧客の現在のアクティビティをトラッキングする永続メタデータ・ストアが必要です。顧客は、視聴したエピソード、エピソードごとの視聴時間、視聴した番組の合計シーズン数などについて知りたいと考えています。顧客は、視聴を停止した場所からストリーミング・アプリケーションによってストリーミングが開始されることも要求しています。ストリーミング・アプリケーションには、どの番組が顧客に最も人気があるか、番組が何分間視聴されているかなどのレポートが必要です。これらのレポートは、問合せでUNNEST句を使用して生成できます。

TVストリーミング・アプリケーションの表の作成およびデータのロード

CREATE TABLE stream_acct(acct_id
        INTEGER, acct_data JSON, PRIMARY KEY(acct_id));

表にいくつかの行を移入します。

INSERT INTO stream_acct VALUES(
1,
{
   "firstName" : "John",
   "lastName" : "Sanders",
   "country" : "USA",
   "contentStreamed": [
      {
         "showName": "Call My Agent",
         "showId": 12,
         "showType": "tvseries",
         "numSeasons" : 2,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 40, "minWatched" : 40 },
                  { "episodeID" : 30, "lengthMin" : 42, "minWatched" : 42 }
               ]
            },
            {
               "seasonNum": 2,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 50, "minWatched" : 50 },
                  { "episodeID" : 30, "lengthMin" : 46, "minWatched" : 46 }
               ]
            }
        ]
     },
     {
         "showName": "Rita",
         "showId": 16,
         "showType": "tvseries",
         "numSeasons" : 1,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                   { "episodeID" : 20, "lengthMin" : 65, "minWatched" : 65 },
                   { "episodeID" : 30, "lengthMin" : 60, "minWatched" : 60 }
                ]
            }
         ]
      }
    ]
  }
);
//second row
INSERT INTO stream_acct VALUES(
2,
{
   "firstName" : "Tim",
   "lastName" : "Greenberg",
   "country" : "USA",
   "contentStreamed": [
      {
         "showName": "Call My Agent",
         "showId": 12,
         "showType": "tvseries",
         "numSeasons" : 2,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 38, "minWatched" : 36 },
                  { "episodeID" : 30, "lengthMin" : 40, "minWatched" : 40 }
               ]
            },
            {
               "seasonNum": 2,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 50, "minWatched" : 50 },
                  { "episodeID" : 30, "lengthMin" : 46, "minWatched" : 46 }
               ]
            }
        ]
     },
     {
         "showName": "Mr.Chef",
         "showId": 13,
         "showType": "tvseries",
         "numSeasons" : 1,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                   { "episodeID" : 20, "lengthMin" : 65, "minWatched" : 65 },
                   { "episodeID" : 30, "lengthMin" : 60, "minWatched" : 60 }
                ]
            }
         ]
      }
    ]
  }
);
//third row
INSERT INTO stream_acct VALUES(
3,
{
   "firstName" : "Aniketh",
   "lastName" : "Shubham",
   "country" : "India",
   "contentStreamed": [
      {
         "showName": "Apprentice",
         "showId": 14,
         "showType": "tvseries",
         "numSeasons" : 2,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 40, "minWatched" : 40 },
                  { "episodeID" : 30, "lengthMin" : 42, "minWatched" : 42 }
               ]
            },
            {
               "seasonNum": 2,
               "numEpisodes" : 2,
               "episodes": [
                  { "episodeID" : 20, "lengthMin" : 50, "minWatched" : 50 },
                  { "episodeID" : 30, "lengthMin" : 46, "minWatched" : 46 }
               ]
            }
        ]
     },
     {
         "showName": "Mystery unfolded",
         "showId": 15,
         "showType": "tvseries",
         "numSeasons" : 1,
         "seriesInfo": [
            {
               "seasonNum" : 1,
               "numEpisodes" : 2,
               "episodes": [
                   { "episodeID" : 20, "lengthMin" : 65, "minWatched" : 65 },
                   { "episodeID" : 30, "lengthMin" : 60, "minWatched" : 60 }
                ]
            }
         ]
      }
    ]
  }
);

例1: 米国の人々のみに視聴された様々な番組と、それらを視聴する人の数をフェッチします。

SELECT $show.showId, count(*) as cnt FROM stream_acct $s,
unnest($s.acct_data.contentStreamed[] as $show)
WHERE $s.acct_data.country = "USA" GROUP BY $show.showId 
ORDER BY count(*) DESC ;

{"showId":12,"cnt":2}
{"showId":13,"cnt":1}
{"showId":16,"cnt":1}

例2: アプリケーションで放送されたすべての番組について、すべてのユーザーの合計視聴時間を取得します。

SELECT $show.showId, sum($show.seriesInfo.episodes.minWatched) AS total_time
FROM stream_acct $s, unnest($s.acct_data.contentStreamed[] AS $show) 
GROUP BY $show.showId ORDER BY sum($show.seriesInfo.episodes.minWatched);

{"showId":13,"total_time":125}
{"showId":15,"total_time":125}
{"showId":16,"total_time":125}
{"showId":14,"total_time":178}
{"showId":12,"total_time":350}

ノート:

ネスト解除演算子(キーワードunnest)は、no-op演算子であるため省略できます。ネスト解除されている配列またはマップに索引がある場合は、UNNEST句を使用することをお薦めします。詳細は、例: 問合せの最適化での索引の使用を参照してください。

UNNEST句のない次の問合せは、前述の(UNNEST句を含む)問合せと同等であり、同じ結果になります。

SELECT $show.showId, sum($show.seriesInfo.episodes.minWatched) AS total_time
FROM stream_acct $s, $s.acct_data.contentStreamed[] AS $show 
GROUP BY $show.showId ORDER BY sum($show.seriesInfo.episodes.minWatched);

{"showId":13,"total_time":125}
{"showId":15,"total_time":125}
{"showId":16,"total_time":125}
{"showId":14,"total_time":178}
{"showId":12,"total_time":350}

例3: 番組およびシーズンごとのユーザーの合計視聴時間のフェッチ

SELECT $show.showId, $seriesInfo.seasonNum, 
sum($seriesInfo.episodes.minWatched) AS length
FROM stream_acct n,
unnest(n.acct_data.contentStreamed[] AS $show, 
$show.seriesInfo[] as $seriesInfo)
GROUP BY $show.showId, $seriesInfo.seasonNum
ORDER BY sum($seriesInfo.episodes.minWatched);

{"showId":14,"seasonNum":1,"length":82}
{"showId":14,"seasonNum":2,"length":96}
{"showId":13,"seasonNum":1,"length":125}
{"showId":15,"seasonNum":1,"length":125}
{"showId":16,"seasonNum":1,"length":125}
{"showId":12,"seasonNum":1,"length":158}
{"showId":12,"seasonNum":2,"length":192}

例4: 非パス式でのネスト解除の使用

これは、ネスト解除式がパス式ではなく、その結果UNNEST句を使用できない例です。たとえば、ユーザーは同じ市外局番に複数の電話番号を設定できます。市外局番が異なる電話番号を持っているユーザーの数を決定するには、(同じ市外局番の電話番号が複数ある場合でも) 1度だけ特定のユーザーをカウントする必要があります。次の問合せでは、市外局番ごとに、その市外局番に電話番号があるユーザーの数が返されます。問合せではseq_distinct()関数が使用され、重複値は削除されます。

SELECT $area, count(*) AS cnt
FROM Users u, seq_distinct(u.address.phones.area) AS $area
GROUP BY $area;

{"area":408,"cnt":1}
{"area":831,"cnt":1}

seq_disctinct関数の詳細は、順序の関数を参照してください。