Decentriq LogoDOCS

Decentriq platform capabilities: Supported SQL Features

Referring to release 1.6 Updated last: 06.12.21

Datatypes

  • VARCHAR

  • INT
    - 
    BIGINT

  • FLOAT

  • NULL
    values are supported

    • NULL
      literal in expressions not yet supported

  • NOT NULL
    annotation supported

Aggregation functions

  • SUM

  • AVG

  • MAX

  • MIN

  • COUNT

  • STDEV

  • STDEVP

Window and ranking functions

  • ROW_NUMBER

  • RANK

  • PERCENT_RANK

  • PERCENTILE_CONT
    (can be used for MEAN)

  • NTILE

  • RANDOM() OVER()
    (To generate a random value)

Scalar functions

  • LENGTH(string)
    -> To get the number of characters of a string

  • ROUND( float_number , n_of_decimals[OPTIONAL] )
    → To round a float number to a give n. of decimals. If the second parameter is not given, it returns an integer. Please note that it can be applied only on columns and cannot be used on aggregation functions

Supported syntax clauses table

Function

Description

Syntax example

SELECT & FROM

Full support

SELECT a, b, c FROM TEST

WILDCARD operator

supports "*"

SELECT * FROM test

WHERE clause

Full support for WHERE clause

SELECT a
FROM test
WHERE b >= 4

GROUP BY

GROUP BY full support

SELECT column1, column 2, COUNT(*)
FROM table
GROUP BY column1, column 2

ORDER BY

You can now order the output rows by one or more columns

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ...
ASC|DESC

JOINs: INNER, LEFT, RIGHT, FULL OUTER, CROSS

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN for joining tables. only supports equality conditions for the ON operator. Rows with NULL keys are treated as if there was no match.

SELECT table1.column, table2.column
FROM table1 LEFT JOIN table2
ON table1.key = table2.key

Fuzzy matching

fuzzystrmatch(column1, column2, length, unique = false)
where 
length
is the Levenshtein distance and the optional 
unique
controls if all or only the best match are returned. Supported on FROM statements. Supports AND clause.
- If unique = false, all rows match where column1 and column2 have Levenshtein distance of at most length.
- If unique = true, then for each element in column1 only the element in column2 with the lowest Levenshtein distance matches. If multiple have the same lowest Levenshtein distance, only the first match is returned. If all are larger than length, no element matches.

SELECT *
FROM table1 INNER JOIN table2
ON table1.last_name = table2.last_name AND
fuzzystrmatch(table1.first_name, table2.first_name, 4, true)

UNION & UNION ALL

It is possible to combine different SELECT statement with same column number and similar column type

SELECT FROM table1
UNION
SELECT FROM table 2

Aliasing of columns, tables and subqueries

Columns, tables and queries can be renamed with the 'AS' clause

SELECT a AS alias
FROM TEST

CAST operator

Cast operator - limited for: BIG_INT, DOUBLE and VARCHAR

SELECT CAST(10 as DOUBLE)
FROM test

CASE operator

CASE operator in SELECT clauses (must include an ELSE clause)

SELECT CASE WHEN a = '1' THEN 'it is one' WHEN a = '2' THEN 'it is two' ELSE 'i do not know' END
FROM test

IN operator

IN operator in the where clause - no support for subquery

SELECT a
FROM test
WHERE b IN (1, 2, 3)

BETWEEN operator

BETWEEN operator in the where clause

SELECT a
FROM test
WHERE b BETWEEN 10 AND 20

Subquery in FROM statement

SELECT a FROM
(SELECT b FROM test)

Subquery

Subquery using WITH clause

WITH subquery_table as (... SUBQUERY ....)
SELECT ... FROM subquery_table

DISTINCT

Support for the DISTINCT clause on the SELECT

SELECT DISTINCT a
FROM table_A

Further functionalities of the platform

  • Queries can use the output of another query in the same data clean room in the 

    from
    statement, to simplify the writing of complex and nested queries

#QUERY_1
SELECT sum(col_a) AS a
FROM table1

#QUERY_2
SELECT a
FROM QUERY_1
WHERE a>5