SQL is a powerful data manipulation language (DML) that can be used to perform complex calculations and format data within your database. The Views Raw SQL module enables you to use SQL on your fields in your Drupal view. This is useful when using Drupal as a data platform for reports and data analysis from your external database. In this post I focus on calculations and functions, data formatting and user defined functions.
Views Raw SQL
Before you can start using SQL in your fields, you will need to download and install the Views Raw SQL module. Once the module is enabled, you will also need to activate the Show the SQL query option in Views settings. The path to views settings is:
You should now see the generated SQL when editing any view.
When using SQL for calculations you need to use the field names as show in the views preview.
Calculations and Functions
The Views Raw SQL module page has an example of the complex calculations possible using SQL. The sample calculation described on the module page reads, “the sum of two fields multiplied by the square root of another field”. Apart from the obvious addition, subtraction, division and multiplication. Most mathematical operators including log, cos and tan will be available. I would advise you to spend time studying your database documentation to get the full list. MariaDB’s list of operators can be found here.
Relational databases have many functions to perform different data manipulation tasks. In data analysis and reporting the most common functions I use include:
- Coalesce to get the first none NULL value
- Concat function to combine the output of different fields
- Round to round of results of calculated values
- Convert function to convert data from one type to another. Two most common reasons for converting data are:
- Data is stored in character type but you need it in a numeric type to perform mathematical calculations
- You need to sort using the field
If you are using Drupal as data platform you can perform all types of calculations and make use of functions available in your database server. Below is a SQL statement showing some basic calculations.
SELECT DATE_FORMAT(d.InvoiceDate, '%Y-%M') AS Calendar_Year ,c.Name AS Genre_Name ,concat(b.GenreId, ' - ', c.Name) AS GenreId_Name ,a.Quantity ,(a.Quantity * a.UnitPrice) AS Actual_Price FROM InvoiceLine a JOIN Track b ON a.TrackId = b.TrackId JOIN Genre c ON b.GenreId = c.GenreId JOIN Invoice d ON a.InvoiceId = d.InvoiceId
Having consistent data formatting standards is an important aspect of Business Intelligence(BI). As an example, it is important to settle on a date format across all reports and data extracts. If you don’t, each developer will have their own format and confuse users. Measures, such as total sales, used across the organisations also need to be standardised by rounding off to a consistent number of decimal places. Data formatting can be done in Drupal using field formatters however if you are using Drupal as a reporting platform on large data sets it is best to do field formatting in the database. Below is snippet showing dates being formatted in SQL.
DATE_FORMAT(InvoiceDate, '%Y-%M') --YYYY-Monthname DATE_FORMAT(InvoiceDate, '%Y-%m') --YYYY-MM
User defined functions (UDF)
Inconsistent presentation of data can cause confusion and give rise to questions about the integrity of the data. To avoid spending time analysing the differences between reports pulled from the same database I suggest using UDFs to standardise some of the commonly performed calculations. A UDF is a database function that can be used in SQL statements. Instead of letting everyone perform the calculation for the actual amount after tax, you can create UDF that users can call. The snippet below defines a function in MariaDB what calculates actual amount for an order line in the Chinook database.
CREATE FUNCTION actual(q INT, c DECIMAL)RETURNS DECIMAL(5, 3)DETERMINISTICRETURN (q*c)*0.86;
The advantage with this approach is UDFs are accessible from different applications accessing the database. The data analysts using application such as Tableau and MS Excel can make use of the UDF. Using the actual function within a Drupal view is no different from using it from any other SQL client. Snippet below shows function being called.
SELECT actual(Quantity, UnitPrice) AS actual_amountFROM InvoiceLine
The ability to use native database functions in your Drupal view gives you access to a powerful set of mathematical and data formatting functions to massage your data for analysis and presentation. In cases where you are connecting to a large data warehouse it is best to let the relational database server perform the calculations and data formatting. UDFs provide you with an abstraction layer you can use to standardise calculations and data formatting across reports pulled from your database. Needless to say when using the Views Raw SQL module you have to be careful with who you grant permission to use this module. I would also suggest connecting to your external database with a database user with read only permission.