CHJBootCamp2018/DAY1b の履歴の現在との差分(No.1)

• 履歴一覧
• 差分 を表示
• ソース を表示
• 履歴 を表示
• CHJBootCamp2018/DAY1b へ行く。
  • 1 (2018-08-18 (土) 19:39:43)
  • 2 (2018-08-18 (土) 19:45:52)
  • 3 (2018-08-21 (火) 12:58:39)
  • 4 (2018-08-21 (火) 18:42:12)
  • 5 (2018-08-22 (水) 00:40:05)
  • 6 (2018-08-23 (木) 00:08:14)

• 追加された行はこの色です。
• 削除された行はこの色です。

#author("2023-06-05T06:05:06+00:00;2018-08-22T06:08:14+00:00","default:ogiwiki","ogiwiki")
[[CHJBootCamp2018]]

*CHJ Boot camp DAY 1
*SQL(0) 準備 [#xd63f6ae]

*関係データベースとSQL(1)
**VPN / SSMSセッティング [#p4d84155]
必要なもの
+VPN接続アカウント
--外来者用WiFi（kokken01またはeduroam）でネットワーク接続後、手順に従いVPN接続
+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）を利用する統合環境
+データベースアカウント
--参照権限のみ

-当面、既存のデータベースを使うことだけを説明します
*SQL(1) SELECT文の基本 [#fa027f20]

**関係データベース（リレーショナルデータベース：RDB）
**関係データベースとSQL [#zef387df]

-既存のデータベースを使うことだけを説明します

**関係データベース（リレーショナルデータベース：RDB） [#iea789dc]
-あらゆるデータを表の組み合わせで表現する
-データを取り出すのが速い、データが壊れない、同時に処理できる

-(参考)[[データベース基礎中の基礎>https://thinkit.co.jp/series/4725]]
--リレーショナルデータベースの基本 https://thinkit.co.jp/article/1042/1
-(参考)[[SQLこれだけ知っていれば大丈夫！>https://tech.nikkeibp.co.jp/it/article/COLUMN/20070209/261546/]]

***準備
-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）を利用する統合環境

-データベースアカウント（配付資料）
--参照権限のみ


***用語
***用語 [#l00c34c9]
-[[データベース管理システム（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||||

||列名1|列名2|列名3|…|
|レコード1|値|値|値||
|レコード2|値|値|値||
|CENTER:：|||||


-クエリ（問い合わせ）：データベースに対するさまざまな処理コマンド
-ビュー：（複数の）表の列を組み合わせて作る仮想的な表


-[[授業資料/表の正規化]]


**[[SQL>https://ja.wikipedia.org/wiki/SQL]]
**[[SQL>https://ja.wikipedia.org/wiki/SQL]]とは [#u036362e]
データベース[[問い合わせ言語>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文
***SQLの書式など（Microsoft SQL Server の場合） [#za7aeb1e]
-コマンドと記号は全て半角
-コマンドは大文字小文字の区別は（原則として）ない
--テーブル名や列名等は大文字小文字の区別あり（DBの設定による）
-コマンドの切れ目のスペースやタブは余計に入っていても、改行を入れてもいい
-文字列を値として指定するときは'値'のようにシングルクオーテーションで囲む
--Unicode文字として明示するときはN'値'とする
--値として'を入れたいときは''としてエスケープ
- --でコメントアウト（実行対象外になる） 
--行の途中からでも可
-/*　～　*/  で複数行コメントアウト

***CHJデータベースの利用 [#je78765a]
-利用するデータベースの選択
 use chunagon_chj

-テーブル
--短単位
--長単位
--書誌情報

**SELECT文 [#j4872c3f]
とりあえずこれだけ！覚える
-データベースからデータを選択して出力するコマンド
-SELECT：データベースの表からデータを選択して出力するコマンド
--SELECT文による出力も表形式

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

---※クエリ実行の取り消し Alt+Break

---短単位表から語彙素,語彙素読みの列を最初の100行出力
 SELECT TOP 100 語彙素,語彙素読み FROM 短単位


-WHERE条件句
-- SELECT 列名,列名2... FROM テーブル WHERE 列名='hoge'
---短単位表から品詞が接続詞であるものの語彙素,語彙素読みの列を出力
***WHERE条件句 [#sd944dfb]
- SELECT 列名,列名2... FROM テーブル WHERE 列名='hoge'
--短単位表から品詞が接続詞であるものの語彙素,語彙素読みの列を出力
 SELECT 語彙素,語彙素読み FROM 短単位 WHERE 品詞='接続詞'
--条件をAND、ORで複数指定できる
---ANDの方が優先順位が高い
---ORとANDを組み合わせるときは()で優先順位を示す

-LIKE 演算子とワイルドカード
--"LIKE"は"="のようなものだが、ワイルドカードを含む完全一致でない場合に使う
--- %：任意の文字列（文字なしでも可）
--- _：任意の一文字
***LIKE 演算子とワイルドカード [#u44bc503]
-"LIKE"は"="のようなものだが、ワイルドカードを含む完全一致でない場合に使う
-ワイルドカード((「中納言」で使えるものと同じ。正規表現とは異なる))
-- %：任意の文字列（文字なしでも可）
-- _：任意の一文字
-- [abc]：カッコ内のいずれかの文字（文字クラス）
-- [^abc]：カッコ内の文字以外（補集合）
-- [a-z]：文字コードの範囲で指定

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


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

*CHJ Boot camp DAY 2
-※ORDER BY句がないとデータベースは適当な順番で出力する！
--データ集合としてはいつも同じだが、ORDER BY句がないと並び順は保証されない


***集計
-グループ化 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) )


**受講者アンケート