单表统计查询的sql

    CREATE TABLE FD_LOAN_INFO_DATA(
       ID VARCHAR(32) NOT NULL,
       SJLY VARCHAR(200),
       SJYSYL VARCHAR(50),
       BCSJ VARCHAR(200),
        CONSTRAINT PK_LOAN_INFO_DATA PRIMARY KEY (ID)
    );


insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('1','平台1','1.0','2014-5-10');
insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('2','平台1','1.0','2014-5-10');
insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('3','平台1','1.5','2014-5-11');
insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('4','平台1','1.6','2014-5-12');

insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('5','平台2','0.6','2014-5-10');
insert into FD_LOAN_INFO_DATA(id,sjly,syl,bcsj) values('6','平台2','1.6','2014-5-12');


根据平台分组，获取每天平均收益率，并且拼成一个字符串，
如果某天没有借款，用0代替
sjly	syl
平台1	1.0,1.5.1.6
平台2	0.6,0,1.6

SELECT A.sjly, listagg(A.SJYSYL_AVG, ' , ') WITHIN GROUP(ORDER BY A.sjly)  AS syl
  FROM (SELECT sjly, AVG(SJYSYL) AS SJYSYL_AVG, bcsj
          FROM FD_LOAN_INFO_DATA
         GROUP BY sjly, bcsj) A
 GROUP BY A.sjly

select sjly,sjysyl from 
(select sjly,to_char(wm_concat(sjysyl)over(partition by sjly order by sjly,bcsj))sjysyl,row_number()over(partition by sjly order by sjly,bcsj)rn from 
(
select t2.sjly, decode(t1.sjysyl, null, 0, t1.sjysyl) sjysyl, t2.bcsj
  from (SELECT sjly, to_char(avg(to_number(SJYSYL))) SJYSYL, bcsj
          FROM FD_LOAN_INFO_DATA T1
         group by bcsj, sjly) t1,
       (select *
          from (select distinct bcsj from FD_LOAN_INFO_DATA),
               (select distinct sjly from FD_LOAN_INFO_DATA)) t2
 where t1.bcsj(+) = t2.bcsj
   and t1.sjly(+) = t2.sjly
 order by t2.sjly, t2.bcsj
)) t4,
(select count(distinct bcsj)ct from FD_LOAN_INFO_DATA) t5
where ct=rn