Как отобразить самую старую дату для уникального пользователя, у которого есть несколько дат в базе данных?

Допустим, мой результат выглядит так (упрощенный пример):

UserName ProfileCreation PurchasePrice PurchaseDate
Alice Dec 21 2019 6:00AM 120.00 Dec 21 2019 8:00AM
Alice Dec 21 2019 6:00AM 90.00 Dec 25 2019 9:00AM
Alice Dec 21 2019 6:00AM 150.00 Jan 02 2020 10:00AM
Bob Jan 01 2020 9:00PM 50.00 Jan 03 2020 11:00PM
Bob Jan 01 2020 9:00PM 70.00 Jan 07 2020 11:00PM

Код для этого вывода, я думаю, будет выглядеть так (не так важно):

SELECT
UserName, ProfileCreation, PurchasePrice, PurchaseDate
FROM Some_Random_Database

Но желаемый результат должен выглядеть так:

UserName ProfileCreation PurchasePrice FirstPurchaseDate NumberOfPurchases AvgOfPurchasePrice
Alice Dec 21 2019 120.00 Dec 21 2019 3 120.00
Bob Jan 01 2020 50.00 Jan 03 2020 2 60.00

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

Я пишу на диалекте SOQL - Salesforce Marketing Cloud.

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

Благодарю за любую помощь, ребята!


sql soql salesforce-marketing-cloud
person Amberjack    schedule 11.02.2021    source источник
comment
Что ты уже испробовал? Вы знаете функции агрегирования?   -  person Maciej Los    schedule 11.02.2021
comment
Ага. SUM () и AVG () в сочетании с GROUP BY. Я также знаю о CONVERT (DATE, TIMESTAMP), чтобы отрезать временную часть метки времени. Но я действительно не знаю, как отображать только первую (или последнюю) из этих дат покупки ...   -  person Amberjack    schedule 11.02.2021
comment
Супер! А как насчет оконных функций: MAX(fieldname) OVER(ORDER BY datefield) или MIN(fieldname) OVER(ORDER BY datefield)?   -  person Maciej Los    schedule 11.02.2021
comment
Никогда раньше такого не видел, но похоже, что это законно. Это может как-то сработать, даже если я не знаю, как именно ... пока :-)   -  person Amberjack    schedule 11.02.2021
comment
Лучший способ научиться самому себе - это пытаться и пытаться, и пытаться;)   -  person Maciej Los    schedule 11.02.2021
comment
Определенно да. Я дам вам знать, если я понял это. Пока спасибо!   -  person Amberjack    schedule 11.02.2021
comment
Хорошо, я должен признать, что беспомощен с этим. Не уверен, какую переменную поставить вместо поля даты.   -  person Amberjack    schedule 11.02.2021
comment
Взгляните сюда: dbFiddle   -  person Maciej Los    schedule 11.02.2021
comment
Выглядит многообещающе! Но это не так. Ряд для Алисы в порядке, но Боб рассчитывается вместе с ней. Спасибо, в любом случае! И, кстати, второе решение, которое вы только что опубликовали (PARTITION), выглядит намного лучше!   -  person Amberjack    schedule 11.02.2021

Ответы (2)


arrow_upward
1
arrow_downward

Примечание: я ничего не знаю о Salesforce Marketing Cloud, но ...

Есть несколько способов добиться этого:

# 1 - стандартный sql

SELECT UserName, ProfileCreation
  , MIN(PurchaseDate) FirstPurchaseDate
  , COUNT(PurchasePrice) NoOfPurchases
  , AVG(PurchasePrice) AvgPurchasePrice
FROM Foo
GROUP BY UserName, ProfileCreation;

# 2 - оконные функции

SELECT DISTINCT UserName, ProfileCreation
  , MIN(PurchaseDate) OVER(PARTITION BY UserName ORDER BY UserName) FirstPurchaseDate
  , COUNT(PurchasePrice) OVER(PARTITION BY UserName ORDER BY UserName) NoOfPurchases
  , AVG(PurchasePrice) OVER(PARTITION BY UserName ORDER BY UserName) AvgPurchasePrice
FROM Foo;
person Maciej Los    schedule 11.02.2021
comment
Мне кажется правильным только второе решение. На основе моих попыток. - person Amberjack; 11.02.2021
comment
Но большое вам спасибо! Это определенно очень помогло! - person Amberjack; 11.02.2021
comment
Всегда пожалуйста. Если это поможет, то примите мой ответ как решение. - person Maciej Los; 11.02.2021
comment
Да, наверное, спасибо Польше! - person Amberjack; 11.02.2021
comment
:) Всегда пожалуйста. - person Maciej Los; 11.02.2021

arrow_upward
-1
arrow_downward

ВЫБЕРИТЕ имя пользователя, создание профиля, PurchasePrice, PurchaseDate FROM Some_Random_Database WHERE (UserName, PurchaseDate) IN (SELECT UserName, max (PurchaseDate) FROM Some_Random_Database GROUP BY UserName);

person lomosapience    schedule 11.02.2021
comment
Это не показывает FirstPurchaseDate, количество покупок и т. Д. В соответствии с запросом. Он ограничивает вывод только одной строкой на имя пользователя. В этом ответе потребуется GROUP BY вместе с некоторыми другими деталями, а предложение WHERE следует удалить. - person Dennis; 11.02.2021
comment
Не совсем то, что я хотел, но все равно спасибо. Это вдохновляет. Я не знал, что вы можете сделать это ГДЕ на подмножестве столбцов. - person Amberjack; 11.02.2021