{"id":110048,"date":"2026-06-25T12:00:00","date_gmt":"2026-06-25T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110048"},"modified":"2026-06-03T09:42:43","modified_gmt":"2026-06-03T09:42:43","slug":"analytics-in-esproc-spl-a-complete-guide","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/analytics-in-esproc-spl-a-complete-guide\/","title":{"rendered":"Analytics in esProc SPL: a complete guide"},"content":{"rendered":"\n

Understand analytics in esProc SPL with this comprehensive guide covering time series analysis, text processing, statistical methods, custom functions, machine learning integration, and interactive dashboards.<\/strong> Part of the “Moving from Python to esProc SPL<\/a>” series.<\/strong><\/p>\n\n\n\n

Data analytics<\/a> has evolved from simple reporting to complex predictive modeling<\/a>, and your choice of tools significantly impacts your analytical capabilities. Whether you’re analyzing sales trends, processing customer feedback, or building predictive models, esProc SPL<\/a> provides the tools you need to transform raw data into actionable insights.<\/p>\n\n\n\n

What makes esProc SPL stand out is its focus<\/em> on data analysis. Unlike general-purpose languages where you might write dozens of lines of code for basic data manipulation<\/a>, esProc SPL offers specialized syntax designed specifically for analytical tasks. This means you can accomplish complex analyses with concise, readable code.<\/p>\n\n\n\n

How time-series analysis works in esProc SPL<\/h2>\n\n\n\n

Time series data<\/a> is everywhere, from stock prices and sales figures to website traffic and sensor readings. Analyzing this data effectively requires specialized techniques, and that’s exactly where esProc SPL comes in.<\/p>\n\n\n\n

How to resample time-series data in esProc SPL<\/h3>\n\n\n\n

Let’s start with a common challenge: you have daily sales data but need to analyze monthly or quarterly trends. This process of changing the frequency of your time series data is called resampling. Here’s how SPL handles it:<\/p>\n\n\n\n

1<\/td>=file(“document\/sales.csv”).import@tc(OrderDate,Amount)   <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

First, I’m importing our sales data from a CSV file, selecting just the order dates and amounts, and sorting everything chronologically. This gives us a clean dataset to work with. The sorting step is crucial for time series analysis because it ensures that our data points are in the correct temporal sequence.<\/p>\n\n\n\n

Now, let’s transform this daily data into monthly aggregates:<\/p>\n\n\n\n

2<\/td>=A1.groups(month@y(OrderDate):month;sum(Amount): sum_amount) <\/td>Group by month and calculate the sum of amounts, it aggregates daily data into monthly totals.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

In the first line, I’m creating a new column called “month” by extracting and combining the year and month from each order date. The `year()` function extracts the year (like “2021”) and the `month()` function extracts the month number (like “1” for January). By combining them with a hyphen, we get identifiers like “2021-1” for January 2021.<\/p>\n\n\n\n

Then, I’m grouping all records by this new month column and calculating the sum of all amounts within each month. The `groups()` function is powerful because it automatically handles the aggregation<\/a> process, similar to a GROUP BY<\/code> in SQL<\/a> but with more flexibility.<\/p>\n\n\n\n

Now we can clearly see the total sales for each month – but what if we want to look at quarterly data instead? Well, we can do that too:<\/p>\n\n\n\n

3<\/td>=A1.groups(year(OrderDate)\/”-Q”\/ceil(month(OrderDate)\/3):quarter;sum(Amount): sum_amount,avg(Amount): avg_amount:) <\/td>Group by quarter and calculate multiple aggregates<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

How to concatenate different data types (such as integers and strings)<\/h3>\n\n\n\n

When concatenating different data types<\/a> such as integers and strings, use the \/ operator. The + operator can only be used for string-to-string concatenation. Here, I’m creating a quarter label by taking the year and adding “Q” plus the quarter number. The quarter number is calculated by dividing the month by 3 and rounding up using the `ceiling()` function. For example:<\/p>\n\n\n

\n