At my job, I need to take some granular data collected in a twentieth of a mile and then roll it up to a tenth of a mile. This task is done with python scripts, but I was wondering if I can do it with a materialized view. Here is an example of what the data looks like it is simplest form, and what I would like the view to look like.

Simplest form:

This is what I want the view to produce:

I have tried using the rollup, sum, MOD, remainder, but not sure how to use them correctly. I'm not even sure if this is possible through a view or not.

I will accept all suggestions and ideas.

CodePudding user response：

What you need is to use TRUNC() function while creating a view such as

CREATE OR REPLACE VIEW v_Route AS
SELECT Route_Number, 
       MIN(TRUNC(Beginning_Mile_Post,1)) AS Beginning_Mile_Post,
       MAX(Ending_Mile_Post) AS Ending_Mile_Post,
       SUM(Route_Length) AS Route_Length
  FROM t
 GROUP BY Route_Number, TRUNC(Beginning_Mile_Post,1) 

Demo