求大神sql的写法

Question

如图，有a,b,c3个分组，其中a、b分组weidu1-weidu5都存在空值，如何查询分组维度完整率，图中只有c分组所有维度都完整的，结果应该是1/3=0.3333，如何写sql，各位大神求教

Answer 1

--1.假设表名为tmp
--2.SQL实现如下：
select round(count(case when c1 = c2 then 1 else null end)/count(distinct fenzu),4)
from(
select  fenzu, count(weidu) as c1 ,count(val) as c2
  from (select * from tmp unpivot include nulls (weidu for val in(weidu1,weidu2,weidu3,weidu4,weidu5))) 
  group by fenzu
  )
 

Answer 2

weidu1~维度5的数据类型都不一样怎么处理呢，版主

Answer 3

weidu1-weidu5的数据类型不一样怎么处理呢

Answer 4

不一致的话，使用cast函数统一转换为varchar2类型即可。
比如：cast(weidu1 as varchar2(xx))，长度xx根据实际情况调整。

Answer 5

试试这个：select round(count(case when c1 = c2 then 1 else null end)/count(distinct fenzu),4)
from(
select  fenzu, count(weidu) as c1 ,count(val) as c2
  from (select * from (select cast(weidu1 as varchar2(40)) as weidu, cast(weidu2 as varchar2(40)) as weidu2,cast(weidu3 as varchar2(40)) as weidu3,
cast(weidu4 as varchar2(40)) as weidu4,cast(weidu5 as varchar2(40)) as weidu5 from tmp) unpivot include nulls (weidu for val in(weidu1,weidu2,weidu3,weidu4,weidu5))) 
  group by fenzu
  )
  

Answer 6

调整了下：select round(count(case when c1 = c2 then 1 else null end)/count(distinct fenzu),4)
from(
select  fenzu, count(weidu) as c1 ,count(val) as c2
  from (select * from (select fenzu,cast(weidu1 as varchar2(40)) as weidu1, cast(weidu2 as varchar2(40)) as weidu2,cast(weidu3 as varchar2(40)) as weidu3,
cast(weidu4 as varchar2(40)) as weidu4,cast(weidu5 as varchar2(40)) as weidu5 from tmp) unpivot include nulls (weidu for val in(weidu1,weidu2,weidu3,weidu4,weidu5))) 
  group by fenzu
  )
  

Answer 7

WITH tab AS
(SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, NULL WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, NULL WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL  )
SELECT SUM(decode(分组完整率,5,1,0))/COUNT(分组完整率) 整体完整率 FROM(
SELECT fenzu,SUM(fenzus)/COUNT(1) 分组完整率 FROM
(SELECT FENZU,
       (DECODE(WEIDU1, NULL, 0, 1) + DECODE(WEIDU2, NULL, 0, 1) + DECODE(WEIDU3, NULL, 0, 1) + DECODE(WEIDU4, NULL, 0, 1) + DECODE(WEIDU5, NULL, 0, 1) ） FENZUS 
       FROM TAB) GROUP BY FENZU);
整体完整率
----------
0.33333333

Answer 8

WITH tab AS
(SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, NULL WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5   FROM DUAL UNION ALL
SELECT 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'b' FENZU, 1 WEIDU1, NULL WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL UNION ALL
SELECT 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5   FROM DUAL  )
SELECT FENZU, SUM(FENZUS) / COUNT(1) 分组完整率
  FROM (SELECT FENZU,
               (DECODE(WEIDU1, NULL, 0, 1) + DECODE(WEIDU2, NULL, 0, 1) +
               DECODE(WEIDU3, NULL, 0, 1) + DECODE(WEIDU4, NULL, 0, 1) +
               DECODE(WEIDU5, NULL, 0, 1) ） FENZUS FROM TAB)
         GROUP BY FENZU;
FENZU 分组完整率
----- ----------
a            4.4
b              4
c              5赠送你一个，分组完整度计算

Answer 9

TEAM 是 fenzu，A B C D E 是weidu 1 2 3 4 5，一个行列转换就搞定了。如果表很大考虑优化可以用表连接代替 not in。
select (
select count(1) from(
select distinct team from te where team not in (
select team from te unpivot include nulls(
weidu for wd in (A,B,C,D,E)
)where weidu is null) ))  / 
(select count(1)from (select distinct team from te)) 数据完整率 from dual

Answer 10

select (case when a.分母=0,then 0,else b.分子/a.分母 end) as 分组维度完整率 from (select count(distinct fenzu) as 分母  from 表) a,
(select count(distinct fenzu) as 分子 from 表 where weidu1 is not null and weidu2 is not null and weidu3 is not null and weidu4 is not null and weidu5 is not null) b