Ora Ora Ora Ora Ora

• November 16, 2009 [ Web ]
  

  おっすオラ忘れ易いルール、無駄無駄無駄無駄無駄無駄ッ
  
  ■DML
  DB製品毎のnullの扱いを事前に調べる(word != ‘検索値'やword = nullではnullにヒットしない、is not nullを使う等)
  null値はもっとも小さな値、or大きな値（nullが含まれるかnullの件数を先に確認しておく、下に例を載せた）
  集計関数はNULLを無視する（下に例を載せた）
  文字列は'で囲む
  取得したいカラムは集計関数に使われるカラムを除いて全てGroupBy句に入れる
  集計関数の条件はhaving句を使用する(where句は元のデータに対するもの)、having句には集計関数が入る（下に例を載せた）
  SQLワイルドカード:複数文字%,1文字_
  WINワイルドカード:複数文字*,1文字?
  エスケープ文字はescape句で設定。like句の後ろにつく。escape '\'
  日付と文字列の変換が必要To_char関数とTo_date関数を使用する
  to_char(column,'yyyy/mm/dd hh24:mi:ss')
  to_date('2009/02/21','yyyy/mm/dd')
  横幅を大きくするset line 150
  縦を大きくするset pages 30
  unionはdistinctが掛かる、全取得はunion all
  inner join onは両方のテーブルにあるものだけ、どちらかだけにあるのは無くなるイメージ
  left (outer) join onは左側のテーブルにしかないものも表示、左に右の該当部をくっつけるイメージ
  right (outer) join onは右側のテーブルにしかないものも表示、右に左の該当部をくっつけるイメージ
  full outer join onは全ての行を表示
  trancateは高速だがロールバックできない、deleteはロールバックできる、論理削除がindexメンテコスト低で良い
  多対多のSQLが含まれないか確認する（下に例を載せた）
  
  ■テーブル結合4種
  1対1、1対多の結合を行う
  １対多でも行数が増えるが、多対多は全組合せになり行数が比例関数的に増えてしまう
  多対多になるのはテーブル設計が悪いのでは？
  1)inner join - on () 両方あるものを表示、欠けがでる
  2)left outer join - on () どちらかは欠けがなく表示
  3)right outer join - on ()  どちらかは欠けがなく表示
  4)full outer join - on ()  欠けがなく表示
  
  select URIID,NAME,UKYAKUID from URIAGE left outer join TORIHIKI
   on (URIAGE.UKYAKUID = TORIHIKI.ID)
  select URIID,NAME,UKYAKUID from TORIHIKI right outer join URIAGE
   on (URIAGE.UKYAKUID = TORIHIKI.ID)
  select URIID,NAME,UKYAKUID from TORIHIKI full outer join URIAGE
   on (URIAGE.UKYAKUID = TORIHIKI.ID)
  select 伝票番号,行番号,処理日,商品名,担当者名,顧客名,数量 from 売上データ
   left outer join 商品マスタ on 売上データ.商品ID = 商品マスタ.商品ID
   left outer join 担当者マスタ on 売上データ.担当者ID = 担当者マスタ.担当者ID
   left outer join 顧客マスタ on 売上データ.顧客ID = 顧客マスタ.顧客ID
   order by 伝票番号,行番号
  
  ■なおjoin on句 と where句での結合の違い
  １）select * from A, B where A.a = B.a and A.c = 3;
  ２）select * from A inner join B on A.a = B.a where A.c = 3;
  両者は同じ結果、前者の方が古くからある構文，後者が新しい構文。だがOuter joinは下記にある通り違う
  https://okwave.jp/qa/q8090979.html

  ■WHEREとJOIN ONで条件を指定した場合の違い
  SELECT * FROM movies LEFT OUTER JOIN counts ON counts.movie_id = movies.id WHERE counts.date_at = '2012-12-16';
  　WHERE句はJOINが終わってから評価される→Outer joinでも12/16でない映画が含まれずLEFTが欠ける
  この問題の解決にはWHERE句に条件を指定するのでなくJOIN ONの追加条件として指定する
  SELECT * FROM movies LEFT OUTER JOIN counts ON counts.movie_id = movies.id AND counts.date_at = '2012-12-16';
  　Outer joinしLEFTの全映画が出力したいので、結合前に12/16以外のカウントを外した上で結合させると逆にLEFT側は全部でる（RIGHTの条件で絞った上でLEFT OUTER JOINだとRIGHTがNULLでもLEFTが出る）
  http://hakutoitoi.hatenablog.com/entry/2012/12/17/005208
  
  ■多対多、1対多の行数
  社員と職域、社員と手当、それぞれ結合しても多の行数(1x多)になる
  こんな設計や操作はするなという事（多x多の行数になる、多対多の結合は行わないこと）
  -- データ挿入：社員
  INSERT INTO x_employee (no,name) VALUES (1, '田中');
  INSERT INTO x_employee (no,name) VALUES (2, '鈴木');
  INSERT INTO x_employee (no,name) VALUES (3, '佐藤');
  -- データ挿入：職域(兼任が発生)
  INSERT INTO x_joblevel (no,res) VALUES (1, '部長');
  INSERT INTO x_joblevel (no,res) VALUES (1, '課長');
  INSERT INTO x_joblevel (no,res) VALUES (2, '平');
  -- データ挿入：手当(一人に幾つか付く)
  INSERT INTO x_compensation (no,allow) VALUES (1, '住宅');
  INSERT INTO x_compensation (no,allow) VALUES (1, '交通');
  INSERT INTO x_compensation (no,allow) VALUES (2, '住宅');
  -- １対多(田中が兼任のため田中分が2行、鈴木1行で都合3行、1対多だとまだ兼任と理解ができる)
  select * from x_employee
   inner join x_joblevel on x_employee.no = x_joblevel.no
  -- 多対多(田中兼任2行x田中手当2行の小計4行、鈴木の1行で都合5行、田中だけで4行でるとマトリックスの意味が不明になる)
  select * from x_employee
   inner join x_joblevel on x_employee.no = x_joblevel.no
   inner join x_compensation on x_employee.no = x_compensation.no

  ■Group by/having/where
  取得したいカラムは集計関数に使われるカラムを除いて全てGroupBy句に入れるの例
  select distinct boss from fun_test_data
  select department, avg(salary) from fun_test_data group by department
  select boss, avg(salary) from fun_test_data group by boss
  select department, boss, avg(salary) from fun_test_data group by department, boss

  集計関数の条件はhaving句を使用する(where句は元のデータに対するもの)、having句には集計関数が入る例
  select department, count(department), avg(salary) from fun_test_data where department!="営業" group by department;
  select department, count(department), avg(salary) from fun_test_data where department!="営業" group by department having avg(salary) > 45;
  ※moreにデータをDDLを置いておくので操作して感じる
  
  集計関数にはsum() count() avg() stddev()標準偏差 variance()分散etc..がある
  
  nullを無視するので、3件が100, null, 200のとき平均は150となる、nullを0にする事
  SELECT AVG(NVL(SURYO,0)) AS 平均, COUNT(NVL(SURYO,0)) AS 件数 FROM TEST1
  
  data2にnullがありカウントしようとするがnullはカウントされず１行目はダメ、件数はkeyをカウントせよ
  select COUNT(DATA2) from EXAMPLE_TABLE where DATA2 is NULL
  select COUNT(ID) from EXAMPLE_TABLE where DATA2 is NULL
  
  ※SQLはバグかどうか分からず失敗かどうか分かりにくい→計算式を書いてからSQLを書く
  集計値の集計を行う際には集計順序に注意せよ | 実践から学ぶ分析の落とし穴 - GiXo Ltd.
  
  ■サブクエリ（副問い合わせ）
  どこでもOK、any() in() some() exists() not exists()等がある、mysqlにはminus() except()はない
  
  /// where条件が1件
  SELECT 受注番号 FROM 受注表 WHERE 商品コード = (SELECT 商品コード FROM 商品表 WHERE 商品名 = 'JUICE');
  /// where条件が複数
  SELECT 受注番号 FROM 受注表 WHERE 商品コード IN (SELECT 商品コード FROM 商品表 WHERE 単価 = 400);
  /// havingに
  SELECT 顧客コード,COUNT(DISTINCT 受注番号) FROM 受注表 GROUP BY 顧客コード HAVING 受注個数 < (SELECT MIN(受注個数) FROM 受注表 WHERE 顧客コード = '002');
  /// fromに
  SELECT MAX(AVG_J) AS MAX_AVG FROM (SELECT AVG(受注個数) AS AVG_J FROM 受注表 GROUP BY 顧客コード) ;
  /// selectに（出力表のカラム数は1個だけで、これが変わる等はできないかと）
  SELECT 顧客コード,AVG(受注個数), (SELECT AVG(受注個数) FROM 受注表) FROM 受注表 GROUP BY 顧客コード;
  
  selectサブクエリにカラムを一つづつ足せば複数も可だが
  select name, res,
   (select allow from x_compensation where x_compensation.no = x_employee.no limit 0, 1),
   (select allow from x_compensation where x_compensation.no = x_employee.no limit 1, 1)
   from x_employee inner join x_joblevel on x_employee.no = x_joblevel.no inner join x_compensation on x_employee.no = x_compensation.no
  
  /// 相関サブクエリ（出力表のカラム数が変わる等はできないかと）
  メインクエリの行ごとにサブクエリを実行、結合をサブに記入している、違う書き方ができるかも知れないがサブからでなくメインから考えている感じ
  例１）
  SELECT * FROM 商品表 AA WHERE '20010401' IN  (SELECT 納品日 FROM 受注表 BB WHERE AA.商品コード = BB.商品コード);
  例２）
  SELECT SUM(受注個数),納品日 FROM 受注表 PP GROUP BY 納品日 HAVING SUM(受注個数) > (SELECT 150 + MAX(受注個数) FROM 受注表 QQ WHERE PP.納品日 = QQ.納品日);
  
  ■Union
  UNION句でマージする複数のSELECT文は、(1)SELECT句のカラム数が同じこと(2)カラムのデータ型が同じか、変換可能であること
  select shit from ass union all select kuso from ketsu
  
  ■SQLはカラム数の増加数で構成考える？
  left outer joinはカラム数がカラム数の合計から共通のjoin onのカラム数を引いた数（行数は同じ）
  　full outer join はカラム数がカラム数の合計から共通のjoin onのカラム数を引いた数（行数はleftの要素数にrightの要素数を合計したもの）
  unionは重複を除外し表を足し合わせるため行数が両表の合計行数（カラム数は合致必要でカラム数は変わらない）
  unian allは重複を除外せず表を足し合わせるため行数が両表の合計行数（カラム数は合致必要でカラム数は変わらない）
  cross joinはカラム数が両表のカラム数の合計、行数は両表の行数の掛け算
  　再帰的にSQL処理はcross joinし条件を付けるか？
  　　標準SQLのFrom句のカンマはcross joinとなる
  with句は副問い合わせを見やすくしたもの
  distinctで(組み合わせで)一意になる行のみにし重複を省く
  UNION とUNION ALLの違い - Qiita
  CROSS JOIN （クロス結合）を使ってデータを取得する - JOIN （結合）を使いこなそう - SQL Server 入門 (sql55.com)
  SQLのDISTINCTとは？（OracleやMySQLで使用する方法） | IT職種コラム (it-kyujin.jp)

  ■テーブル設計、制約
  CONSTRAINT pk_uriage PRIMARY KEY (伝票番号, 行番号),
  CONSTRAINT fk_shohin FOREIGN KEY (商品ID) REFERENCES 商品マスタ(商品ID)
  CONSTRAINT ck_gender CHECK (性別=0 OR 性別=1)
  制約は列に指定しても、うしろで指定してもどちらでもよい
  CONSTRAINTのキー名を省略するとオラクルが勝手につける
  主キーにはNULLは入らない
  ユニークキーにはNULLを入れてもよい
  NOT NULLの未入力はエラー
  
  ■シーケンス
  シーケンスはオブジェクトとして表とは別に作成する
  デクリメントはできないので修正はDROPして作成し直す
  DUALはダミー表
  nextvalはインクリした後の値
  currvalは現在地
  create sequence testseq
  select testseq.nextval from dual
  select testseq.currval from dual
  insert into URIAGE (URIID,URIDATE,UKYAKUID) values (testseq.nextval,'09-11-16','101')
  drop sequence testseq
  
  ■ビュー
  頻繁に行われるような問い合わせをスキーマに格納しておく、パフォーマンスが上がる場合がある
  集約等をしなければパフォーマンスには問題がない。次を使うとパフォーマンスが落ちる。OLAP 関数（RANK, DENSE_RANK, ROW_NUMBER 等）、集約関数（AVG, COUNT, SUM, MIN, MAX）、集合演算子（ UNION, INTERSECT, EXCEPT 等 ）
  ビューは検索だけでなく、データの追加、更新、削除も行える※使用しない方が良い？
  create view 売上ビュー as
   select 伝票番号,行番号,処理日,商品名,担当者名,顧客名,数量 from 売上データ
   left outer join 商品マスタ on 売上データ.商品ID = 商品マスタ.商品ID
   left outer join 担当者マスタ on 売上データ.担当者ID = 担当者マスタ.担当者ID
   left outer join 顧客マスタ on 売上データ.顧客ID = 顧客マスタ.顧客ID
   order by 伝票番号,行番号
  select * from 売上ビュー
  
  ■用語
  concat()…CONCATENATE 文字連結
  coalesce()…コーレス、合体する、与えられた引数のうちNULLでない最初の引数を返す
  LPAD() RPAD()…文字の右詰左詰め
  OLAP…OnLine Analytical Processing＝分析、キューブ
  
  ■インデックス
  １）インデックスが効かない
  関数　where datediff(now(),mod_date)>180
  式　where col1 / 2 = 0
  否定構文　where col1 != 3
  Like検索（前方一致除く）　where col1 like '%string%'とかwhere col1 like '%string'とか
  ２）複合インデックスでは指定順が重要
  index a
   create index idxA1 on tbl (col1);
   create index idxA2 on tbl (col2);
   -> where col1 = x > idxA1が使用される
   -> where col2 = x > idxA2が使用される
   -> where col1 = x AND col2 = x > idxA1か2のどちらかが使用される
  index b
   create index idxB1 on tbl (col1, col2);
   -> where col1 = x > idxB1が使用される
   -> where col2 = x > インデックスは使用されない
   -> where col1 = x AND col2 = x > idxB1が使用される,indexA1,A2より高速
  index c
   create index idxC1 on tbl (col2, col1);
   -> where col1 = x > インデックスは使用されない
   -> where col2 = x > idxC1が使用される
   -> where col1 = x AND col2 = x > idxC1が使用される,indexA1,A2より高速
  
  ■RFM(recency frequenctry monetary)
  SELECT name, MAX(date), MIN(date) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY MAX(date) DESC
  SELECT name, count(x_bill.no) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY count(date) DESC
  SELECT name, sum(total_bill) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no  GROUP BY name ORDER BY sum(total_bill)  DESC
  SELECT name, SUM(price * unit) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC
  ↓
  SELECT name, MAX(date), count(DISTINCT x_bill.no), SUM(price * unit) FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC
  　※購入頻度は多対多で重複が出るのでカウントはdistinct primaryKeyで重複を含めない
  
  ↓順位を点数化のため、MySQLにはRownumがなく変数を使いシンプルな表でまず作った
  SET @rownum_recent=0; 
  SELECT @rownum_recent:=@rownum_recent+1 as rank_recent, no, name, recent  from
   (SELECT x_employee.no, name, MAX(date) as recent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY MAX(date) DESC) as t1
  
  SET @rownum_frequent=0; 
  SELECT @rownum_frequent:=@rownum_frequent+1 as rank_frequent, no, name, frequent from
   (SELECT x_employee.no, name, count(x_bill.no) as frequent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no GROUP BY name ORDER BY count(date) DESC) as t2
  
  SET @rownum_money=0; 
  SELECT @rownum_money:=@rownum_money+1 as rank_money, no, name, money from
   (SELECT x_employee.no, name, SUM(price * unit) as money FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY name ORDER BY SUM(price * unit) DESC) as t3
  
  ↓まとめる(user_noで結合した、別名を付ける計算カラムよりどの元の表かが重要かもLeftかRight joinかで)

  SET @rownum_rank=0;
  SET @rownum_recent=0;
  SET @rownum_frequent=0;
  SET @rownum_money=0;
  SELECT @rownum_rank:=@rownum_rank+1 as rank, rank_recent+rank_frequent+rank_money as least_score_wins, t1_r.no as user_no, t1_r.name, recent, frequent, money, rank_recent, rank_frequent, rank_money
  FROM (
     SELECT @rownum_recent:=@rownum_recent+1 as rank_recent, t1.no, t1.name, recent
     FROM (SELECT x_employee.no, x_employee.name, MAX(date) as recent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY MAX(date) DESC) as t1
  ) as t1_r
  LEFT OUTER JOIN (
     SELECT @rownum_frequent:=@rownum_frequent+1 as rank_frequent, t2.user_no, t2.name, frequent
     FROM (SELECT x_bill.user_no, x_employee.name, count(distinct x_bill.no) as frequent FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_bill.user_no, x_employee.name ORDER BY count(distinct x_bill.no) DESC) as t2
  ) as t2_f
  ON t1_r.no = t2_f.user_no
  LEFT OUTER JOIN (
     SELECT @rownum_money:=@rownum_money+1 as rank_money, t3.no, t3.name, money
     FROM (SELECT x_employee.no, x_employee.name, SUM(price * unit) as money FROM x_employee RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no GROUP BY x_employee.no, x_employee.name ORDER BY SUM(price * unit) DESC) as t3
  ) as t3_m
  ON t2_f.user_no = t3_m.no
  ORDER BY least_score_wins asc
  
  ■NULLの件数を事前に数えておく
  SELECT *
   FROM (SELECT 'value', count(COALESCE(value,0))  FROM bangboo_data.x_data WHERE value IS NULL)
   union all
   (SELECT 'value2', count(COALESCE(value2,0))  FROM bangboo_data.x_data WHERE value2 IS NULL)
   union all
   (SELECT 'v_no_null', count(COALESCE(v_no_null,0))  FROM bangboo_data.x_data WHERE v_no_null IS NULL)
  ↑が安全策
  
  SELECT count_null_1, count_null_2
   FROM (SELECT value, count(COALESCE(value,'')) as count_null_1 FROM x_data WHERE value IS NULL GROUP BY value) as t1,
   (SELECT value2, count(COALESCE(value2,''))  as count_null_2 FROM x_data WHERE value2 IS NULL GROUP BY value2) as t2
  　※From A, B, C でクロス結合となり全組み合わせがでる、1行ずつならOKだが、、
  　※SQLはカウント0件をグルーピングできない、表示自体存在しないことになるから、↑は少々危険で↓で
  SELECT count_null_1, count_null_2
   FROM (SELECT count(COALESCE(value,'')) as count_null_1 FROM x_data WHERE value IS NULL) as t1,
   (SELECT count(COALESCE(value2,''))  as count_null_2 FROM x_data WHERE value2 IS NULL) as t2,
   (SELECT count(COALESCE(v_no_null,0))  as count_null_3 FROM x_data WHERE v_no_null IS NULL) as t3
  
  ■SQL一覧
  　https://qiita.com/tatsuya4150/items/69c2c9d318e5b93e6ccd
  UPDATE テーブル名 SET カラム名 = 値, カラム名 = 値  WHERE id = 1
  INSERT INTO テーブル名 VALUES ('1')DELETE FROM テーブル名 WHERE 条件
  intersectは重複するもの　SELECT name FROM ユーザー１ INTERSECT SELECT name FROM ユーザー2
  　※unionは和集合、exceptは差集合、intersectは積集合
  

  
  More
  
  Posted by funa : 06:54 PM

• Comment (0)
• Trackback (0)

Home
For: Smartphone | PC
/// BANGBOO BLOG ///