It’s useful in OLAP. Time ’s prediction bottom `Aggregate` could be pulled-up, because of Time’s granularity in Aggregate’s group. By the way, the query can be rewritten by mv, when the query's granularity is coarser than mv’s. eg: -- mv: SELECT eventid, floor(ts TO minute), count(*) as cnt FROM events GROUP BY eventid, floor(ts TO minute) -- query: SELECT floor(ts TO hour), count(*) FROM events GROUP BY eventid, floor(ts TO hour)
In the past, I tried to use stream-sql’s expression to solve it. This document is used to describe this problem, Here is a rough document. doc: https://docs.google.com/document/d/1LtSgxhwvnpk2uAXFQiMSX-aQjnkJ6KLrXwXJX-VbsbE/edit#heading=h.ww6moavc8lkj My team has defined more `UnifyRule`s to solve it, because SubstitutionVisitor has high-scalability. Xurenhe Thomas D'Silva <[email protected]> 于2022年1月27日周四 02:54写道： > Hi, > > The existing MaterializedViewRule is not applied when the materialized view > does not have a view predicate but the query contains a predicate. For eg > for the following materialized view > > SELECT eventid, floor(ts to minute), count(*) as cnt > FROM events > GROUP BY eventid, floor(ts TO minute) > > If we have the following query the view is not chosen in the final plan. > > SELECT floor(ts to minute), count(*) > FROM events > WHERE ts >= timestamp'2018-01-01 00:01:00' AND ts < timestamp'2018-01-01 > 00:05:00' > GROUP BY eventid, floor(ts TO minute) > > If I add the predicate "ts > timestamp'2018-01-01 00:01:00' AND ts < > timestamp'2018-01-01 00:05:00'" to the materialized view then the > materialized view is chosen in the final plan. Would it be possible to > modify the MaterializedViewRule to automatically add a view predicate based > on the query when the materialized view being considerent aggregates over a > column that contains a floor()? This predicate would also need to be > applied to the rewritten view. I would like to file a JIRA and work on this > improvement if this makes sense. > > Thank you, > Thomas >
