求一个单表统计查询的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代替
sjlysyl
平台11.0,1.5.1.6
平台20.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
------解决方案--------------------
引用: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代替
sjlysyl
平台11.0,1.5.1.6
平台20.6,0,1.6
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