SQL: объединение двух таблиц с идентификаторами и подсчетами

#sql #database #amazon-redshift

#sql #База данных #amazon-redshift

Вопрос:

Я застрял в одном запросе и не могу найти решение для этого. У меня есть две таблицы. Одна из них касается пользователей, а другая — пользователей. Ниже приведены примеры таблиц:

пользовательская таблица:

 date ___ user_id ____ country 
2020 ___ ara123  _____ canada
2019 ___ ara567 ______ USA
2018 ___ ara890 ______ USA
2017 ___ ara789 ______ canada
  

таблица spender:

 date ___ spender_id ____ country ___ gross_spend ___ net spend 
2020 ___ ara123  _____ Canada.   ___ 3.4 ___________ 4.5
2019 ___ ara567 ______ USA _________ 4.5 ___________ 6.7
2018 ___ abcd34 ______ USA _________ 56.5 __________ 4.3
  

Я пытаюсь объединить эти две таблицы таким образом, чтобы я мог выполнить два вычисления и добавить их в итоговую таблицу. Первое вычисление — это сумма (gross_spend) / count(идентификаторы пользователей), а второе — (gross_spend) / (spender_id). Мне также нужно выполнить левое соединение, чтобы иметь все идентификаторы user_id и общие идентификаторы из идентификатора spender. Однако, когда я соединяю эти две таблицы, результаты обоих этих вычислений получаются одинаковыми. Ниже приведена таблица, которую я ищу:

 date ___ user_id ____ spender_id ____ country ___ cal1 ____ cal2
2020 ___ ara123 _____ ara123    _____ Canada ____ 0.85 ____ 1.7
2019 ___ ara567 _____ ara567   _______ USA ____ 1.126 ___ 2.25
2018 ___ara890 ______ NULL ___________ USA ____ NULL ____ NULL 
2017 ___ ara789 ______NULL ___________ canada ___NULL ___NULL
  

Я попробовал следующий запрос:

 select a.date, a.user_id, b.spender_id, a.country, 
      (b.gross_spend)/count(distinct a.user_id),
      (b.gross_spend)/count(distinct b.spender_id)
from user_table a
LEFT JOIN spender_table b
on a.date = b.date and a.user_id = b.spender_id -- have also tried without joining on second condition
group by 1,2,3,4
  

Проблема, с которой я сталкиваюсь, заключается в том, что для столбцов cal1 и cal2 все значения становятся одинаковыми. Это работает, когда я добавляю только дату, cal1 и cal2. Однако, как только я добавляю spender_id и user_id в таблицы, это вызывает эту проблему. Вы знаете, как я могу это исправить? Идентификаторы должны быть в таблицах.
Спасибо!

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

1. ваш пример упрощен. в вашей первой строке cal1 предполагается, что существует 4 (3,4 / 0,85) пользователя, но вы показали только одного в 2020 году. ваш запрос верен для того, что он стоит, если вы использовали полный набор данных; с предоставленным набором данных cal1 и cal2 должны равняться друг другу — во всех случаях есть либо одна, либо нет записей для деления

Ответ №1:

Вы группируетесь по отдельным идентификаторам user_id и spender_ids, и когда вы это делаете, количество (отдельный идентификатор пользователя / spender_id) всегда будет равно 1.