Complying with the SQL 92 standard, SQL functions in Simple Log Service include linear transformation functions, aggregate functions, window functions, and custom functions of Simple Log Service, such as time series functions, AI algorithm analysis functions, and log clustering functions.
This topic describes the SQL syntax and various types of functions used in Simple Log Service.
SQL syntax
group by
- Query the values of IP addresses after geohash encoding
- Query the project write latency in service logs
order by
case when and if
- Query the information about non-200 requests
- Query the proportion of Tomcat error requests
- Sort and display based on pv for htpt_user-agent
having
join
- Query data by joining two Logstores
- Convert an array to a string, find the difference set of two arrays, and find the maximum and minimum values in an array
nested subqueries
ANY/SOME
unnest
Functions
General comparison functions
- Check whether a value is null or within a range
- Check whether two values are unequal or equal and obtain the maximum and minimum values from multiple columns
General aggregate functions
- Compare the PVs of today with those of yesterday based on NGINX access logs.
- Distribution of source IP addresses from NGINX logs
- Collect statistics on the inbound and outbound NGINX traffic
- Query the top 10 addresses that access NGINX
- Predict PVs based on NGINX access logs
- PPredict PVs based on NGINX access logs
- Analyze the trend of Tomcat request status
- Query the changes of PVs and UVs for Tomcat access over time
- tomcat Query the number of Tomcat error requests and comparison with that of last hour
- Query the top 10 URIs in Tomcat requests
- Query the types and distribution of Tomcat clients
- Query the global distribution of clients
- Query the distribution of request methods
Date and time functions
- Obtain the current date, time, date and time, time zone, local time, and local timestamp
- Parse the format of a string to convert it into a timestamp, and calculate the interval between two time values
- Collect statistics on the inbound and outbound NGINX traffic
- Query the daily consumption and trend prediction for this month
- The cost of each product in the last three months
- Predict expenses for the next three months
String functions
- Convert the case, calculate the string length, and reverse a string
- Obtain the minimum edit distance between two strings, supplement or truncate a string to length n, remove the leading or trailing spaces from a string, truncate a string from a field, and find the position where a string starts
- Obtain the start position of a string in another string, remove the leading and trailing spaces from a string, and split a string into an array
- Delete or replace a part of a string
- Replace or delete values in a field or split a field value into an array
JSON functions
- Convert a string to the JSON format
- Convert JSON data to a string and check whether a string contains a specific value
- Query the value of the element with the specified index and the size of a JSON array
Complex functions
Array functions
Map
Regular expression functions
URL functions
- Obtain parameters in a URL by using a URL function
- Query the fragment, host, path, port, and protocol from a URL
- Query the query condition from a URL and encode or decode a URL
Mathematical calculation functions
Mathematical statistics functions
- Query the correlation of two columns
- Query the population covariance and sample covariance of the pre-tax profit and pre-tax amount from bills
- Query the sample standard deviation and population standard deviation of the pre-tax income
- Query the sample variance and population variance of the pre-tax income
Approximate functions
- Estimate the values for percentiles 0.5 and 0.9
- Predict 95th quantile of I/O latency
- Predict CPU utilization
- Predict trend cycle curves
- Predict network latency
- Predict network traffic
- 2SPred
- Predict PVs
Interval-valued comparison functions and periodicity-valued comparison functions
- Query the PV trend and day-to-day comparison from NGINX access logs
- Display yesterday's consumption and year-on-year comparison with last month
Window function
- Display the proportion of consumption for each product this month
- Query the top 30 URIs with largest PVs per minute
- Query the maximum value in a window
- Query the minimum value in a window
- Query a specific value in a window
- Query the values after the specified line in a window and return the default value if no value is available
- Query the values before the specified line in a window and return the default value if no value is available
Bitwise functions
Geospatial functions
- Construct a point
- Construct a point, line, or polygon
- Construct a spatial geometric entity and convert a spatial geometric entity into the WKT format
- Obtain the polygon at a specific distance from a geometric entity
IP geolocation functions
- Query the values of IP addresses after geohash encoding
- Convert an IP address to a geohash code based on the latitude and longitude
Security check functions
- Check IP address security
- Collect statistics on the number of times that a domain is securely accessed per minute
- Collect statistics on the number of times that a URL is securely accessed per minute