A Guide to Functions in SAP HANA SQL

Functions are an important feature of any database system, as they allow you to perform various operations on data values, such as calculations, transformations, aggregations, or analyses. Functions can also help you to simplify your SQL queries and enhance their functionality and readability.

In this blog post, we will provide a comprehensive guide to functions in SAP HANA SQL, the SQL dialect used by the SAP HANA in-memory database platform. We will explain what functions are, how they are classified, and how they can be used in SAP HANA SQL queries. We will also show you some examples of functions and their syntax in SAP HANA SQL.

What are functions in SAP HANA SQL?

A function is a predefined or user-defined routine that takes one or more input values (arguments) and returns one or more output values (results). A function can be used wherever an expression can be used, such as in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, or JOIN clauses.

Functions can be classified into two categories based on their purpose and behavior:

Scalar functions: These are functions that return a single value for each input value. Scalar functions can be used in any expression that expects a single value. For example, ABS, ROUND, UPPER, SUBSTRING, or CURRENT_DATE are scalar functions.
Aggregate functions: These are functions that return a single value for a group of input values. Aggregate functions can be used in GROUP BY or HAVING clauses to perform calculations or analyses on groups of rows. For example, SUM, AVG, MIN, MAX, or COUNT are aggregate functions.

In addition to these two categories, there are also some special types of functions that have specific functionality or usage:

Window functions: These are functions that perform calculations or analyses over a set of input rows that are defined by a window specification. Window functions can be used in SELECT or ORDER BY clauses to provide advanced analytics over partitions or ranges of rows. For example, RANK, ROW_NUMBER, LAG, LEAD, or CUME_DIST are window functions.
Fulltext functions: These are functions that perform operations on data that has a fulltext index. Fulltext functions can be used in SELECT or WHERE clauses to provide fulltext search capabilities over text data. For example, CONTAINS, SCORE, SNIPPET, or HIGHLIGHT are fulltext functions.
Hierarchy functions: These are functions that allow you to work with hierarchical data such as tables with rows arranged in a tree or directed graph. Hierarchy functions can be used in SELECT or WHERE clauses to provide hierarchical navigation or analysis over parent-child relationships. For example, IS_ANCESTOR_OF, IS_DESCENDANT_OF, IS_LEAF_NODE_OF, or GET_LEVEL_OF are hierarchy functions.
JSON functions: These are functions that return or operate on JSON data. JSON functions can be used in SELECT or WHERE clauses to provide JSON manipulation or extraction capabilities over JSON data. For example, JSON_VALUE, JSON_QUERY, JSON_TABLE, or JSON_MERGE_PATCH are JSON functions.
Series data functions: These are functions that provide special functionality for series data and series tables. Series data is a type of data that consists of ordered sequences of values with timestamps. Series tables are tables that store series data using the SERIES data type. Series data functions can be used in SELECT or WHERE clauses to provide series data manipulation or analysis capabilities over series data. For example, SERIES_ROUND_TIMESTAMP_TO_INTERVAL_START_OF_INTERVAL,
SERIES_DISCRETE_NORMALIZE,
SERIES_FILL,
or SERIES_AGGREGATE are series data functions.

How to use functions in SAP HANA SQL queries?

To use functions in SAP HANA SQL queries, you need to follow the general syntax of:

<function_name> (<argument1>, <argument2>, ...)

(, , ...)

where is the name of the function and , , … are the input values for the function.

Depending on the type and purpose of the function, you may need to specify additional options or clauses for the function.

For example:

For aggregate functions, you may need to specify a DISTINCT option to eliminate duplicate values from the input values.
For window functions, you need to specify an OVER clause with a window specification that defines the set of input rows for the function.
For fulltext functions, you may need to specify a FUZZY option to enable fuzzy search for the input values.
For hierarchy functions, you need to specify a hierarchy name and an optional hierarchy version for the input values.
For JSON functions, you may need to specify a path expression or a format option for the input values.

Here are some examples of using different types of functions in SAP HANA SQL queries:

Scalar functions:

