求一超难存储过程

newkid

原帖由 郭清明 于 2009-9-7 19:42 发表
前辈，select power(2,419) from dual;在 oracle10g里面会报numeric overflow 错误，言外之意是不是这个算法只适合去查询数据量小于419条的表，一旦大于418条，数据库就会报错是吧

for i in res.first .. 2**res.last-1 loop 这个循环相当于要去计算 power(2,419)是吗

我那个需求是不是必须要用背包算法搞啊，我实际的数据量起码上万条，莫非无解？

这个利用BITAND的算法被精度极限所限制的。
connect by的办法试过了没有？效果如何？
你的数据量很大，目标数据一般是由几个组合出来？如果组合数也很大，什么算法都不理想。

郭清明

组合数不定，可以由几条组合也可以由N条组合。
  看来只能先这么做，从可行性角度出发，这次非得让需求迁就开发了。
  非常感谢前辈这半个月来的帮助，谢谢。

郭清明

现在需求让步了，没有背包算法了，从第一条数据往后累加一直加到总金额>=所求金额就返回组合的序列，而且计算之前要先对数据进行排序把金额小的放前面，金额大的放后面，这样累加的时候一定能找到大于等于所求金额的组合，当大于所求金额的时候，拆分肯定也只要拆分最后一个累加的金额就行了。

那怎么去修改那个pl/sql呢？
for i in res.first .. 2**res.last-1 loop   是不是可以改成
for i in res.first..res.last loop 而且只要单循环就可以了？
虽然现在变简单了但我改了一晚上还是没改出来，请前辈帮帮忙。

郭清明

原帖由 郭清明 于 2009-9-9 20:16 发表
现在需求让步了，没有背包算法了，从第一条数据往后累加一直加到总金额>=所求金额就返回组合的序列，而且计算之前要先对数据进行排序把金额小的放前面，金额大的放后面，这样累加的时候一定能找到大于等于所求金额的组合，当大于所求金额的时候，拆分肯定也只要拆分最后一个累加的金额就行了。

那怎么去修改那个pl/sql呢？
for i in res.first .. 2**res.last-1 loop   是不是可以改成
for i in res.first..res.last loop 而且只要单循环就可以了？
虽然现在变简单了但我改了一晚上还是没改出来，请前辈帮帮忙。

在以前的需求中要求，必须首先遍历所有数据找到组合相加之和刚好等于所求金额（背包算法），找不到才去找最接近的，而现在只需要从第一条一直往后累加即可。

如果不要求拆分的话，是不是只要一条 select 语句就能查到了啊

newkid

VAR v_target NUMBER;
EXEC :v_target := 13;

SELECT id,amount,(CASE WHEN total<=:v_target THEN amount ELSE :v_target - last_total END) AS selected_amount
  FROM (
SELECT t.*
      ,SUM(amount) OVER(ORDER BY amount,id) as total
      ,SUM(amount) OVER(ORDER BY amount,id) - amount as last_total
  FROM t_money t
)
WHERE total<=:v_target
      OR total>:v_target
         AND last_total<:v_target
ORDER BY total;

郭清明

现在有个汇率转化问题，有这样一张表：
create table t_rates(rid number primary key, from_currency varchar2(10),to_currency varchar2(10),conversion_rate number);
--rid 是汇率表的主键，from_currency 是现有金额的币种 ，to_currency是目标币种, conversion_rate是汇率
现有如下数据：
rid    from_currency   to_currency   conversion_rate
1        rmb             usd                  0.5
2        riyuan             usd                  0.1

对于我们之前使用的t_money表现在多了一个当前币种字段如下：
alter table t_money add currencyCode varchar2(10);
id         amount    currencyCode
1        2        rmb
2        2        rmb
3        3        rmb
4        5        rmb
5        2        rmb
6        8        riyuan
7        1        riyuan
8        2        riyuan
9        2        riyuan
10        3        riyuan

还是针对之前讨论的需求，仍然是求组合数据得到总金额，但是现在要先对币种的汇率进行转换，因为现有的金额是人民币和日元，所求的金额的币种是美元，汇率关系分别是：人民币对美元  0.5   日元对美元：0.1，因此在组合计算的时候要先转化为美元，再进行相加。并且当组合金额大于所求金额时，拆分当前最后一笔金额。
我今天写了一条超级繁杂的SQL对应此新需求，执行效率很低，请前辈指点。
--以下是求目标金额为3美元的SQL（含拆分过程）
select main.*,
       nvl((select nvl(conversion_rate, 1)
             from t_rates
            where from_currency = main.org_currency
              and upper(to_currency) = 'USD'),
           1) rate
  from (select id,
               total,
               amount,
               (case
                 when total <= 3 then
                  amount
                 else
                  (3 - last_total) / nvl((select nvl(conversion_rate, 1)
                                           from t_rates
                                          where from_currency = currencycode
                                            and upper(to_currency) = 'USD'),
                                         1)
               end) amount_t,
               currencycode org_currency
          from (select a.id,
                       SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id) as total,
                       SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id) - a.amount_usd as last_total,
                       a.amount,
                       a.currencycode
                  from (select m.amount * nvl(t.conversion_rate, 1) amount_usd,
                               m.amount,
                               id,
                               m.currencycode
                          from t_rates t, t_money m
                         where m.currencycode = t.from_currency
                           and upper(t.to_currency) = 'USD') a)
         where total <=
               (select min(total)
                  from (select id, total
                          from (select a.id,
                                       SUM(a.amount_usd) OVER(ORDER BY a.amount_usd, a.id) as total
                                  from (select m.amount *
                                               nvl(t.conversion_rate, 1) amount_usd,
                                               m.amount,
                                               id
                                          from t_rates t, t_money m
                                         where m.currencycode = t.from_currency
                                           and upper(t.to_currency) = 'USD') a)
                         where total >= 3))) main

