SELECT (FROM MATERIALIZED VIEW)

Syntax

SELECT
   expression AS alias [, ...]
FROM relation_name
[WHERE where_clause]
[GROUP BY group_by_clause]
[HAVING having_clause]
[ORDER BY order_by_clause [ASC | DESC] [NULLS [FIRST | LAST]] ]
[LIMIT number];

Description

The SELECT statement is used to retrieve data from a #_materialized_view.

Arguments

FROM <relation_name>

The FROM clause specifies the source Materialized View for the query. A query on a Materialized View can refer to a single Materialized View. Currently, join is not supported between a Materialized View and other relations. The Materialized View name can be specified as a fully, or partially, qualified name via specifying the database_name and/or schema_name in the format [<database_name>.<schema_name>.]<materialized_view_name> (for example,db1.public.pagevisits). Otherwise, the current Database and Schema will be used to identify the Materialized View.

WHERE <where_clause>

Records can be filtered according to supplied predicates in the WHERE clause. Predicates are specified as Boolean expressions, which can use columns from the source Materialized View.

GROUP BY <group_by_clause>

This clause allows grouping of records according to their values for a given set of columns. Then, one or more aggregations can be computed on records in each group. The GROUP BY clause in the query defines a list of one or more columns (separated by ,) as grouping columns. Aggregate expressions are specified among SELECT expressions. Supported aggregate functions on Materialized Views are: count, min, max, sum, and avg.

HAVING <having_clause>

This clause specifies aggregate filter conditions. The HAVING clause can only be used with GROUP BY to filter out groups of records that do not satisfy a condition. Any given column reference in the HAVING clause has to refer to a column from the group by columns list, or it should be referred within an aggregate function.

ORDER BY <order_by_clause>

The ORDER BY clause defines a sorting order on the query’s result, based on a list of one or more columns (separated by ,). Beside ordering columns (which is a required argument), ORDER BY accepts two optional arguments:

• ASC or DESC: This option sets the sort direction to ascending (smaller value first) or descending (larger value first). ASC is the default sorting order.

• NULLS FIRST or NULLS LAST: This option specifies whether a result row, which has the NULL value in the ORDER BY column(s), appears before or after the rows with non-null value(s) for the same column(s) in the sorted results. The default value for this option depends on the sort direction: NULLS LAST is the default for the ASC sort order, and NULLS FIRST is the default for the DESC order.

LIMIT <number>

This clause specifies a constraint on the maximum number of rows that a query retrieves from its results. It enables fetching just a portion of the query’s results on a Materialized View. Its argument is a positive number, which defines the limit count, and no more than that many rows will be fetched from the query’s result.

Currently, a SELECT statement on a Materialized View has an enforced default limit of 1000 records on the results, and a LIMIT clause cannot override this limit to set it to a larger number.

Examples

For the following examples, assume two Materialized Views named pagevisits and usersinfo have been created by the following statements on pageviews and users streams:

CREATE STREAM pageviews (
  viewtime BIGINT,
  userid VARCHAR,
  pageid VARCHAR
) WITH (
  'topic' = 'pageviews',
  'value.format' = 'json'
);

CREATE STREAM "users" (
  registertime BIGINT,
  userid VARCHAR,
  regionid VARCHAR,
  gender VARCHAR,
  interests ARRAY<VARCHAR>,
  contactinfo STRUCT<phone VARCHAR, city VARCHAR, "state" VARCHAR, zipcode VARCHAR>
) WITH (
  'topic'='users',
  'value.format' = 'json'
);

CREATE MATERIALIZED VIEW
  pagevisits
AS SELECT
  pageid, count(userid) AS cnt 
FROM pageviews
GROUP BY pageid;

CREATE MATERIALIZED VIEW
  userinfo
AS SELECT
  userid,
  gender,
  regionid AS district,
  contactInfo->city AS area, 
  interests[1] AS hobby 
FROM "users";

Select all

The following statement selects all records from the pagevisits Materialized View.

SELECT * FROM pagevisits;

Filter records

The following statement selects all records from the pagevisits Materialized View that have more than 5 view counts. In the results, pageid and cnt columns are represented as pgid and view_count respectively.

SELECT
    pageid AS pgid, cnt AS view_count
FROM pagevisits
WHERE cnt > 5;

Order and limit results

The following statement selects top-10 records from the pagevisits Materialized View according to their total view counts. Pages with the NULL value for the cnt column are shown last.

SELECT
    *
FROM pagevisits
ORDER BY cnt DESC
NULLS LAST
LIMIT 10;

Run grouping and aggregation

The following statement groups records in the userinfo Materialized View according to their userid column and returns the total count of records in each group.

SELECT
    userid, count(*) AS user_count
FROM userinfo
GROUP BY userid;