我已经发布了一个与此类似的问题，但现在问题是另一个问题。
我有一个这样的表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;
执行结果：
我想将这些结果添加到案例表中，以获得如下结果：
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
);
但是我必须在列名中放置什么？
我的案例表是这样创建的：
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 行收到语法错误。
你可以用这个：
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
;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句