Блог

У меня есть таблица с клиентами туристического агентства с 4 столбцами: CustomerID, посещенная страна, посещенный город и сумма, потраченная в этой комбинации страна / город.

Клиент мог посещать город несколько раз с разными суммами расходов.

Я хочу запросить идентификаторы клиентов у тех клиентов, которые:

1. Посетили как минимум два города в разных странах
2. Средняя разница в расходах между этими городами составляет не менее 5000.

Я объединил ее так, чтобы получить среднее значение, потраченное для каждого города на одного клиента:

 SELECT customerid, country, city, AVG(amount_spent)
FROM Customers
GROUP BY customerid, country, city, amount_spent;
 

Я понимаю это:

Как бы я мог выводить только клиентов с выполненными двумя условиями, которые в этом случае были бы только CustomerID 7. Customerid 9 не должен выводиться, потому что он не посещает два города в разных странах, а 8 не должен выводиться, потому что разница в среднем потраченном меньше 5000.

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

Чтобы решить эту проблему, агрегируйте (правильно), чтобы получить среднюю сумму, потраченную в каждом городе. Затем используйте самосоединение для выполнения условий:

 WITH cc AS (
      SELECT customerid, country, city, AVG(amount_spent) as avg_amount_spent
      FROM Customers
      GROUP BY customerid, country, city
     )
SELECT DISTINCT cc1.customer_id
FROM cc cc1 JOIN
     cc cc2
     ON cc1.customerid = cc2.customerid AND
        cc1.country <> cc2.country AND
        cc1.avg_amount_spent > cc2.avg_amount_spent   5000