SELECT name, UPPER(name) AS upper_name, ROUND(salary, 2) AS rounded_salary
FROM employees;

1 2	SELECT name, UPPER(name) AS upper_name, ROUND(salary, 2) AS rounded_salary FROM employees;

The example above uses the scalar functions UPPER and ROUND to transform the name and salary columns of the employees table.

Aggregate functions:

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary

FROM employees

GROUP BY department

HAVING COUNT(*) > 10;

The example above uses the aggregate functions COUNT and AVG to calculate the employee count and average salary for each department of the employees table. It also uses a GROUP BY clause to group the rows by department and a HAVING clause to filter the groups by employee count.

Window functions:

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_by_salary
FROM employees
ORDER BY department, rank_by_salary;

SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_by_salary

FROM employees

ORDER BY department, rank_by_salary;

The example above uses the window function RANK to calculate the rank of each employee by salary within each department of the employees table. It also uses an OVER clause with a window specification that defines the partition and order of the input rows for the function.

Fulltext functions:

SELECT title, author, SCORE() AS relevance_score, SNIPPET('text', 'sap hana', FUZZY(0.8)) AS text_snippet
FROM books
WHERE CONTAINS(text, 'sap hana', FUZZY(0.8))
ORDER BY relevance_score DESC;

SELECT title, author, SCORE() AS relevance_score, SNIPPET('text', 'sap hana', FUZZY(0.8)) AS text_snippet

FROM books

WHERE CONTAINS(text, 'sap hana', FUZZY(0.8))

ORDER BY relevance_score DESC;

The example above uses the fulltext functions CONTAINS, SCORE, and SNIPPET to perform a fuzzy fulltext search for the term ‘sap hana’ in the text column of the books table. It also uses a FUZZY option to enable fuzzy search with a similarity threshold of 0.8.

Hierarchy functions:

SELECT product, category, IS_ANCESTOR_OF(category, 'Electronics', 'Product Category') AS is_electronics_ancestor
FROM products;

1 2	SELECT product, category, IS_ANCESTOR_OF(category, 'Electronics', 'Product Category') AS is_electronics_ancestor FROM products;

The example above uses the hierarchy function IS_ANCESTOR_OF to check if each category value in the products table is an ancestor of the value ‘Electronics’ in the hierarchy named ‘Product Category’.

JSON functions:

SELECT id, name, JSON_VALUE(address, '$.city') AS city, JSON_VALUE(address, '$.country') AS country
FROM customers;

1 2	SELECT id, name, JSON_VALUE(address, '$.city') AS city, JSON_VALUE(address, '$.country') AS country FROM customers;

The example above uses the JSON function JSON_VALUE to extract the city and country values from the address column of the customers table. The address column is a JSON column that stores JSON data.

Series data functions:

SELECT id, SERIES_ROUND_TIMESTAMP_TO_INTERVAL_START_OF_INTERVAL(time_series_data, SECONDS 10) AS rounded_time_series_data
FROM sensor_data;

1 2	SELECT id, SERIES_ROUND_TIMESTAMP_TO_INTERVAL_START_OF_INTERVAL(time_series_data, SECONDS 10) AS rounded_time_series_data FROM sensor_data;

The example above uses the series data function SERIES_ROUND_TIMESTAMP_TO_INTERVAL_START_OF_INTERVAL to round the timestamps of the time_series_data column of the sensor_data table to 10-second intervals. The time_series_data column is a SERIES column that stores series data.

Conclusion

In SAP HANA SQL, functions can be classified into two main categories: scalar functions and aggregate functions. There are also some special types of functions that have specific functionality or usage: window functions, fulltext functions, hierarchy functions, JSON functions, and series data functions.

You can use functions in SAP HANA SQL queries by following the general syntax of (, , …). Depending on the type and purpose of the function, you may need to specify additional options or clauses for the function.

We hope that this blog post has helped you to understand functions in SAP HANA SQL. If you have any questions or feedback, please leave a comment below.

Disclaimer: This content is generated by AI.