newkid

就在原有基础上改：
SELECT id
      ,currencyCode
      ,amount
      ,(CASE WHEN total<=:v_target THEN target_amount ELSE :v_target - last_total END) AS target_amount
  FROM (
SELECT t.*
      ,t.amount*r.conversion_rate as target_amount
      ,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) as total
      ,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) - t.amount*r.conversion_rate as last_total
  FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
       AND r.to_currency = 'usd'
)
WHERE total<=:v_target
      OR total>:v_target
         AND last_total<:v_target
ORDER BY total;

郭清明

当发生金额拆分的时候，最后一笔数据实际选取的金额只有一部分，如果要显示最后一笔数据实际被选取的金额，我这样改行不行？
SELECT id,
       currencyCode,
       (CASE
         WHEN total <= 3 THEN
          amount
         ELSE
          (3 - last_total)/nvl((select nvl(conversion_rate, 1)
                                           from t_rates
                                          where from_currency = currencycode
                                            and upper(to_currency) = 'USD'),
                                         1)
       END)amount,
       (CASE
         WHEN total <= 3 THEN
          target_amount
         ELSE
          3 - last_total
       END) AS target_amount
  FROM (SELECT t.*,
               t.amount * r.conversion_rate as target_amount,
               SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) as total,
               SUM(t.amount * r.conversion_rate) OVER(ORDER BY t.amount * r.conversion_rate, id) - t.amount * r.conversion_rate as last_total
          FROM t_money t, t_rates r
         WHERE t.currencyCode = r.from_currency
           AND r.to_currency = 'usd')
WHERE total <= 3
    OR total > 3
   AND last_total < 3
ORDER BY total;


另外，如果要对上万条数据进行查询，这条SQL执行的效率还有可优化的地方吗？

newkid

SELECT id
      ,currencyCode
      ,(CASE WHEN total<=:v_target THEN amount ELSE (:v_target - last_total)/conversion_rate END) AS target_amount
      ,(CASE WHEN total<=:v_target THEN target_amount ELSE :v_target - last_total END) AS target_amount
  FROM (
SELECT t.*
      ,t.amount*r.conversion_rate as target_amount
      ,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) as total
      ,SUM(t.amount*r.conversion_rate) OVER(ORDER BY t.amount*r.conversion_rate,id) - t.amount*r.conversion_rate as last_total
      ,r.conversion_rate
  FROM t_money t, t_rates r
WHERE t.currencyCode = r.from_currency
       AND r.to_currency = 'usd'
)
WHERE total<=:v_target
      OR total>:v_target
         AND last_total<:v_target
ORDER BY total;

这个SQL会对全表作逐行累计，不过上万条也只是小菜一碟。
如果你觉得不满意可以用PLSQL循环，累计到目标数额就退出循环。

郭清明

前辈，你好。
关于SUM(。。。。。。) OVER(ORDER BY 。。。。。。) 我有问题想请教
业务要求，取数时优先取有匹配序列号  (seqNo)的，然后按时间(dueDate)早晚，最后按金额(amount)大小。
sum(amount)  over(order by seqNo,dueDate,amount,id) 这样写的话，不符合要求，因为程序会按照seqNo的大小来排序，而业务并不需要按 seqNo的大小排序(seqNo 是 varchar2类型)，表里面有些数据有 seqNo，有些没有  seqNo,所以只要把有seqNo的数据优先选取就行(没有seqNo 的数据也会选，只是它一定要排在 有seqNo数据的后面)，然后再按时间和金额取。
我处理时制造了伪列 decode(seqNo,null,'10','1') as seqNo_seq  意图使有seqNo的数据为 '1', 没有 seqNo的数据为'10'这样程序就可以优先选取 有 seqNo的数据而又不会去比较那些seqNo的大小，但是最后运行的时候 sum(amount)  over(order by seqNo_seq,dueDate,amount,id) 却选不出任何数据了。
    还请前辈指点，谢谢。