O great god writing SQL-CodePudding

As shown in figure, there is a, b, c3 group, among them a, b group weidu1 - weidu5 have null values, how to query the grouping dimensions full rate, figure only c group all dimensions are complete, the result should be a third=0.3333, how to write SQL, you great spirit consult

CodePudding user response:

 
- 1. Assume the table called TMP 
- 2. SQL implementation is as follows: 
Select round (count (case when c1=c2 then 1 else null end)/count (distinct fenzu), 4) 
The from (
The select fenzu, count (weidu) as c1, count (val) as c2 
The from (select * from TMP unpivot include crosstab (weidu for val in (weidu1 weidu2, weidu3, weidu4, weidu5))) 
Group by fenzu 
)

CodePudding user response:

Weidu1 5 ~ dimension data types are not how to deal with, as a moderator

CodePudding user response:

reference 1st floor qq646748739 response:

 
- 1. Assume the table called TMP 
- 2. SQL implementation is as follows: 
Select round (count (case when c1=c2 then 1 else null end)/count (distinct fenzu), 4) 
The from (
The select fenzu, count (weidu) as c1, count (val) as c2 
The from (select * from TMP unpivot include crosstab (weidu for val in (weidu1 weidu2, weidu3, weidu4, weidu5))) 
Group by fenzu 
)

Weidu1 - weidu5 data type is not the same as how to deal with

CodePudding user response:

Inconsistent, use cast function can be unified into a varchar2 type,
Such as: cast (weidu1 as varchar2 (xx)), the length of the xx adjustment according to actual condition,

CodePudding user response:

Try this:

 
Select round (count (case when c1=c2 then 1 else null end)/count (distinct fenzu), 4) 
The from (
The select fenzu, count (weidu) as c1, count (val) as c2 
The 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 crosstab (weidu for val in (weidu1 weidu2, weidu3, weidu4, weidu5))) 
Group by fenzu 
)

CodePudding user response:

Under the adjusted:

 
Select round (count (case when c1=c2 then 1 else null end)/count (distinct fenzu), 4) 
The from (
The select fenzu, count (weidu) as c1, count (val) as c2 
The 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 crosstab (weidu for val in (weidu1 weidu2, weidu3, weidu4, weidu5))) 
Group by fenzu 
)

CodePudding user response:

 
WITH the TAB AS 
(SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, NULL WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5 FROM DUAL UNION ALL 
SELECT the '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 the 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'c' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL) 
SELECT SUM (decode (full rate group, 5, 0))/COUNT the integrity rate (branch) FROM whole complete rate (
The SELECT fenzu, SUM (fenzus)/COUNT (1) packet integrity rate 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 
The FROM TAB) GROUP BY FENZU);

 
The overall rate of complete 
-- -- -- -- -- -- -- -- -- -- 
0.33333333

CodePudding user response:

 
WITH the TAB AS 
(SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, NULL WEIDU1, 1 WEIDU2, 1 WEIDU3, 1 WEIDU4, 1 WEIDU5 FROM DUAL UNION ALL 
SELECT the 'a' FENZU, 1 WEIDU1, 1 WEIDU2, 1 WEIDU3, NULL WEIDU4, NULL WEIDU5 FROM DUAL UNION ALL 
SELECT the '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 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull