sql - 查找次低的购买日期 SQL

Question

我有一个数据集:

 CustomerID    date
 1             01/01/18
 1             02/01/18
 1             05/12/18
 2             03/03/18
 2             07/11/18
 2             05/12/18

我想按客户分组查找第一个和第二个购买日期:

  CustomerID    1st      2nd
  1             1/1/18   2/1/18
  2             3/3/18   7/11/18

如何使用sql来做呢？对于第一次购买，我只使用:

 SELECT CustomerID, min(date) as 1st FROM table group by 1

但我不知道第二次购买日期怎么办

Answer 1

我们可以尝试在此处使用 ROW_NUMBER 后跟一个数据透视表:

WITH cte AS (
    SELECT CustomerID, date, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY date) rn
    FROM yourTable
)

SELECT
    CustomerID,
    MAX(CASE WHEN rn = 1 THEN date END) "first",
    MAX(CASE WHEN rn = 2 THEN date END) "second"
FROM cte
WHERE rn <= 2
GROUP BY
    CustomerID;

Demo