![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
[[CHJBootCamp2018/DAY2]] **スライド -[[スライドPDF>https://www.ogiso.net/wiki/index.php?plugin=attach&pcmd=open&file=20180823pm_drill.pdf&refer=CHJBootCamp2018%2FDAY2Drill]] **演習1 select L.語彙素,L.語彙素読み,count(*) as 粗頻度, sum(case when B.ジャンル='キリシタン資料' then 1 else 0 end) as キリシタン資料, sum(case when B.ジャンル='狂言' then 1 else 0 end) as 狂言資料 from 長単位 as L inner join 書誌情報 as B on L.サンプルID=B.サンプルID where L.語彙素 like N'御%有る' group by L.語彙素, L.語彙素読み order by count(L.語彙素) desc **演習2 select 語彙素,語彙素読み, sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 end) as 原文カタカナ, sum(case when 原文文字列 not like '%[ァ-ヴ]%' then 1 else 0 end) as 原文カタカナ以外, count(*) as 粗頻度 from 短単位 where サブコーパス名 like '江戸' and 品詞='感動詞-一般' group by 語彙素,語彙素読み order by 語彙素読み **演習3 select 語彙素,語彙素読み,品詞,語種,count(*) as 粗頻度, count (case when サブコーパス名='奈良' then 1 else 0 end) as 奈良, count (case when サブコーパス名='平安' then 1 else 0 end) as 平安, count (case when サブコーパス名='鎌倉' then 1 else 0 end) as 鎌倉, count (case when サブコーパス名='室町' then 1 else 0 end) as 室町, count (case when サブコーパス名='江戸' then 1 else 0 end) as 江戸, count (case when サブコーパス名='明治・大正' then 1 else 0 end) as 明治・大正 from 短単位 where 品詞 like '形容詞%' and len(語彙素読み)>3 group by 語彙素,語彙素読み,品詞,語種 order by 語彙素読み **演習4 use chunagon_chj 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.語彙素 **演習5 select 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.語彙素 **演習6 use chunagon_chj select s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素, 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.サブコーパス名='平安'and s1.品詞 like '助動詞%' and s2.品詞 like '助動詞%' and s3.品詞 like '助動詞%' group by s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素 order by count(*) desc **演習7 select 品詞, sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 end) as 原文カタカナ, count(*) as 粗頻度, CONVERT(float,sum(case when 原文文字列like '%[ァ-ヴ]%'then 1 else 0 end))/count(*) from 短単位 where サブコーパス名 like '江戸' and 品詞='感動詞-一般' group by 品詞 **演習8 use unidicSQL 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 'ナサル' group by 語彙素,語形,書字形 order by 語形 **演習9 use unidicSQL select L.語彙素,L.語彙素読み,S.サブコーパス名,count(S.キー)as 粗頻度 from 短単位語彙素 as L inner join chunagon_chj.dbo.短単位 as S on L.語彙素ID=S.語彙素ID where L.語彙素 like '秋%' and L.語彙素読み like 'アキ%' group by L.語彙素,L.語彙素読み, S.サブコーパス名 order by S.サブコーパス名
