在表中添加行，其形式为其他行的总和。

Question

我已经发布了一个similar question到这一个，但现在的问题是另一个。

我有一张像这样的桌子cases：

  disease   |                country                | year |  number   |  rate
------------+---------------------------------------+------+-----------+--------
 Diphtheria | Austria                               | 1989 |    190.00 |   2.47
 Tetanus    | Austria                               | 1989 |       NaN |    NaN 
 Pertussis  | Austria                               | 1989 |      0.00 |   0.00
 Measles    | Austria                               | 1989 |       NaN |    NaN
 Mumps      | Austria                               | 1989 |      0.00 |   0.00
 Rubella    | Austria                               | 1989 |       NaN |    NaN
 Polio      | Austria                               | 1989 |       NaN |    NaN
 Diphtheria | Belgium                               | 1989 |    180.00 |   2.42
 Tetanus    | Belgium                               | 1989 |      5.00 |   0.04  
 Pertussis  | Belgium                               | 1989 |      1.00 |   0.01
 Measles    | Belgium                               | 1989 |      0.00 |   0.00
 Mumps      | Belgium                               | 1989 |   2052.00 |  50.00
 Rubella    | Belgium                               | 1989 |      0.00 |   0.00
 Polio      | Belgium                               | 1989 |       NaN |    NaN
 Diphtheria | Austria                               | 1990 |      5.00 |   0.01
 Tetanus    | Austria                               | 1990 |    152.00 |   2.41 
 Pertussis  | Austria                               | 1990 |      0.00 |   0.00
 Measles    | Austria                               | 1990 |    850.00 |   3.55
 Mumps      | Austria                               | 1990 |       NaN |    NaN
 Rubella    | Austria                               | 1990 |     55.00 |   3.00
 Polio      | Austria                               | 1990 |      0.00 |   0.00
 Diphtheria | Belgium                               | 1990 |    191.00 |   2.48
 Tetanus    | Belgium                               | 1990 |     20.00 |   2.00
 Pertussis  | Belgium                               | 1990 |      5.00 |   0.40
 Measles    | Belgium                               | 1990 |      0.00 |   0.00
 Mumps      | Belgium                               | 1990 |      0.40 |   0.02
 Rubella    | Belgium                               | 1990 |     85.00 |   6.08
 Polio      | Belgium                               | 1990 |     10.00 |   0.60
 ...        | ...                                   |  ... |       ... |    ...  

我想要添加一些行，计算为一些值的和。我试着更好地解释自己。

以下查询计算白喉、破伤风、百日咳(和其他.)值的数目和比率值之和。并创建带有求和值的行。

查询：

SELECT 
    SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND number <> 'NaN' THEN number END) AS DTP_NUMBER,
    SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND rate <> 'NaN' THEN rate END) AS DTP_RATE,
    SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND number <> 'NaN' THEN number END) AS MMR_NUMBER,
    SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND rate <> 'NaN' THEN rate END) AS MMR_RATE,
    SUM(CASE WHEN disease IN ('Polio') AND number <> 'NaN' THEN number END) AS Polio_NUMBER,
    SUM(CASE WHEN disease IN ('Polio') AND rate <> 'NaN' THEN rate END) AS Polio_RATE,
    country,
    year
FROM cases
GROUP BY country, year;

执行结果：

我想将这些结果添加到cases表中，以便得到如下内容：

  disease   |                country                | year |  number   |  rate
------------+---------------------------------------+------+-----------+--------
 Diphtheria | Austria                               | 1989 |    190.00 |   2.47
 Tetanus    | Austria                               | 1989 |       NaN |    NaN 
 Pertussis  | Austria                               | 1989 |      0.00 |   0.00
 Measles    | Austria                               | 1989 |       NaN |    NaN
 Mumps      | Austria                               | 1989 |      0.00 |   0.00
 Rubella    | Austria                               | 1989 |       NaN |    NaN
 Polio      | Austria                               | 1989 |       NaN |    NaN
 DTP        | Austria                               | 1989 |    190.00 |   2.47
 MMR        | Austria                               | 1989 |      0.00 |   0.00
 Diphtheria | Belgium                               | 1989 |    180.00 |   2.42
 Tetanus    | Belgium                               | 1989 |      5.00 |   0.04  
 Pertussis  | Belgium                               | 1989 |      1.00 |   0.01
 Measles    | Belgium                               | 1989 |      0.00 |   0.00
 Mumps      | Belgium                               | 1989 |   2052.00 |  50.00
 Rubella    | Belgium                               | 1989 |      0.00 |   0.00
 Polio      | Belgium                               | 1989 |       NaN |    NaN
 DTP        | Belgium                               | 1989 |    186.00 |   2.47
 MMR        | Belgium                               | 1989 |   2052.00 |  50.00
 ...        | ...                                   |  ... |       ... |    ...  

