CHJBootCamp2018/DAY2

CHJBootCamp2018

SQL(2) グループ化、表の結合

グループ化と集計

グループ化： GROUP BY 句

• SELECT 列名,列名2... FROM テーブル GROUP BY 列名
  • 短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読みのものごとにまとめて語彙素,語彙素読みの列を出力

    SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み

ORDER BY 句 (復習)

• SELECT 列名,列名2... FROM テーブル GROUP BY 列名 ORDER BY 列名
  • 短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読みのものごとにまとめて語彙素,語彙素読みの列を 語彙素読み,語彙素順に出力

    SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み ORDR BY 語彙素読み,語彙素

COUNT()：集計関数

• 集計した数を返す
  • 短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読みのものごとにまとめて語彙素,語彙素読みの列と集計した語数を出力

    SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み

  • 短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読みのものごとにまとめて語彙素,語彙素読みの列と集計した語数を語彙素読み（アイウエオ）順で出力

    SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み ORDER BY 語彙素読み

  • 短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読みのものごとにまとめて語彙素,語彙素読みの列と集計した語数を語数の多い順で出力

    SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み ORDER BY count(語彙素) DESC

表の結合

• 内部結合 INNER JOIN
  • 結合条件に一致するレコードだけを表示
• 外部結合 LEFT (OUTER) JOIN
  • 軸にする左のテーブルのレコード全てと、結合条件に一致する右テーブルのレコードを表示。一致するものがない場合、右側はNULL（値なし）となる。

書誌情報テーブルと結合

• 短単位・長単位テーブルとはサンプルIDで結合する
  • 必ずコーパスに対応する書誌情報がある（結合できないことはない）

    select top 100 *
    from 短単位 as S inner join 書誌情報 as B on S.サンプルID=B.サンプルID

短単位・長単位テーブルを結合（外部結合）

• 短単位と長単位の結合にはサンプルIDと文字開始位置を使う（連番は短単位と長単位で異なる）
  • 対応する長単位がない場合（長単位の途中にある短単位）、NULLになる。

    select top 100 *
    from 短単位 as S left join 長単位 as L on S.サンプルID=L.サンプルID and S._文字開始位置=L._文字開始位置

応用：構成要素として助動詞を含む長単位名詞

select s.サンプルID,s.連番,s.語彙素,l.キー,l.語彙素 from 長単位 as l
inner join 短単位 as s
 on s.サンプルID=l.サンプルID
  and s._文字開始位置>=l._文字開始位置
  and s._文字開始位置<l._文字終了位置
where s.サブコーパス名='平安'
and s.品詞 like '助動詞%' and l.品詞 like '名詞%'

【補足】

• select * from でテーブルの全ての列をもってくる
• select count(*) as 名前 from テーブル as X　のように列やテーブルに名前（エイリアス）を付けられる
  • 長いテーブル名や、集計結果などの名無しの列で使う
• データベースのNULLは、非常に特殊な値
  • 普通には比較できないので、is nullやISNULL関数等を使う

表の結合と集計

• 書誌情報テーブルと結合して作品別の語数を集計

  select 作品名, count(*) as 総語数
  from 短単位 as S inner join 書誌情報 as B on S.サンプルID=B.サンプルID
  where S.サブコーパス名 like '平安'
  group by 作品名

SQL(3) コーパスデータの扱い

関数のいろいろ

組み込み関数

• https://msdn.microsoft.com/ja-jp/library/ms174318(v=sql.120).aspx

文字列関数

• Excelで使ったのと同じようなものが使えるが名前が違うものも
  • ExcelのSEARCH ＝ charindex,patindex
  • ExcelのMID ＝ substring　など

KWIC生成関数（ユーザー定義関数）

• KWIC生成
  • fn前文脈(サンプルID,出現書字形開始位置,長さ)
  • fn後文脈(サンプルID,出現書字形開始位置,長さ)

select top 100 dbo.fn前文脈(サンプルID,出現書字形開始位置,20),キー,dbo.fn後文脈(サンプルID,出現書字形開始位置,20) from 短単位

