It is a convention in database design and development to avoid storing any data that can be calculated or reconstructed from other fields. Therefore, when building charts in Navicat BI, you may be missing some data. But this is not a problem, as Navicat BI provides calculation fields specifically designed for this purpose. In today's blog, we will use 'calculated fields' to construct a chart that displays the average rental time per customer (i.e. the length of time customers keep movies before returning them).
Obtain customer rental information
As mentioned in previous blog posts in this series, we should create a data source before designing the chart, as we need to specify the data source used for the chart. The data source references tables or files in the connection/ODBC source data, and can select data from tables of different server types. After creating a new data source, we can click on "New Data Source Query" to open the query designer. Here, we can directly write SQL statements in the editor, use a visual query generator, or import queries from Navicat. The following is a SELECT statement that will retrieve customer information, rental amount, movie rental date, and return date:
After saving the query and refreshing the data, we can see all the query fields and result sets:
Now, we can use the rents_date and return_date fields to calculate the lease term. To do this, right-click on return_date in the field list (hold down the Control key on macOS), and then select New Calculated Field from the pop-up menu...:
In the "New Calculation Field" dialog box, you can view many useful functions, including aggregation functions, date time functions, logical functions, and more. We will use the DATEDIFF() function to calculate the number of days between the rental date and return date fields. This function accepts a time unit and start and end dates. We can read the instructions below the function list to learn more information. We will pass a 'D' (day) as the unit and two date fields as follows:
At the bottom of the dialog box, there is a preview that tells us if we have obtained the desired result.
After clicking the 'OK' button, we can see the new fields in the field list and results:
Establish an average leasing time chart for each customer
Since each customer ID is a separate data point, using a scatter plot may be effective. A scatter plot is a data graph that plots a single data point along the X and Y axes. We will use customer-id as the X-axis and rents_duration (Average) as the Y-axis. Just drag the fields to the X-axis and Y-axis fields in the chart designer, aggregate the average values onto rents_duration, and then you can immediately draw the chart!
The following is the complete chart in display mode:
Addendum: Display the number of rentals per customer
雖然平均值很有用，但顯示每位客戶租用一部或多部電影的次數也很有用。為此，我們可以使用聚合函數。我們將計算結果集中的金額條目數，并按 customer_id 進行分組。下面是新計算字段對話框，其中調用了 AGGCOUNT() 函數：
In the chart designer, we will drag the new numb_of_rentals field into the color slot. After adding ascending sorting, the legend items will be sorted in descending order of rental frequency:
We can hover the cursor over a single data point to view its detailed information. At this point, a tool tip will appear displaying the number of rentals, customer ID, and average rental duration:
