如何从基于其他列的值和选择在 postgres 中创建列? [英] How can I create a column in postgres from values and selections based on other columns?

问题描述

我想在我的表中创建一个新字段(或两个)，它是其他字段的串联，这看起来相对简单.但是我用来帮助创建以下字段的 case 语法或 if/when 语法是什么(GPA_TXT 和 newfield)?

I want to create a new field (or two) in my table that is a concatenation of other fields, which seems relatively straightforward. But what is the case syntax or if/when syntax I'd use to help create the following fields (GPA_TXT, and newfield)?

逻辑是:每个GPA应该是#.#，每个新字段应该是:

The logic is: Each GPA should be #.#, each new field should be:

name & "-" & GPA_TXT & (
    case where GPA_TXT > 3.3
        set newfield = newfield & 'GradeA',
    case where GPA_TXT >2.7 and GPA_TXT < 3.3
        set newfield = newfield & "GradeB",
    etc...
)

例如:

name         major     GPA(num) GPA_TXT   [newfield]
Bob          sci       2        02.0      Bob-sci-GradeC-02.0
Jane         chem      3.1      03.1      Jane-chem-GradeB-03.1
Charlie      phys      3.7      03.7      Charlie-phys-GradeA-03.7
Garfield     food      0        00.0      Garfield-food-GradeF-00.0

所以我想我在这里有两个问题:

So I guess I have two questions in here:

1. 如何创建 GPA TXT 字段.
2. 如何编写一个case语句，根据其他字段的值计算一个字段.

如果有人可以将我链接到带有示例的资源或解释，我将不胜感激！我正在查看文档，但没有示例就无处可去.

If anyone can link me to a resource with examples or explain I would greatly appreciate it! I'm looking through the documentation but not getting anywhere without examples.

推荐答案

重要说明:我会创建一个 view 基于您当前的表并避免添加新列，因为它们会使您的架构非规范化.在此处阅读更多信息.

Important note: I would create a view based on your current table and avoided adding new columns, as they will denormalize your schema. Read more here.

此外，我将使用小写名称作为所有标识符以避免 qouting.

Also, I will use lowercase names for all the identifiers to avoid qouting.

• 要形成 GPA_TXT 字段，您可以使用 to_char() 函数:to_char(gpa, 'FM09.0')(FM 会避免前面的空格结果字符串);

• 对于第二个字段，我会使用 GPA 而不是 GPA_TXT 进行数值比较.您可以在 the docs，但块可能是以下一个:

• to form GPA_TXT field you can use to_char() function: to_char(gpa, 'FM09.0') (the FM will avoid space in front of the resulting string);

• for the second field, I would use GPA and not GPA_TXT for numeric comparison. You can check more on CASE construct in the docs, but the block might be the following one:

CASE WHEN gpa >= 3.3 THEN 'A'
     WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
     WHEN gpa > 0 THEN 'C'
     ELSE 'F' END

抱歉，我不知道每个 GPA 是如何分配成绩的，请相应调整.

视图的结果查询可能是(也在 SQL Fiddle 上):

The resulting query for the view might be (also on SQL Fiddle):

SELECT name,major,gpa,
       to_char(gpa, 'FM09.0') AS gpa_txt,
       name||'-'||major||'-Grade'||
  CASE WHEN gpa >= 3.3 THEN 'A'
       WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
       WHEN gpa > 0 THEN 'C'
       ELSE 'F' END || '-' || to_char(gpa, 'FM09.0') AS adesc
  FROM atab;

要构建视图，只需在此查询之前添加 CREATE VIEW aview AS.

To build a view just prepend CREATE VIEW aview AS before this query.

编辑

如果您仍然要添加列，以下应该可以解决问题:

If you still go for adding columns, the following should do the trick:

ALTER TABLE atab ADD gpa_txt text, ADD adesc text;
UPDATE atab SET
    gpa_txt = to_char(gpa, 'FM09.0'),
    adesc = name||'-'||major||'-Grade'||
      CASE WHEN gpa >= 3.3 THEN 'A'
           WHEN gpa > 2.7 AND gpa < 3.3 THEN 'B'
           WHEN gpa > 0 THEN 'C'
           ELSE 'F' END || '-' || to_char(gpa, 'FM09.0');

这篇关于如何从基于其他列的值和选择在 postgres 中创建列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！