我试图从 Postgres 数据库中提取一些统计信息,并进行了以下查询:
SELECT
city,
job_count,
TO_CHAR(job_count * 100 / SUM(job_count) OVER (), '90D00%') AS job_share
FROM (
SELECT
localities.name as city,
COUNT(*) AS job_count
FROM jobads
JOIN localities ON jobads.locality_uuid = localities.uuid
WHERE jobads.external = true
GROUP BY localities.name
ORDER BY job_count DESC
) AS job_count_table;
这是它返回的结果:
city | job_count | job_share
-----------------------+-----------+-----------
City #1 | 1300 | 13.00%
City #2 | 700 | 7.00%
City #3 | 400 | 4.00%
...
City #1200 | 1 | 0.01%
(1200 rows)
它返回数百行,其中大多数的作业数量非常少。我想将所有job_count
小于 100 的行合并到一行中,这样输出将如下所示:
city | job_count | job_share
-----------------------+-----------+-----------
City #1 | 1300 | 13.00%
City #2 | 700 | 7.00%
City #3 | 400 | 4.00%
Other cities | 2000 | 20.00%
(4 rows)
知道我该怎么做吗?
您可以使用另一个级别的聚合,并根据条件值进行分组。