# 2021/03/21 メンター相談

###### tags: `メンター相談`,`3月`

## SQL文でdate型またはdatetime型を日付で抽出する場合、どのような操作を採用しますか？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/rec1Kq2v44p4hVlE5

### 注意点 (日付について)

- 日付は厄介な存在
- 日付データの持ち方は主に3パターン
- unixtimestamp
- Date型
- Date型 + タイムゾーン
- **ただし、具体的な時間型はDBにより微妙に差分があるので、ドキュメントを参照したほうが良い**
- [MySQLの日付型、タイムスタンプに関するドキュメント](https://dev.mysql.com/doc/refman/5.7/en/datetime.html)
- [PostgreSQLのDate/Time Typesに関するドキュメント](https://www.postgresql.org/docs/9.1/datatype-datetime.html)

#### UnixTimeStamp

- 松原さんは、時差が嫌いなので、UnixTimeStampにしている
- 例: [stripe](https://stripe.com/docs/api)
- "created": 1616208256,
- メリット
- 時差の問題が発生しない
- (unixtimeを使わない場合は) Connectionを張る時に時差の影響を受けることがある
- デメリット
- 値を見て、何の値か一瞬ではわからない
- → 開発者用のビューを作ればよい
- → 開発者のための機能と、アプリの機能は分けて考えるべきという考え
- ありがちなミス
- msかsかで間違えることがある。
- だからカラム名にミリセックやセックなどの名前をつける
- バックエンドは処理のみ。時差変換などのロジックはフロントエンドに任せるべきという考え
- 例えば、フロントエンドでUnixTimeに変換してリクエスト送信。バックエンドは受け取ったUnixTimeをそのまま処理。

#### Date型 / Date型+Timezone

- そうではなく日付を保存する流派もある
- 例: [twitter](https://developer.twitter.com/ja/docs/tutorials/promoting-another-users-content)
- "created_at": "Sat Jul 27 18:20:40 +0000 2019",
- Timezoneの落とし穴
- +0930 などの時間のみを保存するのは良くない。必ずタイムゾーンの名前で保存すべき
- → サマータイムでずれてしまうため

#### その他日付に関する注意点

- [dateとかdatetimeとかtimestamp周りの怪しい（ハマりガチな？）挙動](https://dev.mysql.com/doc/refman/5.7/en/datetime.html)
- MySQL の DateTime にはデフォルト値がある
- 間違えた値を入力すると、`0000-00-00`や`00:00:00` (デフォルト値) が補完されて入力されることがある
- → エラーになるのではなく、意図しない値が入力されてしまう
- リクルートでは、補完により意図しない値が入力されたことが起因で障害が発生したことがあった

### 処理の重さについて

- パフォーマンス一番良い → between (indexがはられている場合)
- dateキャストはインデックスの貼り方次第で早くならない可能性
- `日付だけ` と `日付と時間` は異なるカラムタイプと判定される
- Date型は日付データ
- DateTime型は日時データ
- 日時がある場合は、`日付だけ` のカラムを作ってBetweenしてもいいかも
- かんたん → フォーマット関数による抽出で完全一致
- インデックスは効かない
- likeを使う方法は初めてみた
- 型が違うのでできないと思った
- **判断基準 → indexが効くか効かないか**

## SQLのドキュメントで特に参考にしているサイトなどはありますか？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/recxEQtiDc4KRYGTK

- 公式以外だと、 [use the index luke](https://use-the-index-luke.com/ja)
- 都度検索することが多い
- 例: sql date ベストプラクティスで検索
- 個人ブログを見る
- 共通して主張されている内容は採用するなど

## 業務でSQLのクエリを記述する際はどのようなことを意識しますか？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/rec4AZFYJGMxwdtVZ

### 意識していること

- そもそも、可読性が悪いSQL → テーブル設計を疑うべき
- ただし、データ集計・アナリティクス系 は仕方ない
- 1. 可読性重視
- 2. 実行速度が問題になってきたら、直す
- 3. view を作る
- あるクエリの実行結果をテーブルのように保存しておくもの。
- マテリアライズドViewとただのViewがあるので課題をお楽しみに。

### その他

- ほとんどORマッパーで済むことが多いので、あまり書くことがない
- 長いSQL → 一番ネストの深いところからレビューした
- 一番深いところ
- 次に深いところ
- ...

### テーブル設計の見直し頻度について

- nakanoさん
- テーブルを見直すのが面倒、アプリ側で対応しがちだった
- 見直しを決断する判断基準はありますか？
- → 開発速度の停滞が見えてきたら、見直す
- 社内アナウンス系のSaaS作る時に、実行速度が遅いことがあり、原因を探るとテーブル設計に問題があった
- テーブル設計を見直した (データは飛んだ)
- データを飛ばしたくない場合は重複データをとっておき、段階的にマイグレーションすると良い

## 細かくJOIN・まとめてJOINの2パターンの内、どちらのクエリの方が良いでしょうか？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/reccgz71lrdxlnUOl

- パフォーマンス的には、細かくJOINが良い
- まず１つ目でMVP(minimum viable product)を作って、２つ目に書き直すかも？

### Employeesで検証

- やはり細かくJOINのほうが速かった
- 1つ目
- 
- 9秒
- 2つ目
- 
- 1秒

## 「これまでに 70 回以上 Order に関わった Shipper に long_relation フラグを立てる」問題で、 以下のようなクエリを書いたのですが、どちらも syntaxエラーとなってしまいます。どこが問題なのでしょうか？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/recobsFuOQr47C13f

- 理由1. UPDATE句は、WHEREを繋げないといけない
- 理由2. FROMの前にselectが無いので何をすればよいかわからない
- **まず固定値を入れてクエリを通してから、固定値をクエリになおして書いていくと楽かも**
- baby step

-- まず固定値を入れてクエリが通ることを確認
UPDATE Shippers
SET relation = true
WHERE ShipperID = [1]

-- その後目的の値を取得するクエリを書く
UPDATE Shippers
SET relation = true
WHERE ShipperID IN (
    SELECT ShipperID FROM ...
)

参考: 松原さんコード


## SQLの評価順序について、ASは多用しないほうが良い？

https://airtable.com/tblA4yYGHwaqDYKwx/viwAUwNVsR9rOkW6u/reclSZ5hl7U8veVXd

### ASを使うかどうかについて

- 基本的に使う
- ただし、省略のためにはあまりつかわない
- 例: Employees AS E
- 自己参照系のJOINをする場合によく使う
- 例: マッチングアプリ
- マッチングした人を取得するクエリ (いいねしたひといいねされたひと同士を取得する)
- alias は分かりやすくするためによく使う

### その他注意点について

- LIMITには注意が必要
- LIMITで絞り込んだからといって実行速度に影響ない → 早くならない
- = 100000件とってきてから、１件取るため、100000件取得する場合と速度に違いは無い
- indexを貼れば速くなる？
- 例えば、100001件目のデータを取得する際は、INDEX貼っておけば速くなる？
- → indexを貼っても速くなる場合と速くならない場合とがある ( **early row lookup問題** )

### early row lookup 問題

参考: [MySQL ORDER BY / LIMIT performance: late row lookups at EXPLAIN EXTENDED](https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/)

- 早くするためには、INDEXを貼りましょう、とよく言われる
- postgresの場合は、その通り
- mysqlの場合は、early row lookup なので期待した挙動にならない場合がある
- late row lookup
- 指定位置から参照する
- 例: index8件飛ばして、9,10件目だけ参照する
- early row lookup
- 全て参照する(インデックスで取得したidのレコードを毎回取得してしまう)
- 例: indexを全て参照して、1~8は捨てる
- (回避策)まずサブクエリで必要な部分まで取得し、その後実際に欲しいレコードを取得するクエリを実行させる (実質laterowlookup)

### 自己参照型のJOIN (self referencing join) とは

[自己参照JOINの例](https://www.sqlservertutorial.net/sql-server-basics/sql-server-self-join/)

- 一つのテーブル内のカラム同士を比較する場合に使う
- スタッフの上司を取得する
- ある客と同じ地域に住んでいる客を取得する
- empID と managerID が１つのテーブルに存在する場合は、自己参照型のJOINを使う
- 必ず別名をつける必要がある

## その他

- 買った人、同じ地域に住んでる人
- 英語にすると分かりづらいので、ローマ字にしたい、、、、
- 一行でSQL書いていたのはなぜ？
- →手抜き、改行するのがめんどうだっただけ
- ビューとは？
- よく書くSQLの結果をテーブルに保存しておく、高速化の方法のひとつ
- 今後課題にあり
- 年を取得するとき、LIKE句が使えた理由
- OrderDateは文字列だったからかも？
- しかし、mysqlで日付型の場合でも可能だった
- 内部的に date -> string に変換してくれている？
- SQLは暗黙的なデータ変換を行ってくれる？
- sqlの書き方、フォーマット
- 松原さんの使っていたフォーマッター: [Instant SQL Formatter](http://www.dpriver.com/pp/sqlformat.htm)
- VSCodeの拡張機能がある: [VSCodeでSQLをフォーマットしたいよ - Qiita](https://qiita.com/Daara_y/items/6c1ddfa141d38a22e58d)
- lint等かけたことなかった
- 昔いた職場で注意されたこと
- 予約語は大文字にすべき(SELECTとか)
- 関数は特に何も言われなかった
- SELECT や WHERE で改行してね
- しかし、特にルールがあるわけではなかった
- メモ帳の "" とSQLの "" は別の文字なので注意