英文:
Output a ratio and the percentage (as a string?)
问题
I have successfully calculated a value I want from a database. I now want to take that value, put it over the total number from the table I'm looking at (to get the ratio) and also output the percentage. I'm not quite sure if I need to convert those to strings to output them, or some other method? I would love some help.
This is the code I have so far to calculate the value I want in the numerator, but I'm not sure how to combine the denominator (I imagine I'll have to use some sort of COUNT command).
use rt_database;
DELIMITER //
CREATE PROCEDURE Generate_Relationship_Ubiquity()
BEGIN
DECLARE Desired_Relationship VARCHAR (20);
SET Desired_Relationship = 'RS-008';
SELECT COUNT(DISTINCT statements.`Source ID`) AS "Relationship Ubiquity Count"
FROM observations
INNER JOIN statements ON observations.`Statement ID` = statements.`Statement ID`
WHERE observations.`Relationship ID` = Desired_Relationship;
END;
//
DELIMITER ;
CALL Generate_Relationship_Ubiquity;
DROP PROCEDURE Generate_Relationship_Ubiquity;
英文:
I have successfully calculated a value I want from a database. I now want to take that value, put it over the total number from the table I'm looking at (to get the ratio) and also output the percentage. I'm not quite sure if I need to convert those to strings to output them, or some other method? I would love some help.
This is the code I have so far to calculate the value I want in the numerator, but I'm not sure how to combine the denominator (I imagine I'll have to use some sort of COUNT command).
use rt_database;
DELIMITER //
CREATE PROCEDURE Generate_Relationship_Ubiquity()
BEGIN
DECLARE Desired_Relationship VARCHAR (20);
SET Desired_Relationship = 'RS-008';
SELECT COUNT(DISTINCT statements.`Source ID`) AS "Relationship Ubiquity Count"
FROM observations
INNER JOIN statements ON observations.`Statement ID` = statements.`Statement ID`
WHERE observations.`Relationship ID` = Desired_Relationship;
END;
//
DELIMITER ;
CALL Generate_Relationship_Ubiquity;
DROP PROCEDURE Generate_Relationship_Ubiquity;
答案1
得分: 0
你只需要一个子查询而无需 where 条件即可获得总数:
SELECT COUNT(DISTINCT statements.`Source ID`)
/ (select count(distinct statements.`Source ID`) from observations join statements using(`Statement ID`)) ubiquity_ratio
FROM observations...
如果您需要多次使用总数(单独返回它以及比率,或同时返回比率和百分比),请将子查询放在常用表达式中:
WITH total_distinct_sources AS (select count(distinct statements.`Source ID`) as sources from observations join statements using(`Statement ID`))
SELECT
total_distinct_sources.sources total_ubiquity,
COUNT(DISTINCT statements.`Source ID`) "Relationship Ubiquity Count",
COUNT(DISTINCT statements.`Source ID`) / total_distinct_sources.sources ubiquity_ratio,
100 * COUNT(DISTINCT statements.`Source ID`) / total_distinct_sources.sources ubiquity_percent
FROM total_distinct_sources
CROSS JOIN observations
INNER JOIN statements ON observations.`Statement ID` = statements.`Statement ID`
WHERE observations.`Relationship ID` = Desired_Relationship;
英文:
You just need a subquery without the where condition to get the total:
SELECT COUNT(DISTINCT statements.`Source ID`)
/ (select count(distinct statements.`Source ID`) from observations join statements using(`Statement ID`)) ubiquity_ratio
FROM observations...
If you need the total more than once (to return it separately as well as the ratio, or to return both a ratio and a percentage), put the subquery in a common table expression:
WITH total_distinct_sources AS (select count(distinct statements.`Source ID`) as sources from observations join statements using(`Statement ID`))
SELECT
total_distinct_sources.sources total_ubiquity,
COUNT(DISTINCT statements.`Source ID`) "Relationship Ubiquity Count",
COUNT(DISTINCT statements.`Source ID`) / total_distinct_sources.sources ubiquity_ratio,
100 * COUNT(DISTINCT statements.`Source ID`) / total_distinct_sources.sources ubiquity_percent
FROM total_distinct_sources
CROSS JOIN observations
INNER JOIN statements ON observations.`Statement ID` = statements.`Statement ID`
WHERE observations.`Relationship ID` = Desired_Relationship;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论