SQL Functions

Description

sql functions section is comprised of aggregate functions, string and binary functions, datetime functions, conditional functions, numeric functions and spatial functions. The configuration helps Insights formulate the correct SQL statement for various operations.

Each configuration contains the function name for your database, with index-based parameters if applicable (e.g $0, $1 etc)

Available functions

Aggregate functions

1. count

count the number of values in a column,default value: count($0)

2. count distinct

count the number of distinct values in a column, default value: COUNT(DISTINCT $0)

3. sum

find the sum of values in a column, default value: SUM($0)

4. avg

find the average of values in a column, default value: AVG($0)

5. max

find the maximum value in a column, default value: MAX($0)

6. min

find the minimum value in a column, default value: MIN($0)

7. stddev

find the standard deviation of a column, default value: STDDEV($0)

8. variance

find the variance of a column, default: VARIANCE($0)

9. median

find the median value of column, default: MEDIAN($0)

10. mode

find the mode value of a column, default: MODE($0)

11. percentile

find the N-th percentile of a column

$0: percentile $1: column name default value: PERCENTILE_CONT($0) WITHIN GROUP (ORDER BY $1)

String and binary functions

1. trim

trim empty spaces from both sides of a string. Default: TRIM(BOTH $0 FROM $1)

2. left trim

trim empty spaces from left side of a string. Default: TRIM(LEADING $0 FROM $1)

3. right trim

trim empty spaces from right side of a string. Default: TRIM(TRAILING $0 FROM $1)

4. lower

convert string to lower case. Default: LOWER($0)

5. upper

convert string to upper case. Default: UPPER($0)

6. string concatenation

concatenate two strings. Default: CONCAT($0, $1)

7. string position

find the index of $0 in $1. Position starts from 1. Default: POSITION($0, $1)

8. string position starting with initial position

find the index of $0 in $1, starting from index $2. Default: POSITION($0, $1, $2)

9. string length

find the number of characters in a string $0. Default: LENGTH($0)

10. string substitute

find substring $1 in string $0, and replace it with $2. Default: REPLACE($0, $1, $2)

11. regexp replace

find regexp pattern $1 in string $0, and replace it with $2. Default: REGEXP_REPLACE($0, $1, $2)

12. regexp like

check if string $0 is like regexp pattern $1. Default: REGEXP_LIKE($0, $1)

13. substring

find the substring of $0, starting from index $1, and ending at $2. Default: SUBSTRING($0, $1, $2)

14. left

find the left-most $1 characters of string $0. Default: LEFT($0, $1)

15. right

find the right-most $1 characters of string $0. Default: RIGHT($0, $1)

16. like

check if string $0 is like template $1. Default: $0 LIKE $1

Datetime functions

1. extract date

extract the date part of a timestamp $0. Default: CAST($0 AS DATE)

2. extract time

extract the time part of a timestamp $0. Default: CAST($0 AS TIME)

3. extract year

extract the year part of a timestamp or date $0. Default: DATEPART(YEAR, $0)

4. extract quarter

extract the quarter part of a timestamp or date $0. Default: DATEPART(QUARTER, $0)

5. extract month

extract the month part of a timestamp or date $0. Default: DATEPART(MONTH, $0)

6. extract day of month

extract the day of month of a timestamp or date $0. Default: DATEPART(DAY, $0)

7. extract day of week

extract the day of week of a timestamp or date $0. Default: DATEPART(WEEKDAY, $0)

8. extract hour

extract hour of a timestamp or date $0. Default: DATEPART(HOUR, $0)

9. extract minute

extract minute of a timestamp or date $0. Default: DATEPART(MINUTE, $0)

10. extract second

extract second of a timestamp or date $0. Default: DATEPART(SECOND, $0)

11. datedif year

find the difference in years between two timestamp $0 and $1. Default: DATEDIFF(YEAR, $0, $1)

12. datedif month

find the difference in month of two timestamp $0 and $1. Default: DATEDIFF(MONTH, $0, $1)

13. datedif day

find the difference in days of two timestamp $0 and $1. Default: DATEDIFF(DAY, $0, $1)

14. datedif hour

