*CHJ Boot camp DAY 1 *関係データベースとSQL(1) -当面、既存のデータベースを使うことだけを説明します **関係データベース(リレーショナルデータベース:RDB) -あらゆるデータを表の組み合わせで表現する -データを取り出すのが速い、データが壊れない、同時に処理できる -(参考)[[データベース基礎中の基礎>https://thinkit.co.jp/series/4725]] --リレーショナルデータベースの基本 https://thinkit.co.jp/article/1042/1 ***準備 -Microsoft SQL Server Management Studio(SSMS)のインストール --https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017 --データベース(SQL Server)を利用する統合環境 -データベースアカウント(配付資料) --参照権限のみ ***用語 -[[データベース管理システム(DBMS)>https://ja.wikipedia.org/wiki/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E7%AE%A1%E7%90%86%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0]] --[[''SQL Server''>https://ja.wikipedia.org/wiki/Microsoft_SQL_Server]], MySQL, PostgreSQL, Oracle, etc. -データベースサーバー -データベースインスタンス -表(テーブル) --列(カラム) --レコード(行) |>|>|>|CENTER:テーブル|h ||列名1|列名2|列名3| |レコード1|||| |レコード2|||| -クエリ(問い合わせ):データベースに対するさまざまな処理コマンド -ビュー:(複数の)表の列を組み合わせて作る仮想的な表 -[[授業資料/表の正規化]] **[[SQL>https://ja.wikipedia.org/wiki/SQL]] データベース[[問い合わせ言語>https://ja.wikipedia.org/wiki/%E5%95%8F%E3%81%84%E5%90%88%E3%82%8F%E3%81%9B%E8%A8%80%E8%AA%9E]] --SQLの基本 https://thinkit.co.jp/article/1046/1 **SELECT文 とりあえずこれだけ!覚える -データベースからデータを選択して出力するコマンド --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) ) **受講者アンケート