Возврат первой, второй и третьей самых ранних дат покупки для каждого покупателя

Question

Для списка адресов электронной почты покупателя я пытаюсь вернуть по одной строке на покупателя со столбцами "1stOrderDate", "2ndOrderDate", "3rdOrderDate" и "TotalNumberofOrders"

Я попытался использовать функцию ROW_Number в предложении WHERE подзапросов, но он сообщает, что оконные функции недопустимы в предложении WHERE, поэтому любая помощь по заполнению приведенных ниже запросов будет с благодарностью получена!

SELECT 
PT.email AS 'Email',
MIN(OT.orderdate) AS '1stOrderDate',
???               AS '2ndOrderDate',
???               AS '3rdOrderDate',
COUNT(DISTINCT OT.order_reference) AS 'TotalNumberOfOrders'    

FROM dbo.Orders AS OT
JOIN dbo.Purchaser AS PT ON OT.account_reference = PT.account_reference

GROUP BY PT.Email

Answer 1

Вы можете сделать это с row_number() и условная агрегация:

SELECT PT.email,
       MAX(CASE WHEN seqnum = 1 THEN OT.OrderDate END) as OrderDate_1,
       MAX(CASE WHEN seqnum = 2 THEN OT.OrderDate END) as OrderDate_2,
       MAX(CASE WHEN seqnum = 3 THEN OT.OrderDate END) as OrderDate_3,
       COUNT(DISTINCT OT.order_reference) AS TotalNumberOfOrders   
FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY account_reference ORDER BY o.orderdate) as seqnum
      FROM dbo.Orders o
     ) OT JOIN
     dbo.Purchaser PT
     ON OT.account_reference = PT.account_reference
GROUP BY PT.Email

Пара заметок:

• Не используйте одинарные кавычки для псевдонимов столбцов. Вместо этого выберите имена столбцов, которые не требуют экранирования.
• Для segnum = 1 логика, вы можете использовать MIN(), но я думаю, что последовательность является преимуществом здесь.

РЕДАКТИРОВАТЬ:

Я думаю, что проблема заключается в разнице между account_reference а также email, Попробуй это:

SELECT email,
       MAX(CASE WHEN seqnum = 1 THEN OT.OrderDate END) as OrderDate_1,
       MAX(CASE WHEN seqnum = 2 THEN OT.OrderDate END) as OrderDate_2,
       MAX(CASE WHEN seqnum = 3 THEN OT.OrderDate END) as OrderDate_3,
       COUNT(DISTINCT OT.order_reference) AS TotalNumberOfOrders   
FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY pt.email ORDER BY o.orderdate) as seqnum
      FROM dbo.Orders o JOIN
           dbo.Purchaser PT
           ON OT.account_reference = PT.account_reference
     ) OT 
GROUP BY PT.Email