Advertisement
Guest User

https://blog.dbi-services.com/oracle-row-pattern/

a guest
Jun 11th, 2020
160
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table raw(dateRep date,day int,month int,year int,cases int,deaths int,countriesAndTerritories text,geoId text ,countryterritoryCode text,popData2018 int,continentExp text);
  2.  
  3. \copy raw FROM 'Downloads/covid.csv' WITH (FORMAT CSV, HEADER);
  4.  
  5. WITH rawdata AS (
  6. select countriesandterritories country
  7.   , daterep AS day
  8.   , cases
  9.   , cases - LAG(cases) OVER w AS casesdeltabefore
  10.   , LEAD(cases) OVER w - cases AS casesdeltaafter
  11.   , deaths
  12.   , deaths - LAG(deaths) OVER w AS deathsdeltabefore
  13.   , LEAD(deaths) OVER w - deaths AS deathsdeltaafter
  14.   from raw
  15.   where countriesandterritories='Switzerland'
  16.   WINDOW w AS (PARTITION BY countriesandterritories ORDER BY daterep)
  17.   order by daterep
  18. ),
  19.  
  20. trends AS (
  21.         SELECT country, day
  22.         ,cases
  23.         ,CASE WHEN casesdeltabefore != 0 THEN casesdeltabefore / abs(casesdeltabefore) ELSE 0 END AS casetrendbefore
  24.         ,CASE WHEN casesdeltaafter != 0 THEN casesdeltaafter / abs(casesdeltaafter) ELSE 0 END AS casetrendafter
  25.         ,deaths
  26.         ,CASE WHEN deathsdeltabefore != 0 THEN deathsdeltabefore / abs(deathsdeltabefore) ELSE 0 END AS deathstrendbefore
  27.         ,CASE WHEN deathsdeltaafter != 0 THEN deathsdeltaafter / abs(deathsdeltaafter) ELSE 0 END AS deathstrendafter
  28.         FROM rawdata
  29. )
  30.  
  31. , turning_points AS (
  32.         SELECT Country, day, cases, deaths FROM trends
  33.         WHERE  casetrendbefore != casetrendafter AND casetrendafter = -1
  34. )
  35. SELECT *,row_number() OVER ()
  36. FROM turning_points
  37. ;
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement