报表业务需求,实在想不出更好的写法了,求指导优化: 大致需求如下: 按日期统计各门店销售:包括 门店,销售数量,销售总额,购物金额,折扣金额,销售净额(总额-开卡-折扣),销售笔数,客单价 等! salepayrpt_base 为POS支付流水表(流水号,门店,商品,数量,销售类型,销售金额,折扣金额),saletype in (8, 9)为购物卡销售/激活,
1、把 select shopid from shop where shopname like '%凤%' 改为select shopid from shop where instr(shopname, '凤') > 0 2、把没有必要进行连接的表用exists()代替,例如表e,你的结果集中都不没有这个表的字段3、可以用with as语句将需要的数据提取为临时表,再进行连接,而且可以重复使用这个临时表,例如:表a,with as语句可以百度一下。
这个应该是性能问题了sql语句有点繁琐,两次执行的时间不同,楼上的已经解释了 优化的话,你可以通过执行计划来查看 我感觉有两个地方可以看看 1.就是shop这张表的数据量大概有多少? 因为这个语句select shopid from shop where shopname like '%凤%'是不走索引的 2.salepayrpt_base这张表是大数据表,sdate这个字段是否有函数索引?如果是普通索引的话 trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')也是没有用的;还有就是f和g两个临时表我觉得可以用函数代替了
shop表是个小数据表:300条记录左右,salepryrpt_base 针对sdate有建函数trunc索引的,另外昨天试了一下f和g表用函数代替时,小数据量很快的,但是大数据量时展开时,经过函数返回的值每一条都要运算,10W记录时就要运算10W次,效率也不高,甚至比子查询还要慢:函数如下:create or replace function tl_getGoodsPssdata ( i_goodstype in int, --0:取起初数量 ,1:取期末数量 i_datatype varchar2, --1:数量,2:金额 i_goodsid int, --商品编码 i_shopid varchar2, --机构编码 i_datefrom varchar2, --起始时间 i_dateto varchar2 --终止时间 ) return varchar2 DETERMINISTIC ------------------------------------------------------------- --tl_getGoodsPssdata 根据时间段及商品、机构取对应时间段内起初、末数量() --输入: --输出: --步骤: --建立: longsg 2014。3.13 --算法: ------------------------------------------------------------- is v_result number(10,2); v_date date; begin if (i_goodstype = 0) then select min(sdate) into v_date from goodspssrpt where goodsid = i_goodsid and shopid = i_shopid and trunc( sdate) between to_date(i_datefrom, 'yyyy-mm-dd') and to_date(i_dateto, 'yyyy-mm-dd') ; if(i_datatype = 1) then select nvl(openqty ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid and trunc(a.sdate) = v_date ; elsif(i_datatype = 2) then select nvl(opencostv ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid and trunc(a.sdate) = v_date ; end if; elsif (i_goodstype = 1) then select max(sdate) into v_date from goodspssrpt where goodsid = i_goodsid and shopid = i_shopid and trunc( sdate) between to_date(i_datefrom, 'yyyy-mm-dd') and to_date(i_dateto, 'yyyy-mm-dd') ; if(i_datatype = 1) then select nvl(closeqty ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid and trunc( sdate) = v_date ; elsif(i_datatype = 2) then select nvl(closecostv ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid and trunc(a.sdate) = v_date ; end if; end if; if (v_result is null) then v_result := 0; end if; return(v_result); end;
随便改了下,也不知道对不对 select x.*, x.salevalue - x.discvalue as saleamt, decode(x.custcount, 0, 0, round((x.salevalue - x.discvalue) / x.custcount, 2)) as avgvalue from (select e.regionid, b.regionid as csregionid, b.regionname as cityname, a.shopid, c.shopname, sum(a.salevalue) as totalsalevalue, sum(a.discvalue) as totaldisvalue, -- g.salevalue - g.discvalue as saleamt, -- f.tsaleamt, -- g.custcount, sum(case when a.salevalue not in (1, 2) then salevalue end) over() as tsaleamt, sum(case when a.salevalue not in (1, 2) then discvalue end) over() as discvalue, count(distinct(case when a.salevalue not in (1, 2) then a.sheetid end)) over() as custcount from salepayrpt_base a, region b, shop c, region e where a.shopid = c.shopid and c.regionid = b.regionid and a.saletype not in (8, 9) and b.headregionid = e.regionid and trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd') and trunc(a.sdate) <= to_date('2014-03-05', 'yyyy-mm-dd') -- and a.shopid in -- (select shopid from shop where shopname like '%凤%') and c.shopname like '%凤%' -----change and b.regionid = 401 group by e.regionid, b.regionid, b.regionname, a.shopid, c.shopname) x order by 4
大致需求如下:
按日期统计各门店销售:包括 门店,销售数量,销售总额,购物金额,折扣金额,销售净额(总额-开卡-折扣),销售笔数,客单价 等!
salepayrpt_base 为POS支付流水表(流水号,门店,商品,数量,销售类型,销售金额,折扣金额),saletype in (8, 9)为购物卡销售/激活,
改为select shopid from shop where instr(shopname, '凤') > 0 2、把没有必要进行连接的表用exists()代替,例如表e,你的结果集中都不没有这个表的字段3、可以用with as语句将需要的数据提取为临时表,再进行连接,而且可以重复使用这个临时表,例如:表a,with as语句可以百度一下。
优化的话,你可以通过执行计划来查看
我感觉有两个地方可以看看
1.就是shop这张表的数据量大概有多少? 因为这个语句select shopid from shop where shopname like '%凤%'是不走索引的
2.salepayrpt_base这张表是大数据表,sdate这个字段是否有函数索引?如果是普通索引的话 trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')也是没有用的;还有就是f和g两个临时表我觉得可以用函数代替了
shop表是个小数据表:300条记录左右,salepryrpt_base 针对sdate有建函数trunc索引的,另外昨天试了一下f和g表用函数代替时,小数据量很快的,但是大数据量时展开时,经过函数返回的值每一条都要运算,10W记录时就要运算10W次,效率也不高,甚至比子查询还要慢:函数如下:create or replace function tl_getGoodsPssdata
(
i_goodstype in int, --0:取起初数量 ,1:取期末数量
i_datatype varchar2, --1:数量,2:金额
i_goodsid int, --商品编码
i_shopid varchar2, --机构编码
i_datefrom varchar2, --起始时间
i_dateto varchar2 --终止时间
)
return varchar2 DETERMINISTIC
-------------------------------------------------------------
--tl_getGoodsPssdata 根据时间段及商品、机构取对应时间段内起初、末数量()
--输入:
--输出:
--步骤:
--建立: longsg 2014。3.13
--算法:
-------------------------------------------------------------
is
v_result number(10,2);
v_date date;
begin if (i_goodstype = 0) then
select min(sdate) into v_date from goodspssrpt where goodsid = i_goodsid and shopid = i_shopid
and trunc( sdate) between to_date(i_datefrom, 'yyyy-mm-dd') and
to_date(i_dateto, 'yyyy-mm-dd') ;
if(i_datatype = 1) then select nvl(openqty ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid
and trunc(a.sdate) = v_date ;
elsif(i_datatype = 2) then
select nvl(opencostv ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid
and trunc(a.sdate) = v_date ; end if;
elsif (i_goodstype = 1) then
select max(sdate) into v_date from goodspssrpt where goodsid = i_goodsid and shopid = i_shopid
and trunc( sdate) between to_date(i_datefrom, 'yyyy-mm-dd') and
to_date(i_dateto, 'yyyy-mm-dd') ;
if(i_datatype = 1) then
select nvl(closeqty ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid
and trunc( sdate) = v_date ;
elsif(i_datatype = 2) then
select nvl(closecostv ,0) into v_result from goodspssrpt a where goodsid = i_goodsid and shopid = i_shopid
and trunc(a.sdate) = v_date ; end if;
end if;
if (v_result is null) then
v_result := 0;
end if;
return(v_result);
end;
select x.*,
x.salevalue - x.discvalue as saleamt,
decode(x.custcount,
0,
0,
round((x.salevalue - x.discvalue) / x.custcount, 2)) as avgvalue
from (select e.regionid,
b.regionid as csregionid,
b.regionname as cityname,
a.shopid,
c.shopname,
sum(a.salevalue) as totalsalevalue,
sum(a.discvalue) as totaldisvalue,
-- g.salevalue - g.discvalue as saleamt,
-- f.tsaleamt,
-- g.custcount,
sum(case
when a.salevalue not in (1, 2) then
salevalue
end) over() as tsaleamt,
sum(case
when a.salevalue not in (1, 2) then
discvalue
end) over() as discvalue,
count(distinct(case
when a.salevalue not in (1, 2) then
a.sheetid
end)) over() as custcount
from salepayrpt_base a, region b, shop c, region e
where a.shopid = c.shopid
and c.regionid = b.regionid
and a.saletype not in (8, 9)
and b.headregionid = e.regionid
and trunc(a.sdate) >= to_date('2013-09-29', 'yyyy-mm-dd')
and trunc(a.sdate) <= to_date('2014-03-05', 'yyyy-mm-dd')
-- and a.shopid in
-- (select shopid from shop where shopname like '%凤%')
and c.shopname like '%凤%' -----change
and b.regionid = 401
group by e.regionid, b.regionid, b.regionname, a.shopid, c.shopname) x
order by 4