SELECT

The SELECT statement allows you to specify a list of columns and expressions to be selected from one or more tables.

Basic Syntax

The basic syntax for a SELECT statement is as follows:

  1. SELECT column1, column2, ...
  2. FROM table_name;

Here, column1, column2, etc. refer to the names of the columns from which we want to retrieve data, and table_name refers to the name of the table from which we want to retrieve the data.

This statement selects all the columns from the table specified in the FROM clause. If you want to select all columns from the table, you can use the asterisk (*) wildcard character instead of listing individual columns.

  1. SELECT *
  2. FROM table_name;

Filtering SELECT Statements with WHERE Clause

The WHERE clause is used to filter the results of a SELECT statement based on a specified condition. The syntax for using WHERE clause is as follows:

  1. SELECT column1, column2, ..., columnN
  2. FROM table_name
  3. WHERE condition;

Here, the condition is an expression that evaluates to either true or false. Only the rows that satisfy the condition will be included in the result set.

Examples of WHERE Clause

  1. -- Select all rows from the system_metrics table where idc is 'idc0'
  2. SELECT *
  3. FROM system_metrics
  4. WHERE idc = 'idc0';
  6. -- Select all rows from the system_metrics table where the idc is 'idc0' or 'idc0'
  7. SELECT *
  8. FROM system_metrics
  9. WHERE idc IN ('idc0', 'idc1');

SELECT Statements with LIMIT Clause

The LIMIT clause is used to limit the number of rows returned by a SELECT statement. The syntax for using LIMIT clause is as follows:

  1. SELECT column1, column2, ...
  2. FROM table_name
  3. LIMIT number_of_rows;

Here, the number_of_rows parameter specifies the maximum number of rows to return.

Examples of LIMIT Clause

Here are some examples of using the LIMIT clause:

  1. -- Select the first 10 rows from the system_metrics table
  2. SELECT *
  3. FROM system_metrics
  4. LIMIT 10;

Joining Tables with SELECT Statements

The JOIN clause is used to combine rows from two or more tables based on a related column between them. The syntax for using JOIN clause is as follows:

  1. SELECT column1, column2, ...
  2. FROM table1
  3. JOIN table2
  4. ON table1.column = table2.column;

Here, the table1 and table2 are the names of the tables to be joined. The column is the related column between the two tables.

Please refer to JOIN for more information.

Grouping SELECT Statements with GROUP BY Clause

The GROUP BY clause is used to group the rows in a SELECT statement based on one or more columns. The syntax for using GROUP BY clause is as follows:

  1. SELECT column1, column2, ..., aggregate_function(column)
  2. FROM table_name
  3. GROUP BY column1, column2, ...;

Here, the aggregate_function is a function that performs a calculation on a set of values, such as AVG, COUNT, MAX, MIN, or SUM. The column is the column to group the data by.

Examples of GROUP BY Clause

  1. -- Select the total number of idc for each idc
  2. SELECT idc, COUNT(host) as host_mun
  3. FROM system_metrics
  4. GROUP BY idc;
  6. -- Select the idc's average cpu_util
  7. SELECT idc, AVG(cpu_util) as cpu_avg
  8. FROM system_metrics
  9. GROUP BY idc;

Please refer to GROUP BY for more information.

Example of RANGE query

  1. SELECT
  2.      ts,
  3.      host,
  4.      min(cpu) RANGE '10s',
  5.      max(cpu) RANGE '10s' FILL LINEAR
  6. FROM host_cpu
  7. ALIGN '5s' BY (host) FILL PREV;

Please refer to RANGE QUERY for more information.