PostgreSQL：在单个 SQL 语法中按从文本列计算的数值排序

Question

一列具有诸如“1/200”、“3.5”或“6”之类的字符串值。如何在单个 SQL 查询中将此字符串转换为数值？

我的实际 SQL 更复杂，这是一个简单的示例：

SELECT number_value_in_string FROM table 

number_value_in_string 的格式将是以下之一：

• ##
• #.##
• #/###

我需要按此列的数值排序。但当然 postgres 不同意我的观点，即 1/200 是一个正确的数字。

Answer 1

看到你的名字，我不得不发布你的答案的简化：

SELECT id, number_value_in_string FROM table
 ORDER BY CASE WHEN substr(number_value_in_string,1,2) = '1/'
        THEN 1/substr(number_value_in_string,3)::numeric 
        ELSE number_value_in_string::numeric END, id;

_{忽略可能被零除。}

Answer 2

我会定义一个存储函数来将字符串转换为数值，或多或少像这样：

CREATE OR REPLACE FUNCTION fraction_to_number(s CHARACTER VARYING)
RETURN DOUBLE PRECISION AS
BEGIN
   RETURN
   CASE WHEN s LIKE '%/%' THEN
       CAST(split_part(s, '/', 1) AS double_precision) 
       / CAST(split_part(s, '/', 2) AS double_precision)
   ELSE
       CAST(s AS DOUBLE PRECISION)
   END CASE
END

然后你可以ORDER BYfraction_to_number(weird_column)

如果可能的话，我会重新审视数据设计。所有这些复杂性真的有必要吗？

Answer 3

这个 postgres SQL 做到了这一点：

select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array(number_value_in_string, '/') as parts from table) x

这是对此代码的测试：

select (parts[1] :: decimal) / (parts[2] :: decimal) as quotient
FROM (select regexp_split_to_array('1/200', '/') as parts) x

输出：

0.005

请注意，您需要将其包装在 case 语句中，以防止被零除错误和/或数组超出范围如果列不包含正斜杠，则会出现边界问题等。

另请注意，您可以在没有内部选择的情况下完成此操作，但必须使用regexp_split_to_array代码> 两次（每个部分一次），您可能会受到性能影响。尽管如此，内联编码可能更容易，并且只需接受较小的性能损失即可。

Answer 4

我设法解决了我的问题。谢谢大家。
它是这样的，在一个 SQL 中。 （我使用的是 POSTGRESQL）

它将对输入的字符串进行排序，如“#”、“#.#”或“1/#”

SELECT id, number_value_in_string FROM table ORDER BY CASE WHEN position('1/' in number_value_in_string) = 1 
    THEN 1/substring(number_value_in_string from (position('1/' in number_value_in_string) + 2) )::numeric 
    ELSE number_value_in_string::numeric 
END ASC, id

希望这对将来的人有帮助。