CHJBootCamp2018/DAY2Drill
をテンプレートにして作成
開始行:
[[CHJBootCamp2018/DAY2]]
**演習1
select L.語彙素,L.語彙素読み,count(*) as 粗頻度,
sum(case when B.ジャンル='キリシタン資料' then 1 else 0 ...
sum(case when B.ジャンル='狂言' then 1 else 0 end) as 狂...
from 長単位 as L inner join 書誌情報 as B on L.サンプルI...
where L.語彙素 like N'御%有る'
group by L.語彙素, L.語彙素読み
order by count(L.語彙素) desc
**演習2
select 語彙素,語彙素読み,
sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 ...
sum(case when 原文文字列 not like '%[ァ-ヴ]%' then 1 els...
count(*) as 粗頻度 from 短単位
where サブコーパス名 like '江戸' and 品詞='感動詞-一般'
group by 語彙素,語彙素読み
order by 語彙素読み
**演習3
select 語彙素,語彙素読み,品詞,語種,count(*) as 粗頻度,
count (case when サブコーパス名='奈良' then 1 else 0 end...
count (case when サブコーパス名='平安' then 1 else 0 end...
count (case when サブコーパス名='鎌倉' then 1 else 0 end...
count (case when サブコーパス名='室町' then 1 else 0 end...
count (case when サブコーパス名='江戸' then 1 else 0 end...
count (case when サブコーパス名='明治・大正' then 1 else...
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 ...
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.品詞 like '%助詞%' and s2.語彙素 = '恐れる’
order by s1.品詞,s1.語彙素
**演習5
select s1.語彙素,s1.語彙素読み,s1.品詞,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
from 短単位as s1
inner join 短単位as s2 on s1.サンプルID=s2.サンプルID an...
where s2.語彙素 = '良い' and s2.活用形 like '連体形%'
and s1.品詞 like '名詞%' order by s1.品詞,s1.語彙素
**演習6
use chunagon_chj
select s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙素+...
count(*) as 頻度from 短単位 as s1
inner join 短単位as s2 on s1.サンプルID=s2.サンプルID an...
inner join 短単位as s3 on s2.サンプルID=s3.サンプルID an...
where s1.サブコーパス名='平安'and s1.品詞 like '助動詞%'...
group by s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙...
order by count(*) desc
**演習7
select 品詞,
sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 ...
count(*) as 粗頻度,
CONVERT(float,sum(case when 原文文字列like '%[ァ-ヴ]%'th...
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.キ...
from 短単位語彙素 as L
inner join chunagon_chj.dbo.短単位 as S on L.語彙素ID=S....
where L.語彙素 like '秋%' and L.語彙素読み like 'アキ%'
group by L.語彙素,L.語彙素読み, S.サブコーパス名
order by S.サブコーパス名
終了行:
[[CHJBootCamp2018/DAY2]]
**演習1
select L.語彙素,L.語彙素読み,count(*) as 粗頻度,
sum(case when B.ジャンル='キリシタン資料' then 1 else 0 ...
sum(case when B.ジャンル='狂言' then 1 else 0 end) as 狂...
from 長単位 as L inner join 書誌情報 as B on L.サンプルI...
where L.語彙素 like N'御%有る'
group by L.語彙素, L.語彙素読み
order by count(L.語彙素) desc
**演習2
select 語彙素,語彙素読み,
sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 ...
sum(case when 原文文字列 not like '%[ァ-ヴ]%' then 1 els...
count(*) as 粗頻度 from 短単位
where サブコーパス名 like '江戸' and 品詞='感動詞-一般'
group by 語彙素,語彙素読み
order by 語彙素読み
**演習3
select 語彙素,語彙素読み,品詞,語種,count(*) as 粗頻度,
count (case when サブコーパス名='奈良' then 1 else 0 end...
count (case when サブコーパス名='平安' then 1 else 0 end...
count (case when サブコーパス名='鎌倉' then 1 else 0 end...
count (case when サブコーパス名='室町' then 1 else 0 end...
count (case when サブコーパス名='江戸' then 1 else 0 end...
count (case when サブコーパス名='明治・大正' then 1 else...
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 ...
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.品詞 like '%助詞%' and s2.語彙素 = '恐れる’
order by s1.品詞,s1.語彙素
**演習5
select s1.語彙素,s1.語彙素読み,s1.品詞,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20) as ...
from 短単位as s1
inner join 短単位as s2 on s1.サンプルID=s2.サンプルID an...
where s2.語彙素 = '良い' and s2.活用形 like '連体形%'
and s1.品詞 like '名詞%' order by s1.品詞,s1.語彙素
**演習6
use chunagon_chj
select s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙素+...
count(*) as 頻度from 短単位 as s1
inner join 短単位as s2 on s1.サンプルID=s2.サンプルID an...
inner join 短単位as s3 on s2.サンプルID=s3.サンプルID an...
where s1.サブコーパス名='平安'and s1.品詞 like '助動詞%'...
group by s1.キー+s2.キー+s3.キー, s1.語彙素+'/'+s2.語彙...
order by count(*) desc
**演習7
select 品詞,
sum(case when 原文文字列 like '%[ァ-ヴ]%' then 1 else 0 ...
count(*) as 粗頻度,
CONVERT(float,sum(case when 原文文字列like '%[ァ-ヴ]%'th...
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.キ...
from 短単位語彙素 as L
inner join chunagon_chj.dbo.短単位 as S on L.語彙素ID=S....
where L.語彙素 like '秋%' and L.語彙素読み like 'アキ%'
group by L.語彙素,L.語彙素読み, S.サブコーパス名
order by S.サブコーパス名
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
