SQLの基礎その2


# Select文で取得したデータの利用法

実際にselectしたデータは、主に以下の方法で使われる。
1. コンピュータプログラムから利用する
    1. ruby,phpなどからSQL文をなげ、DBからユーザー情報などを取得するといった形である。
取り込むにはコネクタ・ドライバなどを用いたりする。
1. CSVファイルに書き出し、表計算ソフトに取り込む。
    1. SQLからcsvとして取り出し、Excelなどの計算ソフトに取り込む。

# データをCSVファイルに書き出す。

* TSVとは、区切り文字である`タブ`で区切ったテキストデータ。

まずは以下のsqlを実行
```
select * from products;
```
その後、エキスポートするには以下のボタンをクリック。

![スクリーンショット 2024-08-31 22.55.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/27755d43-107f-fc81-c4a9-e15bd096309d.png)

あとは名前と場所、フォーマットを選択すると保存できる。
こんな感じ。
![スクリーンショット 2024-08-31 22.57.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/635fc55e-a49a-85ee-ba3d-7db340f82070.png)


## excelで取り込む場合

1. ファイルのインポートから`csv`を選択
1. その後、データの取り出しを選択。
1. テキストファイルウィザードをいれる。
    1. 区切り文字
    1. 取り込み開始行
    1. 文字化けしている場合は`元のファイル`をUnicode(UTF-8)日本語を含む文字を使う場合の規格。
1. 区切った後の列のデータ形式を選択し完了。
1. データを返す先を選択（どこが左上で開始するか。）

## googleスプレッドシートで取り込む場合。
googleのブラウザがあれば簡単にできる。
共通に使えるツールとして利用する事がメリットである。

1. GoogleChromeからスプレッドシートを開く。
![スクリーンショット 2024-08-31 23.11.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/eec1dfcc-5692-1c37-7e69-fd1830d995ec.png)

1. 新しいスプレッドシートの作成から、空白のスプレッドシートを作成。
![スクリーンショット 2024-08-31 23.12.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/f6ef4657-55af-4787-15bf-45454e4f0435.png)



インポートを選択。

![スクリーンショット 2024-08-31 23.13.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/e339ff69-7a5a-2ea6-89ea-26e183cad247.png)

その後、アップロードから、ローカルにあるcsvをアップロードする。

![スクリーンショット 2024-08-31 23.14.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/6ca82efd-f312-1099-47ad-9e5f2b1626ac.png)

すると、ファイルを取り込めたのを確認できる。

![スクリーンショット 2024-08-31 23.17.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/70d22def-65b3-75b6-71e3-33d0d1f4f4aa.png)


## 情報セキュリティについて。

扱うデータの情報漏洩について注意する必要がある。
組織で運用するDBには個人情報、機密情報があるため。
漏洩すると企業の信用度が下がり倒産するなどもある。

情報セキュリティに関する無料コンテンツとして、以下のサイトがあるため確認するのもよいだろう。

https://www.ipa.go.jp/security/videos/list.html

# データの集約
集約関数とは、SWLでテーブルの値を集約するために使う。
- sum
    - 合計
- avg
    - 平均
- min
    - 最小
- max
    - 最大
- count
    - 合計

### 合計
これを元に、月別売り上げ、月別アクセス数、ユニークユーザー数などを上記の関数を元に取得していく。

2017年の合計打ち上げ金額を求めるとする。

まずは、以下のsqlで、ordersを全部みる事ができる。
```:sql
select * from orders;
```

次に、以下のsqlで2017年の1月中のオーダーの内容のsqlになる。

```
select
  *
from
  orders
where
  order_time >='2017-01-01 00:00:00'
  and order_time < '2017-02-01 00:00:00';
```

次にamount(列の一つ)の合計をとりたいので、以下のように改変する。(なぜか画像がコードブロックの中に埋め込まれる。)
```
select
  sum(amount)
from
  orders
where
  order_time >='2017-01-01 00:00:00'
  and order_time < '2017-02-01 00:00:00';
```

### 平均
全商品の平均価格priceを教えて欲しいと言われたとする。
avg(expr)とすることで、exprの平均値を取る事ができる。

```:sql
select avg(price) from products;
```

### 最小値
表品価格の最小値を出すとする。min関数を用いる。
```sql
select min(price) from products;
```

