【SQL】DECLARE文で、オリジナルの変数を定義しよう！

クエリ内で、自分が定義した変数が使えたらな～

なんて考えたことはありませんか？
実は、そんな夢を叶えてくれる構文が、SQLには用意されているのです！

きょうは、クエリ内で変数を定義できるDECLARE文について紹介します！

1 DECLAREを使いこなそう！

DECLAREを使いこなそう！

DECLAREとは

DECLAREとは、クエリ内で変数を定義することができる文のことです。
以下、変数定義の流れと実際の書き方です。

DECLAREで変数名と変数のデータ型（STRING, DATETIMEなど）を定義
↓
SETで変数に値を格納

DECLAREの構文

■定義したい変数が1つの場合

DECLARE 変数名 データ型;
SET 変数名 = 好きな値;

SELECT
    変数名

■定義したい変数（どちらも同じデータ型）が2つの場合

DECLARE 変数名1, 変数名2 データ型;
SET 変数名1 = 好きな値1;
SET 変数名2 = 好きな値2;

SELECT
    変数名1,
    変数名2

■定義したい変数（どちらも異なるデータ型）が2つの場合

DECLARE 変数名1 データ型1;
DECLARE 変数名2 データ型2;
SET 変数名1 = 好きな値1;
SET 変数名2 = 好きな値2;

SELECT
    変数名1,
    変数名2

※ DECLARE文とSET文は、共に文末に「；」をつけないとエラーになるので注意！

DECLAREを使ったクエリ例

以下の2つのテーブルから、2022年1月1日~2022年1月3日の、日別の登録者数と購入者数を横並びに抽出するとします。

id	name	user_created_date
1	太郎	2019-04-06
2	よしお	2019-04-06
3	SAKI	2019-04-06
...	...	...
22300	ジム	2022-03-26

usersテーブル（毎日1人以上はユーザー登録があるものとします）

id	item	purchased_date
1	干し柿	2019-04-06
2	干し柿	2019-04-06
3	干し柿まんじゅう	2019-04-07
...	...	...
5600	幻の干し柿	2022-3-26

purchasesテーブル（たまに購入数が0の日もあるものとします）

もしDECLAREを使わないなら、以下のようなクエリになります。

WITH
u AS (
    SELECT
        user_created_date,
        COUNT(id) AS n_of_users
    FROM
        users
    WHERE
        user_created_date BETWEEN "2022-01-01" AND "2022-01-03"
    GROUP BY
        user_created_date
),

p AS (
    SELECT
        purchased_date,
        COUNT(id) AS n_of_purchases
    FROM
        purchases
    WHERE
        purchased_date BETWEEN "2022-01-01" AND "2022-01-03"
    GROUP BY
        purchased_date
)

SELECT
    u.user_created_date AS days,
    u.n_of_users,
    p.n_of_purchases
FROM
    u
    LEFT OUTER JOIN  p
        ON u.user_created_date = p.purchased_date
ORDER BY
    days asc

ただ、これだと各テーブル定義部分で、

BETWEEN "2022-01-01" AND "2022-01-03" を2か所に書く必要がある
後で別の日付を指定したいときに、2か所両方修正する必要がある

というデメリットがあり、手間とミスの可能性の上昇につながります...。

次に、DECLAREを使ったクエリが、以下になります。

DECLARE d1, d2 DATETIME;
SET d1 = "2022-01-01";
SET d2 = "2022-01-03";

WITH
u AS (
    SELECT
        user_created_date,
        COUNT(id) AS n_of_users
    FROM
        users
    WHERE
        user_created_date BETWEEN d1 AND d2
    GROUP BY
        user_created_date
),

p AS (
    SELECT
        purchased_date,
        COUNT(id) AS n_of_purchases
    FROM
        purchases
    WHERE
        purchased_date BETWEEN d1 AND d2
    GROUP BY
        purchased_date
)

SELECT
    u.user_created_date AS days,
    u.n_of_users,
    p.n_of_purchases
FROM
    u
    LEFT OUTER JOIN  p
        ON u.user_created_date = p.purchased_date
ORDER BY
    days asc

先ほどのクエリと見比べてみると、

クエリ冒頭で、DECLAREとSETで変数が定義されている
各テーブル定義部分で、WHERE句内の日時がd1とd2に入れ替わっている

という違いがあります。

このように、DECLAREを使った方が、同じ文を何回も書く必要がないし、別の日付を指定したいときも、冒頭の変数に格納する値を1か所変えるだけで済むので、効率的ですね！

分析時によく使うデータ型

最後に、以下によく使う変数のデータ型をまとめておきます。
データ分析時に変数定義するときは、「自分が何のデータ型を定義しようとしているのか」を意識してみてください！

/*文字列の変数定義*/
DECLARE a STRING; 
SET a = "ほしがき";

/*数字の変数定義*/
DECLARE b INT64;
SET b = 123;

/*日付の変数定義*/
DECLARE c DATE;
SET c = "2022-01-01";

/*日時の変数定義*/
DECLARE d DATETIME;
SET d = "2022-01-01 23:59:59";

最後まで読んでいただいて、ありがとうございました！
また次の記事でお会いしましょう(^^)/