Блог

Существует два типа пользователей, которые покупают билеты в кино либо в городе А, городе В, городе С, либо онлайн.

У меня есть следующие таблицы, как:

Местоположения: Эта таблица состоит из местоположений киноцентров

 |--------------|------------|------------| | Towns | latitude | longitude | |--------------|------------|------------| | Town_A | 72.92629 | -12.89272 | | Town_B | 93.62789 | -83.10172 | | Town_C | 68.92612 | -67.17242 | |--------------|------------|------------|  

Пользователи: Эта таблица содержит историю покупок пользователя, т. е. онлайн или в городах. Также состоит из широты/долготы пользователя во время покупки.

 |------------|------------|------------|--------------| | user_id | latitude | longitude | Towns | |------------|------------|------------|--------------| | 1 | 21.89027 | -53.03772 | Town_A | | 1 | 23.87847 | -41.78172 | Town_C | | 1 | 39.62847 | -80.19892 | online | | 1 | 77.87092 | -96.39242 | Town_A | | 2 | 71.87782 | -38.03782 | online | | 2 | 83.37847 | -62.78278 | Town_B | | 3 | 89.81924 | -80.73892 | Town_B | | 3 | 27.87282 | -18.39183 | Town_A | |------------|------------|------------|--------------|  

Я хочу найти ближайший город на основе lat/long пользователя во время его покупки. Итоговая таблица будет выглядеть следующим образом:

 |------------|------------|------------|--------------|-----------------| | user_id | latitude | longitude | Towns | nearest_town | |------------|------------|------------|--------------|-----------------| | 1 | 21.89027 | -53.03772 | Town_A | Town_B | lt;--- Town_B is near based on his lat/long (Irrespective of his purchase town) | 1 | 23.87847 | -41.78172 | Town_C | Town_A | lt;--- Town_A is near based on his lat/long | 1 | 39.62847 | -80.19892 | online | Town_Online | | 1 | 77.87092 | -96.39242 | Town_A | Town_A | | 2 | 71.87782 | -38.03782 | online | Town_Online | | 2 | 83.37847 | -62.78278 | Town_B | Town_C | | 3 | 89.81924 | -80.73892 | Town_B | Town_A | | 3 | 27.87282 | -18.39183 | Town_A | Town_A | |------------|------------|------------|--------------|-----------------|  

SQL — запрос (Снежинка) моя попытка:

 With specific_location as (  select user_id,  latitude,  longitude,  case when Towns in ('Town_A','Town_B','Town_C') then 'Town' else 'Town_Online' end as purchase_in  from Locations )  select *,   case when purchase_in = 'Town' then  (select Towns from Location qualify row_number() over (order by haversine(user.latitude,user.longitude,location.latitude,location.longitude))=1)  else purchase_in  end as nearest_town  from specific_location  

I’m getting an error: syntax error unexpected 'when' and unexpected 'else'

В вашем CTE specific_location отсутствует СОЕДИНЕНИЕ USERS , так как в самих местоположениях нет user_id столбца.

Я бы также сделал обогащенного пользователя, чтобы добавить последовательность, чтобы позже совпадение местоположения могло быть четко для каждой строки пользователя, а затем присоединить пользователя/местоположение во втором CTE, и, таким образом, выбор, который вы делаете в конце, с предварительно вычисленными значениями:

Я также поменял ваши два заявления о ценностях на заявления IFF

 WITH enriched_user AS (  SLECT   u.user_id,  u.latitude,  u.longitude,  u.town,  seq4() as seq,  IFF(towns IN ('Town_A','Town_B','Town_C'), 'Town', 'Town_Online') AS purchase_in  FROM user AS u ), user_and_closest_location AS (  SELECT   u.user_id,  u.latitude,  u.longitude,  u.town,  u.purchase_in  l.town as closest_town  haversine(u.latitude, u.longitude, l.latitude, l.longitude)  FROM enriched_user AS u,  location AS l  QUALIFY row_number() OVER (PARTION BY u.seq ORDER BY haversine(u.latitude, u.longitude, l.latitude, l.longitude)) = 1 ) SELECT   u.user_id,  u.latitude,  u.longitude,  u.town,  IFF(u.purchase_in = 'Town', u.closest_town, u.purchase_in) AS nearest_town FROM user_and_closest_location AS u ORDER BY 1,2,3;   

Логика расчета соединения на основе расстояния для всех строк заключается в том, что это будет быстрее, и если есть вещи, для которых вы не хотите этого делать, было бы лучше сократить ввод там, но затем вам нужно будет вернуться к вводу, чтобы зафиксировать пропущенные значения.

 WITH enriched_user AS (  SLECT   u.user_id,  u.latitude,  u.longitude,  u.town,  seq4() as seq,  IFF(towns IN ('Town_A','Town_B','Town_C'), 'Town', 'Town_Online') AS purchase_in  FROM user AS u ), user_and_closest_location AS (  SELECT   u.user_id,  u.latitude,  u.longitude,  u.town,  u.purchase_in  l.town as closest_town  haversine(u.latitude, u.longitude, l.latitude, l.longitude)  FROM enriched_user AS u,  location AS l  WHERE u.purchase_in = 'Town'  QUALIFY row_number() OVER (PARTION BY u.seq ORDER BY haversine(u.latitude, u.longitude, l.latitude, l.longitude)) = 1 ) SELECT   u.user_id  u.latitude,  u.longitude,  u.town,  IFF(u.purchase_in = 'Town', ucl.closest_town, u.purchase_in) AS nearest_town FROM enriched_user user_and_closest_location AS u LEFT JOIN user_and_closest_location AS ucl   ON u.seq = ucl.seq ORDER BY 1,2,3;  

кроме того, в городах можно было бы перевернуть, чтобы они не были «онлайн»

 IFF(towns IN ('Town_A','Town_B','Town_C'), 'Town', 'Town_Online') AS purchase_in  

становящийся:

 IFF(towns != 'online', 'Town', 'Town_Online')  

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