導入と目的

ユースケース

モノのインターネット (IoT) というと、動いているモノをコーディネートするものと思われがちですが、実際には多くの企業が静止しているモノを扱っています。実は、この業界では、静止した機器を扱う企業も少なくありません。例えば、ビル設備の消費電力を計測し、節電対策に必要な粒度の時系列データを提供するスマートメータシステムがあります。

目的

直近の7日間の分割した曜日ごとの電力消費量を、過去の全期間における曜日ごとの平均消費量と比較するビジネスレポートを作成します。

このアプローチは、異常を検出し、消費電力のパターンを明らかにし、意図しない高コストにつながる可能性のある消費電力の増減を日常的に監視することを目的としています。

メソッド

この記事では、GridDBデータベースの時系列データを集約に変換する高度なSQLクエリの構築方法を説明します。

このクエリでは、いくつかの重要なSQL時間関数を使用します。これらの関数の使用については、詳細な解説を行います。

このチュートリアルでは、複数の入れ子になったサブクエリを持つクエリを作成する手順を説明し、構造全体の概要を失うことなくサブクエリを結合する方法を解説します。

前提条件

この記事の資料を使用するには、以前のGridDBブログ記事こちらで説明したように、SQLクライアント、例えばSQLWorkbench/Jをインストールする必要があります。

チュートリアル

生データ

スマートメーターは、1分間に2回、タイムスタンプを生成します。データベースには、タイムスタンプ形式のdatetimeと、フロート形式のpowerの2つのカラムしかありません。

データベース内の多くのシングルエントリは、パワーカラムの値が非常に小さく、毎日2,880のエントリが生成されています。集計せずに意味のある結論を出すのはかなり困難です。

一方で、電力消費量が曜日によって異なることは、非常に直感的に理解できます。例えば、私たちの家庭では、平日はそれほど電力を必要としませんが、週末には多くの電力を使用します。逆に、オフィスビルでは、平日は消費量が多く、土日は消費量が少ないことがわかります。このような理由から、私たちはレポートの重要な要素として曜日にこだわっています。

まずは、生のデータを確認してみましょう。

SELECT
  datetime,
  power
FROM
  power2
LIMIT 10

曜日の抽出

GridDBはネイティブSQLの EXTRACT() 関数をサポートしており、年、月、日、曜日などの日付の構成要素を分離するのに役立ちます。

EXTRACT()は、数値形式の値を返します。ここでは、データの集約に曜日 (DOW とも呼ばれる) を使用するので、直接文字列形式に変換します。集計次元は、それ自体が集計可能な形式では許されません。

必要な結果を得るために、クエリの中で一方の関数を他方の関数にラップするだけです。

SELECT
  CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW,
  power
FROM
  power2
LIMIT 10

最初のサブクエリの時間です。SQLは、新しい列を作成したばかりのここで、DOWによるグループ化を許可しません。最初の問い合わせを、さらに集約に使用する新しい問い合わせに「入れ子」にします。

SELECT
  DOW,
  SUM(power) as power
FROM 
  (SELECT 
    power, 
    CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
  FROM power2
  ) AS TEST 
GROUP BY DOW

曜日ごとの平均消費量

データベースに登録されている全期間の消費電力を集計していることを念頭に置く必要があります。1週間だけの消費量と比較することはできません。また、合計の代わりに平均を計算することもできません。これは、テーブル内の単一エントリの平均値を生成しますが、曜日ごとの平均値は生成しないからです。

この問題を解決するには、各曜日がデータの中で何回出てくるかを数えて、それを区切りとして使う必要があります。SQLネイティブのCOUNT()関数を使って行数を数えます。そうすることで、1日に生成されたタイムスタンプの数が得られ、それを2 * 60 * 24で割って日数を求めます。

SELECT
  DOW,
  COUNT(power) / (2 * 60 * 24) AS count_days,
  SUM(power) as power
FROM 
  (SELECT 
    power, 
    CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
  FROM power2
  ) AS TEST 
GROUP BY DOW

そのため、ユニークなDOWをカウントする式を移動させ、曜日ごとの実際の平均消費電力をメインのクエリで直接計算します。

SELECT
  DOW,
  SUM(power) / (COUNT(power) / (2 * 60 * 24)) as power
FROM 
  (SELECT 
    power, 
    CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
  FROM power2
  ) AS TEST 
GROUP BY DOW

この7日間のフィルター

さて、比較の最初の部分が完了したら、次は過去7日間の曜日ごとの消費電力を調べてみましょう。先ほどのクエリを使いますが、日数を計算する必要はありません。各DOWは一度だけ表示されます。

その代わり、データを直近の7日間に限定する必要があります。過去を振り返るには？

ここでは、NOW()を使用します。NOW()は、クエリが実行された瞬間の日付と時刻を持つタイムスタンプを生成します。

さらに、GridDBでサポートされているTIMESTAMP_ADD()関数を利用します。これはタイムスタンプの値を変換するものです。TIMESTAMP_ADD()は、年、月、日、時、秒、ミリ秒の異なる部分を変更することができます。一見、何かを追加するだけのように見えますが、逆のこともできます。そのためには、引数に負の数を指定します。

シンプルなマジック！？

