SELECT sản phẩm, vị trí, năm, của năm "Max" TỪ sales1 WHERE vị trí như 'Pen% RETURN MODEL CẬP NHẬT ROWS phân vùng bằng cách (sản phẩm) DIMENSION BY (địa điểm, năm) các biện pháp (số tiền) - bỏ qua NAV ([' Pensacola ', BẤT CỨ] = max (s) [' Pensacola, năm giữa năm 2005 và 2006]) ORDER BY sản phẩm, địa điểm, năm | The MODEL or SPREADSHEET Predicate in Oracle s SQL Giving PRODUCT LOCATION YEAR AMOUNT Blueberries Pensacola 2005 7650 Blueberries Pensacola 2006 9000 Cotton Pensacola 2005 13600 Cotton Pensacola 2006 16000 Lumber Pensacola 2005 2975 Lumber Pensacola 2006 3500 Then we will use the MAX aggregate function and a BETWEEN condition on the RhS SELECT product location year s Year Max FROM sales1 WHERE location like Pen MODEL RETURN UPDATED ROWS PARTITION BY product DIMENSION BY location year MEASURES amount s -- IGNORE NAV s Pensacola ANY max s Pensacola year between 2005 and 2006 ORDER BY product location year Giving PRODUCT LOCATION YEAR Year Max Blueberries Pensacola 2005 9000 Blueberries Pensacola 2006 9000 Cotton Pensacola 2005 16000 Cotton Pensacola 2006 16000 Lumber Pensacola 2005 3500 Lumber Pensacola 2006 3500 We are not constrained to using wildcards on the RHS calculation of aggregates. In this case we controlled which rows would be included in the aggregate using the BETWEEN predicate. 192 Chapter I Revisiting CV with Value Offsets Using Multiple MEASURES Values We have seen how to use the CV function inside an RHS expression. The CV function copies the value from the LHS and uses it in a calculation. We can also use logical offsets from the current value. For example cv -1 would indicate the current value minus one. Suppose we wanted to calculate the increase in sales for each year cv . We will need the sales from the previous year to make the calculation cv -1. We will restrict the data for the example look first at sales in Pensacola SELECT product location year amount FROM salesl WHERE location like Pen ORDER BY product location year Giving PRODUCT LOCATION YEAR AMOUNT Blueberries Pensacola 2005 7650 Blueberries Pensacola 2006 9000 Cotton Pensacola 2005 13600 Cotton Pensacola 2006 l6000 Lumber Pensacola 2005 2975 Lumber Pensacola 2006 3500 We will PARTITION BY product in this example and we will DIMENSION BY location and year. We will use two new MEASURES
