CHJBootCamp2018/DAY2 の履歴(No.1)

SELECT 列名,列名2... FROM テーブル
- 短単位表から語彙素,語彙素読みの列を（全部）出力
```
SELECT 語彙素,語彙素読み FROM 短単位
```
- 短単位表から語彙素,語彙素読みの列を最初の100行出力
```
SELECT TOP 100 語彙素,語彙素読み FROM 短単位
```

WHERE条件句
- SELECT 列名,列名2... FROM テーブル WHERE 列名='hoge'
  - 短単位表から品詞が接続詞であるものの語彙素,語彙素読みの列を出力
```
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'
```

LIKE 演算子とワイルドカード
- "LIKE"は"="のようなものだが、ワイルドカードを含む完全一致でない場合に使う
  - %：任意の文字列（文字なしでも可）
  - _：任意の一文字

短単位表から品詞が形容詞ではじまるものの語彙素,語彙素読みの列を出力
```
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形容詞%'
```
短単位表から品詞が接続詞で語彙素読みがカ行で始まるものの語彙素,語彙素読みの列を出力
```
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞 LIKE '形容詞%' AND 語彙素読み LIKE '[カ-コ]%'
```

ORDER BY 句（並べ替え）
- ORDER BY 列名 (DESC)
  - 短単位表から品詞が接続詞であるものの語彙素,語彙素読みの列を語彙素読み順に出力
```
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞' ORDER BY 語彙素読み
```

↑

CHJ Boot camp DAY 2

↑

集計

グループ化 GROUP BY
- SELECT 列名,列名2... FROM テーブル GROUP BY 列名
  - 短単位表から品詞が接続詞であるもののを同じ語彙素,語彙素読みのものを一行にまとめて語彙素,語彙素読みの列を出力
```
SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み
```

count 集計関数
- 集計した数を返す
  - 短単位表から品詞が接続詞であるもののを同じ語彙素,語彙素読みのものを一行にまとめて語彙素,語彙素読みの列と集計した語数を出力
```
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み
```
  - 短単位表から品詞が接続詞であるもののを同じ語彙素,語彙素読みのものを一行にまとめて語彙素,語彙素読みの列と集計した語数を語彙素読み（アイウエオ）順で出力
```
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み ORDER BY 語彙素読み
```
  - 短単位表から品詞が接続詞であるもののを同じ語彙素,語彙素読みのものを一行にまとめて語彙素,語彙素読みの列と集計した語数を語数の多い順で出力
```
SELECT 語彙素,語彙素読み,count(語彙素) FROM 短単位 WHERE 品詞='接続詞' GROUP BY 語彙素,語彙素読み ORDER BY count(語彙素) DESC
```

↑

SQLの書式など（SQL Server）

コマンドと記号は全て半角
コマンドは大文字小文字の区別は（原則として）ない
コマンドの切れ目のスペースやタブは余計に入っていて、改行を入れてもいい
文字列を値として指定するときは'値'のようにシングルクオーテーションで囲む
- 値として'を入れたいときは''としてエスケープ
--でコメントアウト（実行対象外になる）。行の途中からでも可
- /*　～　*/ で複数行コメントアウト

↑

表の結合

（参考）SQL素人でも分かるテーブル結合(inner joinとouter join) https://qiita.com/naoki_mochizuki/items/3fda1ad6594c11d7b43c

内部結合 INNER JOIN
外部結合 LEFT (OUTER) JOIN

↑

関係データベースとSQL(2)

↑

BCCWJデータ

use chunagon_bccwj

↑

関数のいろいろ

↑

組み込み関数

https://msdn.microsoft.com/ja-jp/library/ms174318(v=sql.120).aspx

↑

ユーザー定義関数

KWIC生成
- fn前文脈(サンプルID,出現書字形開始位置,長さ)
- fn後文脈(サンプルID,出現書字形開始位置,長さ)

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 '鰻'

↑

コーパスデータを使った実習

↑

補足

select * from でテーブルの全ての列をもってくる
select count(*) as 名前 from　のように名前を付けられる

↑

