Блог

Я пытаюсь определить географию наиболее загруженных потребителей для каждого местоположения, но затем возвращаю ровно столько географических данных для каждого местоположения, чтобы получить по крайней мере 90% потребителей для каждого местоположения. База данных — postgres.

БД возится с данными https://www.db-fiddle.com/f/uUgChHGoF33khmXZPRxTkR/2

В этих данных указаны 7 локаций с их наиболее загруженными географическими районами и процент от общего числа, который представляет каждая локация-гео.
Выборка данных (например, geo 609 представляет 75,7% бизнеса местоположения A):

     Location    Geo     loc_geo_pct_total
    A           609     0.757
    A           479     0.193
    A           463     0.006
    A           606     0.003
    ...
    D           609     0.903
    D           604     0.060
    ...and so on
  

Я подумал, что начну с попытки получить совокупную сумму для каждого местоположения при сортировке по убыванию geo%, чтобы результат выглядел следующим образом:

     Location    Geo     loc_geo_pct_total   cumul_loc_geo
    A           609     0.757               0.757
    A           479     0.193               0.950
    A           463     0.006               0.956
    A           606     0.003               0.959
    ...
    D           609     0.903               0.903
    D           604     0.060               0.963
    ...and so on
  

Я пробовал разные запросы, включая этот, но этот запрос неверен, потому что он продолжает накапливаться независимо от местоположения.

     select location, geo, 
    sum(pctoftotal) over (order by location, geo desc rows between unbounded preceding and current row) as loc_geo_cumul_pct
    from tdata
    order by 1, 3 desc;
  

Как мне изменить этот запрос и вернуть результаты, подобные приведенной выше форме?

Как только я выясню это, я смогу перейти ко второй проблеме, где я хочу показать достаточно географических регионов для каждого местоположения, чтобы получить > = 90%. Итак, мои данные в конечном итоге будут показывать 2 географии на местоположение, за исключением того, что местоположению D требуется только одна география, потому что geo 609 превышает 0,9.

Буду признателен за любую помощь по первому вопросу, а затем я смогу заняться вторым вопросом.

Вам нужно использовать partition by:

 select location, geo, 
    sum(pctoftotal) over (partition by location order by geo desc rows between unbounded preceding and current row) as loc_geo_cumul_pct
    from tdata
  

Редактировать 1:

 select location, geo, 
        sum(pctoftotal) over (partition by location order by loc_geo_pct_total desc rows between unbounded preceding and current row) as loc_geo_cumul_pct
        from tdata
  

Редактировать 2:

 --selecting only rows where loc_geo_cumul_pct<=start_loc_geo`
Select * from
(
--find first value for each location where loc_geo_cumul_pct>=0.9
Select *,min(case when loc_geo_cumul_pct>=0.9 then loc_geo_cumul_pct end) over (partition by location) start_loc_geo
from
 (
   select location, geo, 
            sum(pctoftotal) over (partition by location order by loc_geo_pct_total desc rows between unbounded preceding and current row) as loc_geo_cumul_pct
            from tdata
 ) X
) Y 
Where loc_geo_cumul_pct<=start_loc_geo