### 最大値
商品価格の最大値を出すとする。max関数を用いる。
```
select max(price) from products;
```

### nullについて
値がない事を示す表現である。
集約関数では**nullは無視される。**

- 10
- 20
- null

で平均を出す場合`(10+20)/2 = 15`という扱いになる。

また、DBを作る場合は、なるべくnullが入らないようにDBの設計を行えるようにしよう。
例えば、nullの代わりに0,1を使うようにするとか。

### 行数を数えるcount

ユーザーが何人いるか調べるとする。count関数を使う。
以下の内容なら、usersの行全てを計算する。

```
select count(*) from users;
```

女性だけをカウントするとしたら、以下のようにさらにwhereを使って絞る。
```
select count(*) from users where gender = 2;
```

応用とsて、指定した区間のユニークユーザー数（決まった期間にアクセスしたユーザーの数）を求めたいとする。
一人のユーザーが10回アクセスしたとしても一回とする。

サイトへのアクセスログはaccess_logに入っているため、access_logのカラムを使う。

以下のsqlで2017年1月1日のリクエスト数を見れた。
```:sql
select
  *
from
  access_logs
where
  request_month ='2017-01-01 00:00:00';
```
次に行数を求める。ただし同じユーザーIDは一回とする。
count(distinct user_id)で、ユーザーIDの重複を除外してカウントする。

```:sql
select
  count(distinct user_id)
from
  access_logs
where
  request_month ='2017-01-01 00:00:00';
```

### データをグループ化する。
都道府県ごとのユーザー数が欲しいとする。
group by句を使う。
カウントなどを使って集計する時にグループ単位で集計を行う。
group byで指定した列名でグループ化される。
```:構文
select
  列名
from
 テーブル名
group by 列名;
```

以下のsqlでprefecture_idと、そのidに所属するユーザーの数をカウントする。
```:sql
select prefecture_id,count(*) from users group by prefecture_id;
```

計算のイメージ。
1. usersテーブルのprefecture_idを、groupbyでprefecture_idごとのグループを作る。
1. 各グループの中でcount(*)をして再計算する。

期間ごとに集計するとする。
2017年の月別ユニークユーザー数を出すものとする。

以下のsqlで2017年から18年のアクセスログを出す。
```:sql
select
 *
from
 access_logs
where
  request_month >= '2017-01-01'
  and request_month < '2018-01-01';
```

これを、request_monthごとにグループ化する。
以下のsqlで月毎のユニークユーザー数を出す事ができた。

```:sql
select
 request_month,
 count(distinct user_id)
from
 access_logs
where
  request_month >= '2017-01-01'
  and request_month < '2018-01-01'
group by
  request_month;
```

### さらに集約家かを絞り込むhaving
whereともにているが、集計結果に対してさらに条件式を適応させるときに使用する。

```:構文
select
 from
where
 group by
having
```
順番を必ず上記のようにしないといけない。`group by`の後にいれないといけない。

上記のsqlから、user_ifが630以上のものをだすとする。
```
use mydb;
select
 request_month,
 count(distinct user_id)
from
 access_logs
where
  request_month >= '2017-01-01'
  and request_month < '2018-01-01'
group by
  request_month
having
 count(distinct user_id) >= 630;
```

## select分の記述順序は以下のようにすること。

1. select
    1. 行の指定
1. from
    1. テーブルの指定
1. where
    1. 絞り込み条件の指定
1. group by
    1. グループ化の条件を指定
1. having
    1. グループ化した後の絞り込み条件
1. order by
    1. 並び替え条件
1. limit
    1. 取得する行数の制限

## 実行順序は以下の通り。

記載したSQLは実際には以下のように実行される。selectが異なる事さえおぼえておけば大体は良いと思う。
1. from
1. where
1. group by
1. having
1. select
1. order by
1. limit


# データの並び替え
order by句を用いてデータの並び替えを行える。

商品一覧を、価格が高い順に並び替えるとする。
降順（大→小）にする。
- asc
    - デフォルトで、昇順
    - ただし、今後のバージョンアップなどで変わる事もあるため、並び順が重要な場合は明示的にしてやること。
- desc
    - 指定して行う。降順

