sql - Postgresql:如何在 PostgreSQL 中使用 "crosstab"？

Question

这里我想以下面的形式打印数据。

示例:

callnumber1|callnumber2|CALL-IN|CALL-OUT|SMS-IN|SMS-OUT|FirstCallDate|LastCallDate  
---------  +-------------+---------+----------+--------+---------+---------------+----  
123456     |    654321 |     1 |      2 |    1 |     1 | 2014-02-12  | 2013-03-12    
23456      |     54321 |     0 |      1 |    0 |     1 | 2014-02-12  | 2013-03-12  

表:表1

create table table1  
(  
   callnumber1 int,  
   callnumber2 int,  
   calltype varchar,  
   calldate date  
);    

插入一些数据

insert into table1 values(123456,654321,'CALL-IN','1-2-2014');  

交叉表查询。

select * from crosstab($$select   callnumber1,callnumber2,calltype,calldate,count(callnumber1||callnumber2) as totalcalls  
from table1   
where calltype in ('CALL-IN','CALL-OUT','SMS-IN','SMS-OUT')  
group by callnumber1,callnumber2,calltype  
order by callnumber1,callnumber2,calltype  
$$,  
$$ values('CALL-IN'),('CALL-OUT'),('SMS-IN'),('SMS-OUT')$$)  
as table1(callnumber1 int,callnumber2 int,"CALL-IN" int,"CALL-OUT" int,"SMS-IN"   int,"SMS-OUT" int,FirstCallDate date,LastCallDate date);  

Answer 1

为此您不需要 crosstab()。条件计数可以完成这项工作:

SELECT callnumber1, callnumber2
     , count(calltype = 'CALL-IN'  OR NULL) AS call_in
     , count(calltype = 'CALL-OUT' OR NULL) AS call_out
     , count(calltype = 'SMS-IN'   OR NULL) AS sms_in
     , count(calltype = 'SMS-OUT'  OR NULL) AS sms_out
     , min(calldate) AS first_calldate
     , max(calldate) AS last_calldate
     , count(*)      AS total_calls  
FROM   table1   
WHERE  calltype in ('CALL-IN','CALL-OUT','SMS-IN','SMS-OUT')  
GROUP  BY 1,2
ORDER  BY 1,2

• 使用 count(*) 而不是 count(callnumber1||callnumber2)，假设两列都定义为 NOT NULL。

• count(calltype = 'CALL-IN' OR NULL) 是如何工作的？
  Compute percents from SUM() in the same SELECT sql query