sql - 选择按值分组的行之间共享的最后日期的所有值

Question

我有一个 Postgresql 表，其中包含一段时间内国家及其大陆的值列表。值可以为 NULL。我想随着时间的推移获得每个大陆的总和，直到每个大陆都有数据的最新日期。

这是我的表(view on DB Fiddle):

| continent | country | date       | value | id  |
| --------- | ------- | ---------- | ----- | --- |
| Europe    | Germany | 2020-05-25 | 10    | 1   |
| Europe    | Germany | 2020-05-26 | 11    | 2   |
| Europe    | Germany | 2020-05-27 | 12    | 3   |
| Europe    | Germany | 2020-05-28 | 13    | 4   |
| Europe    | Italy   | 2020-05-25 | 20    | 5   |
| Europe    | Italy   | 2020-05-26 | 21    | 6   |
| Europe    | Italy   | 2020-05-27 | 22    | 7   |
| Europe    | Italy   | 2020-05-28 | 23    | 8   |
| Europe    | France  | 2020-05-25 | 30    | 9   |
| Europe    | France  | 2020-05-26 | 31    | 10  |
| Europe    | France  | 2020-05-27 | 32    | 11  |
| Europe    | France  | 2020-05-28 | NULL  | 12  |
| Africa    | Congo   | 2020-05-25 | 40    | 13  |
| Africa    | Congo   | 2020-05-26 | 41    | 14  |
| Africa    | Congo   | 2020-05-27 | NULL  | 15  |

这就是我想要回来的。请注意，欧洲包括截至 27 日的数据，因为法国没有 28 日的数据，非洲包括 26 日的数据，因为这是其国家拥有数据的最后日期。

| continent | date       | value |
| --------- | ---------- | ----- |
| Europe    | 2020-05-27 | 66    |
| Africa    | 2020-05-26 | 41    |
| Europe    | 2020-05-26 | 63    |
| Africa    | 2020-05-25 | 40    |
| Europe    | 2020-05-25 | 60    |

我设法通过包括每个大陆拥有每个日期数据的国家数量来几乎达到目标。

SELECT
    countries.continent,
    countries.date,
    SUM(countries.value) AS value,
    COUNT(countries.country) AS countries_count
FROM
    countries
WHERE
    countries.value IS NOT NULL
GROUP BY
    countries.continent,
    countries.date
ORDER BY
    countries.date DESC,
    countries.continent;

| continent | date       | value | countries_count |
| --------- | ---------- | ----- | --------------- |
| Europe    | 2020-05-28 | 36    | 2               |
| Europe    | 2020-05-27 | 66    | 3               |
| Africa    | 2020-05-26 | 41    | 1               |
| Europe    | 2020-05-26 | 63    | 3               |
| Africa    | 2020-05-25 | 40    | 1               |
| Europe    | 2020-05-25 | 60    | 3               |

我还设法获得了每个大陆的国家数量。

SELECT
    countries.continent,
    COUNT(DISTINCT countries.country) as number_of_countries
FROM
    countries
GROUP BY
    countries.continent;

| continent | number_of_countries |
| --------- | ------------------- |
| Africa    | 1                   |
| Europe    | 3                   |

我被困在如何组合两个查询以过滤掉没有获得该大陆全部国家/地区数量的行(例如，选择 countries_count 为 3 为 Europe 的行和 1 为 0x1046.

这是我想要返回的最终结果:

| continent | date       | value |
| --------- | ---------- | ----- |
| Europe    | 2020-05-27 | 66    |
| Africa    | 2020-05-26 | 41    |
| Europe    | 2020-05-26 | 63    |
| Africa    | 2020-05-25 | 40    |
| Europe    | 2020-05-25 | 60    |

或者也许有一种完全不同的方式来解决这个问题？

View on DB Fiddle

Answer 1

您可以将大陆上的国家数量与每个日期的可用数量进行比较——然后只使用两者匹配的日期(“完整数据”)。

不幸的是，Postgres 不支持 count(distinct)作为窗函数。但你可以这样做:

SELECT c.continent, c.date,
       SUM(c.value) AS value,
        COUNT(c.country) AS countries_count
FROM (SELECT c.*,
             COUNT(*) OVER (PARTITION BY continent, date) as num_on_date
      FROM countries c
      WHERE value IS NOT NULL
     ) c JOIN
     (SELECT continent, COUNT(DISTINCT country) as num_countries
      FROM countries
      GROUP BY continent
     ) cc
     ON cc.continent = c.continent
WHERE num_on_date = num_countries
GROUP BY c.continent, c.date
ORDER BY c.date DESC, c.continent;

Here是一个db<> fiddle 。

您也可以使用 HAVING 中的过滤器执行此操作。条款:

SELECT c.continent, c.date,
       SUM(c.value) AS value,
        COUNT(c.country) AS countries_count
FROM countries c
WHERE value IS NOT NULL
GROUP BY c.continent, c.date
HAVING COUNT(*) = (SELECT COUNT(DISTINCT c2.country)
                   FROM countries c2
                   WHERE c2.continent = c.continent
                  )
ORDER BY c.date DESC, c.continent;

这会进行聚合，然后只保留行数与国家/地区数匹配的行。