hero
Home About

Categories

Archive

May 2024

April 2024

March 2024

February 2024

January 2024

December 2023

November 2023

October 2023

September 2023

August 2023

July 2023

June 2023

May 2023

April 2023

March 2023

February 2023

January 2023

December 2022

back to home

The badass guide to data handling - Using SQL to aggregate and join Peakboard Hub List data

14 November 2023 • 4 mins

In today’s article, we will learn how to use SQL statements with functions for aggregation and table join, in order to extract data from Peakboard Hub tables.

We’ve also covered various topics related to Peakboard Hub lists in other articles (check out the links at the bottom of this page).

Why would you want to use aggregation / joins when you can use data flows for data manipulation?

The most obvious reason is to limit network traffic. Let’s say you have 10k+ rows of sensor data, and you want to get the number of rows. Then it doesn’t make sense to download all 10k+ rows just to count them. Instead, you can let Peakboard Hub do the counting, and just download one row, which contains the result.

The same is true with table joins: Let’s assume you have orders and order lines. The order has a date column. But you want to see the order lines of the order on a specific date. Of course, you could download all orders and all order lines, join them, and throw away all order lines that do not belong to orders on your desired date. Or, you can just use a table join to solve this problem on the source side, to avoid transferring too much data over the network.

Aggregate data by using aggregate functions

Let’s take a look at an example of how we can aggregate data by using aggregate functions.

The basis of this example is a simple table of sensor data. Specifically, it’s a list of temperature values. We have two columns:

  • TS - the date and time when the temperature was taken
  • Temperature - the actual temperature

image

Let’s assume we are not interested in all the data. The only thing we want to know (and present on our dashboard later) is the maximum and the minimum temperature that was recorded today. To do that, we can give this SQL statement to the Hub:

select 
min(temperature) as MinTemperature, 
max(temperature) as MaxTemperature
from temperaturedata
where cast(TS as date) = cast(getdate() as date)

To use direct, plain SQL in the Peakboard Hub List data source, you need to activate the SQL mode by clicking on the “Select with SQL” button:

image

Then, you can use the above statement and check your result on the right side. Now, you have condensed thousands of rows down to the two numbers you’re interested in:

image

Joining Peakboard Hub lists

Now, let’s take a look at how we can join two Peakboard Hub lists.

In this example, we have a very simple relational connection between two tables:

  • “Products” contains the products we have in stock. It has a column for the product number, and a column for the quantity of that product in the warehouse.

  • “ProductTexts” contains a brief product description for each product number, in different languages (English, German, and Chinese).

Here’s the “Products” table:

image

And here’s the “ProductTexts” table:

image

In our Peakboard application, we want a single table that contains our current stock quantities for each product and the German description texts. We can do that with this SQL statement:

select p.ProductNo, Quantity, Producttext
from
Products as p inner join ProductTexts as pt
on p.ProductNo = pt.ProductNo
where Language = 'DE'

and here is the result:

image

Conclusion

Preparing data in the source before bringing them into the Peakboard application is a wise choice, because it increases performance and limits network bandwidth. Beside Peakboard Hub lists (as shown in this article), this works with almost any database or SQL-based environment. You should consider this option every time you design a new data source for a Peakboard project.

The badass guide to data handling - Using SQL to aggregate and join Peakboard Hub List data

Newsletter

Want to get notified when a new article is published? Sign up below!