アドバイザー開発チームのぐりこ ( @glico800 ) です。
普段良く使っている Redash にて Query Results Data Source という機能を使ったところ、ちょっと罠を踏み抜いたので簡単に解説を残しておこうと思います。
Query Results Data Source とは?
複数のクエリ結果を結合したクエリを書くことができる機能です。
主に異なる Data Source を使ったクエリ同士を結合するのに使います。
MySQL の関数が使えない問題
ビザスクでは RDBMS として MySQL を主に使っているので、Redash 上でクエリを書くときも基本的に MySQL を使います。
しかし、Query Results を Data Source に指定してクエリを書くと、NOW() や CONVERT_TZ() のような MySQL の関数がエラーになってしまいます。
SQLite で書かないといけなかった
社内で聞いてみたところ「Query Results は Redash が用意している Data Source なので単に Query Results の RDBMS が MySQL じゃないのかも」と教えてもらいました。
調べてみると公式にちゃんと「SQLite 使ってるよ」と書いてありました(うっかり)
Redash uses an in-memory SQLite database to make this possible. As a result, queries against large result sets may fail if Redash runs out of memory.
https://redash.io/help/user-guide/querying/query-results-data-source
MySQL → SQLite の書き換え
SQLite だ分かったのは良かったのですが、私は SQLite は全然書いたことがなくちんぷんかんです。
そこで、個人的に普段 MySQL でクエリを書くときによく使う表現を SQLite ではどう書くのか調べてみました。
現在日時の取得
-- MySQL
SELECT NOW(),
CURRENT_TIMESTAMP,
LOCALTIME,
LOCALTIMESTAMP;
-- SQLite
SELECT datetime('now'),
CURRENT_DATE,
CURRENT_TIMESTAMP,
CURRENT_TIME;
日時フォーマット
-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m'),
DATE_FORMAT('2021-01-02 03:04:05', '%Y-%m-%d %H:%i:%s')
-- SQLite
SELECT strftime('%Y-%m', 'now'),
strftime('%Y-%m-%d %H:%M:%S', '2021-01-02 03:04:05')
関数名も引数の順番もフォーマット指定の引数の仕様もちょっとずつ違うので注意が必要です。
日時の加減算
○ヶ月前、○年前のような過去の日付や○ヶ月後、○年後のような未来の日付を計算する方法です。
-- MySQL
SELECT NOW() - INTERVAL 1 YEAR,
NOW() + INTERVAL 1 YEAR,
'2021-01-02 03:04:05' - INTERVAL 2 MONTH,
'2021-01-02 03:04:05' - INTERVAL 2 DAY;
-- SQLite
SELECT datetime('now', '-1 year'),
datetime('now', '+1 year'),
datetime('2021-01-02 03:04:05', '-2 month'),
datetime('2021-01-02 03:04:05', '-2 day')
タイムゾーンの変更(UTC→JST)
タイムゾーン周りは他にもやり方があると思うのですが、ここでは簡単な方法を紹介します。
-- MySQL
SELECT CONVERT_TZ(NOW(), '+00:00','+09:00'),
CONVERT_TZ('2021-01-02 03:04:05', '+00:00', '+09:00');
-- SQLite
SELECT datetime('now', '+9 hour'),
datetime('2021-01-02 03:04:05', '+9 hour');
割合を求める
-- MySQL
SELECT 30 / 256 * 100 AS `Ratio`;
-- SQLite (NG)
SELECT 30 / 256 * 100 AS `Ratio`;
SQLite で割合のような小数を含む計算をするとき、そのまま書くと整数に丸められてしまいます。
-- SQLite (OK)
SELECT cast(30 as REAL) / cast(256 as REAL) * 100 AS `Ratio1`,
round(cast(30 as REAL) / cast(256 as REAL) * 100, 1) AS `Ratio2`;
cast 関数を使って浮動小数点数(REAL)に変換してあげることで正しく計算ができるようになります。小数点以下の桁数を指定する場合はさらに round 関数を使います。