SUM CASE, когда DISTINCT?

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

Некоторые примеры таблиц:

user таблица:

| id | net_worth |
------------------
| 1  | 100       |
| 2  | 1000      |

visit таблица:

| id | location   | user_id |
-----------------------------
| 1  |  mcdonalds | 1       |
| 2  |  mcdonalds | 1       |
| 3  |  mcdonalds | 2       |
| 4  |  subway    | 1       |

Мы хотим найти общую чистую стоимость пользователей, посещающих каждое место. Пользователь 1 дважды посещал McDonalds, но мы не хотим дважды учитывать его собственный капитал. В идеале мы можем использовать SUM, но добавлять чистую стоимость только в том случае, если этот пользователь еще не был подсчитан в этом месте. Что-то вроде этого:

-- NOTE: Hypothetical query
SELECT
  location,
  SUM(CASE WHEN DISTINCT user.id then user.net_worth ELSE 0 END) as total_net_worth
FROM visit
  JOIN user on user.id = visit.user_id
GROUP BY 1;

Идеальный результат:

| location  | total_net_worth |
-------------------------------
| mcdonalds | 1100            |
| subway    | 100             |

Эта конкретная база данных — Redshift/PostgreSQL, но было бы интересно, если бы существовало универсальное решение SQL. Возможно ли что-то подобное вышеописанному?

sql amazon-redshift

04.11.2022 15:20

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вы можете использовать функцию окна, чтобы получить уникальных пользователей, а затем присоединиться к таблице пользователей:

select v.location, sum(u.net_worth)
from "user" u
  join (
    select location, user_id, 
           row_number() over (partition by location, user_id) as rn
    from visit
    order by user_id, location, id
  ) v on v.user_id = u.id and v.rn = 1
group by v.location;

Это стандартный ANSI SQL, в Postgres это также можно выразить с помощью distinct on ()

select v.location, sum(u.net_worth)
from "user" u
  join (
    select distinct on (user_id, location) *
    from visit
    order by user_id, location, id
  ) v on v.user_id = u.id
group by v.location;

04.11.2022 15:30

Вы можете присоединиться к пользовательской таблице с различными значениями комбинации местоположения и идентификатора пользователя, как в приведенном ниже общем SQL.

SELECT v.location, SUM(u.net_worth)
FROM (SELECT location, user_id FROM visit GROUP BY location, user_id) v
  JOIN user u on u.id = v.user_id
GROUP BY v.location;

04.11.2022 15:31

Ответ принят как подходящий

Вы не хотите рассматривать повторяющиеся записи в таблице посещений. Поэтому вместо этого выберите отдельные строки из таблицы.

SELECT
  v.location,
  SUM(u.net_worth) as total_net_worth
FROM (SELECT DISTINCT location, user_id FROM visit) v
JOIN user u on u.id = v.user_id
GROUP BY v.location
ORDER BY v.location;

Хорошее, логичное решение!

— 04.11.2022 16:22

04.11.2022 15:31