Although we often think of the Internet of Things<\/strong> as something that coordinates moving entities. In fact, many companies in the industry deal with stationary devices. One example is a smart metering system that measures power consumption in a building facility and delivers time series data in the granularity that allows developing power-saving strategies.<\/p>\n We will create a business report that compares power consumption in the last seven days split per day of the week against the average consumption per day of the week in the whole previous period of time.<\/p>\n This approach aims to detect anomalies, unveil power consumption patterns, and routinely monitor increases and decreases in power consumption that may result in an unintended high cost.<\/p>\n The article will explain how to build an advanced SQL query that transforms time series data from the GridDB<\/strong> database into an aggregation.<\/p>\n The query will use a few essential SQL time functions. A detailed commentary on their utilization will be provided.<\/p>\n The tutorial will walk you through the process of creating a query with multiple nested subqueries and explain how to combine them without losing an overview of the whole structure.<\/p>\n To use the materials from this article, you need to install an SQL client, for instance, SQLWorkbench\/J as described in one of the previous GridDB blog posts here<\/a>.<\/p>\n The smart meters produce a timestamp twice in a minute. The database has only two columns: datetime in the timestamp format and power in a float format.<\/p>\n Many single entries in the database have very small values in the power column, and there are 2,880 entries being produced each day. It is quite challenging to make any meaningful conclusions without an aggregation.<\/p>\n On the other side, it seems quite intuitive that power consumption varies depending on the day of the week: we do not need much power during the week at our homes, but on the weekend, we use a lot of it. On the contrary, the office building would demonstrate high consumption on the weekdays and low consumption on Saturday and Sunday. That’s why we stick to the day of the week as an important dimension in our report.<\/p>\n Let us first check the raw data, though.<\/p>\nObjectives<\/h3>\n
Methods<\/h3>\n
Prerequisites<\/h3>\n
Tutorial<\/h1>\n
Raw Data<\/h2>\n
SELECT \n datetime, \n power \nFROM \n power2 \nLIMIT 10\n<\/code><\/pre>\n<\/div>\n