find the difference in hours of two timestamp $0 and $1. Default: DATEDIFF(HOUR, $0, $1)

15. datedif minute

find the difference in minutes of two timestamp $0 and $1. Default: DATEDIFF(MINUTE, $0, $1)

16. datedif second

find the difference in seconds of two timestamp $0 and $1. Default: DATEDIFF(SECOND, $0, $1)

17. timedif hour

find the difference in hours between two time $0 and $1. Default: TIMEDIFF(HOUR, $0, $1)

18. timedif minute

find the difference in minutes between two time $0 and $1. Default: TIMEDIFF(MINUTE, $0, $1)

19. timedif second

find the difference in seconds between two time $0 and $1. Default: TIMEDIFF(SECOND, $0, $1)

20. create timestamp from milliseconds

Create a timestamp object from milliseconds $0 that have passed since 1970-01-01 00:00:00.

Default: TO_TIMESTAMP($0/1000.0)

21. create timestamp from components

Create a timestamp from the following date and time components: $0: year, $1: month, $2: day, $3: hour, $4: minute, $5: second Default:

TO_TIMESTAMP($0 || '-' || $1 || '-' || $2 || ' ' || $3 || ':' || $4 || ':' || $5,
      CASE WHEN length($0) > 2 THEN 'YYYY-MM-DD hh24:mi:ss' ELSE 'YY-MM-DD hh24:mi:ss' END)

21. create timestamp from text

Create a timestamp object from a text $0 based on the format $1.

Default: TO_TIMESTAMP($0, ‘$1’)

22. time from hours minutes seconds

Create a time object from $0 (hours), $1(minutes) and $2(seconds).

Default: TO_TIME( $0 || ':' || $1 || ':' || $2, 'hh24:mi:ss')

23. create time from milliseconds

Create a time object from the milliseconds that have elapsed since midnight.

Default: TO_TIME($0/1000.0)

25. create date from milliseconds

Create a date object from the milliseconds that have passed since 1970-01-01 midnight.

Default: TO_DATE($0/1000.0)

26. create date from components

Create a date object from $0: year, $1: month, $2: day.

Default: TO_DATE($0 || '-' || $1 || '-' ||$2, CASE WHEN length($0) > 2 THEN 'YYYY-MM-DD' ELSE 'YY-MM-DD' END)

27. current timestamp

query the current timestamp. Default: CURRENT_TIMESTAMP

28. current date

query the current date. Default: CURRENT_DATE

29. now

Alternative to query the current timestamp. Default: NOW()

30. timestamp add interval

add a specified number of intervals to timestamp $0. The number to add is $1, and the unit of interval is $2.

Default value: none

Example: For Google Bigquery, TIMESTAMP_ADD($0, INTERVAL $1 $2)

31.** time add interval**

add a specified number of intervals to time $0. The number to add is $1, and the unit of interval is $2.

Default value: none

Example, for Google BigQuery, TIME_ADD(TIME($0), INTERVAL $1 $2)

32. generate time series

Generate a series of rows starting from timestamp/date $0, ending at $1. The step is defined by the number of steps $2 and the interval type $3.

Default: none

Example: For Google BigQuery UNNEST(GENERATE_TIMESTAMP_ARRAY($0, $1, INTERVAL $2 $3))

33.** dateadd year**

add years ($1) to a date $0. Default: DATEADD(YEAR, $1, $0)

34. dateadd month

add months ($1) to a date $0. Default: DATEADD(MONTH, $1, $0)

35. dateadd day

add days ($1) to a date $0. Default: DATEADD(DAY, $1, $0)

36. dateadd hour

add hours ($1) to a date $0. Default: DATEADD(HOUR, $1, $0)

37. dateadd minute

add minutes ($1) to a date $0. Default: DATEADD(MINUTE, $1, $0)

38. dateadd second

add seconds ($1) to a date $0. Default: DATEADD(SECOND, $1, $0)

39. timeonlyadd hour

add hours ($1) to a time $0. Default: DATEADD(HOUR, $1, $0)

40. timeonlyadd minute

add minutes ($1) to a time $0. Default: DATEADD(MINUTE, $1, $0)

41. timeonlyadd second

add seconds ($1) to a time $0. Default: DATEADD(SECOND, $1, $0)

Conditional functions

1. case when

default: CASE WHEN $0 THEN $1 END

2. case when else

default: CASE WHEN $0 THEN $1 ELSE $2 END

3. is null

default: $0 IS NULL

4. is not null

default: $0 IS NOT NULL

5. nullif

returns null if $0 is equal to $1, otherwise it returns $0. Default: NULLIF($0, $1)

6. ifnull

if $0 is null, then return $1, otherwise it returns $0. Default: IFNULL($0, $1)

7. coalesce

returns $0 if it is not null, otherwise it returns $1. Default: COALESCE($0, $1)

8. and

default: $0 AND $1

9. or

default: $0 OR $1

10. between

test if a number $0 is between $1 and $2. Default: $0 BETWEEN $1 AND $2

11. not negate a condition. Default: NOT($0)

Numeric Functions

1. abs

find the absolute value of a number $0. Default: ABS($0)

2. acos

find the acosine value of a number $0. Default: ACOS($0)

3. asin

find the ASINE value of a number $0. Default: ASINE($0)

4. atan

find the ATAN value of a number $0. Default: ATAN($0)

5. sin

find the SINE value of a number $0. Default: SIN($0)

6. sinh

find the SINH value of a number $0. Default: SINH($0)

7. cos

find the COS value of a number $0. Default: COS($0)

8. cosh

find the COSH value of a number $0. Default: COSH($0)

9. tan

find the TAN value of a number $0. Default: TAN($0)

10. tanh

find the TANH value of a number $0. Default: TANH($0)

11. ln

find the natural Log value of a number $0. Default: LN($0)

12. log

find the $1-based logarithm value of a number $0. Default: LOG($1, $0)

13. log10

find the 10-based log value of a number $0. Default: LOG10($0)

14. negation

find the negated value of a number $0. Default: -($0)

15. add

add two numbers $0 and $1. Default: ($0) + ($1)

16. subtract

subtract two numbers $0 and $1. Default: ($0) – ($1)

17. multiply

multiply two numbers $0 and $1. Default: $0 * $1

18. divide

divide two numbers $0 and $1. Default: $0/NULLIF($1, 0)

19. power

find the value of $0 raised to the power of $1. Default: POWER($0, $1)

20. mod

Perform the modulo operation between $0 and $1. Default: MOD($0, $1)

21. round

returns the nearest INTEGER of number $0. Default: ROUND($0)

22. round to

find the nearest numeric value of number $0 at specified decimal place $1. Default: ROUND($0, $1)

23. ceiling

returns the smallest integer that is greater than number $0. Default: CEIL($0)

24. floor

returns the greatest integer that is smaller than number $1. Default: FLOOR($0)

25. sign

returns the sign of a number $0. 1 means positive and -1 means negative. Default: SIGN($0)

26. truncate

returns a number truncated to the specified decimal places $1. Default: TRUNC($0, $1)

Conversion functions

1. cast

CAST input $0 to type $1. Default: CAST($0 AS $1)

Spatial functions

1. st_asbinary

returns the wkb of geometry $0

2. st_geometryFromText

converts the WKT string $0 to geogmetry

3. st_geographyFromText

converts the WKT string $0 to geography

4. st_intersects

used in where clause to determine of $0 and $1 intersects

Example: ST_INTERSECTS($0, $1), or ST_INTERSECTS($0, $1) = 1

5. st_within

used in where clause to determine of $0 is within $1.

6. st_centroid

Find the centroid of shape column $0.

7. st_distance_sphere

Find the geodetic distance between $0 and $1.

8. linestring

Create a linestring from point $0 and point $1, with a specified srid (optional) $2

9. make_point

Create a point using longitude $0, latitude $1 and srid (optional) $2.

10. geodesic length

Find the geodesic length or perimeter of shape $0.

11. geodesic area

Find the geodesic area of polygon $0.

12. st_buffer

Find the geodesic buffer of shape $0.

13. st_x

Find the x or longitude of point $0

14. st_y

Find the y or latitude of point $0

15. st_transform

Project the shape $0 to another SRID $1.