Блог

Главная - Вопросы по программированию - Подсчет количества пользователей в стране — проблема с SQL-запросом

Подсчет количества пользователей в стране — проблема с SQL-запросом

#sql #postgresql #count

#sql #postgresql #подсчет

Вопрос:

У меня есть две таблицы.

  • Один, содержащий страны с идентификатором и геометрией
  • Другой содержит клиентов с именем, геометрией и логическим значением, показывающим, активны они или нет.
 Countries
iD     name     geometry
1634   UK       xxx
2357   USA      xxx
3345   Mexico   xxx
4694   Italy    xxx

Customers
name         geometry    statusActive
Hernandez    xxx         TRUE
Taylor       xxx         FALSE
Monte        xxx         TRUE
Winter       xxx         TRUE
Best         xxx         TRUE
Twist        xxx         TRUE

Я хочу узнать, сколько активных клиентов в каждой стране.

Шаг 1 — Объединение данных: я мог бы объединить две таблицы на основе геометрии. С помощью ST_Contains я могу проверить, какой из клиентов находится в какой стране. Это работает хорошо. В результате я получаю таблицу от клиентов, в которой дополнительно указан столбец идентификатора страны. Кроме того, возвращаются только клиенты со статусом active.

Это мой оператор SQL:

 SELECT customers.*, countries.id
FROM customers
JOIN countries ON ST_Contains(countries.geometry, customers.geometry)
WHERE customers.statusActive IS TRUE

Это результирующая таблица

 Customers
name        geometry    statusActive    id
Hernandez   xxx         TRUE            1634
Monte       xxx         TRUE            4694
Winter      xxx         TRUE            2357
Best        xxx         TRUE            2357
Twist       xxx         TRUE            4694

Шаг 2 — Подведите итоги клиентов в каждой стране. Я не знаю, как я могу этого добиться. Я уже пытался использовать GROUP BY на основе идентификатора страны, но запрос выполняется вечно.
Это мой оператор SQL:

 SELECT covid.county.fips, Count (*) As Number
FROM covid.tweets_sub
JOIN covid.county on ST_Contains(covid.county.geom, covid.tweets_sub.geom)
WHERE covid.tweets_sub.corona_related is TRUE
GROUP BY covid.county.fips
ORDER BY covid.county.fips
LIMIT 100

Что я делаю не так?

Вот как должен выглядеть желаемый результат:

 NumberActiveUsers   ID
1                   1634
2                   2357
0                   3345    
2                   4694

Комментарии:

1. Это правильный запрос для вашей модели данных, он просто медленный, потому что он должен вычислять страну всех ваших клиентов, сверяя их геометрию с геометрией каждой страны. Я полагаю, что ваш первый запрос был быстрее, поскольку вы извлекли только первые несколько строк, если вы попытаетесь вычислить страну для каждого клиента, это займет гораздо больше времени. Я бы посоветовал вам изменить свою модель данных так, чтобы country_id сохранялся в клиентах, а также в геометрии — вы можете просмотреть его при вставке строки и заполнить значение по строкам для ваших существующих клиентов.

2. большое спасибо за проверку кода. По крайней мере, я могу быть уверен, что это будет работать правильно! Спасибо за подсказку, как адаптировать структуру данных для более эффективного хранения данных!