select top 100 サンプルID,dbo.fn前文脈(サンプルID,出現書字形開始位置,20),キー,dbo.fn後文脈(サンプルID,出現書字形開始位置,20),語彙素,語彙素読み,品詞 from 短単位
where 語彙素 like '鰻'

BCCWJデータを使うには

use chunagon_bccwj

• CHJとほとんど同じデータ構造。サイズが大きいことに注意
• サブコーパス名でなくBCCWJは「レジスター」*1

---

CASE式をつかった集計

• CASE WHEN ● THEN ○○ WHEN ■ THEN □□ ELSE △ END

 select 作品名, count(*) as 総語数,sum(CASE WHEN 本文種別='歌' THEN 1 ELSE 0 END) as 歌語数
 from 短単位 as S inner join 書誌情報 as B on S.サンプルID=B.サンプルID
 where S.サブコーパス名 like '平安'
 group by 作品名

• ※本文種別でグループ化して出力した表をExcelのピボットテーブルで集計した方が早くて間違えにくい場合も。

連続する語の取り出し

• 同一の表を一語ずつずらしながら結合する。コーパスデータの扱いで頻出するテクニック。
  • （前方後方共起による検索条件指定ができる）

    select top 100 s1.語彙素,s1.語彙素読み,s1.品詞,dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1.キー,dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20)
    from 短単位 as s1 inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番 + 10 = s2.連番
    where s2.語彙素 like '鰻'

• 【追補】「心」の一つ前に来る形容詞連体形の用例

  select s1.サンプルID,s1.出現書字形開始位置,
  dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1.キー,dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1.語彙素,s1.語彙素読み,s1.原文文字列
  from 短単位 as s1
  inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番+10 = s2.連番
  where s1.品詞 like '形容詞%' and s1.活用形 like '連体形%' and s2.語彙素 = '心' 

N-gram

• 語彙素6グラム

  select top 100
  s1.キー+s2.キー+s3.キー+s4.キー+s5.キー+s6.キー,
  s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+s5.語彙素+'/'+s6.語彙素
  from 短単位 as s1
   inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番 + 10 = s2.連番
   inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID and s2.連番 + 10 = s3.連番
   inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID and s3.連番 + 10 = s4.連番
   inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID and s4.連番 + 10 = s5.連番
   inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID and s5.連番 + 10 = s6.連番
  where s1.サブコーパス名='平安' 
  order by s1.サンプルID, s1.連番

• 【追補】語彙素6グラムの集計(多いもの順、記号等を含まない)

  select top 100
  s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+s5.語彙素+'/'+s6.語彙素 , count(*)
  from 短単位 as s1
   inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番 + 10 = s2.連番
   inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID and s2.連番 + 10 = s3.連番
   inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID and s3.連番 + 10 = s4.連番
   inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID and s4.連番 + 10 = s5.連番
   inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID and s5.連番 + 10 = s6.連番
  where s1.サブコーパス名='明治・大正' 
  and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not like '%記号%'
  and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not like '%空白%'
  group by s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+s5.語彙素+'/'+s6.語彙素
  order by count(*) desc

【追補】N語以内の指定

• 「食う」の前方5語以内に来る 名詞＋を

  select s1.サンプルID,s1.出現書字形開始位置,
  dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1.キー,dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20)
  from 短単位 as s1
   inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番+10 = s2.連番
   inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID and s2.連番+50 >= s3.連番  and s2.連番 < s3.連番
  where s1.品詞 like '名詞%' and s2.語彙素 = 'を' and s3.語彙素 ='食う'

文数・文節数・語数

select 作品名--,S.本文種別
,sum(case when S.文境界 = 'B' then 1 else 0 end) as 文数
,sum(case when 文節='B' then 1 else 0 end) as 文節数
,count(L.語彙素) as 長単位数
,count(S.語彙素) as 短単位数
from 短単位 as S inner join 書誌情報 as B on S.サンプルID = B.サンプルID
left join 長単位 as L on S.サンプルID = L.サンプルID and S._文字開始位置 = L._文字開始位置
where B.サブコーパス名 like '平安'
group by B.作品名--,S.本文種別

