What's New Qiita

20201023のMySQLに関する記事は5件です。

  • 投稿日：2020-10-23T21:37:27+09:00

SQLで日付の範囲データを作成する（MySQL）

はじめに

日付の開始日と終了日だけで、間の日付を埋めたいときってありますよね？...はい、きっとあるはずです！

例えば「ある商品の売上トランザクションデータを持っており、これを〇年〇月〇日～〇年〇月〇日までの各日で何点ずつ売れたのか、売れなかった日も含めて一覧を作りたい」といった場合、このトランザクションデータとLEFT JOINできる日付のマスタがあると便利ですよね。

今回はSQLのみで、始点と終点の日付から、間の日付を穴埋めした日付データを作る方法を書いておきます。

検証バージョン

MySQL 5.6

DATE_ADD関数のINTERVALを使うことで実現する

基本的な考えはDATE_ADD関数を用いて+1ずつ日付を足していく

-- 例えば、DATE_ADD関数を用いて1日加算すると下記の結果が得られる
SELECT DATE_ADD('2020-10-01', INTERVAL 1 DAY) as DATE;
+--------------------+
| DATE               |
+--------------------+
| 2020-10-02         |
+--------------------+

これを繰り返し加算すればよいので、加算するための表を作成する

CREATE TABLE number (
    number smallint
)
;
-- [0-9]までのデータを入れる
insert into number values (0);
insert into number values (1);
insert into number values (2);
insert into number values (3);
insert into number values (4);
insert into number values (5);
insert into number values (6);
insert into number values (7);
insert into number values (8);
insert into number values (9);

上記の表からSELECTした結果をDATE_ADD関数のINTERVALにセットしていくSQLを書けばよい

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM number;
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |
| 2020-10-05 |
| 2020-10-06 |
| 2020-10-07 |
| 2020-10-08 |
| 2020-10-09 |
| 2020-10-10 |
+------------+

最後にほしい日付の範囲に絞る

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM number
WHERE  DATE_ADD('2020-10-01', INTERVAL number DAY) BETWEEN '2020-10-01' AND '2020-10-05';
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |
| 2020-10-05 |
+------------+

これで完成です！が、これだと日付の範囲が10日分しか作れないので、numberテーブルのデータを拡張するVIEWを作る

-- ひとまず[0-999]の1000行のVIEWを作成
CREATE VIEW vw_number AS
SELECT a.number + (b.number * 10) + (c.number * 100) AS number
FROM number a, number b, number c;

上記のVIEWには1000行のデータが入っているので3年近くは作れます（必要に応じて拡張してください）

SELECT DATE_ADD('2020-10-01', INTERVAL number DAY) as DATE FROM vw_number
WHERE  DATE_ADD('2020-10-01', INTERVAL number DAY) BETWEEN '2020-10-01' AND '2022-10-01';
+------------+
| DATE       |
+------------+
| 2020-10-01 |
| 2020-10-02 |
| 2020-10-03 |
| 2020-10-04 |

| 2022-09-28 |
| 2022-09-29 |
| 2022-09-30 |
| 2022-10-01 |
+------------+

その他、DATE_ADD関数のINTERVALを使っているので月単位に変更することも簡単

SELECT DATE_FORMAT(DATE_ADD('2020-10-01', INTERVAL number MONTH), '%Y-%m') as MONTH FROM vw_number
WHERE  DATE_ADD('2020-10-01', INTERVAL number MONTH) BETWEEN '2020-10-01' AND '2022-10-01';
+---------+
| MONTH   |
+---------+
| 2020-10 |
| 2020-11 |
| 2020-12 |
| 2021-01 |

| 2022-07 |
| 2022-08 |
| 2022-09 |
| 2022-10 |
+---------+

これならトランザクションデータにLEFT JOIN出来る日付マスタを簡単に作れますね！

おわり

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-10-23T15:11:35+09:00

MySQl練習帖①

雑

コマンド羅列

create

CREATE TABLE テーブル名 (カラム名 型(桁数) オプション, カラム名 型(桁数) オプション...);

mysql> CREATE TABLE user (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20) NOT NULL,
    -> email VARCHAR(255) UNIQUE,
    -> age INT,
    -> gender TINYINT COMMENT '1が男、2が女',
    -> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

