その18から続きます。

応用1)全期間における平均客単価を知りたい！

補足:単価は小数第1位で四捨五入
客単価とは1回の購買によってユーザー1人当たりが支払う総額のこと。
例えばユーザーAさんがカフェで飲食をしたとき
コーヒー300円とケーキ400円を購入したとき、この場合客単価は700円

select round(avg(amount), 0) from orders o;

応用2)月別の平均客単価を調べたい！(小数第1位で四捨五入)

続きを読む →

その17から続きます。

データベース構造の操作における 注意点について

alter table, drop table, drop database等

・操作は基本的には取り消せない
・特に実務において本番環境を操作するときはサービスをメンテナンスモードにしてバックアップを取ってからalter tableなどを実行するのが安全
・想定外に時間がかかりシステムトラブルになる場合があるので注意！
・テスト環境でalter tableなどのテストをして問題点を洗い出してから
本番環境で実行するのがオススメ

例：データベースの作成book_storeという名前のデータベースを作成する。

続きを読む →

その16から続きます

1:データの更新(新規データを追加する(insert))

例：新商品を1件追加したい！商品名は”新商品A”で価格を1000円とする

insert into products(name,price)values('新商品A', 1000);

列リストを省略して1件レコードを追加する

続きを読む →

15から続きます。

1:条件分岐caseとは？

SQLで場合分けを記述する際に使う。場合分けを、条件分岐とよぶ。

例：ユーザーのアクティビティ度合いによって施策を変えたいので
ユーザーを累計注文回数でランク分けしてランクが高い順にしたい！
A 5回以上 B 2回以上 C 1回

続きを読む →

14から続きます。

1:サブクエリとは？

ある問い合わせの結果に基づいて、異なる問い合わせを行う仕組み

例:2017年12月に商品を購入していないユーザーにメルマガを送りたいので該当ユーザー一覧を出したい！

補足:必要な情報はユーザーid、名字、email、ER図を参考に

select
id,last_name,email
from
users
where id not in(
select
user_id
from
orders
where
order_time >= '2017-012-01 00:00:00'
and order_time < '2018-01-01 00:00:00');

応用)全商品の平均単価よりも単価が高い商品の一覧を表示したい！

続きを読む →

13から続きます。

テーブルとビューの違いは?

テーブル

・実際のデータを保存

ビュー

・ビューの中にはセレクト文が保存される
・ビュー自体はデータを持たない
・order by句が使えない=並び替えできない
・ビューに対する更新は不可能ではないが制限があるデータを保存しないので記憶装置の容量を節約できる。
・よく使うセレクト文をビューにしておくことで使い回しができるが
パフォーマンス低下を招く場合がある

例：都道府県別のユーザー数を知りたい！まずはselect文から

select
p.name,
count(*)
from
users as u
inner join
prefectures as p
on u.prefecture_id = p.id
group by
prefecture_id;

これをviewにすることもできる。

続きを読む →

その12から続きます。

外部結合とは？

片方のテーブルの情報がすべて出力されるテーブルの結合

外部結合は欠落のあるデータを取り扱う結合

構文：外部結合 left outer join

select テーブル名1.列名,テーブル名2.列名・・・
from
テーブル名1
left outer join
テーブル名2
on テーブル名1.列名 = テーブル名2.列名；

left outer join / right outer join

left outer join・・・左側(from句で最初に書いたテーブル)をマスターとする。

right outer join・・・右側(from句で後に書いたテーブル)をマスターとする。

select
u.last_name,
u.id,
o.user_id,
o.id
from
users u
left outer join
orders as o
on u.id = o.user_id
order by u.id;

※ポイント

続きを読む →

その11から続きます。

1:内部結合(inner join)

例：ユーザー一覧を取得したい！都道府県IDで出力されてもよくわからないので都道府県名も表示したい！

select
 users.id,
 users.last_name,
 users.first_name,
 prefectures.name
from users
inner join
prefectures
on users.prefecture_id = prefectures.id;

※ポイント

まず名字と名前と県IDを取得
select id, last_name, first_name, prefecture_id from users;
次にprefecture_idと都道府県名(prefectures.nane)を結びつける(inner join on)

短く書くこともできる

select
 u.id,
 u.last_name,u.first_name, p.name
from
 users as u
inner join
 prefectures as p
 on u.prefecture_id = p.id;

さらに短く書くこともできる(asを省略)

select
 u.id,
 u.last_name,u.first_name, p.name
from
 users u
inner join
 prefectures p 
on u.prefecture_id = p.id;

さらにさらに短くもできる。(innerを省略)

続きを読む →

その９から続きます。

日付と時刻の演算

現在の日付 (current_date)

select current_date();

現在の時刻　(current_timestamp)

select current_timestamp();

n日後の日付

select current_date() + 3;

n日前の日付

select current_date() – 3;

x時間後の時刻

select current_timestamp() + interval 6 hour;

x時間前の時刻

select current_time() – interval 6 hour;

extract 日付や時刻の特定の部分 (年や月)までを取り出す

orders テーブルから注文日時(order_timeカラム)、
2017年01月のレコードを取得する。

select * from orders where extract(year_month from order_time)=201701;

ordersテーブルから
注文日時(order_timeカラムが)、2017年のレコードを取得する。

select * from orders where
extract(year from order_time)=2017;

orders テーブルから注文日時(order_timeカラムが)、
1月のレコードを取得する。

select
*
from
orders
where
extract(month from order_time)=1;

その１０に続きます。