基本的な集約関数/日付関数等の勉強をしました。
備忘として残しておきます。
以下のテーブルで実践しようと思います。
日付型については、DATETIMEで指定しています。GETDATE関数を使用する事で現在の日時を取得を行えます。
INSERTの際に、CREATE_DATA_TIMEカラムにGETDATE()で日時の格納を行いました。
CREATE TABLE VANILLA(
code INT UNIQUE,
level INT,
attack INT,
defense INT,
name VARCHAR(30),
attribute VARCHAR(10),
type VARCHAR(10),
CREATE_DATA_TIME DATETIME NOT NULL,
UPDATE_DATA_TIME DATETIME);
GO
INSERT INTO VANILLA (code,level,attack,defense,name,attribute,type,create_data_time) VALUES (1,3,1300,2000,'岩石の巨兵','地','岩石',GETDATE());
INSERT INTO VANILLA (code,level,attack,defense,name,attribute,type,create_data_time) VALUES (2,4,1500,1200,'幻獣王ガゼル','地','獣',GETDATE());
INSERT INTO VANILLA (code,level,attack,defense,name,attribute,type,create_data_time) VALUES (3,3,600,1500,'闇・道化師のサギー','闇','魔法使い',GETDATE());
INSERT INTO VANILLA (code,level,attack,defense,name,attribute,type,create_data_time) VALUES (4,4,1500,1200,'ワイバーンのせんし','地','獣',GETDATE());
INSERT INTO VANILLA (code,level,attack,defense,name,attribute,type,create_data_time) VALUES (5,4,1200,1500,'ルイーズ','地','獣戦士',GETDATE());
GO
下記のようなデータが作成された事を確認しました。
CREATE_DATA_TIMEに登録された値の180日前/180日後の取得を行います。
日付の計算は、DATEADD関数を使用する事で行えます。
DATEADD(要素,数値,対象データ) が構文になります。
今回の場合は、DATEADD(d,-10,CREATE_DATE_TIME)/DATEADD(d,10,CREATE_DATE_TIME) で日付の取得を行います。dで指定している部分を、m(月)/yy(年)に変更する事で月単位等での計算も行えます。
1> SELECT CREATE_DATA_TIME,DATEADD(d,-10,CREATE_DATA_TIME) AS Time1 , DATEADD(d,10,CREATE_DATA_TIME) AS Time2 FROM VANILLA;
2> GO
CREATE_DATA_TIME Time1 Time2
----------------------- ----------------------- -----------------------
2022-07-31 22:28:26.393 2022-07-21 22:28:26.393 2022-08-10 22:28:26.393
2022-07-31 22:28:26.393 2022-07-21 22:28:26.393 2022-08-10 22:28:26.393
2022-07-31 22:28:26.393 2022-07-21 22:28:26.393 2022-08-10 22:28:26.393
2022-07-31 22:28:26.393 2022-07-21 22:28:26.393 2022-08-10 22:28:26.393
2022-07-31 22:28:26.397 2022-07-21 22:28:26.397 2022-08-10 22:28:26.397
1> SELECT CREATE_DATA_TIME,DATEADD(m,-1,CREATE_DATA_TIME) AS Time1 , DATEADD(m,1,CREATE_DATA_TIME) AS Time2 FROM VANILLA;
2> GO
CREATE_DATA_TIME Time1 Time2
----------------------- ----------------------- -----------------------
2022-07-31 22:28:26.393 2022-06-30 22:28:26.393 2022-08-31 22:28:26.393
2022-07-31 22:28:26.393 2022-06-30 22:28:26.393 2022-08-31 22:28:26.393
2022-07-31 22:28:26.393 2022-06-30 22:28:26.393 2022-08-31 22:28:26.393
2022-07-31 22:28:26.393 2022-06-30 22:28:26.393 2022-08-31 22:28:26.393
2022-07-31 22:28:26.397 2022-06-30 22:28:26.397 2022-08-31 22:28:26.397
お次に、日付の差を取得する為にDATADIFF関数を使用します。
前準備で type/UPDATE_DATA_TIMEカラム をUPDATE文で更新しています。
DATEDIFF関数は、DATEDIFF(要素,対象データ1,対象データ2) が構文となります。
今回の場合は、DATEDIFF(d,CREATE_DATA_TIME,UPDATE_DATA_TIME) で日付の差を取得します。
UPDATE VANILLA SET type = '戦士族',UPDATE_DATA_TIME = DATEADD(d,1,GETDATE()) Where code = 1;
UPDATE VANILLA SET type = '魔法使い族',UPDATE_DATA_TIME = DATEADD(d,1,GETDATE()) Where code = 2;
UPDATE VANILLA SET type = '悪魔族',UPDATE_DATA_TIME = DATEADD(d,1,GETDATE()) Where code = 3;
UPDATE VANILLA SET type = '獣戦士族',UPDATE_DATA_TIME = DATEADD(d,1,GETDATE()) Where code = 4;
UPDATE VANILLA SET type = '獣族',UPDATE_DATA_TIME = DATEADD(d,1,GETDATE()) Where code = 5;
SELECT
CREATE_DATA_TIME,
UPDATE_DATA_TIME,
DATEDIFF(d,CREATE_DATA_TIME,UPDATE_DATA_TIME) as DIFF
FROM VANILLA;
GO
CREATE_DATA_TIME UPDATE_DATA_TIME DIFF
----------------------- ----------------------- -----------
2022-07-31 22:28:26.393 2022-08-01 23:09:20.513 1
2022-07-31 22:28:26.393 2022-08-01 23:09:20.520 1
2022-07-31 22:28:26.393 2022-08-01 23:09:20.523 1
2022-07-31 22:28:26.393 2022-08-01 23:09:20.527 1
2022-07-31 22:28:26.397 2022-08-01 23:09:20.527 1
最後に集約関数について
この辺は業務でたまに弄ります。平均を出したい場合にAVGや行数を求めるCOUNT等が存在しています。
試しに、attack値/defense値の平均値を取得してみます。
1> SELECT AVG(attack) AS avg_attack,AVG(defense) AS avg_defense FROM VANILLA;
2> go
avg_attack avg_defense
----------- -----------
1220 1480
(1 行処理されました)
COUNT関数を使用して、attributeカラムが'地'の値となっている行の取得を行います。
1> SELECT COUNT(attribute) AS type_1 FROM VANILLA WHERE attribute = '地';
2> GO
type_1
-----------
4
(1 行処理されました)
あとはMAX/MINをよく使ってました。
言葉通りですが、最大値/最小値の取得が出来ます。
1> SELECT MAX(attack) AS max_attack,MIN(attack) AS min_attack FROM VANILLA;
2> GO
max_attack min_attack
----------- -----------
1500 600
(1 行処理されました)
集約関数は業務でも使用する事がありました。レコード件数調べる必要がある時に使ってましたね。
COUNT(*)でやってましたが、今考えるとNOT NULLで制約設けているカラムでやった方が良かったなと思います。
確かテーブル定義書なくて、こういうやり方だったのかな？集計速度とか違いそうです。今度検証してみようかな、、
以上です。何かの参考になれば、幸いです。