Can I do complex rollups or sums in Oracle View?

user_18310227 0
Viewed 29

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:

Route Number Beginning Mile Post Ending Mile Post Route Length
001 0 0.02 105.6
001 0.02 0.04 105.6
001 0.04 0.06 105.6
001 0.06 0.08 105.6
001 0.08 0.10 105.6
001 0.10 0.12 105.6
001 0.12 0.14 105.6

This is what I want the view to produce:

Route Number Beginning Mile Post Ending Mile Post Route Length
001 0 0.1 528
001 0.1 0.14 211.2

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.

plsql oracle sql
1 Answers

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

user_58413060
Related