查找表中聚合值之间的最大值。

Question

我正在使用一个PostgreSQL数据库和一个在Node.js中构建的web应用程序。

我有一张像这样的桌子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
 ...        | ...                                   |  ... |       ... |    ...   

总共有8040行，7个不同的疾病值，32个不同的国家值和36个不同的年份值。

我必须结合一些基于疾病的价值，并找到最大值。例如，我需要将白喉、破伤风和百日咳合并成一个新值(称为DTP)，其数量(和比率)是单个值的总和。麻疹、腮腺炎和风疹也是如此。其他价值(脊髓灰质炎)仍然保持现状。

因此，这是一个中间步骤：

  disease   |                country                | year |  number   |  rate
------------+---------------------------------------+------+-----------+--------
 DTP        | Austria                               | 1989 |    190.00 |   2.47
 MMR        | Austria                               | 1989 |      0.00 |   0.00
 Polio      | Austria                               | 1989 |       NaN |    NaN
 DTP        | Belgium                               | 1989 |    186.00 |   2.47
 MMR        | Belgium                               | 1989 |   2052.00 |  50.00
 Polio      | Belgium                               | 1989 |       NaN |    NaN
 DTP        | Austria                               | 1990 |    157.00 |   2.42
 MMR        | Austria                               | 1990 |    905.00 |   6.55
 Polio      | Austria                               | 1990 |      0.00 |   0.00
 DTP        | Belgium                               | 1990 |    216.00 |   4.88
 MMR        | Belgium                               | 1990 |     85.40 |   7.00
 Polio      | Belgium                               | 1990 |     10.00 |   0.60
 ...        | ...                                   |  ... |       ... |    ...   

求和值时，我认为NaN是0。

在此之后，我需要获得每个不同疾病元素的最大值，因此：

max DTP number =  216.00
max DTP rate = 4.88
max MMR number = 2052.00
max MMR rate = 5.00
max Polio number = 10.00
max Polio rate = 0.60

我需要的是最大值，所以我不介意创建中间表。不管创建与否，两者都是可以的.

我该怎么做？

Answer 1

你可以用这个：

WITH intermediate_table AS
(
    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
)
SELECT MAX(DTP_NUMBER) AS MAX_DTP_NUMBER,
    MAX(DTP_RATE) AS MAX_DTP_RATE,
    MAX(MMR_NUMBER) AS MAX_MMR_NUMBER,
    MAX(MMR_RATE) AS MAX_MMR_RATE,
    MAX(Polio_NUMBER) AS MAX_Polio_NUMBER,
    MAX(Polio_RATE) AS MAX_Polio_RATE
FROM intermediate_table;

如果需要，可以在查询中使用ROUND。