Comment on page
SELECT (FROM MATERIALIZED VIEW)
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];
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.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.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
.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.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.
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.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";
The following statement selects all records from the
pagevisits
Materialized View.SELECT * FROM pagevisits;
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;
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;
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 modified 1mo ago