SQL基于位置从总数中选择百分比

Question

我想解决以下问题：

给出每种运动类型的项目在区域10中占该体育类型项目总数的百分比。

我有两张桌子：

events ：列出所有体育赛事。列‘sport_type’定义了运动事件的类型；它有一个点几何图形。

zone：列出所有区域，其名称为ex。‘10区’它们有多边形几何学。

若要列出我所拥有的每一类体育项目的数量：

select e.sport_type as Sport_Type, count(e.sport_type) as Number_of_Events
from events e
group by e.sport_type

为了列出第10区每种类型的运动项目的数量，我有：

select e.sport_type as Sport_Type, count(e.sport_type) as Number_of_Events
from events e
inner join zones z
on st_contains(z.geom, e.geom)
where z.name = 'region 10'
group by e.sport_type

为了解决这个问题，我尝试过这样的方法，但没有奏效：

select e1.sport_type as Sport_Type,  (x.Numev / count(e1.sport_type)) as Number_of_Events
from events e1, (select e2.sport_type as evtype, count(e2.sport_type) as Numev
from programs e2
inner join zones z
on st_contains(z.geom, e2.geom)
where z.name = 'region 10'
group by e2.sport_type) x
group by e1.sport_type, x.Numev

Answer 1

使用两个子选择创建一个非常不言自明的查询：

SELECT sport_type, 100. * r10.total / sports.total AS r10_percent
FROM (
    SELECT sport_type, count(sport_type) AS total
    FROM events
    GROUP BY sport_type) sports
JOIN (
    SELECT sport_type, count(sport_type) AS total
    FROM events
    JOIN zones ON ST_Contains(zones.geom, events.geom)
    WHERE zones.name = 'region 10'
    GROUP BY events.sport_type) r10 USING (sport_type);

Answer 2

还没有测试，只是尝试，因为您有很多事件，您可以很容易地计算百分比(例如:1多选择外部)

select 
    e.sport_type as Sport_Type, 
    count(*) as totalEvents,
    sum(CASE 
            WHEN z.name = 'region 10' THEN 1
            ELSE 0
        END
    ) as totalEventsInRegion10
from events e
    left join zones z on st_contains(z.geom, e.geom)
group by e.sport_type