![]() W is defined as WINDOW w AS (PARTITION BY countriesandterritories ORDER BY daterep). The key line, sign(cases - LAG(cases) OVER w) sign(LEAD(cases) OVER w - cases) = 0 WINDOW w AS (PARTITION BY countriesandterritories ORDER BY daterep) ![]() WHERE countriesandterritories = 'Switzerland' , sign(cases - LAG(cases) OVER w) sign(LEAD(cases) OVER w - cases) = 0 ![]() # \copy raw FROM 'Downloads/covid.csv' WITH (FORMAT CSV, HEADER) Īnd after some fiddling (the first attempt had 3 CTEs and 37 lines of code), I came up with this SQL (which yields the exact same result): WITH peaks AS ( # create table raw(dateRep date,day int,month int,year int,cases int,deaths int,countriesAndTerritories text,geoId text ,countryterritor圜ode text,popData2018 int,continentExp text) So, I downloaded the CSV as "covid.csv", created a little database, created a "rawdata" table and filled it: # create database covid However, Vik Fearing stated "You cannot do MATCH_RECOGNIZE with window functions" (spoiler: you can, at least for simple cases like this). I claimed that the main task here would be to add the grammar to PostgreSQL, as the task at hand can be done with normal WINDOW functions already. The example challenge in the blog post is to find "cases" peaks in COVID-19 data. So, this morning I was made aware of this blog post covering a rather new (completely new to me, TBTH) SQL:2016 feature, MATCH_RECOGNIZE, as something that could be added to PostgreSQL. ![]()
0 Comments
Leave a Reply. |