[Redshift] 関数を使用してユーザー情報から次の誕生日を割り出してカウントダウンする

はじめに

登録されたユーザーの生年月日から誕生日までのカウントダウンを表示する方法を書きます。

環境

MacOSX 10.10.5 Yosemite
Redshift 1.0.1125

準備

ユーザー情報テーブル：user_profile

CREATE TABLE IF EXISTS blog;

CREATE TABLE blog.user_profile (
  user_id INTEGER
  , user_name VARCHAR(50)
  , birth DATE
  , sex SMALLINT
  , PRIMARY KEY(user_id)
);

INSERT INTO blog.user_profile VALUES
  (1001,'Abiel','1980-01-01',0)
 ,(1002,'Salome','1990-03-05',0)
 ,(1003,'Cornelius','2000-12-31',1)
 ,(1004,'Joseph','1958-08-29',1)
 ,(1005,'Drusilla','1997-12-13',0);

# select * from blog.user_profile;
 user_id | user_name |   birth    | sex 
---------+-----------+------------+-----
    1001 | Abiel     | 1980-01-01 |   0
    1002 | Salome    | 1990-03-05 |   0
    1003 | Cornelius | 2000-12-31 |   1
    1004 | Joseph    | 1958-08-29 |   1
    1005 | Drusilla  | 1997-12-13 |   0
(5 rows)

左から、ユーザーID、ユーザー名、生年月日、性別。

使用する関数について

TO_CHAR

TIMESTAMP型から指定した日時形式を取得できます。
Amazon Redshift | TO_CHAR 関数

GETDATE

UTC（協定世界時間）で現在日時を取得します。
Amazon Redshift | GETDATE 関数

CONVERT_TIMEZONE

タイムスタンプのタイムゾーンを別のタイムゾーンに変換します。
Amazon Redshift | CONVERT_TIMEZONE 関数

DATEADD

タイムスタンプの値を指定した日時形式で増減できます。
Amazon Redshift | DATEADD 関数

使用例

SQL

SELECT
  GETDATE()
  , TO_CHAR(GETDATE(), 'YYYYMMDD')
  , TO_CHAR(GETDATE(), 'MM-DD')
  , TO_CHAR(GETDATE(), 'hh24/mi/ss')
  , CONVERT_TIMEZONE('JST', GETDATE())
  , DATEADD(year, 1, GETDATE());

実行結果

.
       getdate       | to_char  | to_char | to_char  |  convert_timezone   |      date_add       
---------------------+----------+---------+----------+---------------------+---------------------
 2016-12-14 17:06:31 | 20161214 | 12-14   | 17/06/31 | 2016-12-15 02:06:31 | 2017-12-14 17:06:31
(1 row)

次の誕生日

上記を踏まえて、次の誕生日と今日現在からの日数を作成してみたいと思います。

SQL

SELECT
  user_id
  , user_name
  , birth
  , T.today
 
  -- 次の誕生日を作成
  , CASE -- 生年月日と今日の月日が同じ場合、今日の年月日を作成
         WHEN TO_CHAR(birth,'MMDD') = TO_CHAR(T.today,'MMDD')
         THEN TO_CHAR(T.today,'yyyy-mm-dd')
 
         -- 今年の誕生日が今日より後の場合、今日の年と生年月日の月日を組み合わせ、今年の誕生年月日を作成
         WHEN TO_CHAR(birth,'MMDD') > TO_CHAR(T.today,'MMDD')
         THEN (TO_CHAR(T.today,'YYYY') || '-' || TO_CHAR(birth,'MM-DD'))
 
         -- 今年の誕生日が今日より前の場合、来年の年と生年月日の月日を組み合わせ、来年の誕生年月日を作成
         WHEN TO_CHAR(birth,'MMDD') < TO_CHAR(T.today,'MMDD')
         THEN (TO_CHAR(DATEADD(year,1,T.today),'YYYY') || '-' || TO_CHAR(birth,'MM-DD'))
 
         END next_birthday

  -- 日数のカウントダウンを作成
  , CASE -- 月日が同じ場合は0日を表示
         WHEN TO_CHAR(birth,'MMDD') = TO_CHAR(T.today,'MMDD')
         THEN 0
 
         -- 今年の誕生日が今日より後の場合、今年の誕生年月日を作成し、今日を引く事で日数の差を作成
         WHEN TO_CHAR(birth,'MMDD') > TO_CHAR(T.today,'MMDD')
         THEN TO_DATE(TO_CHAR(T.today,'YYYY') || TO_CHAR(birth,'MMDD'),'YYYYMMDD') - TO_DATE(T.today,'YYYY-MM-DD')
 
         -- 今年の誕生日が今日より前の場合、既に今年の誕生日は迎えているので、
         -- DATEADDで来年の誕生年月日を作成してから、DATEDIFFで今日と比較して日数の差を作成
         WHEN TO_CHAR(birth,'MMDD') < TO_CHAR(T.today,'MMDD')
         THEN DATEDIFF(day
                       ,T.today
                       ,DATEADD(year
                                ,1
                                ,TO_DATE(TO_CHAR(T.today,'YYYY') || TO_CHAR(birth,'MMDD'),'YYYYMMDD')
                                )
                       )
 
         END days

FROM
  blog.user_profile
  , 
  (select convert_timezone('JST', GETDATE()) AS today) AS T
ORDER BY
  user_id;

現在年月日を使用する箇所が複数あり、全部でconvert_timezone...とかやるのは辛いので、FROM句で今日の日付をサブクエリで作成してクロスジョイン。SELECT句で「T.today」として使っています。

実行結果

.
 user_id | user_name |   birth    |        today        | next_birthday | days 
---------+-----------+------------+---------------------+---------------+------
    1001 | Abiel     | 1980-01-01 | 2016-12-15 02:10:05 | 2017-01-01    |   17
    1002 | Salome    | 1990-03-05 | 2016-12-15 02:10:05 | 2017-03-05    |   80
    1003 | Cornelius | 2000-12-31 | 2016-12-15 02:10:05 | 2016-12-31    |   16
    1004 | Joseph    | 1958-08-29 | 2016-12-15 02:10:05 | 2017-08-29    |  257
    1005 | Drusilla  | 1997-12-13 | 2016-12-15 02:10:05 | 2017-12-13    |  363
(5 rows)

さいごに

思っていたよりSQLが面倒になってしまいました。他にもっと効率的かつ見やすい関数の使い方を探してみます。