SQLの基礎その2

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

実際にselectしたデータは、主に以下の方法で使われる。

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

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

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

まずは以下のsqlを実行

select * from products;

その後、エキスポートするには以下のボタンをクリック。

あとは名前と場所、フォーマットを選択すると保存できる。
こんな感じ。

excelで取り込む場合

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

googleスプレッドシートで取り込む場合。

googleのブラウザがあれば簡単にできる。
共通に使えるツールとして利用する事がメリットである。

1. GoogleChromeからスプレッドシートを開く。
   

2. 新しいスプレッドシートの作成から、空白のスプレッドシートを作成。
   

インポートを選択。

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

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

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

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

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

データの集約

集約関数とは、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関数を用いる。

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ごとのグループを作る。
2. 各グループの中で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. 行の指定
2. from
   1. テーブルの指定
3. where
   1. 絞り込み条件の指定
4. group by
   1. グループ化の条件を指定
5. having
   1. グループ化した後の絞り込み条件
6. order by
   1. 並び替え条件
7. limit
   1. 取得する行数の制限

実行順序は以下の通り。

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

1. from
2. where
3. group by
4. having
5. select
6. order by
7. 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;

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

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;

絶対値・四捨五入の取得、文字列の演算、日付の取得

以下のように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;

てすと。

• 宛名で苗字+さん
• メールアドレス
• 女性だけ
  に送信したい。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
  • 日付や時刻の特定の部分まで取り出す

実行すると、こんな感じで時間が表示される。

以下のように、記載する内容でフォーマットが変わったりする。ケースによって変える事。

select current_date() + 3;
出力：20240904

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

以下の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;