Redshift PostgreSQL Различный ON Оператор
У меня есть набор данных, который я хочу проанализировать, чтобы увидеть атрибуцию мультитач. Набор данных составлен лидерами, которые ответили на маркетинговую кампанию и их маркетинговый источник.
Каждый лидер может ответить на несколько кампаний, и я хочу, чтобы их первый источник маркетинга и их последний источник маркетинга были в одной таблице.
Я думал, что смогу создать две таблицы и использовать оператор выбора из обеих. Первая таблица будет пытаться создать таблицу с самым последним источником маркетинга от каждого человека (используя электронную почту в качестве своего уникального идентификатора).
create table temp.multitouch1 as (
select distinct on (email) email, date, market_source as last_source
from sf.campaignmember
where date >= '1/1/2016' ORDER BY DATE DESC);
Тогда я бы создал таблицу с дедуплицированными электронными письмами, но на этот раз для первого источника.
create table temp.multitouch2 as (
select distinct on (email) email, date, market_source as first_source
from sf.campaignmember
where date >= '1/1/2016' ORDER BY DATE ASC);
Наконец, я хотел просто выбрать электронное письмо и присоединить к нему первый и последний рыночные источники, каждый в своей колонке.
select a.email, a.last_source, b.first_source, a.date
from temp.multitouch1 a
left join temp.multitouch b on b.email = a.email
Поскольку отличная версия не работает с версией redshift postgresql, я надеялся, что у кого-то возникла идея решить эту проблему другим способом.
РЕДАКТИРОВАНИЕ 2/22: Для большего контекста я имею дело с людьми и кампаниями, на которые они ответили. Каждая запись является "ответом на кампанию", и каждый человек может иметь более одного ответа на кампанию с несколькими источниками. Я пытаюсь сделать выборочное утверждение, которое приведет к дедупликации по человеку, а затем будет иметь столбцы для первого источника кампании / маркетинга, на который они ответили, и последнего источника кампании / маркетинга, на который они ответили соответственно.
РЕДАКТИРОВАТЬ 2/24: Идеальный результат - это таблица с 4 столбцами: электронная почта, last_source, first_source, date.
Первый и последний исходные столбцы будут одинаковыми для людей с одной записью участника кампании и разными для всех, у кого более одной записи участника кампании.
2 ответа
Я полагаю, что вы могли бы использовать row_number() внутри выражений, например:
SELECT
email
, MIN(first_source) AS first_source
, MIN(date) first_date
, MAX(last_source) AS last_source
, MAX(date) AS last_date
FROM (
SELECT
email
, date
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date ASC) = 1 THEN market_source
ELSE NULL
END AS first_source
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date DESC) = 1 THEN market_source
ELSE NULL
END AS last_source
FROM sf.campaignmember
WHERE date >= '2016-01-01'
) s
WHERE first_source IS NOT NULL
OR last_source IS NOT NULL
GROUP BY
email
проверено здесь: SQL Fiddle
Настройка схемы PostgreSQL 9.3:
CREATE TABLE campaignmember
(email varchar(3), date timestamp, market_source varchar(1))
;
INSERT INTO campaignmember
(email, date, market_source)
VALUES
('a@a', '2016-01-02 00:00:00', 'x'),
('a@a', '2016-01-03 00:00:00', 'y'),
('a@a', '2016-01-04 00:00:00', 'z'),
('b@b', '2016-01-02 00:00:00', 'x')
;
Запрос 1:
SELECT
email
, MIN(first_source) AS first_source
, MIN(date) first_date
, MAX(last_source) AS last_source
, MAX(date) AS last_date
FROM (
SELECT
email
, date
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date ASC) = 1 THEN market_source
ELSE NULL
END AS first_source
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date DESC) = 1 THEN market_source
ELSE NULL
END AS last_source
FROM campaignmember
WHERE date >= '2016-01-01'
) s
WHERE first_source IS NOT NULL
OR last_source IS NOT NULL
GROUP BY
email
| email | first_source | first_date | last_source | last_date |
|-------|--------------|---------------------------|-------------|---------------------------|
| a@a | x | January, 02 2016 00:00:00 | z | January, 04 2016 00:00:00 |
| b@b | x | January, 02 2016 00:00:00 | x | January, 02 2016 00:00:00 |
& небольшое расширение к запросу, подсчитать количество контактных точек.
SELECT
email
, MIN(first_source) AS first_source
, MIN(date) first_date
, MAX(last_source) AS last_source
, MAX(date) AS last_date
, MAX(numof) AS Numberof_Contacts
FROM (
SELECT
email
, date
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date ASC) = 1 THEN market_source
ELSE NULL
END AS first_source
, CASE
WHEN ROW_NUMBER() OVER (PARTITION BY email ORDER BY date DESC) = 1 THEN market_source
ELSE NULL
END AS last_source
, COUNT(*) OVER (PARTITION BY email) as numof
FROM campaignmember
WHERE date >= '2016-01-01'
) s
WHERE first_source IS NOT NULL
OR last_source IS NOT NULL
GROUP BY
email
Вы можете использовать старое доброе левое соединение групповым максимумом.
SELECT DISTINCT c1.email, c1.date, c1.market_source
FROM sf.campaignmember c1
LEFT JOIN sf.campaignmember c2
ON c1.email = c2.email AND c1.date > c2.date AND c1.id > c2.id
LEFT JOIN sf.campaignmember c3
ON c1.email = c3.email AND c1.date < c3.date AND c1.id > c3.id
WHERE c1.date >= '1/1/2016' AND c2.date >= '1/1/2016'
AND (c2.email IS NULL OR c3.email IS NULL)
Это предполагает, что у вас есть уникальный столбец идентификатора, если (дата, адрес электронной почты) уникальный идентификатор не требуется.