芝尾幸一郎(@shibacow)
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とは、このような複雑さを隠して、楽にデータを取られるようにしています。
目標は、多様なデーセットから、必要とするデータを取り出す事にある。
- 選択
- 集約
##選択
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)
動画投稿日ごとに、集約して、投稿数を表示せよ。
-「病院が来い」を含むコメントを抜き出す
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に言及したコメントを拾い上げてみよう。
- 言葉の始まりはいつからだろう？
- 最も多いタグは何かな？
- 動画の投稿時間と再生数に関係はあるかな？
- 煽りコメントから、そのコメントを主に使うユーザーの年齢は類推できるか？
- コメントはいつが多い？
- 他の動画のコメントに現れた動画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