そーす

福岡在住。iOS/Androidアプリ, Webフロントエンドのエンジニアです。Swift, Kotlin, JavaScript, ReactNative

データが無い日をUnionで補完する

BIツールなどを使ってグラフとかを日付順に出したい時に

「今日の〇〇のデータは無いけど△△では今日のデータあるので〇〇のグラフも今日の表示が欲しい」

みたいなことがある場合の対処法です。

Unionでデータを追加する

DBにデータを入れるわけには行かないのでクエリの結果にのみ入れるようにします。

例えばpaymentsというテーブルから過去1ヶ月分の日毎の支払件数を出したい場合は、

SELECT date(paid_at) AS date,
       count(amount) AS COUNT
FROM payments
WHERE paid_at IS NOT NULL
  AND date(paid_at) >= date_sub(CURRENT_DATE, interval 1 MONTH)
GROUP BY date
ORDER BY date DESC

こんな感じでとりあえずはとれます。

ただ、今日取引が無かった場合はグラフには表示されません(X軸は昨日までの表記になる)

そこでUNIONを使ってダミーデータを追加します。

SELECT date(t.paid_at) AS date,
       count(t.amount) AS COUNT
FROM (
        (SELECT paid_at,
                amount
         FROM payments
         WHERE paid_at IS NOT NULL)
      UNION
        (SELECT CURRENT_DATE AS paid_at,
                                NULL AS amount)) t
WHERE t.paid_at IS NOT NULL
  AND date(t.paid_at) >= date_sub(CURRENT_DATE, interval 1 MONTH)
GROUP BY date
ORDER BY date DESC

ポイントはUNIONはカラム数カラム名を同じにしなければならないことです。 ダミーデータの方をpaymentsテーブルのカラムに合わせてもよいのですが、 カラムが多いと面倒なので、元のpaymentsテーブルも必要なカラムのみをSELECTするようにしたほうが楽なんじゃないかなと思います。

UNIONじゃなくて他にも方法あるのかなー