SQL中2列的差异%

Question

我有两个表-国家(id，名字，大陆)和population_years (id，人口，年份，country_id)。数据来源于2000-2010年，我试图计算这段时间内每个大陆平均人口的百分比差异。我试图通过创建一个临时表来实现它，该表生成的输出为：

​

但是，当我试图计算% diff (从下面的代码中可以看到)时，我不知道如何引用代码中的'avg pop 2000‘和'avg pop 2010’列，因为它们没有被分配给我可以引用的变量。在代码中，我使用了avg_pop_2010和avg_pop_2000来引用这些列--显然这并不有效。

WITH avg_pop AS( SELECT countries.continent, 
ROUND(AVG(CASE WHEN population_years.year = 2000 THEN population_years.population END), 2) as 'avg pop 2000',
ROUND(AVG(CASE WHEN population_years.year = 2010 THEN population_years.population END), 2) as 'avg pop 2010'
FROM countries 
JOIN population_years 
WHERE population_years.country_id = countries.id
GROUP BY 1)

SELECT countries.continent, ROUND(((avg_pop_2010 - avg_pop_2000)/avg_pop_2000)*100.0, 2) AS '%diff'
FROM avg_pop;

Answer 1

另一种选择是重复这些表达式--稍微优化一下：

select
    c.continent,
    round(avg(case when py.year = 2000 then py.population end), 2) avg_pop_2000,
    round(avg(case when py.year = 2010 then py.population end), 2) avg_pop_2010,
    round(
        100.* avg(case when py.year = 2010 then py.population else - py.population end)
        / avg(case when py.year = 2000 then py.population end),
        2
    ) percent_diff
from countries c
inner join population_years py on py.country_id = c.id
where py.year in (2010, 2020)
group by c.continent

边注：

• 不要使用单引号作为标识符！它们代表标准SQL中的文字字符串；一般来说，您应该更喜欢不需要引用的标识符。如果需要引用，请使用标准双引号(")，SQLite可以识别它
• 使用where子句对相关年份进行预筛选可以提高查询的效率
• 使用标准的联接语法；联接条件转到联接的on子句，而不是where子句。
• 舍入，然后计算百分比差是不准确的；先计算，然后再四舍五入。
• 表别名使查询更易于写入和读取。

Answer 2

    WITH avg_pop AS( SELECT countries.continent, 
    ROUND(AVG(CASE WHEN population_years.year = 2000 THEN population_years.population END), 2) as avg_pop_2000,
    ROUND(AVG(CASE WHEN population_years.year = 2010 THEN population_years.population END), 2) as avg_pop_2010
    FROM countries 
    JOIN population_years 
    WHERE population_years.country_id = countries.id
    GROUP BY 1)

    SELECT continent, ROUND((( avg_pop_2000 - avg_pop_2010)/avg_pop_2010)*100, 2) AS '%diff'
    FROM avg_pop;

下面是一个小演示：

演示

当检查其他评论和答案时，我意识到你需要给出不同的别名而不需要“”，所有的都会起作用.我已经更新了我的答案和演示。