表结构:名称 是否为空? 类型
----------------------------------------- -------- --------------
YEAR NOT NULL NUMBER(38) 年
MONTH NOT NULL NUMBER(38) 月
PRD_TYPE_ID NOT NULL NUMBER(38) 产品类型Id
EMP_ID NOT NULL NUMBER(38) 员工id
AMOUNT NUMBER(8,2) 销售数量Sql语句:
select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2
and emp_id = 21
model
partition by (prd_type_id)
dimension by (month,year)
measures(amount sales_amount) (
sales_amount[1,2004]=sales_amount[1,2003],
sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
sales_amount[3,2004] = round(sales_amount[3,2003] * 1.25, 2)
)
order by prd_type_id,year,monthSQL语句的作用:该查询获取2003年内由员工#21完场的产品类型为#1和#2的销售量,并根据2003年的销售计算出2004年1月,2月和3月的销售量预测值。请问:能否帮忙找个SQL语句的意思(MODEL,partition,dimension,measure等关键词的用法)。
谢谢啦。
----------------------------------------- -------- --------------
YEAR NOT NULL NUMBER(38) 年
MONTH NOT NULL NUMBER(38) 月
PRD_TYPE_ID NOT NULL NUMBER(38) 产品类型Id
EMP_ID NOT NULL NUMBER(38) 员工id
AMOUNT NUMBER(8,2) 销售数量Sql语句:
select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2
and emp_id = 21
model
partition by (prd_type_id)
dimension by (month,year)
measures(amount sales_amount) (
sales_amount[1,2004]=sales_amount[1,2003],
sales_amount[2,2004] = sales_amount[2,2003] + sales_amount[3,2003],
sales_amount[3,2004] = round(sales_amount[3,2003] * 1.25, 2)
)
order by prd_type_id,year,monthSQL语句的作用:该查询获取2003年内由员工#21完场的产品类型为#1和#2的销售量,并根据2003年的销售计算出2004年1月,2月和3月的销售量预测值。请问:能否帮忙找个SQL语句的意思(MODEL,partition,dimension,measure等关键词的用法)。
谢谢啦。
详细介绍
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );Table created.
SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);SQL>
SQL> select * from all_sales; YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.6425 rows selected.SQL>
SQL>
SQL> --An Example of the MODEL Clause
SQL>
SQL> SELECT prd_type_id, year, month, sales_amount
2 FROM all_sales
3 WHERE prd_type_id BETWEEN 1 AND 2
4 AND emp_id = 21
5 MODEL
6 PARTITION BY (prd_type_id)
7 DIMENSION BY (month, year)
8 MEASURES (amount sales_amount) (
9 sales_amount[1, 2004] = sales_amount[1, 2003],
10 sales_amount[2, 2004] =
11 sales_amount[2, 2003] + sales_amount[3, 2003],
12 sales_amount[3, 2004] = ROUND(sales_amount[3, 2003] * 1.25, 2)
13 )
14 ORDER BY prd_type_id, year, month;PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2004 1
1 2004 2
1 2004 3
1 2005 2 12644.65
1 2005 3
1 2005 4 25026.45
1 2005 5 17212.66
1 2005 6 15564.26
1 2005 11 13026.73
1 2005 12 10032.64
1 2006 1 16034.84
1 2006 2 15644.65
1 2006 6 15564.66
1 2006 7 15644.65
1 2006 8 16434.82
1 2006 9 19654.57
1 2006 10 21764.19
1 2006 11 13026.73
2 2004 1
2 2004 2
2 2004 3
2 2005 1 26034.84
2 2005 7 62654.82
2 2005 8 26434.82
2 2005 9 15644.65
2 2005 10 21264.19
2 2006 3 20167.83
2 2006 4 25056.45
2 2006 5
2 2006 12 10034.6430 rows selected.SQL>
SQL>
SQL>
SQL> drop table all_sales;Table dropped.
SQL> CREATE TABLE all_sales (
2 year INTEGER,
3 month INTEGER,
4 prd_type_id INTEGER,
5 emp_id INTEGER ,
6 amount NUMBER(8, 2)
7 );Table created.SQL>
SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,1 ,1 ,21 ,16034.84);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,2 ,1 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,3 ,2 ,21 ,20167.83);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,4 ,2 ,21 ,25056.45);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,5 ,2 ,21 ,NULL);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,6 ,1 ,21 ,15564.66);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,7 ,1 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,8 ,1 ,21 ,16434.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,9 ,1 ,21 ,19654.57);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,10 ,1 ,21 ,21764.19);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,11 ,1 ,21 ,13026.73);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2006,12 ,2 ,21 ,10034.64);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,22 ,16634.84);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,1 ,2 ,21 ,26034.84);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,2 ,1 ,21 ,12644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,3 ,1 ,21 ,NULL);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,4 ,1 ,21 ,25026.45);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,5 ,1 ,21 ,17212.66);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,6 ,1 ,21 ,15564.26);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,7 ,2 ,21 ,62654.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,8 ,2 ,21 ,26434.82);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,9 ,2 ,21 ,15644.65);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,10 ,2 ,21 ,21264.19);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,11 ,1 ,21 ,13026.73);SQL> insert into all_sales (YEAR,MONTH,PRD_TYPE_ID,EMP_ID,AMOUNT)
2 values(2005,12 ,1 ,21 ,10032.64);SQL>
SQL> select * from all_sales; YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
---------- ---------- ----------- ---------- ----------
2006 1 1 21 16034.84
2006 2 1 21 15644.65
2006 3 2 21 20167.83
2006 4 2 21 25056.45
2006 5 2 21
2006 6 1 21 15564.66
2006 7 1 21 15644.65
2006 8 1 21 16434.82
2006 9 1 21 19654.57
2006 10 1 21 21764.19
2006 11 1 21 13026.73
2006 12 2 21 10034.64
2005 1 2 22 16634.84
2005 1 2 21 26034.84
2005 2 1 21 12644.65
2005 3 1 21
2005 4 1 21 25026.45
2005 5 1 21 17212.66
2005 6 1 21 15564.26
2005 7 2 21 62654.82
2005 8 2 21 26434.82
2005 9 2 21 15644.65
2005 10 2 21 21264.19
2005 11 1 21 13026.73
2005 12 1 21 10032.6425 rows selected.SQL>
SQL> -- Use symbolic notation to explicitly indicate the meaning of the dimensions
SQL>
SQL>
SQL> SELECT prd_type_id, year, month, sales_amount
2 FROM all_sales
3 WHERE prd_type_id BETWEEN 1 AND 2
4 AND emp_id = 21
5 MODEL
6 PARTITION BY (prd_type_id)
7 DIMENSION BY (month, year)
8 MEASURES (amount sales_amount) (
9 sales_amount[month=1, year=2004] = sales_amount[month=1, year=2003],
10 sales_amount[month=2, year=2004] =
11 sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003],
12 sales_amount[month=3, year=2004] =
13 ROUND(sales_amount[month=3, year=2003] * 1.25, 2)
14 )
15 ORDER BY prd_type_id, year, month;PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2005 2 12644.65
1 2005 3
1 2005 4 25026.45
1 2005 5 17212.66
1 2005 6 15564.26
1 2005 11 13026.73
1 2005 12 10032.64
1 2006 1 16034.84
1 2006 2 15644.65
1 2006 6 15564.66
1 2006 7 15644.65
1 2006 8 16434.82
1 2006 9 19654.57
1 2006 10 21764.19
1 2006 11 13026.73
2 2005 1 26034.84
2 2005 7 62654.82
2 2005 8 26434.82
2 2005 9 15644.65
2 2005 10 21264.19
2 2006 3 20167.83
2 2006 4 25056.45
2 2006 5
2 2006 12 10034.6424 rows selected.SQL>
SQL>
SQL>
SQL>
SQL> drop table all_sales;