alter

mysql> ALTER TABLE (テーブル名) MODIFY (カラム名) （変更後）;

insert

INSERT INTO テーブル名 (カラム1, カラム2, ...カラムn) VALUES (カラム1の値, カラム2の値, ...カラムnの値);

valuesはカンマ区切りで複数レコードを１クエリで挿入できる

delete

select

SELECT (カラム名) FROM (テーブル名) WHERE （条件）;

論理演算子

  • AND(&&)
  • OR (||)
  • NOT

不等号

  • =
  • !=
  • >, >=
  • <. <=
  • BETWEEN a AND b (a < b)

LIKE

%をワイルドカードとして部分一致検索に使える

mysql> SELECT * FROM people WHERE name LIKE '%こ';

IN

リストで検索

WHERE カラム名 IN (値1, 値2, 値3...値N);

DISTINCT

SELECT DISTINCTで重複カラムを削除して取得できる。

order by

asc, desc 昇順、降順に並び替えできる

JOIN

join文の種類によって対応する値がない場合の扱いが異なる

INNNER JOIN

部分集合

LEFT JOIN

左側のテーブルは全て表示（対応する値がないセルはNULLになる）

RIGHT JOIN

LEFT JOINの右版

集計関数

  • 基本的にGROUP BYと組み合わせて集計を行う。
  • selectできるのはGROUP BYしたカラムのみ。
  • カラムみたいな扱いができる。

MIN(), MAX()

最大値。最小値

AVG()

平均

SUM()

総和

