使用复杂SQL查询的覆盖率百分比…?

SELECT AVG(covered)
FROM (
  SELECT CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS covered
  FROM app a
  LEFT JOIN skill s ON (s.id_app = a.id AND s.lvl >= 2)
  GROUP BY a.id
)

更有效的方法 MySQL :

SELECT AVG
       (
         IFNULL
         (
           (
           SELECT 1
           FROM skill s
           WHERE s.id_app = a.id
           AND s.lvl >= 2
           LIMIT 1, 1
           ), 0
         )
       )
FROM app a

一旦找到第二个熟练工,这将停止计数。 person 对于每一个 app .

如果你有一些效率 应用程序 但是很多 人 s。

未经测试的

select convert(float,count(*)) / (select count(*) from app) as percentage
from (
    select count(*) as number
    from skill
    where lvl >= 2
    group by id_app ) t
where t.number >= 2

逻辑是:百分比=100*(感兴趣的应用程序数)/(应用程序总数)

select 'percentage' = 
-- 100 times
  ( cast( 100 as float ) * 
-- number of apps of interest
  ( select count(id_app) 
    from ( select id_app, count(*) as skilled_count
           from skill
           where lvl >= 2
           group by id_app 
           having count(*) >= 2 ) app_counts ) 
-- divided by total number of apps
  / ( select count(*) from app ) 

需要转换为float,这样SQL就不只是做整数算术了。

SELECT SUM( CASE lvl WHEN 3 THEN 1 WHEN 2 THEN 1 ELSE 0 END ) / SUM(1) FROM SKILL

如果数据库具有if/then函数,而不是 CASE 用这个。例如,在MySQL中:

SELECT SUM( IF( lvl >= 2, 1, 0 ) ) / SUM(1) FROM SKILL

我不确定这是否比Tvanfosson的答案更好或更差,但不管怎么说,这里是:

SELECT convert(float, count(*)) / (Select COUNT(id) FROM APP) AS percentage
FROM APP INNER JOIN SKILL ON APP.id = SKILL.id 
WHERE (
   SELECT COUNT(id) 
   FROM SKILL AS Skill2 WHERE Skill2.id_app = APP.id and lvl >= 2
) >= 2