```:昇順
select * from products order by price;

直接指定するなら、
select * from products order by price asc;
```
```降順
select * from products order by price desc;
```


## 複数の並び替え条件を指定する。

商品一覧を価格が高い順に並び替える。価格が同じ時はidの登録順で並び替える。

```:sql
select * from products order by price desc,id asc;
```

![スクリーンショット 2024-09-01 14.41.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/c19e4df6-e9c7-ec1f-8b86-6d793964d86f.png)

実際にテストで並び替えてみる。
ユーザー一覧を、生年月日が古い順にならべるとする。

```:sql
select * from users order by birthday acs,prefecture_id asc; 
```

# 関数・演算子

税込価格は、以下のような形でだした。
```
select name,price * 1.08 from products;
```

SQLで使える演算子。
- `+`
    - たす
- `-`
    - ひく
- `*`
    - かける
- `/`
    - わる
- `%`
    - あまり

実は、とくにDBを使わなくても普通に計算はできる。
以下のようなものもできる。
```
select 10 + 3;
select 10 - 3;
select 10 * 3;
```

## nullを含む場合の注意点

以下のようにnullをいれて演算をしてしまった場合、**回答が全てnullになってしまう**
という事に注意する。

```
select  10 + null;
select  10 - null;
select  10 * null;
select  10 / null;
```

![スクリーンショット 2024-09-01 14.53.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/a36bcc70-a53b-2ebc-06c5-75c0bcd70891.png)

## 絶対値・四捨五入の取得、文字列の演算、日付の取得
以下のようにabsを入れる事で絶対値を求める事ができる。

```
select abs(10);
select abs(-10);
```

以下のround関数で、四捨五入を出力できる。
```:構文
round(対象の数値,丸めの桁数)
```
丸め桁数が0の場合少数第一位で四捨五入される。10.555の場合は、11になる。`round(10.555,0)`
丸めの桁数が1の場合、少数第二位で四捨五入される。10.555の場合は、10.6になる。`round(10.555,1)`

```:sql
select id, name,round(price * 1.08,0) from products;
```

文字列の演算
ユーザーの一覧を、`山田 太郎さん`といった形で`苗字 スペース 名前さん`で出したい。
文字列連結では、`concat`関数を利用する。

```
select concat(last_name, ' ',first_name,'さん') from users;
```

![スクリーンショット 2024-09-01 15.13.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/6105942b-bbd3-4f49-5741-222439d69e46.png)


てすと。
- 宛名で`苗字+さん`
- メールアドレス
- 女性だけ
に送信したい。concat関数と、女性だけの場合はwhere句で指定する。

concatで苗字+さんと、emailをselectする。
それをuserテーブルから、女性だけwhereで絞り込む。
```
select concat(last_name,'さん'),email from users where gender=2;
```

日付について
```
n日後の日付
正：select current_date() +  interval 3 day;
n日前の日付
正：select current_date() -  interval 3 day;
x時間前の時刻
正：select current_timestamp() - interval 6 hour;
```

### 日付の演算子

- 現在の日付
    - current_date
- 現在の時刻
    - current_timestamp
- n日後の日付
    - `+ interval 3 day`
- n日前の日付
    - `- interval 3 day`
- x時間後の時刻
    - `+ interval 6 hour`
- x時間前の時刻
    - `- interval 6 hour`
- extract
    - 日付や時刻の特定の部分まで取り出す


実行すると、こんな感じで時間が表示される。
![スクリーンショット 2024-09-01 15.24.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/f6e472ef-3a55-6e47-0ce6-19ed5813fdb9.png)


以下のように、記載する内容でフォーマットが変わったりする。ケースによって変える事。
```
select current_date() + 3;
出力：20240904

select current_date() + interval 3 day;
出力：2024-09-04
```


![スクリーンショット 2024-09-01 15.25.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1288524/b8b0173e-c4cc-4be4-2e5a-2a943217b360.png)


以下のsqlでorderテーブルから注文日時が2017年1月のレコードを取得する。
```
select * from orders where extract(year_month from order_time) = 201701;
```

ordersテーブルから、2017年のレコードを取得する。
```
select * from orders where extract(year from order_time) = 2017;
```

ordersテーブルから1月のレコードを取得する。
```
select * from orders where extract(month from order_time) = 1;
```