細かいところ

  • テーブル名・カラム名が予約語とかぶるときはバッククォート(　`　)を使う
  • NULLを検索したいときは=ではなくISを使う
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-10-23T14:02:51+09:00

MySQLデータ型まとめ

MySQLとは

DBMS（DataBase Management System）のひとつ、高速でポピュラー

データ型

詳細は次章で

  • 整数(TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT)
  • 浮動小数点
  • 固定小数点
  • BIT
  • 日付・時刻(DATE, TIME, DATETIME, TIMESTAMP, YEAR)
  • CHAR・VARCHAR
  • BINARY・VARBINARY
  • BLOB・TEXT
  • ENUM
  • SET

詳細説明

整数

TINYINT

8bitで表現できる整数。

  • 符号付きで -128 ~ 127
  • 符号なしで 0 ~ 255

の整数を格納できる。

SMALLINT

16bitで表現できる整数。

  • 符号付きで -32768 ~ 32767
  • 符号なしで 0 ~ 65535

の整数を格納できる。

MEDIUMINT

24bitで表現できる整数。

  • 符号付きで -8388608 ~ 8388607
  • 符号なしで 0 ~ 16777215

の整数を格納できる。

INT

32bitで表現できる整数。

  • 符号付きで -2147483648 ~ 2147483647
  • 符号なしで 0 ~ 4294967295

の整数を格納できる。

BIGINT

64bitで表現できる整数。

  • 符号付きで -9223372036854775808 ~ 9223372036854775807
  • 符号なしで 0 ~ 18446744073709551615

の整数を格納できる。

浮動小数点

小数点付きの数値を表現できる。桁数や仮数部の大きさにより誤差が生じてしまう場合がある(参考)

FLOAT

32bitで表現できる符号付き小数

  • 符号 : 1bit
  • 指数 : 8bit
  • 仮数 : 23bit

で構成される。
およそ7桁くらいの小数を表現できる。

DOUBLE

64bitで表現できる符号付き小数

  • 符号 : 1bit
  • 指数 : 11bit
  • 仮数 : 52bit

で構成される。
およそ16桁くらいの小数を表現できる。

固定小数点

予め小数点以下の桁数を指定するため精度が高い

DECIMAL[(M[,D])]

M : 精度。合計桁数 (max65)
D : 小数点以下の桁数 (max30)

± （ 10 ^　M \div 10^D ）

の範囲を表現できる

BIT

ビット型。1~64bitのbit値を格納できる

日付・時刻型(DATE, TIME, DATETIME, TIMESTAMP, YEAR)

DATE

日付
'1000-01-01' から '9999-12-31'
フォーマット ： YYYY-MM-DD

DATETIME[(fsp)]

日付と時間の組み合わせ
'1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999'
フォーマット ： YYYY-MM-DD HH:MM:SS[.fraction]

TIMESTAMP[(fsp)]

タイムスタンプ
TIMESTAMP 値は、エポック ('1970-01-01 00:00:00' UTC) からの秒数
1970-01-01 00:00:01.000000 UTC から 2038-01-19 03:14:07.999999 UTC
フォーマット ：

TIME[(fsp)]

時間
'-838:59:59.000000' から '838:59:59.000000'
フォーマット ： HH:MM:SS[.fraction]

YEAR[(2|4)]

2 桁または 4 桁の形式の年
4 桁の形式では、値は 1901 から 2155 と 0000
2 桁の形式では、値は 70 から 69 ( 1970 から 2069 に相当)
フォーマット ： YYYY または YY

CHAR・VARCHAR

CHARは余った文字数を空白文字で埋める
→末尾スペースは取得時に削除される

BINARY

char/ varcharのバイナリデータ版

TEXT

varcahrの文字数指定しなかった版でOK

BLOB

varbinaryの文字数指定しなかった版

ENUM

文字列のリストを予め定義し、その中の文字列のみ格納できる。NULLも格納できる。
リストの先頭からインデックスが割り振られ、内部的にはインデックスが格納されている。

SET

ENUMの複数要素選択できる版

参考文献

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-10-23T00:15:30+09:00

[Rails] find と find_by の違い

この記事ではmacOS Catalina10.15.6にインストールしたRuby 2.6.5を使っています。
findとfind_byについて曖昧なところがあったので、自分なりにまとめてみました。

find

  • 引数にはid(主キー)を指定します。
  • 指定したidのレコードを取得するというイメージです。
  • idが見つからない時は例外(RecordNotFound)が返って来ます。赤いエラーページが発生してしまうのですね。
  • 引数を複数指定することも可能です。
モデル名.find(idデータ)
  • 具体的には以下のように使います。
Item.find(1) # => idが1のレコードを返す
Item.find(1,3,5) # => 指定したidを配列で返す

find_by

  • データは１番最初に合致した1件のみ取得できます。
  • id以外のカラム名なども検索条件にできます。
  • 条件に合致するものがないとnilを返します。取得データがなくてもfind_byの記述以降も処理を続けたい時に便利ですね。
  • 条件は複数指定可能です。
モデル名.find_by(条件)
  • 具体的には以下のように使います。
Article.find_by(title: 'hoge') 
# => titleが'hoge'で最初に合致したデータを返す

Fruit.find_by(name: 'apple', color: 'red') 
# => nameが'apple'でcolorが'red'のデータを返す

2つ条件を指定して1つだけ合致しなかった場合

Fruit.find_by(name: 'apple', color: 'blue') #=> nilを返す
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-10-23T00:15:30+09:00

find と find_by の違い

この記事ではmacOS Catalina10.15.6にインストールしたRuby 2.6.5を使っています。
findとfind_byについて曖昧なところがあったので、自分なりにまとめてみました。

find

  • 引数にはid(主キー)を指定します。
  • 指定したidのレコードを取得するというイメージです。
  • idが見つからない時は例外(RecordNotFound)が返って来ます。赤いエラーページが発生してしまうのですね。
  • 引数を複数指定することも可能です。
モデル名.find(idデータ)
  • 具体的には以下のように使います。
Item.find(1) # => idが1のレコードを返す
Item.find(1,3,5) # => 指定したidを配列で返す

find_by

  • データは１番最初に合致した1件のみ取得できます。
  • id以外のカラム名なども検索条件にできます。
  • 条件に合致するものがないとnilを返します。取得データがなくてもfind_byの記述以降も処理を続けたい時に便利ですね。
  • 条件は複数指定可能です。
モデル名.find_by(条件)
  • 具体的には以下のように使います。
Article.find_by(title: 'hoge') 
# => titleが'hoge'で最初に合致したデータを返す

Fruit.find_by(name: 'apple', color: 'red') 
# => nameが'apple'でcolorが'red'のデータを返す

2つ条件を指定して1つだけ合致しなかった場合

Fruit.find_by(name: 'apple', color: 'blue') #=> nilを返す
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