数据表内容:
产品、年、月、销量 (具体测试数据请参见后面的建表语句和插入语句)。我想实现如附图中的统计结果:(以测试数据中的2015年10月为例)
如何能用Oracle的统计分析函数在一个语句中实现,请大家帮忙指点一下,多谢!下面evernote笔记链接中,有我做的一个Excel,可以用来理解和验证我想要的结果。
http://www.evernote.com/l/AB8h2ctcy-xCRpYIMvYhRMn5kjGChPSD1v4/--创建临时表
create table temp_fruit
(
name varchar2(10),
year number,
month varchar2(2),
sales number,
primary key (name,year,month)
);--插入测试数据
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'01',49);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'02',66);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'03',44);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'04',34);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'05',11);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'06',98);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'07',56);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'08',38);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'09',62);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'10',37);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'11',59);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'12',56);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'01',40);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'02',50);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'03',96);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'04',25);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'05',98);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'06',90);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'07',38);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'08',16);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'09',20);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'10',55);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'11',15);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'12',32);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'01',75);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'02',54);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'03',28);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'04',59);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'05',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'06',38);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'07',45);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'08',62);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'09',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'10',53);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'11',99);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'12',36);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'01',20);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'02',91);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'03',86);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'04',10);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'05',3);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'06',35);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'07',9);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'08',7);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'09',69);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'10',12);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'01',91);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'02',95);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'03',15);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'04',57);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'05',85);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'06',14);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'07',2);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'08',80);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'09',19);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'10',20);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'01',69);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'02',98);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'03',54);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'04',11);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'05',33);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'06',72);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'07',29);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'08',44);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'09',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'10',83);
产品、年、月、销量 (具体测试数据请参见后面的建表语句和插入语句)。我想实现如附图中的统计结果:(以测试数据中的2015年10月为例)
如何能用Oracle的统计分析函数在一个语句中实现,请大家帮忙指点一下,多谢!下面evernote笔记链接中,有我做的一个Excel,可以用来理解和验证我想要的结果。
http://www.evernote.com/l/AB8h2ctcy-xCRpYIMvYhRMn5kjGChPSD1v4/--创建临时表
create table temp_fruit
(
name varchar2(10),
year number,
month varchar2(2),
sales number,
primary key (name,year,month)
);--插入测试数据
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'01',49);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'02',66);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'03',44);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'04',34);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'05',11);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'06',98);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'07',56);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'08',38);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'09',62);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'10',37);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'11',59);
insert into temp_fruit(name,year,month,sales) values('香蕉',2014,'12',56);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'01',40);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'02',50);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'03',96);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'04',25);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'05',98);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'06',90);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'07',38);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'08',16);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'09',20);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'10',55);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'11',15);
insert into temp_fruit(name,year,month,sales) values('苹果',2014,'12',32);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'01',75);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'02',54);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'03',28);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'04',59);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'05',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'06',38);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'07',45);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'08',62);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'09',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'10',53);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'11',99);
insert into temp_fruit(name,year,month,sales) values('桃子',2014,'12',36);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'01',20);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'02',91);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'03',86);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'04',10);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'05',3);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'06',35);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'07',9);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'08',7);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'09',69);
insert into temp_fruit(name,year,month,sales) values('香蕉',2015,'10',12);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'01',91);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'02',95);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'03',15);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'04',57);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'05',85);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'06',14);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'07',2);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'08',80);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'09',19);
insert into temp_fruit(name,year,month,sales) values('苹果',2015,'10',20);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'01',69);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'02',98);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'03',54);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'04',11);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'05',33);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'06',72);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'07',29);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'08',44);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'09',60);
insert into temp_fruit(name,year,month,sales) values('桃子',2015,'10',83);
sum(a.sales)over(partition by a.name,a.year) "本期累计",
sum(b.sales)over(partition by a.name,a.year) "同期累计"
from temp_fruit a left join temp_fruit b
on b.year=to_char(a.year-1) and a.name=b.name and a.month=b.month
order by 1,2,3
增长率不知道是怎么算的。
SELECT A.NAME, B.SALES2015, C.SALES2014
, CASE WHEN NVL(C.SALES2014, 0) <> 0 THEN ROUND((NVL(B.SALES2015, 0)-NVL(C.SALES2014, 0))/C.SALES2014*100,0) END UP2015
, D.SALESALL2015, E.SALESALL2014
, CASE WHEN NVL(E.SALESALL2014, 0) <> 0 THEN ROUND((NVL(D.SALESALL2015, 0)-NVL(E.SALESALL2014, 0))/E.SALESALL2014*100,0) END UPALL2015
FROM
(SELECT DISTINCT "NAME" FROM TEMP_FRUIT) A
LEFT JOIN (SELECT "NAME", SUM(SALES) SALES2015 FROM TEMP_FRUIT WHERE "YEAR"=2015 AND "MONTH"='10' GROUP BY "NAME") B ON A."NAME"=B."NAME"
LEFT JOIN (SELECT "NAME", SUM(SALES) SALES2014 FROM TEMP_FRUIT WHERE "YEAR"=2014 AND "MONTH"='10' GROUP BY "NAME") C ON A."NAME"=C."NAME"
LEFT JOIN (SELECT "NAME", SUM(SALES) SALESALL2015 FROM TEMP_FRUIT WHERE "YEAR"=2015 AND "MONTH"<='10' GROUP BY "NAME") D ON A."NAME"=D."NAME"
LEFT JOIN (SELECT "NAME", SUM(SALES) SALESALL2014 FROM TEMP_FRUIT WHERE "YEAR"=2014 AND "MONTH"<='10' GROUP BY "NAME") E ON A."NAME"=E."NAME"
(select a.name "产品",a.year,a.month,a.sales "本期",b.sales "同期",
sum(a.sales)over(partition by a.name,a.year) "本期累计",
sum(b.sales)over(partition by a.name,a.year) "同期累计"
from temp_fruit a left join temp_fruit b
on b.year=to_char(a.year-1) and a.name=b.name and a.month=b.month
order by 1,2,3)
select 产品,year,month,本期,
decode(同期,null,'无同期',同期) "同期",
nvl2(round(((本期-同期)/同期),2),round(((本期-同期)/同期),2)*100||'%','无同期') "同比增长",
本期累计,
同期累计,
nvl2(round(((本期累计-同期累计)/同期累计),2),round(((本期累计-同期累计)/同期累计),2)*100||'%','无同期') "累计同比增长"
from t
,本期201510
,同期201410
,百分比
,本期累计
,同期累计
,round((本期累计 - 同期累计) / 同期累计
,2) 累计同比增长
FROM (SELECT cux1.name 产品
,cux1.sales 本期201510
,cux2.sales 同期201410
,round((cux1.sales - cux2.sales) / cux2.sales
,2) 百分比
, (SELECT SUM(cux3.sales)
FROM cux_temp_fruit cux3
WHERE
cux3.year = 2015
AND cux3.name = cux1.name
GROUP BY cux3.name
) 本期累计
, (SELECT SUM(cux4.sales)
FROM cux_temp_fruit cux4
WHERE
cux4.year = 2014
AND cux4.name = cux2.name
AND cux4.month BETWEEN 1 AND 10
GROUP BY cux4.name
) 同期累计
FROM cux_temp_fruit cux1
,cux_temp_fruit cux2
WHERE cux1.name = cux2.name
AND cux1.year = cux2.year + 1
AND cux1.month = cux2.month
AND cux1.year = 2015
AND cux1.month = 10
ORDER BY cux1.sales) 把表名cux_temp_fruit 换成你的表名就行了
ROUND((本期累计 - 同期累计) / 同期累计 * 100, 0) || '%' AS 同比累计增长
FROM (SELECT NAME AS 产品,
SUM(CASE
WHEN T.YEAR = 2015 AND
T.MONTH = 10 THEN
SALES
END) AS 本期,
SUM(CASE
WHEN T.YEAR = 2015 - 1 AND
T.MONTH = 10 THEN
SALES
END) AS 同期,
SUM(CASE
WHEN T.YEAR = 2015 THEN
SALES
END) AS 本期累计,
SUM(CASE
WHEN T.YEAR = 2015 - 1 THEN
SALES
END) AS 同期累计
FROM TEMP_FRUIT T
WHERE T.YEAR IN (2015, 2015 - 1) AND
T.MONTH <= 10
GROUP BY NAME)