CHJBootCamp2019/DAY1-1
をテンプレートにして作成
開始行:
[[https://sites.google.com/ogiso.net/chj-bootcamp/2019]]
*SQL(1) SELECT文の基本 [#qf3e8a5e]
**関係データベースとSQL [#u342ae7d]
-既存のデータベースを使うことだけを説明します
**関係データベース（リレーショナルデータベース：RDB） [#y...
-あらゆるデータを表の組み合わせで表現する
-データを取り出すのが速い、データが壊れない、同時に処理で...
-(参考)[[データベース基礎中の基礎>https://thinkit.co.jp/s...
--リレーショナルデータベースの基本 https://thinkit.co.jp/...
-(参考)[[SQLこれだけ知っていれば大丈夫！>https://tech.nik...
***用語 [#b408aab2]
-[[データベース管理システム（DBMS）>https://ja.wikipedia....
--[[''SQL Server''>https://ja.wikipedia.org/wiki/Microsof...
-データベースサーバー
-データベース
-表（テーブル）
--列（カラム）：テーブルの設計上決まっている（めったに増...
--レコード（行）：追加されどんどん増えることが多い
|>|>|>|CENTER:テーブル|h
||列名1|列名2|列名3|…|
|レコード1|値|値|値||
|レコード2|値|値|値||
|CENTER:：|||||
-クエリ（問い合わせ）：データベースに対するさまざまな処理...
-[[授業資料/表の正規化]]
**[[SQL>https://ja.wikipedia.org/wiki/SQL]]とは [#o68f411f]
データベース[[問い合わせ言語>https://ja.wikipedia.org/wik...
--SQLの基本 https://thinkit.co.jp/article/1046/1
***SQLの書式など（Microsoft SQL Server の場合） [#y91d9441]
-コマンドと記号は全て半角
-コマンドは大文字小文字の区別は（原則として）ない
--テーブル名や列名等は大文字小文字の区別あり（DBの設定に...
-コマンドの切れ目のスペースやタブは余計に入っていても、改...
-文字列を値として指定するときは'値'のようにシングルクオー...
--Unicode文字として明示するときはN'値'とする
--値として'を入れたいときは''としてエスケープ
- --でコメントアウト（実行対象外になる）
--行の途中からでも可
-/* ～ */ で複数行コメントアウト
***CHJデータベースの利用 [#wc03ce62]
-利用するデータベースの選択
use chunagon_chj
-テーブル
--短単位
--長単位
--書誌情報
**SELECT文 [#sc4a34b1]
とりあえずこれだけ！覚える
-SELECT：データベースの表からデータを選択して出力するコマ...
--SELECT文による出力も表形式
--SELECT 列名,列名2... FROM テーブル
---短単位表から語彙素,語彙素読みの列を（全部）出力
SELECT 語彙素,語彙素読み FROM 短単位
---※クエリ実行の取り消し Alt+Break
---短単位表から語彙素,語彙素読みの列を最初の100行出力
SELECT TOP 100 語彙素,語彙素読み FROM 短単位
***WHERE条件句 [#s74f3290]
- SELECT 列名,列名2... FROM テーブル WHERE 列名='hoge'
--短単位表から品詞が接続詞であるものの語彙素,語彙素読みの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'
--条件をAND、ORで複数指定できる
---ANDの方が優先順位が高い
---ORとANDを組み合わせるときは()で優先順位を示す
***LIKE 演算子とワイルドカード [#xeb1214c]
-"LIKE"は"="のようなものだが、ワイルドカードを含む完全一...
-ワイルドカード((「中納言」で使えるものと同じ。正規表現と...
-- %：任意の文字列（文字なしでも可）
-- _：任意の一文字
-- [abc]：カッコ内のいずれかの文字（文字クラス）
-- [^abc]：カッコ内の文字以外（補集合）
-- [a-z]：文字コードの範囲で指定
--短単位表から品詞が形容詞ではじまるものの語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形...
--短単位表から品詞が接続詞で語彙素読みがカ行で始まるもの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形...
***ORDER BY 句（並べ替え） [#zc0d5910]
-ORDER BY 列名 (DESC)((DESCを付けると逆順（降順）に出力))
--短単位表から品詞が接続詞であるものの語彙素,語彙素読みの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
-※ORDER BY句がないとデータベースは適当な順番で出力する！
--データ集合としてはいつも同じだが、ORDER BY句がないと並...
*SQL(2) グループ化、表の結合 [#q42c8b54]
**グループ化と集計 [#s760678b]
***グループ化： GROUP BY 句 [#ac226038]
- SELECT 列名,列名2... FROM テーブル GROUP BY 列名
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
***ORDER BY 句 (復習) [#w0f3a3e8]
- SELECT 列名,列名2... FROM テーブル GROUP BY 列名 ORDER ...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
***COUNT()：集計関数 [#t441e7bd]
-集計した数を返す
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
**表の結合 [#f57e729c]
-内部結合 INNER JOIN
--結合条件に一致するレコードだけを表示
-外部結合 LEFT (OUTER) JOIN
--軸にする左のテーブルのレコード全てと、結合条件に一致す...
***書誌情報テーブルと結合 [#n6f07946]
-短単位・長単位テーブルとはサンプルIDで結合する
--必ずコーパスに対応する書誌情報がある（結合できないこと...
select top 100 *
from 短単位 as S inner join 書誌情報 as B on S.サンプルI...
***短単位・長単位テーブルを結合（外部結合） [#se5cf1ef]
-短単位と長単位の結合にはサンプルIDと文字開始位置を使う（...
--対応する長単位がない場合（長単位の途中にある短単位）、N...
select top 100 *
from 短単位 as S left join 長単位 as L on S.サンプルID=L...
***応用：構成要素として助動詞を含む長単位名詞 [#r196e727]
select s.サンプルID,s.連番,s.語彙素,l.キー,l.語彙素 from...
inner join 短単位 as s
on s.サンプルID=l.サンプルID
and s._文字開始位置>=l._文字開始位置
and s._文字開始位置<l._文字終了位置
where s.サブコーパス名 like '平安%'
and s.品詞 like '助動詞%' and l.品詞 like '名詞%'
***【補足】 [#p57a4fd3]
- select ''*'' from でテーブルの全ての列をもってくる
- select count(*) ''as 名前'' from テーブル ''as X'' の...
--長いテーブル名や、集計結果などの名無しの列で使う
-[[データベースのNULLは、非常に特殊な値>https://ja.wikipe...
--普通には比較できないので、[[IS NULL>https://docs.micros...
**表の結合と集計 [#eb277cb2]
-書誌情報テーブルと結合して作品別の語数を集計
select 作品名, count(*) as 総語数
from 短単位 as S inner join 書誌情報 as B on S.サンプルI...
where S.サブコーパス名 like '平安%'
group by 作品名
*SQL(3) コーパスデータの扱い [#w070fb03]
**関数のいろいろ [#y53adf0b]
***組み込み関数 [#d1e39267]
-https://msdn.microsoft.com/ja-jp/library/ms174318(v=sql....
***[[文字列関数>https://msdn.microsoft.com/ja-jp/library/...
-Excelで使ったのと同じようなものが使えるが名前が違うものも
--ExcelのSEARCH ＝ charindex,patindex
--ExcelのMID ＝ substring など
***KWIC生成関数（ユーザー定義関数） [#cedbd7b3]
-KWIC生成
--fn前文脈(サンプルID,出現書字形開始位置,長さ)
--fn後文脈(サンプルID,出現書字形開始位置,長さ)
select top 100 dbo.fn前文脈(サンプルID,出現書字形開始位...
select top 100 サンプルID,dbo.fn前文脈(サンプルID,出現書...
where 語彙素 like '鰻'
**BCCWJデータを使うには [#l5d974e2]
use chunagon_bccwj
-CHJとほとんど同じデータ構造。サイズが大きいことに注意
-サブコーパス名でなくBCCWJは「レジスター」((BCCWJにおける...
------
**CASE式をつかった集計 [#rb5b6824]
-CASE WHEN ● THEN ○○ WHEN ■ THEN □□ ELSE △ END
select 作品名, count(*) as 総語数,sum(CASE WHEN 本文種...
from 短単位 as S inner join 書誌情報 as B on S.サンプル...
where S.サブコーパス名 like '平安%'
group by 作品名
--※本文種別でグループ化して出力した表をExcelのピボットテ...
**連続する語の取り出し [#jc14f554]
-同一の表を一語ずつずらしながら結合する。コーパスデータの...
--（前方後方共起による検索条件指定ができる）
select top 100 s1.語彙素,s1.語彙素読み,s1.品詞,dbo.fn前...
from 短単位 as s1 inner join 短単位 as s2 on s1.サンプル...
where s2.語彙素 like '鰻'
-【追補】「心」の一つ前に来る形容詞連体形の用例
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.品詞 like '形容詞%' and s1.活用形 like '連体形%...
***N-gram [#n541ec6d]
-語彙素6グラム
select top 100
s1.キー+s2.キー+s3.キー+s4.キー+s5.キー+s6.キー,
s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID ...
inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID ...
inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID ...
where s1.サブコーパス名 like '平安%'
order by s1.サンプルID, s1.連番
-【追補】語彙素6グラムの集計(多いもの順、記号等を含まない)
select top 100
s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID ...
inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID ...
inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID ...
where s1.サブコーパス名 like '明治・大正%'
and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not ...
and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not ...
group by s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語...
order by count(*) desc
***【追補】N語以内の指定 [#q7093180]
-「食う」の前方5語以内に来る 名詞＋を
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
where s1.品詞 like '名詞%' and s2.語彙素 = 'を' and s3....
**文数・文節数・語数 [#if3b6eb4]
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.サンプルI...
left join 長単位 as L on S.サンプルID = L.サンプルID and...
where B.サブコーパス名 like '平安%'
group by B.作品名--,S.本文種別
**文番号の利用 [#s8b7da7c]
-文番号の列 _文番号 を使うことで同一文中の共起語などを簡...
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,30),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.語彙素 like '秋' and s2.語彙素 like '夕%'
order by s1.サンプルID,s1.出現書字形開始位置
**サブクエリ [#i31e88bb]
括弧でくくったSELECT文に名前を付けて、あたかもテーブルで...
-異なり語数
select x.サブコーパス名, count(*) from
(select サブコーパス名, count(*) as 語数 from 短単位 wh...
group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名
-TTR（Type Token Ratio）
select x.サブコーパス名, count(*) as 異なり語数, sum(語...
CONVERT(float,count(*))/sum(語数) as TTR from
(select サブコーパス名, count(*) as 語数 from 短単位 wh...
group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名
**一時テーブルの作成 [#y506ba20]
-#で始まるテーブル名は、DB切断後削除される一時テーブルと...
-複雑なサブクエリを書くより、一時テーブルに書き出した方が...
--SELECT カラム INTO 新しい書き込み先のテーブル FROM テー...
---権限がないのでエラー
select * into 鰻テーブル from 短単位 where 語彙素='鰻'
---OK
select * into #鰻テーブル from 短単位 where 語彙素='鰻'
select * from #鰻テーブル
--drop table：テーブルの削除（一時テーブル以外は権限上削...
drop table #鰻テーブル
***応用例：記号を含まない短単位テーブルを作る [#uf6a107e]
select ROW_NUMBER() OVER (PARTITION BY サンプルID ORDER ...
into #記号なし短単位
from 短単位 where サブコーパス名 like '平安%'
and 品詞 not like '%記号%' and 品詞 != '空白'
select * from #記号なし短単位 ORDER BY サンプルID,新連番
**（参考）辞書データベース（unidicSQL） [#w8573fd8]
***辞書の階層化された見出し表を扱う [#zb72eb32]
use unidicSQL
-語彙素
--語形
---書字形
--語彙素を出力
select * from 短単位語彙素 where 語彙素 like '夏%' and ...
--語彙素の全ての語形,入力活用型を出力
SELECT 語形,入力活用型 from 短単位語形 as F inner join ...
where 語彙素 like '見る' and 語彙素読み like 'ミル'
--語彙素の全ての書字形を出力
SELECT 語形,書字形 from 短単位書字形 as O inner join 短...
where 語彙素 like '見る' and 語彙素読み like 'ミル'
***コーパスと辞書を繋ぐ [#wa27e7a3]
-語彙素ID, 語形ID, 書字形ID
use chunagon_bccwj
select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単...
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター
-上と同じものを外部結合
use chunagon_bccwj
select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単...
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター
終了行:
[[https://sites.google.com/ogiso.net/chj-bootcamp/2019]]
*SQL(1) SELECT文の基本 [#qf3e8a5e]
**関係データベースとSQL [#u342ae7d]
-既存のデータベースを使うことだけを説明します
**関係データベース（リレーショナルデータベース：RDB） [#y...
-あらゆるデータを表の組み合わせで表現する
-データを取り出すのが速い、データが壊れない、同時に処理で...
-(参考)[[データベース基礎中の基礎>https://thinkit.co.jp/s...
--リレーショナルデータベースの基本 https://thinkit.co.jp/...
-(参考)[[SQLこれだけ知っていれば大丈夫！>https://tech.nik...
***用語 [#b408aab2]
-[[データベース管理システム（DBMS）>https://ja.wikipedia....
--[[''SQL Server''>https://ja.wikipedia.org/wiki/Microsof...
-データベースサーバー
-データベース
-表（テーブル）
--列（カラム）：テーブルの設計上決まっている（めったに増...
--レコード（行）：追加されどんどん増えることが多い
|>|>|>|CENTER:テーブル|h
||列名1|列名2|列名3|…|
|レコード1|値|値|値||
|レコード2|値|値|値||
|CENTER:：|||||
-クエリ（問い合わせ）：データベースに対するさまざまな処理...
-[[授業資料/表の正規化]]
**[[SQL>https://ja.wikipedia.org/wiki/SQL]]とは [#o68f411f]
データベース[[問い合わせ言語>https://ja.wikipedia.org/wik...
--SQLの基本 https://thinkit.co.jp/article/1046/1
***SQLの書式など（Microsoft SQL Server の場合） [#y91d9441]
-コマンドと記号は全て半角
-コマンドは大文字小文字の区別は（原則として）ない
--テーブル名や列名等は大文字小文字の区別あり（DBの設定に...
-コマンドの切れ目のスペースやタブは余計に入っていても、改...
-文字列を値として指定するときは'値'のようにシングルクオー...
--Unicode文字として明示するときはN'値'とする
--値として'を入れたいときは''としてエスケープ
- --でコメントアウト（実行対象外になる）
--行の途中からでも可
-/* ～ */ で複数行コメントアウト
***CHJデータベースの利用 [#wc03ce62]
-利用するデータベースの選択
use chunagon_chj
-テーブル
--短単位
--長単位
--書誌情報
**SELECT文 [#sc4a34b1]
とりあえずこれだけ！覚える
-SELECT：データベースの表からデータを選択して出力するコマ...
--SELECT文による出力も表形式
--SELECT 列名,列名2... FROM テーブル
---短単位表から語彙素,語彙素読みの列を（全部）出力
SELECT 語彙素,語彙素読み FROM 短単位
---※クエリ実行の取り消し Alt+Break
---短単位表から語彙素,語彙素読みの列を最初の100行出力
SELECT TOP 100 語彙素,語彙素読み FROM 短単位
***WHERE条件句 [#s74f3290]
- SELECT 列名,列名2... FROM テーブル WHERE 列名='hoge'
--短単位表から品詞が接続詞であるものの語彙素,語彙素読みの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'
--条件をAND、ORで複数指定できる
---ANDの方が優先順位が高い
---ORとANDを組み合わせるときは()で優先順位を示す
***LIKE 演算子とワイルドカード [#xeb1214c]
-"LIKE"は"="のようなものだが、ワイルドカードを含む完全一...
-ワイルドカード((「中納言」で使えるものと同じ。正規表現と...
-- %：任意の文字列（文字なしでも可）
-- _：任意の一文字
-- [abc]：カッコ内のいずれかの文字（文字クラス）
-- [^abc]：カッコ内の文字以外（補集合）
-- [a-z]：文字コードの範囲で指定
--短単位表から品詞が形容詞ではじまるものの語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形...
--短単位表から品詞が接続詞で語彙素読みがカ行で始まるもの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形...
***ORDER BY 句（並べ替え） [#zc0d5910]
-ORDER BY 列名 (DESC)((DESCを付けると逆順（降順）に出力))
--短単位表から品詞が接続詞であるものの語彙素,語彙素読みの...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
-※ORDER BY句がないとデータベースは適当な順番で出力する！
--データ集合としてはいつも同じだが、ORDER BY句がないと並...
*SQL(2) グループ化、表の結合 [#q42c8b54]
**グループ化と集計 [#s760678b]
***グループ化： GROUP BY 句 [#ac226038]
- SELECT 列名,列名2... FROM テーブル GROUP BY 列名
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
***ORDER BY 句 (復習) [#w0f3a3e8]
- SELECT 列名,列名2... FROM テーブル GROUP BY 列名 ORDER ...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'...
***COUNT()：集計関数 [#t441e7bd]
-集計した数を返す
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
--短単位表から品詞が接続詞であるものを同じ語彙素,語彙素読...
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE...
**表の結合 [#f57e729c]
-内部結合 INNER JOIN
--結合条件に一致するレコードだけを表示
-外部結合 LEFT (OUTER) JOIN
--軸にする左のテーブルのレコード全てと、結合条件に一致す...
***書誌情報テーブルと結合 [#n6f07946]
-短単位・長単位テーブルとはサンプルIDで結合する
--必ずコーパスに対応する書誌情報がある（結合できないこと...
select top 100 *
from 短単位 as S inner join 書誌情報 as B on S.サンプルI...
***短単位・長単位テーブルを結合（外部結合） [#se5cf1ef]
-短単位と長単位の結合にはサンプルIDと文字開始位置を使う（...
--対応する長単位がない場合（長単位の途中にある短単位）、N...
select top 100 *
from 短単位 as S left join 長単位 as L on S.サンプルID=L...
***応用：構成要素として助動詞を含む長単位名詞 [#r196e727]
select s.サンプルID,s.連番,s.語彙素,l.キー,l.語彙素 from...
inner join 短単位 as s
on s.サンプルID=l.サンプルID
and s._文字開始位置>=l._文字開始位置
and s._文字開始位置<l._文字終了位置
where s.サブコーパス名 like '平安%'
and s.品詞 like '助動詞%' and l.品詞 like '名詞%'
***【補足】 [#p57a4fd3]
- select ''*'' from でテーブルの全ての列をもってくる
- select count(*) ''as 名前'' from テーブル ''as X'' の...
--長いテーブル名や、集計結果などの名無しの列で使う
-[[データベースのNULLは、非常に特殊な値>https://ja.wikipe...
--普通には比較できないので、[[IS NULL>https://docs.micros...
**表の結合と集計 [#eb277cb2]
-書誌情報テーブルと結合して作品別の語数を集計
select 作品名, count(*) as 総語数
from 短単位 as S inner join 書誌情報 as B on S.サンプルI...
where S.サブコーパス名 like '平安%'
group by 作品名
*SQL(3) コーパスデータの扱い [#w070fb03]
**関数のいろいろ [#y53adf0b]
***組み込み関数 [#d1e39267]
-https://msdn.microsoft.com/ja-jp/library/ms174318(v=sql....
***[[文字列関数>https://msdn.microsoft.com/ja-jp/library/...
-Excelで使ったのと同じようなものが使えるが名前が違うものも
--ExcelのSEARCH ＝ charindex,patindex
--ExcelのMID ＝ substring など
***KWIC生成関数（ユーザー定義関数） [#cedbd7b3]
-KWIC生成
--fn前文脈(サンプルID,出現書字形開始位置,長さ)
--fn後文脈(サンプルID,出現書字形開始位置,長さ)
select top 100 dbo.fn前文脈(サンプルID,出現書字形開始位...
select top 100 サンプルID,dbo.fn前文脈(サンプルID,出現書...
where 語彙素 like '鰻'
**BCCWJデータを使うには [#l5d974e2]
use chunagon_bccwj
-CHJとほとんど同じデータ構造。サイズが大きいことに注意
-サブコーパス名でなくBCCWJは「レジスター」((BCCWJにおける...
------
**CASE式をつかった集計 [#rb5b6824]
-CASE WHEN ● THEN ○○ WHEN ■ THEN □□ ELSE △ END
select 作品名, count(*) as 総語数,sum(CASE WHEN 本文種...
from 短単位 as S inner join 書誌情報 as B on S.サンプル...
where S.サブコーパス名 like '平安%'
group by 作品名
--※本文種別でグループ化して出力した表をExcelのピボットテ...
**連続する語の取り出し [#jc14f554]
-同一の表を一語ずつずらしながら結合する。コーパスデータの...
--（前方後方共起による検索条件指定ができる）
select top 100 s1.語彙素,s1.語彙素読み,s1.品詞,dbo.fn前...
from 短単位 as s1 inner join 短単位 as s2 on s1.サンプル...
where s2.語彙素 like '鰻'
-【追補】「心」の一つ前に来る形容詞連体形の用例
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.品詞 like '形容詞%' and s1.活用形 like '連体形%...
***N-gram [#n541ec6d]
-語彙素6グラム
select top 100
s1.キー+s2.キー+s3.キー+s4.キー+s5.キー+s6.キー,
s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID ...
inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID ...
inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID ...
where s1.サブコーパス名 like '平安%'
order by s1.サンプルID, s1.連番
-【追補】語彙素6グラムの集計(多いもの順、記号等を含まない)
select top 100
s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+...
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID ...
inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID ...
inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID ...
where s1.サブコーパス名 like '明治・大正%'
and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not ...
and s1.品詞+s2.品詞+s3.品詞+s4.品詞+s5.品詞+s6.品詞 not ...
group by s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語...
order by count(*) desc
***【追補】N語以内の指定 [#q7093180]
-「食う」の前方5語以内に来る 名詞＋を
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID ...
inner join 短単位 as s3 on s2.サンプルID=s3.サンプルID ...
where s1.品詞 like '名詞%' and s2.語彙素 = 'を' and s3....
**文数・文節数・語数 [#if3b6eb4]
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.サンプルI...
left join 長単位 as L on S.サンプルID = L.サンプルID and...
where B.サブコーパス名 like '平安%'
group by B.作品名--,S.本文種別
**文番号の利用 [#s8b7da7c]
-文番号の列 _文番号 を使うことで同一文中の共起語などを簡...
select s1.サンプルID,s1.出現書字形開始位置,
dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,30),s1....
from 短単位 as s1
inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID a...
where s1.語彙素 like '秋' and s2.語彙素 like '夕%'
order by s1.サンプルID,s1.出現書字形開始位置
**サブクエリ [#i31e88bb]
括弧でくくったSELECT文に名前を付けて、あたかもテーブルで...
-異なり語数
select x.サブコーパス名, count(*) from
(select サブコーパス名, count(*) as 語数 from 短単位 wh...
group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名
-TTR（Type Token Ratio）
select x.サブコーパス名, count(*) as 異なり語数, sum(語...
CONVERT(float,count(*))/sum(語数) as TTR from
(select サブコーパス名, count(*) as 語数 from 短単位 wh...
group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名
**一時テーブルの作成 [#y506ba20]
-#で始まるテーブル名は、DB切断後削除される一時テーブルと...
-複雑なサブクエリを書くより、一時テーブルに書き出した方が...
--SELECT カラム INTO 新しい書き込み先のテーブル FROM テー...
---権限がないのでエラー
select * into 鰻テーブル from 短単位 where 語彙素='鰻'
---OK
select * into #鰻テーブル from 短単位 where 語彙素='鰻'
select * from #鰻テーブル
--drop table：テーブルの削除（一時テーブル以外は権限上削...
drop table #鰻テーブル
***応用例：記号を含まない短単位テーブルを作る [#uf6a107e]
select ROW_NUMBER() OVER (PARTITION BY サンプルID ORDER ...
into #記号なし短単位
from 短単位 where サブコーパス名 like '平安%'
and 品詞 not like '%記号%' and 品詞 != '空白'
select * from #記号なし短単位 ORDER BY サンプルID,新連番
**（参考）辞書データベース（unidicSQL） [#w8573fd8]
***辞書の階層化された見出し表を扱う [#zb72eb32]
use unidicSQL
-語彙素
--語形
---書字形
--語彙素を出力
select * from 短単位語彙素 where 語彙素 like '夏%' and ...
--語彙素の全ての語形,入力活用型を出力
SELECT 語形,入力活用型 from 短単位語形 as F inner join ...
where 語彙素 like '見る' and 語彙素読み like 'ミル'
--語彙素の全ての書字形を出力
SELECT 語形,書字形 from 短単位書字形 as O inner join 短...
where 語彙素 like '見る' and 語彙素読み like 'ミル'
***コーパスと辞書を繋ぐ [#wa27e7a3]
-語彙素ID, 語形ID, 書字形ID
use chunagon_bccwj
select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単...
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター
-上と同じものを外部結合
use chunagon_bccwj
select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単...
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター
ページ名:
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
