CHJBootCamp2018

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

グループ化と集計

グループ化: GROUP BY 句

ORDER BY 句 (復習)

COUNT():集計関数

表の結合

書誌情報テーブルと結合

短単位・長単位テーブルを結合(外部結合)

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

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 '名詞%'

【補足】

表の結合と集計

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

関数のいろいろ

組み込み関数

文字列関数

KWIC生成関数(ユーザー定義関数)

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

CASE式

 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 作品名

連続する語の取り出し

文数・文節数・語数

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文に名前を付けて、あたかもテーブルであるかのように扱える

辞書データベース(unidicSQL)

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

use unidicSQL

コーパスと辞書を繋ぐ

一時テーブルの作成

応用例:文番号入りの短単位テーブルを作る

drop table  #短単位文番号

--文頭(文境界=B)の表(仮テーブル#文番号tmp)を連番入りで作る
select ROW_NUMBER() over(order by サンプルID,連番) as 文番号,* into #文番号tmp from 短単位
where サブコーパス名='奈良' and 文境界='B'

--上のテーブルを一つずらして結合して、文の開始連番と次の文の開始連番の入った表(#短単位文番号)を作る
select a.サンプルID,a.文番号,a.連番 as 文開始連番,b.連番 as 次文開始連番 into #短単位文番号
 from #文番号tmp as a inner join #文番号tmp as b on a.文番号+1=b.文番号
 order by a.サンプルID,a.連番

--最初の仮テーブルを消す
drop table  #文番号tmp

--#短単位文番号テーブルに最後の一文の情報を追加(次文開始連番は最後の語の連番)
insert into #短単位文番号 (サンプルID,文番号,文開始連番,次文開始連番) values ('10-万葉0759_00020',4809,45380,45520) 

--短単位テーブルを#短単位文番号テーブルと結合
select 文番号,s.* from 短単位 as s
inner join #短単位文番号 as x on s.サンプルID =x.サンプルID and s.連番>=x.文開始連番 and s.連番<x.次文開始連番
order by s.サンプルID,s.連番

質疑・相談会

受講者アンケート


*1 BCCWJにおけるサブコーパスは、出版/図書館/特定目的の別。

トップ   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS