SQLite functions

SQLite is a popular database management system that provides various built-in functions to manipulate and retrieve data from the database. These functions can be classified into different categories, including aggregate functions, date and time functions, window functions, string functions, math functions, and JSON functions.

Aggregate functions

Aggregate functions are used to perform calculations on a set of values and return a single result. SQLite provides a variety of aggregate functions such as COUNT, SUM, AVG, MAX, and MIN. These functions are commonly used in queries to retrieve statistics about the data in a table. For example, you can use the COUNT function to count the number of rows in a table, or the SUM function to calculate the total value of a column.

Name	Description
SUM	Calculates the sum of all values in a given column of a table.
AVG	Calculates the average value of a given column of a table.
MAX	Returns the maximum value in a given column of a table.
MIN	Returns the minimum value in a given column of a table.
COUNT	Returns the number of rows that match a specified condition or all rows in a table.
GROUP_CONCAT	Concatenates the values of a given column of a table into a single string, separated by a specified delimiter.
TOTAL	Calculates the sum of all values in a given column of a table, including NULL values.

Date and Time functions

SQLite provides various Date and Time functions to manipulate dates and times. Some of the common date and time functions include DATE, TIME, DATETIME, STRFTIME, and JULIANDAY. These functions allow you to perform operations such as converting dates and times to different formats, extracting components such as the day or month from a date, and calculating the difference between two dates.

Name	Description
DATE	Extracts the date portion of a date/time expression, returning a string in the format “YYYY-MM-DD”.
TIME	Extracts the time portion of a date/time expression, returning a string in the format “HH:MM:SS”.
DATETIME	This function combines a date and time expression into a single string in the format “YYYY-MM-DD HH:MM:SS”.
STRFTIME	This function formats a date/time expression according to a specified format string.
JULIANDAY	This function returns the Julian day number for a given date/time expression.

Window functions

Window functions are used to perform calculations on a specific set of rows in a result set. SQLite supports several window functions, including ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions are commonly used in analytical queries to perform calculations such as running totals, moving averages, and rank order.

Name	Description
ROW_NUMBER	Assigns a unique sequential number to each row within a result set.
RANK	Assigns a rank to each row within a result set based on the value of a specified column. Rows with the same value in the specified column receive the same rank.
DENSE_RANK	This function is similar to RANK() but does not leave gaps in the ranking sequence when there are ties.
NTILE	Divides the result set into a specified number of groups, assigning each row to one of the groups based on the value of a specified column.
LAG	Returns the value of a specified column from the previous row in the result set.
LEAD	Returns the value of a specified column from the next row in the result set.
FIRST_VALUE	Returns the value of a specified column from the first row in the result set.
LAST_VALUE	Returns the value of a specified column from the last row in the result set.

String functions

String functions are used to manipulate strings in the database. SQLite provides several built-in string functions such as SUBSTR, LENGTH, UPPER, LOWER, and REPLACE. These functions allow you to perform operations such as extracting a substring from a string, converting a string to upper or lower case, and replacing a substring with another string.

Name	Description
LENGTH	Returns the length of a string in characters.
UPPER	Converts a string to uppercase.
LOWER	Converts a string to lowercase.
SUBSTR	Extracts a substring from a string based on a specified starting position and length.
REPLACE	Replaces all occurrences of a specified string with another string in a given string.
TRIM	Removes leading and trailing whitespace characters from a string.

Math functions

Math functions are used to perform mathematical calculations on numeric data. SQLite provides various built-in math functions, such as ABS, ROUND, CEIL, FLOOR, and RANDOM. These functions allow you to perform operations such as finding the absolute value of a number, rounding a number to a specific decimal place, and generating a random number.

Name	Description
ABS	Returns the absolute value of a number.
ROUND	Rounds a number to a specified number of decimal places.
CEIL	Returns the smallest integer greater than or equal to a given number.
FLOOR	Returns the largest integer less than or equal to a given number.
EXP	Returns the exponential value of a given number.
LOG	Returns the natural logarithm of a given number.
POWER	Returns the result of raising a given number to a specified power.
SQRT	Returns the square root of a given number.
SIN	Returns the sine value of a given angle.
COS	Returns the cosine value of a given angle.
TAN	Returns the tangent value of a given angle.
ASIN	Returns the arc sine (inverse sine) of a given number.
ACOS	Returns the arc cosine (inverse cosine) of a given number.
ATAN	Returns the arc tangent (inverse tangent) of a given number.

JSON functions

SQLite provides several built-in JSON functions to work with JSON data. Some of the common JSON functions include JSON(), JSON_ARRAY(), JSON_OBJECT(), and JSON_EXTRACT(). These functions allow you to create and manipulate JSON objects and arrays, and extract data from JSON strings.

Name	Description
JSON	Converts a JSON-formatted string into a SQLite value.
JSON_EXTRACT	Extracts a value from a JSON object or array.
JSON_ARRAY	Creates a new JSON array from a list of values.
JSON_OBJECT	Creates a new JSON object from a list of key-value pairs.
JSON_TYPE	Returns the type of a JSON value.

In conclusion, SQLite provides a comprehensive set of built-in functions that enable you to perform various data manipulation and retrieval tasks. Whether you need to perform calculations on numeric data, manipulate strings, or work with date and time data, SQLite’s built-in functions can help you accomplish your goals efficiently and effectively.