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 Database.
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
orDESC
: This option sets the sort direction to ascending (smaller value first) or descending (larger value first).ASC
is the default sorting order.NULLS FIRST
orNULLS LAST
: This option specifies whether a result row, which has theNULL
value in theORDER 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 theASC
sort order, andNULLS FIRST
is the default for theDESC
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;
Last updated