如何获得每个ID的正数和负数计数？

Question

我希望能够为每个 ID 获取负值和正值的数量。 示例 Fiddle ID=1 有 2 笔正交易和 0 笔负交易。等等。

 with trans_detail as 
 (
 select 1 as trans_id, 100 as trans_amount  from dual union all
 select 1 as trans_id, 200 as trans_amount  from dual union all
 select 2 as trans_id, -100 as trans_amount  from dual union all
 select 2 as trans_id, -300 as trans_amount  from dual union all
 select 3 as trans_id, 400 as trans_amount   from dual union all
 select 3 as trans_id, -500 as trans_amount  from dual
 )

 select trans_id,
       count(*) over (partition by trans_id) as pos_count,
       count(*) over (partition by trans_id) as neg_count        
from trans_detail
where trans_amount > 0
UNION
select trans_id,
       count(*) over (partition by trans_id) as pos_count,
       count(*) over (partition by trans_id) as neg_count        
from trans_detail
where trans_amount < 0;

期望结果：

 ID   POS_COUNT   NEG_COUNT
---- ----------- -----------
 1    2           0 
 2    0           2
 3    1           1

- zundarz

Answer 1

每次看到正数或负数时，计数器加1，并将其累加起来。

select trans_id,
sum(case when trans_amount >=0 then 1 else 0 end) as pos_amt,
sum(case when trans_amount < 0 then 1 else 0 end) as neg_amt
from trans_detail
group by trans_id

http://sqlfiddle.com/#!4/db410/12

- duffn

Answer 2

select trans_id, 
nvl(sum(case when trans_amount < 0 then 1 end),0) as neg,
nvl(sum(case when trans_amount > 0 then 1 end),0) as pos
from trans_detail
group by trans_id

SQL Fiddle: http://sqlfiddle.com/#!4/db410/15

SQL Fiddle是一个在线SQL数据库测试工具，允许用户编写SQL查询和DDL语句，并在不同的数据库（如MySQL、PostgreSQL、Oracle等）之间进行比较。上面提供的链接将带您到一个名为"db410"的数据库示例，其中包含一些表格和数据可以用于测试和练习SQL查询。

- Vamsi Prabhala

Answer 3

您可以使用条件计数：

with trans_detail as 
 (
 select 1 as trans_id, 100 as trans_amount  from dual union all
 select 1 as trans_id, 200 as trans_amount  from dual union all
 select 2 as trans_id, -100 as trans_amount  from dual union all
 select 2 as trans_id, -300 as trans_amount  from dual union all
 select 3 as trans_id, 400 as trans_amount   from dual union all
 select 3 as trans_id, -500 as trans_amount  from dual
 )
select trans_id,
       count(case when trans_amount >= 0 then trans_id end) as pos_count,
       count(case when trans_amount < 0 then trans_id end) as neg_count        
from trans_detail
group by trans_id
order by trans_id;

  TRANS_ID  POS_COUNT  NEG_COUNT
---------- ---------- ----------
         1          2          0
         2          0          2
         3          1          1

计数忽略 null 值，因此每个 case 的隐式空 'else' 表示这些行不会被计算。如果您愿意，可以添加 else null，但这只会使它变得稍微长一些。（我把零视为 "positive"，但您可能想完全忽略它，就像您的问题一样；在这种情况下，只需返回到 > 0）。

SQL Fiddle

您也可以使用 sign 函数, 在 case 或 decode 中使用：

select trans_id,
       count(decode(sign(trans_amount), 1, trans_id)) as pos_count,
       count(decode(sign(trans_amount), -1, trans_id)) as neg_count        
from trans_detail
group by trans_id
order by trans_id;

SQL Fiddle; 这个忽略了零，但如果你想要包括它，可以在任一解码中加入它。

- Alex Poole

Answer 4

试试这个

select trans_id,
       Sum(case when trans_amount>=0 then 1 else 0 end) as pos_count,
       Sum(case when trans_amount<0 then 1 else 0 end) as neg_count,
from trans_detail
group by trans_id

- Madhivanan