SQL講座3:表の結合とコーパスデータの扱い
講師:片山久留美(国立国語研究所)
練習問題・解答例
--「短単位」「書誌情報」テーブルにどんな情報が入っているか
select top 100 * from 短単位
select top 100 * from 書誌情報
--問題1
select B.作品名,count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
where S.サブコーパス名 like '平安%'
group by B.作品名
--問題1参考(書誌情報テーブルだけで「作品名」をcountすると)
select 作品名,count(*) from 書誌情報
where サブコーパス名 like '平安%'
group by 作品名
--問題2
select B.サブコーパス名,B.作品名,count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
group by B.サブコーパス名,B.作品名
order by B.サブコーパス名,B.作品名
--問題3
select S.語彙素,S.語彙素読み,count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
where S.サブコーパス名 ='和歌集'
and S.品詞 like '動詞%' and S.本文種別 like '歌%' and B.巻名等 like '%恋%'
group by S.語彙素,S.語彙素読み
order by count(S.語彙素)desc
--問題4
select S.語彙素,S.語彙素読み,count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
where B.部 = '大坂' and S.品詞 ='助動詞'
and S.本文種別 like '会話%'
and S.サブコーパス名='江戸-洒落本'
group by S.語彙素,S.語彙素読み,S.本文種別
order by count(S.語彙素)desc
--問題5
select B.成立年, B.作品名, S.語彙素,S.語彙素読み,S.書字形, count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
where S.サブコーパス名 like '明治・大正%'
and S.品詞 like '副詞%' and S.語種 = '漢'
group by B.成立年,B.作品名,S.語彙素,S.語彙素読み,S.書字形
order by B.成立年,B.作品名,S.語彙素読み,S.語彙素,S.書字形
--問題6
select S.原文文字列,B.巻名等,count(*)
from 短単位 as S inner join 書誌情報 as B
on S.サンプルID=B.サンプルID
where S.語彙素='恋' and S.サブコーパス名='奈良-万葉集'
group by S.原文文字列,B.巻名等
order by S.原文文字列,B.巻名等
--表の結合・補足(短単位テーブルと長単位テーブルの結合)
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.サブコーパス名 like '平安%' and s.品詞 like '助動詞%' and l.品詞 like '名詞%'
--表の結合・補足(多重形態論テーブルと短単位テーブルの結合)
select * from 多重形態論 as T left join 短単位 as S
on T.サンプルID=S.サンプルID and T._文字開始位置=S._文字開始位置
--問題7
select サンプルID,
dbo.fn前文脈(サンプルID,出現書字形開始位置,20),
キー,
dbo.fn後文脈(サンプルID,出現書字形開始位置,20),
語彙素,語彙素読み,品詞,活用型,活用形
from 短単位
where 品詞 like '動詞%' and 活用型 not like '文語%' and サブコーパス名 like '鎌倉%'
--問題8
select S.語彙素,S.語彙素読み,S.品詞,S.語種,count(*) as 粗頻度,
sum (case when B.時代 = '1奈良' then 1 else 0 end) as 奈良,
sum (case when B.時代 = '2平安' then 1 else 0 end) as 平安,
sum (case when B.時代 = '3鎌倉' then 1 else 0 end) as 鎌倉,
sum (case when B.時代 = '4室町' then 1 else 0 end) as 室町,
sum (case when B.時代 = '5江戸' then 1 else 0 end) as 江戸,
sum (case when B.時代 = '6明治' then 1 else 0 end) as 明治,
sum (case when B.時代 = '7大正' then 1 else 0 end) as 大正,
sum (case when B.時代 = '8昭和' then 1 else 0 end) as 昭和
from 短単位 as S inner join 書誌情報 as B on S.サンプルID=B.サンプルID
where 品詞 like '形容詞%'
group by 語彙素,語彙素読み,品詞,語種
order by 語彙素読み
--問題9
select 語彙素,語彙素読み,語種,count(*) as 総語数,
sum(CASE WHEN 原文文字列 like '%[一-龠]%' THEN 1 ELSE 0 END) as 漢字表記語
from 短単位
where サブコーパス名='室町-狂言' and 品詞 like '名詞%'
group by 語彙素,語彙素読み,語種
order by count(*)desc
--問題10
select s1.サブコーパス名,s1.語彙素,s1.語彙素読み,s1.品詞,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20) as 前文脈,
s1.キー,
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20) as 後文脈
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID
and s1.連番 + 10 = s2.連番
where s1.品詞 like '%助詞%' and s2.語彙素 = '恐れる'
order by s1.品詞,s1.語彙素
--問題11
select s1.サンプルID,s1.語彙素,s1.語彙素読み,s1.品詞,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20)
as 前文脈, s1.キー,
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20)
as 後文脈
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID
and s1.連番= s2.連番 + 10
where s2.語彙素 = '良い' and s2.活用形 like '連体形%'
and s1.品詞 like '名詞%' order by s1.品詞,s1.語彙素
--問題12
select s1.キー+s2.キー+s3.キー as 出現形, s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素 as 語彙素,
count(*) as 頻度 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.連番
where s1.サブコーパス名 like '平安%'
and s1.品詞 like '助動詞%'
and s2.品詞 like '助動詞%'
and s3.品詞 like '助動詞%'
group by s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素
order by count(*) desc
--問題13
select s1.サンプルID,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,30),
s1.キー,s1.原文文字列,
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,30),
s2.語彙素,s2.語彙素読み
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID
and s1._文番号 = s2._文番号
where s1.語彙素 like '秋' and s2.語彙素 like '夕%'
and s1.サブコーパス名 like '和歌集%'
order by s1.サンプルID