Chapter 4. Statistics, analytic, OLAP functions in business scenarios 193
Example 4-29 Customer usage profile view
CREATE VIEW profile(cust_id, avg_amt, sd_amt) AS
SELECT cust_id, AVG(charge_amt), STDDEV(charge_amt)
FROM custtrans
WHERE date BETWEEN '2002-01-01' and '2002-03-31'
GROUP BY cust_id
Step 2
Detect and flag unusually large charges by writing them to the BIG_CHARGES
table as shown in Example 4-30.
Example 4-30 Detect & flag unusually large charges
CREATE TRIGGER big_chrg
AFTER INSERT ON custtrans
REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL
WHEN (newrow.charge_amt > (SELECT avg_amt + 2e0 * sd_amt
FROM profile
WHERE profile.cust_id = newrow.cust_id))
INSERT INTO big_charges(cust_id,charge_amt)
VALUES(newrow.cust_id, newrow.charge_amt)
Here the incoming transaction’s charge amount is checked to see whether it
exceeds twice the standard deviation value above the average for this customer,
and writes this transaction to the BIG_CHARGES table if true.
Another trigger (not shown here) needs to be written for the BIG_CHARGES
table which sets off alarms when the number of such charges over a given period
exceeds pre-defined thresholds.
4.3.6 Plot monthly stock prices movement with percentage change
The objective is to determine the average price of the stock per month and its
percentage change over the previous month. This information can be used for
trend analysis for making investment decisions.
In our example, we use the stock price data for a 6-month period.
Data
The main attributes are stock symbol, date, and closing price of the stock on that
date.
BI functions showcased
OVER, ORDER BY, ROWS BETWEEN
194 High-Function Business Intelligence in e-business
Steps
The necessary steps are shown in Example 4-31.
Example 4-31 Monthly movement of stock prices with percentage change
WITH month_avg(month,avg_price) AS
(
SELECT MONTH(date), AVG(close_price)
FROM stocktab
WHERE symbol = ‘HAL’
GROUP BY MONTH(date)
),
month_avgs(month,cur_avg,last_avg) AS
(
SELECT month,avg_price,MAX(avg_price) OVER (ORDER BY month ROWS BETWEEN
1 PRECEDING AND 1 PRECEDING)
FROM month_avg
)
SELECT month, DEC(cur_avg,7,3) AS “average price”,
DEC(100e0*(cur_avg - last_avg)/last_avg,7,3) AS “% Change”
FROM month_avgs
The first temporary table created is a straight summation of the averages by
month. The second temporary table uses the first to create a table with the
additional column showing the preceding month average. Then we select from
the second temporary table and calculate the monthly percentage change.
The results of this query are shown in Figure 4-36.
Figure 4-36 Monthly averages and percent change
Attention: The ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
clause gets the value of the expression for the immediately preceding row.
MAX does not have any impact on a single row.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.