我该怎么做？我曾想过使用这样的查询：

    INSERT INTO cases (column names) 
    VALUES (
            SELECT 
            SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND number <> 'NaN' THEN number END) AS DTP_NUMBER,
            SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND rate <> 'NaN' THEN rate END) AS DTP_RATE,
            SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND number <> 'NaN' THEN number END) AS MMR_NUMBER,
            SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND rate <> 'NaN' THEN rate END) AS MMR_RATE,
            SUM(CASE WHEN disease IN ('Polio') AND number <> 'NaN' THEN number END) AS Polio_NUMBER,
            SUM(CASE WHEN disease IN ('Polio') AND rate <> 'NaN' THEN rate END) AS Polio_RATE,
            country,
            year
        FROM cases
        GROUP BY country, year
    );

但我要在列名里放什么？

编辑1

我的cases表是这样创建的：

CREATE TABLE IF NOT EXISTS cases (
    id SERIAL PRIMARY KEY,
    disease VARCHAR(64) NOT NULL,
    country VARCHAR(255) NOT NULL,
    year NUMERIC(4) NOT NULL,
    number NUMERIC(20, 2) NOT NULL,
    rate NUMERIC(20, 2) NULL,
    UNIQUE (disease, country, year, number));

当我运行Pham X. Bach创建的查询时，我得到：

ERROR:  ERROR:  null values in the "number" column violate the non-null constraint
DETAIL:  The error line contains (8077, DTP, Poland, 2015, null, null).

如果以这种方式更改创建表的查询：

CREATE TABLE IF NOT EXISTS cases (
    id SERIAL PRIMARY KEY,
    disease VARCHAR(64) NOT NULL,
    country VARCHAR(255) NOT NULL,
    year NUMERIC(4) NOT NULL,
    number NUMERIC(20, 2),
    rate NUMERIC(20, 2),
    UNIQUE (disease, country, year, number));

然后我执行你的查询，我得到：

Error Message ERROR: A duplicate key value violates the unique constraint "cases_disease_country_year_number_key"
DETAIL:  The key (disease, country, year, number)=(Polio, United Kingdom, 1987, 0.00) yet exists.

如果运行Anadi Sharma查询，则在第3行中会出现语法错误。

Answer 1

你可以用这个：

INSERT INTO cases (
    disease, country, year, 
    number, 
    rate
) 
SELECT 
    'DTP' AS disease, country, year,
    COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND number <> 'NaN' THEN number END), 'NaN') AS number,
    COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND rate <> 'NaN' THEN rate END), 'NaN') AS rate
FROM cases
GROUP BY country, year
UNION ALL
SELECT 
    'MMR' AS disease, country, year,
    COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND number <> 'NaN' THEN number END), 'NaN') AS number,
    COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND rate <> 'NaN' THEN rate END), 'NaN') AS rate
FROM cases
GROUP BY country, year
UNION ALL
SELECT 
    'Polio_Sum' AS disease, country, year,
    COALESCE(SUM(CASE WHEN disease IN ('Polio') AND number <> 'NaN' THEN number END), 'NaN') AS number,
    COALESCE(SUM(CASE WHEN disease IN ('Polio') AND rate <> 'NaN' THEN rate END), 'NaN') AS rate
FROM cases
GROUP BY country, year
;

Answer 2

内部选择查询的结构不是cases表。您应该使用union来解决这个问题。尝试使用以下查询：

INSERT INTO cases 
    SELECT 
        disease,
        country,
        year,
        SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND number <> 'NaN' THEN number END) AS number,
        SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') AND rate <> 'NaN' THEN rate END) AS rate            
    FROM cases
    GROUP BY 1, 2, 3
    UNION ALL
    SELECT 
        disease,
        country,
        year,
        SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND number <> 'NaN' THEN number END) AS number,
        SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') AND rate <> 'NaN' THEN rate END) AS rate            
    FROM cases
    GROUP BY 1, 2, 3
    UNION ALL
    SELECT 
        disease,
        country,
        year,
        SUM(CASE WHEN disease IN ('Polio') AND number <> 'NaN' THEN number END) AS number,
        SUM(CASE WHEN disease IN ('Polio') AND rate <> 'NaN' THEN rate END) AS rate            
    FROM cases
    GROUP BY 1, 2, 3