输出一个比率和百分比(作为字符串?)

huangapple go评论47阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年4月20日 09:31:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76059922.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定