在SQL中不同行的两列中进行计算 [英] Calculation in two columns of different row in sql
问题描述
我有桌子t1
ID NAME AGE GENDER BALANCE
----- ----- --------- ----- ---------
1001 John 10 M 10
1002 Meena 5 F 0
1003 Nikh 11 M 0
1004 divs 7 F 0
1005 neha 4 F 0
从第二行开始，如果性别"为M，则余额"(第二行)应为 年龄(2)+平衡(1)
from second row, if Gender is M then Balance (2nd row) should be age(2)+balance(1)
其他余额(1)-年龄(2)
else Balance(1)-age(2)
最终结构应为
ID NAME AGE GENDER BALANCE
----- ----- --------- ----- ---------
1001 John 10 M 10
1002 Meena 5 F 5
1003 Nikh 11 M 16
1004 divs 7 F 9
1005 neha 4 F 5
请帮助我进行查询/procedure
Please help me with query /procedure
推荐答案
这样的事情怎么样?
with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select id,
name,
age,
gender,
sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
from sample_data;
ID NAME AGE GENDER BALANCE
---------- ----- ---------- ------ ----------
1001 John 10 M 10
1002 Meena 5 F 5
1003 Nikh 11 M 16
1004 divs 7 F 9
1005 neha 4 F 5
我猜测第一行的余额(这里是id的顺序)是10，因为那是约翰的年龄，他是男性，而不是某个任意数字.
I'm guessing that the balance of the first row (I'm assuming an order of id, here) is 10 because that's John's age and he's male, rather than it being some arbitrary number.
ETA:以下是上述解决方案的替代方案.我高度建议您针对类似于生产的数据量测试所有内容(在这里我使用了with子句来模拟一个名为sample_data的具有5行的表，您只需要使用该表即可).这样，您可以获得的时间安排应突出显示您的方案中性能最高的方法.希望您的经理不会对事实视而不见(如果他是事实，请逃之fast.)
ETA: here are the alternatives to the above solution. I HIGHLY recommend you test everything against a production-like volume of data (where I've used the with clause to mimic a table called sample_data with 5 rows, you would just need to use your table). That way, you can get timings that should highlight the most performant method for your scenario; hopefully your manager won't be blind to facts (if he is, run. Run fast!)
1)没有分析功能的SQL语句:
1) SQL statement with no analytic functions:
with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select sd1.id,
sd1.name,
sd1.age,
sd1.gender,
sum(case when sd2.gender = 'F' then -1 * sd2.age else sd2.age end) balance
from sample_data sd1
inner join sample_data sd2 on (sd1.id >= sd2.id)
group by sd1.id,
sd1.name,
sd1.age,
sd1.gender
order by id;
ID NAME AGE GENDER BALANCE
---------- ----- ---------- ------ ----------
1001 John 10 M 10
1002 Meena 5 F 5
1003 Nikh 11 M 16
1004 divs 7 F 9
1005 neha 4 F 5
2)程序性(慢速逐行{打哈欠})方法(不推荐):
2) Procedural (slow-by-slow row-by-row {yawn}) method (NOT recommended):
create or replace procedure calc_balance1
as
v_balance number := 0;
cursor cur is
with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select id,
name,
age,
gender,
balance
from sample_data;
begin
for rec in cur
loop
v_balance := v_balance + case when rec.gender = 'F' then -1 * rec.age
else rec.age
end;
dbms_output.put_line('id = '||rec.id||', name = '||rec.name||', age = '||rec.age||', gender = '||rec.gender||', balance = '||v_balance);
end loop;
end calc_balance1;
/
begin
calc_balance;
end;
/
id = 1001, name = John, age = 10, gender = M, balance = 10
id = 1002, name = Meena, age = 5, gender = F, balance = 5
id = 1003, name = Nikh, age = 11, gender = M, balance = 16
id = 1004, name = divs, age = 7, gender = F, balance = 9
id = 1005, name = neha, age = 4, gender = F, balance = 5
但是，如果您需要为此提供一个过程，则可以将查询与解析函数一起使用，并将其粘贴在ref游标中，例如:
However, if you had to come up with a procedure for this, I'd use the query with an analytic function and just stick it in a ref cursor, eg:
create or replace procedure calc_balance2 (p_refcur out sys_refcursor)
as
begin
open p_refcur for with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select id,
name,
age,
gender,
sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
from sample_data
order by id;
end calc_balance2;
/
------------------
我看到了您编写的过程；这是我的替代方法:
------------------
I see the procedure you wrote; here's how I would do it instead:
-- mimicking your test_divs table:
create table test_divs as
select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual;
create or replace procedure t1_d12_v2
as
begin
merge into test_divs tgt
using (select id,
name,
age,
gender,
sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
from test_divs) src
on (tgt.id = src.id)
when matched then
update set tgt.balance = src.balance;
end t1_d12_v2;
/
select * from test_divs;
ID NAME AGE GENDER BALANCE
---------- ----- ---------- ------ ----------
1001 John 10 M 10
1002 Meena 5 F 0
1003 Nikh 11 M 0
1004 divs 7 F 0
1005 neha 4 F 0
begin
t1_d12_v2;
commit;
end;
/
select * from test_divs;
ID NAME AGE GENDER BALANCE
---------- ----- ---------- ------ ----------
1001 John 10 M 10
1002 Meena 5 F 5
1003 Nikh 11 M 16
1004 divs 7 F 9
1005 neha 4 F 5
我非常建议您使用类似生产的数据来测试这两种方法，然后看看哪种方法效果更好. (如果您的经理真的对分析功能不满意，我会将"src"子查询交换为我想到的另一个sql语句-带有join和group by的语句.)
I HIGHLY suggest you test both methods with your production-like data and see which one performs better. (If your manager is really dead set against the analytic function, I would swap the "src" subquery to be the other sql statement that I came up with - the one with the join and group by.)
像您正在做的那样逐行进行更新将对循环中的每次遍历在sql和pl/sql之间进行两次上下文切换.当您可以在单个sql语句中完成全部操作时，何必麻烦您呢?认真地.
Doing updates row-by-row like you're doing is going to be doing context switches between sql and pl/sql twice for every pass through the loop. Why bother, when you could do the whole thing in a single sql statement. Seriously.
这篇关于在SQL中不同行的两列中进行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
