现在我有个表 table1 ,
记录一个月内,每个号码拨打的长途地
call_num area
号码 地域
132 深圳
123 北京
123 北京
123 北京
132 深圳
132 上海
145 上海
145 上海
123 广州
132 广州
145 广州
145 深圳
123 广州
132 北京
123 深圳
123 深圳
123 北京
132 北京
132 上海
145 苏州
145 苏州
123 广州
132 苏州
145 苏州
145 苏州
123 广州 但是我现在需要统计构建这样一个结果表call_num long_addr_1 long_addr_2 long_addr3
号码 长途地1 长途地2 长途地3即把当月每个号码拨打次数最多那3个长途地给列出来,并且形成上面的表格
该如何构建呢例如对于号码123 ,当月的数据就可能是
call_num long_addr_1 long_addr_2 long_addr3
号码 长途地1 长途地2 长途地3
----------------------------------------------
123 北京 深圳 广州
记录一个月内,每个号码拨打的长途地
call_num area
号码 地域
132 深圳
123 北京
123 北京
123 北京
132 深圳
132 上海
145 上海
145 上海
123 广州
132 广州
145 广州
145 深圳
123 广州
132 北京
123 深圳
123 深圳
123 北京
132 北京
132 上海
145 苏州
145 苏州
123 广州
132 苏州
145 苏州
145 苏州
123 广州 但是我现在需要统计构建这样一个结果表call_num long_addr_1 long_addr_2 long_addr3
号码 长途地1 长途地2 长途地3即把当月每个号码拨打次数最多那3个长途地给列出来,并且形成上面的表格
该如何构建呢例如对于号码123 ,当月的数据就可能是
call_num long_addr_1 long_addr_2 long_addr3
号码 长途地1 长途地2 长途地3
----------------------------------------------
123 北京 深圳 广州
from tb
group by call_num;你看这样是否符合你的要求?
(SELECT call_num, area, call_count,
ROW_NUMBER () OVER (PARTITION BY call_num ORDER BY call_count DESC) rank1
FROM (SELECT call_num, area, COUNT (*) call_count
FROM table1
GROUP BY call_num, area))
SELECT num1.call_num, num1.area, num2.area, num3.area
FROM (SELECT call_num, area, rank1
FROM a1
WHERE rank1 = 1) num1
JOIN
(SELECT call_num, area, rank1
FROM a1
WHERE rank1 = 2) num2 ON (num1.call_num = num2.call_num)
JOIN
(SELECT call_num, area, rank1
FROM a1
WHERE rank1 = 3) num3 ON (num1.call_num = num3.call_num)
(select num,area,count(1) cnt
from tablog
group by num,area
order by cnt desc having rownum <2) a,
(select
select num,area,count(1) cnt
from tablog
group by num,area
order by cnt desc having rownum <3) b,
(select
select num,area,count(1) cnt
from tablog
group by num,area
order by having rownum <4) c
where a.num = b.num and a.num = c.num and
b.area <> a.area and a.area <> b.area and a.area <> c.area);
应该能符合你的功能要求;但通话记录这类张数据量估计会很大,最好是使用过程来实现
SQL> insert into table1 values(132,'深圳');已创建 1 行。SQL> insert into table1 values(123,'北京');已创建 1 行。SQL> insert into table1 values(123,'北京');已创建 1 行。SQL> insert into table1 values(123,'北京');已创建 1 行。SQL> insert into table1 values(132,'深圳');已创建 1 行。SQL> insert into table1 values(132,'上海');已创建 1 行。SQL> insert into table1 values(145,'上海');已创建 1 行。SQL> insert into table1 values(145,'上海');已创建 1 行。SQL> insert into table1 values(123,'广州');已创建 1 行。SQL> insert into table1 values(132,'广州');已创建 1 行。SQL> insert into table1 values(145,'广州');已创建 1 行。SQL> insert into table1 values(145,'深圳');已创建 1 行。SQL> insert into table1 values(123,'广州');已创建 1 行。SQL> insert into table1 values(132,'北京');已创建 1 行。SQL> insert into table1 values(123,'深圳');已创建 1 行。SQL> insert into table1 values(123,'深圳');已创建 1 行。SQL> insert into table1 values(123,'北京');已创建 1 行。SQL> insert into table1 values(132,'北京');已创建 1 行。SQL> insert into table1 values(132,'上海');已创建 1 行。SQL> insert into table1 values(145,'苏州');已创建 1 行。SQL> insert into table1 values(145,'苏州');已创建 1 行。SQL> insert into table1 values(123,'广州');已创建 1 行。SQL> insert into table1 values(132,'苏州');已创建 1 行。SQL> insert into table1 values(145,'苏州');已创建 1 行。SQL> insert into table1 values(145,'苏州');已创建 1 行。SQL> insert into table1 values(123,'广州');已创建 1 行。SQL>
SQL> select call_num,wmsys.wm_concat(area||cnt) long_addr
2 from (
3 select call_num,area,count(1) as cnt,row_number() over(partition by call_num order by call_num,count(1) desc) as rn
4 from table1
5 group by call_num,area
6 )
7 where rn<=3
8 group by call_num; CALL_NUM LONG_ADDR
---------- --------------------------------------------------------------------------------
123 北京4,广州4,深圳2
132 深圳2,上海2,北京2
145 苏州4,广州1,上海2SQL>