Queries the data of columns that match the specified conditions from a streaming data table.

Syntax

SELECT setQuantifier selectItem (',' selectItem)*
      (FROM relation (',' relation)*)
      (WINDOW  windowExpression)
      (WHERE where=booleanExpression)
      (GROUP BY groupBy)
    ;

Parameters for selectItem

Parameter Description Sample statement
* Specifies that all columns in the specified table are queried. SELECT * FROM Test;
columnName Specifies the columns that you want to query. The specified columns support aggregate functions and other settings. For more information, see Column configuration. SELECT gender FROM Test;
AS Specifies the aliases used in the query. SELECT REGISTERTIME AS rtime FROM Test;

Column configuration

Value Description Sample statement
MAX Returns the maximum value of a specified column. SELECT item_type,MAX(price) FROM orders GROUP BY item_type;
MIN Returns the minimum value of a specified column. SELECT item_type,MIN(price) FROM orders GROUP BY item_type;
SUM Returns the sum of values in a specified column. SELECT item_type,SUM(price) FROM orders GROUP BY item_type;
COUNT Returns the number of values in a specified column. SELECT item_type,COUNT(item_id) FROM orders GROUP BY item_type;
AVG Returns the average value of the specified column. You can use SUM(col)/COUNT(*) to obtain the average value. SELECT item_type, SUM(price)/COUNT(*) AS avg_price FROM orders GROUP BY item_type;
LEN(column_name) Returns the length of a specified column. SELECT LEN(column_name) FROM orders;
IFNULL(value1,value2) To return the value of value1 or value2, comply with the following rules:
  • value1 and value2 must be of the same data type.
  • If the value1 field is empty, the value of value2 is returned.
  • If the value1 field is not empty, the value of value1 is returned.
SELECT IFNULL("Hello", "RUNOOB");
TRIM(column_name) Deletes the spaces at the start of a string and the end of the string. SELECT TRIM(column_name) FROM orders;
CONCAT(str1, str2,...) Returns a concatenated string. SELECT CONCAT("Hello", "RUNOOB");
SUBSTRING(string, offset, len) Truncates a part of a string. The value starts from 1. len specifies the truncated length. SELECT SUBSTRING("Hello",1 ,2);
UCASE(column_name) Converts the name of a column to an uppercase string. SELECT UCASE(column_name) FROM orders;
LCASE(column_name) Converts the name of a column to a lowercase string. SELECT LCASE(column_name) FROM orders;

Value description for WHERE clauses

Operator Description Sample statement
= Equal to SELECT * FROM Test WHERE gender = 'FEMALE';
!= Not equal to SELECT * FROM Test WHERE gender != 'FEMALE';
<> Not equal to SELECT * FROM Test WHERE REGISTERTIME <> 1;
> Greater than SELECT * FROM Test WHERE REGISTERTIME > 1;
>= Greater than or equal to SELECT * FROM Test WHERE REGISTERTIME >= 1;
< Less than SELECT * FROM Test WHERE REGISTERTIME < 1;
<= Less than or equal SELECT * FROM Test WHERE REGISTERTIME <= 1;
BETWEEN Returns values between two specified values. SELECT * FROM Test WHERE REGISTERTIME BETWEEN 1 AND 3;
LIKE Searches for the specified pattern in the column that is specified in the WHERE clause. SELECT * FROM Test WHERE USERID LIKE 'USER%';
IS NULL Checks for NULL values. SELECT * FROM Test WHERE USERID IS NULL;
IS NOT NULL Checks for non-NULL values. SELECT * FROM Test WHERE USERID IS NOT NULL;
AND Filters records based on two or more conditions. If the first condition and second condition are met, the AND operator returns a record. SELECT * FROM Test WHERE GENDER = 'MALE' AND REGIONID = 'REGION_1';
OR Filters records based on two or more conditions. If the first condition or the second condition is met, the OR operator returns a record. SELECT * FROM Test WHERE GENDER = 'MALE' OR REGIONID = 'REGION_1';
IN Queries data that matches one of the specified values. SELECT * FROM Test WHERE REGIONID IN (REGION_1','REGION_2');
NOT IN Queries data that does not match all specified values. SELECT * FROM Test WHERE REGIONID NOT IN (REGION_1','REGION_2');

GROUP BY clause description

GROUP BY clauses group query results. The following sample code provides an example of GROUP BY clauses:
SELECT column_name, aggregate_function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name;

WINDOW clause description

You can use WINDOW clauses to specify the method that is used to group data with the same primary key into a window. The window is used for operations such as aggregation and join operations. The window adds the following additional system columns to the data to specify the window boundary: WINDOWSTART and WINDOWEND. The following window types are supported:
  • HOPPING window
    Input data is grouped into fixed-size windows that may overlap based on the timestamps of the data. You must specify the size and sliding distance for a hopping window. You can execute the following statement to create a query for a hopping window: 
    SELECT windowstart, windowend, item_id, SUM(quantity)
  FROM orders
  WINDOW HOPPING (SIZE 20 SECONDS, ADVANCE BY 5 SECONDS)
  GROUP BY item_id;
  • TUMBLING window
    Input data is grouped into fixed-size non-overlapping windows based on the timestamps of the data. You must specify the size of a tumbling window. Tumbling windows are a special case of hopping windows. The size of a tumbling window is equal to the sliding distance. You can execute the following statement to create a query for a tumbling window: 
    SELECT windowstart, windowend, item_id, SUM(quantity)
  FROM orders
  WINDOW TUMBLING (SIZE 20 SECONDS)
  GROUP BY item_id;
  • Session window
    If you want to group input data into sessions, you must specify an interval during which no event appears for the session window. For example, you can set the interval during which no event appears to 5 minutes. If a primary key such as alice is specified and no new input data is received for more than 5 minutes, the current session of alice is closed. When the subsequent data of alice is received, a new session is started. You can execute the following statement to create a query for a session window: 
    SELECT windowstart, windowend, item_id, SUM(quantity)
  FROM orders
  WINDOW SESSION (20 SECONDS)
  GROUP BY item_id;