The most recent SQL standard I am aware of (SQL 2016) provides a feature designed to find matches in time series. So far, only Oracle has implemented this functionality into their latest version of the product.
At this point, no other database vendor has followed them and added similar functionality. If you want to model this state-of-the-art technology in PostgreSQL, you have to work with the windowing function and subselects. Matching time series patterns in Oracle is pretty powerful; there is not just one type of query to achieve this in PostgreSQL.