（復習）表の結合

 select 作品名, count(*) as 総語数
 from 短単位 as S inner join 書誌情報 as B on S.サンプルID=B.サンプルID
 where S.サブコーパス名 like '平安'
 group by 作品名

↑

CASE式

CASE WHEN ● THEN ○○ WHEN ■ THEN □□ ELSE △ END

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

↑

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

use unidicSQL

語彙素
- 語形
  - 書字形

語彙素を出力

select * from 短単位語彙素 where 語彙素 like '夏%' and 語彙素読み like 'ナツ%'

語彙素の全ての語形,入力活用型を出力

SELECT 語形,入力活用型 from 短単位語形 as F inner join 短単位語彙素 as L on L.語彙素ID=F.語彙素ID 
where 語彙素 like '見る' and 語彙素読み like 'ミル'

語彙素の全ての書字形を出力

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 'ミル'

↑

コーパスと辞書を繋ぐ

語彙素ID, 語形ID, 書字形ID

select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単位語彙素 as L inner join chunagon_bccwj.dbo.短単位 as SUW on L.語彙素ID=SUW.語彙素ID
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター

↑

表の結合（復習）

内部結合 INNER JOIN
外部結合 LEFT (OUTER) JOIN

上と同じものを外部結合

select L.語彙素,SUW.レジスター,count(SUW.キー) from 短単位語彙素 as L left join chunagon_bccwj.dbo.短単位 as SUW on L.語彙素ID=SUW.語彙素ID
where L.語彙素 like '夏%' and L.語彙素読み like 'ナツ%'
group by L.語彙素, SUW.レジスター

↑

連続する語の取り出し

同一の表を一語ずつずらしながら結合する（前方後方共起による検索条件指定ができる）

select top 100 s1.語彙素,s1.語彙素読み,s1.品詞,dbo.fn前文脈(s1.サンプルID,s1.出現書字形開始位置,20),s1.キー,dbo.fn後文脈(s1.サンプルID,s1.出現書字形開始位置,20)
from 短単位 as s1 inner join 短単位 as s2 on s1.サンプルID=s2.サンプルID and s1.連番 + 10 = s2.連番
where s2.語彙素 like '鰻'

N-gram

select top 100
s1.キー+s2.キー+s3.キー+s4.キー+s5.キー+s6.キー,
s1.語彙素+'/'+s2.語彙素+'/'+s3.語彙素+'/'+s4.語彙素+'/'+s5.語彙素+'/'+s6.語彙素
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.連番
 inner join 短単位 as s4 on s3.サンプルID=s4.サンプルID and s3.連番 + 10 = s4.連番
 inner join 短単位 as s5 on s4.サンプルID=s5.サンプルID and s4.連番 + 10 = s5.連番
 inner join 短単位 as s6 on s5.サンプルID=s6.サンプルID and s5.連番 + 10 = s6.連番
where s1.サブコーパス名='平安' 
order by s1.サンプルID, s1.連番

↑

サブクエリ

括弧でくくったSELECT文に名前を付けて、あたかもテーブルであるかのように扱える

異なり語数

select x.サブコーパス名, count(*) from
 (select サブコーパス名, count(*) as 語数 from 短単位 where 品詞 not like '記号%'
  group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名

TTR（Type Token Ratio）

use chunagon
select x.サブコーパス名, count(*) as 異なり語数, sum(語数) as 延べ語数, 
CONVERT(float,count(*))/sum(語数) as TTR from
 (select サブコーパス名, count(*) as 語数 from 短単位 where 品詞 not like '記号%'
  group by 語彙素ID,サブコーパス名) as x
group by x.サブコーパス名

↑

一時テーブルの作成

#で始まるテーブル名は、DB切断後削除される一時テーブルとなる。現在の権限でも一時テーブルは作成できる。

SELECT カラム INTO 新しい書き込み先のテーブル FROM テーブル WHERE条件句

select * into 鰻テーブル from 短単位 where 語彙素='鰻'

CREATE TABLE テーブル名 ( 列名データ型 , 列名2 データ型 )

create table #ogiso_tmp ( ID int ,調査語彙素 varchar(10) ,調査語彙素読み varchar(10) )

↑