SELECT
  DOW,
  SUM(power) as power
FROM 
  (SELECT 
    power, 
    CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
  FROM power2
  WHERE datetime > TIMESTAMP_ADD(DAY, NOW(), -7)
  AND datetime < = NOW()
  ) AS TEST 
GROUP BY DOW

データをフィルタリングするために、二重のWHERE句を追加しています。万が一、タイムスタンプが未来にあるような奇妙な値を持つデータがあった場合には、上限をNOW()と指定して整理しています。

SQL Joinを使って結果を一致させる

ここでは、SQLのJOINコマンドを使用して2つのクエリを結合し、両方の期間の電力消費量を一致させます。出力結果の列の起源を区別するために、名前の変更を行います。1つ目のクエリの各カラムには末尾に_alltimeを付け、2つ目のクエリのカラムには_lastweekをラベルとして付けます。

SQL JOINでは、サブクエリにもエイリアスを与えて、キーを指定できるようにする必要があります。両方のサブクエリからの値をマッチさせるために使用されるカラムです。

SELECT * FROM

    (SELECT
        DOW as DOW_alltime,
        SUM(power) / (COUNT(power) / (2 * 60 * 24)) as power_alltime
    FROM 
        (SELECT 
            power, 
            CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
        FROM power2
        ) AS TEST_alltime 
    GROUP BY DOW_alltime) as ALLTIME

JOIN

    (SELECT
        DOW as DOW_lastweek,
        SUM(power) as power_lastweek
    FROM 
        (SELECT 
            power, 
            CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
        FROM power2
        WHERE datetime > TIMESTAMP_ADD(DAY, NOW(), -7)
        AND datetime < = NOW()
        ) AS TEST_lastweek 
    GROUP BY DOW_lastweek) as LASTWEEK

ON 

ALLTIME.DOW_alltime = LASTWEEK.DOW_lastweek

最終報告に向けて、準備が整いました。

最終報告書

最終的なテーブルをより読みやすく、可能性のあるアノマリーをより目立たせるために、いくつかの装飾を行います。

• 同じ値なのでDOWコラムの1つを削除します
• DOWの値を人が理解しやすいように名前を変えます
• 最後の週の消費量と全体の平均消費量の差としてデルタ消費量を計算します
• 差異を目立たせて分類しやすくするために、「増加」または「減少」の2つの値だけの列を追加します
• 最後に、列の順序を変更します。一番左のDOWから始まり、新しいセミグループの列、そしてすべての数字の列が続きます

最終的に得られたモンスタークエリを見てください。

SELECT 

    CASE DOW_alltime
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
        WHEN '6' THEN 'Saturday'
        WHEN '0' THEN 'Sunday'
    end
    as day_of_week,
    case 
        when (power_alltime - power_lastweek) > 0 then 'descreased'
        when (power_alltime - power_lastweek) < 0 then 'increased'
        else 'the same'
    end as status,
    power_alltime,
    power_lastweek,
    power_alltime - power_lastweek as power_usage_delta

FROM

    (SELECT
        SUM(power) / (COUNT(power) / (2 * 60 * 24)) as power_alltime,
        DOW as DOW_alltime
    FROM 
        (SELECT 
            power, 
            CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
        FROM power2
        ) AS TEST_alltime 
    GROUP BY DOW_alltime) as ALLTIME

JOIN

    (SELECT
        SUM(power) as power_lastweek,
        DOW as DOW_lastweek
    FROM 
        (SELECT 
            power, 
            CAST(EXTRACT(DAY_OF_WEEK, datetime) as STRING) AS DOW 
        FROM power2
        WHERE datetime > TIMESTAMP_ADD(DAY, NOW(), -7)
        AND datetime < = NOW()
        ) AS TEST_lastweek 
    GROUP BY DOW_lastweek) as LASTWEEK

ON 

ALLTIME.DOW_alltime = LASTWEEK.DOW_lastweek

この種のレポートは、電力消費パターンの望ましくない変化に目を光らせるのに役立ちます。

これにより、特定の曜日に予想外の電力消費があった場合などの異常を迅速に拾うことができます。また、レポートをエクスポートして関係者に送信することで、対策の必要性を認識し、意思決定を促進することができます。

オープンソースのツールを使ってビジネスレポートを作成するメリット

最近では、有料のビジネスインサイトツールが数多く登場していますが、古い（SQLなど）オープンソースや新しい（GridDBなど）オープンソースのツールでも、同じ品質のものを提供することができ、洗練されたレポートを構築するための柔軟性もあります。

GridDBは、時間関数を中心とした広範なSQLコマンドをサポートしています。GridDBは、膨大な量のデータをホストしても、クエリのパフォーマンスがミリ秒単位で遅延することはありません。

最後に

このチュートリアルでは、高度なSQLクエリを書くためのロジックを理解していただき、日常的にSQLクエリを使用していただけることを願っています。

ハッピー・クエリー！

ブログの内容について疑問や質問がある場合は Q&A サイトである Stack Overflow に質問を投稿しましょう。 GridDB 開発者やエンジニアから速やかな回答が得られるようにするためにも "griddb" タグをつけることをお忘れなく。 https://stackoverflow.com/questions/ask?tags=griddb