Top / 研修 / データビジュアライゼーション / Hive講習

データビジュアライゼーションワークショップ

Hive研修

sqLの基礎

SQLはあるデータの固まりを取り出す一連の手続きです

例えば、

SELECT * FROM nicodata.tags WHERE tag="動画" LIMIT 10

これで、tagデータのなかからタグが動画の情報を取得します。

SELECT smid FROM nicodata.tags WHERE tag="動画" LIMIT 10

こうすればタグが動画のsmidをみる事が出来ます。

もしこれを、プログラムでやったなら?

var dataset = open("tagdata","rb");
var smids=new array();
foreach (dataset as data){
  if(data.tag=="動画"){
    smids.append(data.smid);
    if (smids.length>10){break;}
  }
}
print skids;

みたいな複雑な書き方になる。 SQLとは、このような複雑さを隠して、楽にデータを取られるようにしています。

SQLの目的

目標は、多様なデーセットから、必要とするデータを取り出す事にある。

選択

SELECT * FROM nicodata.video_info WHERE to_date(updated_time) > to_date('2012-01-01 00:00:00') LIMIT 10;

動画投稿日が、2012-01-01以降の物を選択して、10件を上限に表示せよ。

集約

SELECT to_date(update_time),count(*) FROM nicodata.video_info GROUP BY to_date(update_time)

動画投稿日ごとに、集約して、投稿数を表示せよ。

SQLのサンプル集

  • 「病院が来い」を含むコメントを抜き出す
    SELECT * FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%" LIMIT 10
  • 「病院が来い」を含むコメントがいくつあるか調べる
    SELECT count(*) FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%"
  • 「病院が来い」を含むコメントが多い動画を抜き出し、多い順に並べる。
    • 病院が来いというコメントを含む動画を、動画ごとに集約し、コメント数で多い順にソートする。
      SELECT smid,count(*) as cnt FROM nicodata.comment_data_sampling100 WHERE comment_string LIKE "%病院が来い%" GROUP BY smid ORDER BY cnt DESC LIMIT 20

調べてみよう

  • 8888の使用頻度は、月別にどう変化した?
  • 他の動画idに言及したコメントを拾い上げてみよう。
  • 言葉の始まりはいつからだろう?
  • 最も多いタグは何かな?
  • 動画の投稿時間と再生数に関係はあるかな?
  • 煽りコメントから、そのコメントを主に使うユーザーの年齢は類推できるか?
  • コメントはいつが多い?

サンプルSQL ] [#i4e89497]

  • 他の動画のコメントに現れた動画ID
    select a.sid as asid,count(a.sid) as cnt FROM (
    select regexp_extract(comment_string,"(sm[0-9]+)",0) as sid from 
    nicodata.comment_data where comment_string rlike "sm[0-9]+" AND regexp_extract(comment_string,"(sm[0-9]+)",0) != smid 
    ) a GROUP BY a.sid ORDER By cnt DESC LIMIT 200

トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2015-02-01 (日) 14:38:24 (1723d)