サブクエリ

括弧でくくったSELECT文に名前を付けて、あたかもテーブルであるかのように扱える

• 異なり語数

  select x.サブコーパス名, count(*) from
   (select サブコーパス名, count(*) as 語数 from 短単位 where 品詞 not like '記号%'
    group by 語彙素ID,サブコーパス名) as x
  group by x.サブコーパス名

• TTR（Type Token Ratio）

  select x.サブコーパス名, count(*) as 異なり語数, sum(語数) as 延べ語数, 
  CONVERT(float,count(*))/sum(語数) as TTR from
   (select サブコーパス名, count(*) as 語数 from 短単位 where 品詞 not like '%記号%'
    group by 語彙素ID,サブコーパス名) as x
  group by x.サブコーパス名

辞書データベース（unidicSQL）

辞書の階層化された見出し表を扱う

use unidicSQL

• 語彙素
  • 語形
    • 書字形

• 語彙素を出力

  select * from 短単位語彙素 where 語彙素 like '夏%' and 語彙素読み like 'ナツ%'

• 語彙素の全ての語形,入力活用型を出力

  SELECT 語形,入力活用型 from 短単位語形 as F inner join 短単位語彙素 as L on L.語彙素ID=F.語彙素ID 
  where 語彙素 like '見る' and 語彙素読み like 'ミル'

• 語彙素の全ての書字形を出力

  SELECT 語形,書字形 from 短単位書字形 as O inner join 短単位語形 as F on F.語形ID=O.語形ID inner join 短単位語彙素 as L on L.語彙素ID=F.語彙素ID 
  where 語彙素 like '見る' and 語彙素読み like 'ミル'

コーパスと辞書を繋ぐ

• 語彙素ID, 語形ID, 書字形ID

  use chunagon_bccwj
  select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単位語彙素 as L inner join chunagon_bccwj.dbo.短単位 as SUW on L.語彙素ID=SUW.語彙素ID
  where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
  group by L.語彙素, SUW.レジスター

• 上と同じものを外部結合

  use chunagon_bccwj
  select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単位語彙素 as L left join chunagon_bccwj.dbo.短単位 as SUW on L.語彙素ID=SUW.語彙素ID
  where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
  group by L.語彙素, SUW.レジスター

一時テーブルの作成

• #で始まるテーブル名は、DB切断後削除される一時テーブルとなる。現在の権限でも一時テーブルは作成できる。
• 複雑なサブクエリを書くより、一時テーブルに書き出した方が処理が早いことが多い

• SELECT カラム INTO 新しい書き込み先のテーブル FROM テーブル WHERE条件句
  • 権限がないのでエラー

    select * into 鰻テーブル from 短単位 where 語彙素='鰻'

  • OK

    select * into #鰻テーブル from 短単位 where 語彙素='鰻'
    
    select * from #鰻テーブル

• drop table：テーブルの削除（一時テーブル以外は権限上削除できない）

  drop table #鰻テーブル

【追補】応用例：記号を含まない短単位テーブルを作る

select ROW_NUMBER() OVER (PARTITION BY サンプルID ORDER BY サンプルID,連番) *10 as 新連番, *
into #記号なし短単位
from 短単位 where サブコーパス名='平安'
and 品詞 not like '%記号%' and 品詞 != '空白'

select * from #記号なし短単位 ORDER BY サンプルID,新連番

【追補2018/08/31】文番号の利用

• 文番号の列 _文番号 が増えました。
• これを使うことで同一文中の共起語などを簡単に見つけることができます。

select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,30),s1.キー,dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,30),s1.語彙素,s1.語彙素読み,s1.原文文字列
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1._文番号 = s2._文番号
where s1.語彙素 like '秋'  and s2.語彙素 like '夕%' 
order by s1.サンプルID,s1.出現書字形開始位置

質疑・相談会

受講者アンケート

• https://docs.google.com/forms/d/1Xe3Jk_lvUWZ06FTNP85tJdBZLNcjF8howa7NjoBEnUE/