SQL インデックス

基本はB-tree(B+tree)インデックスが利用されることが多い。

特徴としては、ノードにキーを持ち、リーフノードにポインタがある。作成時はキーをソートする。
リーフノードまでの距離が均一でバランスがいいのが特徴。

インデックスが必要な箇所

大規模(数万以上)なテーブル。規模が小さいとフルスキャンと大差ない。

カーディナリティ(入る値の種類数のこと)が高い列。さらに入る値が集中していないこと。

検索条件や結合条件の列以外は無意味。(検索条件内でもインデックスの列で演算などするとダメ)注意点として、否定形(<>)やORやLIKE演算子はダメで、暗黙の型変換もダメ。

主キーは既にインデックスされているので不要。

統計基礎 標準偏差、相関係数、線形回帰

標準偏差、相関係数、線形回帰の求め方。
手動計算とエクセル関数を使った場合。

分析基礎

・基本

結論は金額換算して分かりやすく。継続観察していくならKPIを設定する。

・サンプルの中で変数に属性を追加。

例)
来店率が下がっている顧客、上がっている顧客、特定の時期に上がる顧客等。
購入金額が高い顧客(Aランク)、低い顧客(Bランク)など。

これにより、顧客のニーズの仮説や、分析から排除の必要性等を検討する。

・次に他のサンプルと比較。

サンプル1(会員等)での特徴(標準偏差や中央値等)がサンプル2(非会員等)と比較してどう違うか。そうすることで、サンプル1はサンプル2と比べてはどのような特徴があるのか分かる。

・複数のサンプルでの比較

会員と商品の関係での特徴は、非会員と商品の関係と比較してどう違うか。
会員は特定商品のリピート率が高い等。

SQLで相関係数(傾き)

標準偏差や分散などは標準の関数であるが、相関係数はない。
集計用に傾きだけ欲しかったので、とりあえずSQLで求めた

テーブル名:t
結果:17.2482570475901

傾きで利用したストアド

SQl Server Management Studioでの結果をそのままエクセルに貼り付けたかったので、
以下のような書き方で解決した。

SQL DB正規化

直ぐ忘れてしまう。

先ず、一方の値が決まると他方も決まるものを、関数従属性という。

第一正規化

一つのセルに複数のデータを持たない。

第二正規化

部分関数従属を取り除く。
部分関数従属とは、主キーの一部だけで一意に決まる列のこと。

主キーが複数あり、その内の一つだけで値が決まるものを別のテーブルにする。

第三正規化

推移的関数従属を取り除く。

推移的関数従属とは、テーブル内の段階的な関数従属のこと。
つまり、主キー以外と関数従属があること。

主キー以外の項目で、他の列の値を一意に決めるものを別テーブルにする。

例えば、(主)社員ID → 部署コード → 部署名

部署コードが決まれば部署名が決まる。当然主である社員IDと部署コードは関数従属の関係なので、
段階的な関数従属があるといえる。

部分関数従属、推移的関数従属を取り除くことの必要性。

あるテーブルの中で推移的関数従属が残っていると、間違った登録が可能。(会社コードと会社名)
また、主キーが不明の段階で登録できない。

キー| メンバー名 | 会社コード  | 会社名
1  | 太郎     | 0000   | AAA
2  | 次郎     | 0001   | AAA

テーブルの関連

1対1、1対多、多対多があるが、通常1対多のみ使う。

多対多となる場合、関連実体というテーブルを作る。ただ、通常の業務要件では、
マスタとマスタを直接関連付けることはないので、普通に考えると多対多とはならない。

ストアドプロシージャ

SQLServer

・定義

create procedure ストアドプロシージャ名
@パラメータ名 型
as
begin
処理
end

・変数

宣言
declare @変数名 型

データ型
varchar:文字列
int:数値
decimal:小数
date:日付

代入
set 変数 = 値

・カーソル
1行ずつループして処理する方法。

カーソルの宣言
declare カーソル名 cursor for (select 文)

カーソルを開く
open カーソル名

データの取得
fetch next from カーソル名 into 変数リスト

ループ処理の実行

while 条件式
begin
処理内容 where current of カーソル名
end

※ループの定番
while @@fetch_status = 0

カーソルを閉じる
close カーソル名

カーソルの開放
deallocate カーソル名

MySQL

CREATE ROUTINEの権限が必要。

・基本
create procedure sample01()
select now();
mysql>call sample01;

・削除
mysql>drop procedure sample01;

・一覧
show procedure status;

・中身
show create procedure sample01;

・引数をとる
create procedure sample01(in a int, in b int)
select a + b;
mysql>call sample01(10,5);

・返り値
create procedure sample01(out x int)
set x = 3;
mysql> call sample01(@var);
mysql> select @var;

・複数のクエリを発行
delimiter //
create procedure sample01()
begin
select 1;
select 2;
end//
delimiter ;
mysql> call sample01;

・IF文
delimiter //
create procedure sample02(in x int)
begin
if x = 1 then
select “inp 1”;
elseif x = 2 then
select “int 2”;
else
select “inp else”;
end if
end //
delimiter ;
call sample02(1);

・select~into文で変数へ
delimiter //
create procedure p(in a int, out sum int)
begin
select a * 2 into @tmp;
set sum = @tmp;
end //
delimiter ;
call p (4,@sum);
select @sum;

・カーソル単位で処理する

declare xxx cursorという形で宣言する。
for select でselectの結果を定義する。
fetch cursor for カラムで代入。
処理前にopenして最後にcloseする。

 

MySQL alter、select評価順

・準備

create database DB character set utf8;

use DB;

drop table if exists TABLE1;
create table TABLE1(
id int primary key auto_increment not null,
name varchar(255)
) default character set=utf8

・Alter

テーブルのリネーム
alter table TABLE1 rename TABLE2;

列の追加
alter table TABLE2 add column NAME2 varchar(255);

列の変更
alter table TABLE2 change NAME2 NAME3 int;

列の削除
alter table TABLE2 drop NAME3;

作成済みデータベースの文字コード変更
alter database DB character set utf8;

制約の追加方法
alter table TABLE1 add unique (NAME);

ちなみに、primaryとuniqueは複数指定すると、複合になるのでuniqueをそれぞれ複数の列の場合、
create table tbl (id int ,nm varchar(5) , unique(id) , unique(nm));
のように指定。

・制約の種類

not null
check
unique key
primary key
foreign key

・select式の評価順

from
on – join
where
group by
having
select
distinct
order by
top(limit)

・文字列型

https://dev.mysql.com/doc/refman/8.0/ja/char.html

char(n)
固定長で、右側にスペースが埋められる。

varchar(n)
可変長、255を超えてnを指定するとプリフィックスが2バイトとなる。プリフィックス格納されたデータに対するストレージとは別。ストレージについてはtextもvarcharも保存した容量のみ。

両方とも、nはバイトではなく文字数。また、MyISAMとInnoDBでの違いがあるので、注意。5.5未満はMyISAM、5.5以降はInnoDB