Shulou(Shulou.com)06/01 Report--

Statistics are involved in many requirements: mean, accumulation, range mean, adjacent record comparison and so on.

These operations will be counted multiple times, or have a clear statistical range, or return records with different datasets.

It can be divided into the following categories according to different scenarios:

1. Total statistics

two。 Rolling statistics

3. Range statistics

4. (adjacent) row comparison

Build test data:

SQL > desc criss_sales

Name Type Nullable Default Comments

--

DEPT_ID VARCHAR2 (6) Y

SALE_DATE DATE Y

GOODS_TYPE VARCHAR2 (4) Y

SALE_CNT NUMBER (10) Y

SQL > select * from criss_sales order by dept_id,sale_date desc

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT

--

D01 2014-5-4 G02 80

D01 2014-4-30 G03 800

D01 2014-4-8 G01 200

D01 2014-3-4 G00 700

D02 2014-5-2 G03 900

D02 2014-4-27 G01 300

D02 2014-4-8 G02 100

D02 2014-3-6 G00 500

one。 Total statistics

The most commonly used total statistics are the mean or summation, which sometimes requires the same row of records to contain different ranges of full statistics.

Example:

For the data set statistics department sales sum, company-wide sales sum, department sales average, company-wide sales average

SQL > select

2 dept_id

3, sale_date

4, goods_type

5, sale_cnt

6, sum (sale_cnt) over (partition by dept_id) dept_total

7, sum (sale_cnt) over () cmp_total

8, avg (sale_cnt) over (partition by dept_id) avg_dept

9, avg (sale_cnt) over () avg_cmp

10 from criss_sales

11

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT DEPT_TOTAL CMP_TOTAL AVG_DEPT AVG_CMP

--

D01 2014-5-4 G02 80 1780 3580 445 447.5

D01 2014-4-8 G01 200 1780 3580 445 447.5

D01 2014-4-30 G03 800 1780 3580 445 447.5

D01 2014-3-4 G00 700 1780 3580 445 447.5

D02 2014-5-2 G03 900 1800 3580 450 447.5

D02 2014-4-8 G02 100 1800 3580 450 447.5

D02 2014-3-6 G00 500 1800 3580 450 447.5

D02 2014-4-27 G01 300 1800 3580 450 447.5

In this way, on the same line, you get department-wide statistics (mean / summation) and company-wide statistics (mean / summation).

two。 Rolling statistics

One of the most common scenarios for scrolling statistics is accumulation.

Example:

Calculate the cumulative value of the sales tree of the department and the whole company.

SQL > select

2 dept_id

3, sale_date

4, goods_type

5, sale_cnt

6, sum (sale_cnt) over (partition by dept_id order by dept_id,sale_date rows between unbounded preceding and current row) dept_cur_total

7, sum (sale_cnt) over (order by dept_id,sale_date rows between unbounded preceding and current row) cmp_cur_total

8 from criss_sales

9

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT DEPT_CUR_TOTAL CMP_CUR_TOTAL

--

D01 2014-3-4 G00 700 700 700

D01 2014-4-8 G01 200 900 900

D01 2014-4-30 G03 800 1700 1700

D01 2014-5-4 G02 80 1780 1780

D02 2014-3-6 G00 500 500 2280

D02 2014-4-8 G02 100 600 2380

D02 2014-4-27 G01 300 900 2680

D02 2014-5-2 G03 900 1800 3580

Of course, scrolling queries can also calculate the current average ~ I won't repeat it here.

three。 Range statistics

Sometimes, we tend to focus on data within a certain range, such as time range (data within a week) and record range (the first three records to the current record).

Example: sort by date and find the sum of three adjacent sales records

SQL > select

2 dept_id

3, sale_date

4, goods_type

5, sale_cnt

6, sum (sale_cnt) over (order by sale_date rows between 1 preceding and 1 following) CON_1_CNT

7 from criss_sales

8

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT CON_1_CNT

--

D01 2014-3-4 G00 700 1200

D02 2014-3-6 G00 500 1400

D01 2014-4-8 G01 200 800

D02 2014-4-8 G02 100 600

D02 2014-4-27 G01 300 1200

D01 2014-4-30 G03 800 2000

D02 2014-5-2 G03 900 1780

D01 2014-5-4 G02 80 980

Example of time range:

Sort by date, find the sales volume from the first three days to the next three days before the current record date and

SQL > select

2 dept_id

3, sale_date

4, goods_type

5, sale_cnt

6, sum (sale_cnt) over (order by sale_date range

7 between interval'3' day preceding

8 and interval'3' day following) sum_7_days

9 from criss_sales

10

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT SUM_7_DAYS

--

D01 2014-3-4 G00 700 1200

D02 2014-3-6 G00 500 1200

D01 2014-4-8 G01 200 300

D02 2014-4-8 G02 100 300

D02 2014-4-27 G01 300 1100

D01 2014-4-30 G03 800 2000

D02 2014-5-2 G03 900 1780

D01 2014-5-4 G02 80 980

four。 (adjacent) row comparison

Its practical over (order by xxx rows between 1 preceding and 0 following) can also realize the comparison of adjacent rows.

However, Oracle provides two more convenient functions

Lead () is compared to a later line

Lag () compares to the previous line

Sorted by time, showing the number of current records and the number of sales of adjacent records

SQL > select

2 dept_id

3, sale_date

4, goods_type

5, sale_cnt

6, lag (sale_cnt,1) over (order by sale_date) lag_1

7, lead (sale_cnt,1) over (order by sale_date) lead_1

8, first_value (sale_cnt) over (order by sale_date rows between 1 preceding and 0 following)

9 from criss_sales

10

DEPT_ID SALE_DATE GOODS_TYPE SALE_CNT LAG_1 LEAD_1 FIRST_VALUE (SALE_CNT) OVER (ORDE

D01 2014-3-4 G00 700 500 700

D02 2014-3-6 G00 500 700 200 700

D01 2014-4-8 G01 200 500 100 500

D02 2014-4-8 G02 100 200 300 200

D02 2014-4-27 G01 300 100 800 100

D01 2014-4-30 G03 800 300 900 300

D02 2014-5-2 G03 900 800 80 800

D01 2014-5-4 G02 80 900 900

The last column compares over (order by xxx rows between 1 preceding and 0 following) with lag. We can also get the results we want to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.