[Redshift] 関数を使用してユーザー情報の生年月日から現在の年齢、曜日を表示する

はじめに

登録されたユーザーの生年月日から年齢と曜日を表示する方法について、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、ユーザー名、生年月日、性別。

使用する関数について

DATEDIFF

指定した年月日時と年月日時の期間を知る事ができます。
Amazon Redshift | DATEDIFF 関数

SQL使用例

SELECT
  birth
  , getdate()
  , DATEDIFF(year,     birth, getdate()) AS year
  , DATEDIFF(month,    birth, getdate()) AS month
  , DATEDIFF(week,     birth, getdate()) AS week
  , DATEDIFF(day,      birth, getdate()) AS day
  , DATEDIFF(hour,     birth, getdate()) AS hour
  , DATEDIFF(minute,   birth, getdate()) AS minute
  , DATEDIFF(second,   birth, getdate()) AS second
  , DATEDIFF(millisec, birth, getdate()) AS millisecond
  , DATEDIFF(microsec, birth, getdate()) AS microsecond
FROM
  blog.user_profile
ORDER BY
  user_id;

第１引数に何を抽出したいか指定するだけです。
上記ではgetdate()でUTC現在日時を指定していますね。

実行結果

.
   birth    |       getdate       | year | month | week |  day  |  hour  |  minute  |   second   |  millisecond  |   microsecond    
------------+---------------------+------+-------+------+-------+--------+----------+------------+---------------+------------------
 1980-01-01 | 2016-12-14 14:39:52 |   36 |   443 | 1928 | 13497 | 323942 | 19436559 | 1166193592 | 1166193592000 | 1166193592000000
 1990-03-05 | 2016-12-14 14:39:52 |   26 |   321 | 1397 |  9781 | 234758 | 14085519 |  845131192 |  845131192000 |  845131192000000
 2000-12-31 | 2016-12-14 14:39:52 |   16 |   192 |  832 |  5827 | 139862 |  8391759 |  503505592 |  503505592000 |  503505592000000
 1958-08-29 | 2016-12-14 14:39:52 |   58 |   700 | 3042 | 21292 | 511022 | 30661359 | 1839681592 | 1839681592000 | 1839681592000000
 1997-12-13 | 2016-12-14 14:39:52 |   19 |   228 |  992 |  6941 | 166598 |  9995919 |  599755192 |  599755192000 |  599755192000000
(5 rows)

EXTRACT

こちらも引数に指定して抽出します。
Amazon Redshift | EXTRACT 関数

SQL使用例

SELECT
  birth
  , EXTRACT(year FROM birth) AS year
  , EXTRACT(dow FROM birth) AS days_of_week
FROM
  blog.user_profile
ORDER BY
  user_id;

上記の様に「dow」などと指定すると曜日を数値で取得できます。
0〜6の数が日曜〜土曜までを表します。

実行結果

.
   birth    | year | days_of_week 
------------+------+--------------
 1980-01-01 | 1980 |            2
 1990-03-05 | 1990 |            1
 2000-12-31 | 2000 |            0
 1958-08-29 | 1958 |            5
 1997-12-13 | 1997 |            6
(5 rows)

現在の年齢と生年月日の曜日を作成

以上を踏まえて年齢と曜日を作成してみたいと思います。

SQL

SELECT
  user_id
  , user_name
  , birth
  , T.today

  -- 曜日番号から曜日を作成。
  , CASE EXTRACT(dow FROM birth)
         WHEN 0 THEN 'sun'
         WHEN 1 THEN 'mon'
         WHEN 2 THEN 'tue'
         WHEN 3 THEN 'wed'
         WHEN 4 THEN 'thu'
         WHEN 5 THEN 'fri'
         WHEN 6 THEN 'sat'
		 END dow

  -- 期間（年）
  , DATEDIFF(YEAR, birth, T.today) AS year_diff

  -- 年齢
    -- 「生年月日の年」と「今日の年」の「年数の差」を作成。
  , DATEDIFF(YEAR, birth, T.today) +
    -- 今年の誕生日と今日を比較して、誕生日が来ていなければ上記の「年数の差」から1を引く。
    (CASE EXTRACT(doy FROM TO_DATE(TO_CHAR(T.today,'YYYY')||TO_CHAR(birth,'MMDD'),'YYYYMMDD')) > EXTRACT(doy FROM T.today)
          WHEN TRUE  THEN -1 -- 「今年の誕生日 >  今日」なので、まだ誕生日が来ていないので「年数の差」に-1する。
          WHEN FALSE THEN 0  -- 「今年の誕生日 =< 今日」なので、既に誕生日が来ているので「年数の差」はそのまま。
          END) AS age
FROM
  blog.user_profile
    -- JSTの日時を作成してクロスジョインする。
    CROSS JOIN (SELECT TRUNC( CONVERT_TIMEZONE('JST',GETDATE()) ) AS today) AS T
ORDER BY
  user_id;

曜日は、関数EXTRACTで取得した数値をCASEで分岐させて曜日の文字列に置換しています。
年齢は、DATEDIFFで年数の差を作成していますが、あくまでも年数の差ですので、SQLでも解説している様に、誕生日が来ていなければ1を引く必要があります。

実行結果

.
 user_id | user_name |   birth    |   today    | dow | year_diff | age 
---------+-----------+------------+------------+-----+-----------+-----
    1001 | Abiel     | 1981-03-01 | 2016-12-18 | sun |        35 |  35
    1002 | Salome    | 2000-02-29 | 2016-12-18 | tue |        16 |  16
    1003 | Cornelius | 2000-12-31 | 2016-12-18 | sun |        16 |  15
    1004 | Joseph    | 1958-08-29 | 2016-12-18 | fri |        58 |  58
    1005 | Drusilla  | 2000-12-13 | 2016-12-18 | wed |        16 |  16
(5 rows)

「年数の差」だけのカラムyear_diffとageを比べると、誕生日が来ていないuser_id=1003は-1されました。

さいごに

年齢と曜日を割り出したい場合が有るという話を聞くので今後も使えるのではと思っています。