为每个客户获取最近的两个日期

basically, I need to retrieve the last two dates for customers who purchased in at least two different dates, implying there are some customer who had purchased only in one date, the data has the following form

基本上，我需要检索至少在两个不同日期购买的客户的最后两个日期，这意味着有一些客户只购买了一个日期，数据有以下形式

client_id  date
1          2016-07-02
1          2016-07-02
1          2016-06-01
2          2015-06-01

and I would like to get it in the following form

我想把它写成下面的形式

client_id      previous_date     last_date
1              2016-06-01        2016-07-02

remarques:

意见:

a client can have multiple entries for the same date

客户端可以有多个相同日期的条目

a client can have entries only for one date, such customer should be discarded

客户端只能有一个日期的条目，这样的客户应该被丢弃

4 个解决方案

#1

Rank your dates with DENSE_RANK. Then group by client_id and show the last dates (ranked #1 and #2).

用DENSE_RANK给你的约会排序。然后按client_id分组并显示最后的日期(排名第1和第2)。

select
  client_id,
  max(case when rn = 2 then date end) as previous_date,
  max(case when rn = 1 then date end) as last_date
from
(
  select 
    client_id, 
    date, 
    dense_rank() over (partition by client_id order by date desc) as rn
  from mytable
)
group by client_id
having max(rn) > 1;

#2

build up:

建立:

t=# create table s153 (c int,  d date);
CREATE TABLE
t=# insert into s153 values (1,'2016-07-02'), (1,'2016-07-02'),(1,'2016-06-01'),(2,'2016-06-01');
INSERT 0 4

query:

查询:

t=# with a as (
select distinct c,d from s153
)
, b as (
select c,nth_value(d,1) over (partition by c order by d) last_date, nth_value(d,2) over (partition by c order by d) prev_date
from a
)
select * from b where prev_date is not null
;
 c | last_date  | prev_date
---+------------+------------
 1 | 2016-06-01 | 2016-07-02
(1 row)

#3

UNTESTED:

测试:

We use a common table expression to assign a row number based on the date in descending order and then only include those records having a row number <=2 and then ensure that those having 1 row are excluded by the having.

我们使用一个通用的表表达式根据日期降序分配行号，然后只包含行号<=2的记录，然后确保有一行的记录被have排除在外。

WITH CTE AS (
  SELECT Distinct Client_ID
       , Date
       , row_number() over (partition by clientID order by date desc) rn 
  FROM Table)

SELECT  Client_ID, min(date) previous_date, max(date) last_date)
FROM CTE
WHERE RN <=2 
GROUP BY Client_ID
HAVING max(RN) > 1

#4

~~All you need is a group by...~~

你所需要的只是一群……

--test date declare @tablename TABLE ( client_id int, [date] datetime ); insert into @tablename values( 1 , '2016-07-02'), (1 , '2016-07-02'), (1 , '2016-06-01'), (2 , '2015-06-01'); --query SELECT client_id,MIN([DATE]) AS [PREVIOUS_DATE], MAX([DATE]) AS [LAST_DATE] FROM @tablename GROUP BY client_id

Updated

更新

-- create data
create table myTable
(
    client_id integer,
    given_date date
);

insert into myTable
values( 1 ,  '2016-07-02'),
   (1 ,  '2016-07-02'),
   (1 , '2016-06-01'),
   (1 , '2016-06-03'),
   (1 , '2016-06-09'),
   (2 , '2015-06-01'),
   (3 , '2016-06-03'),
   (3 , '2016-06-09');

-- query
SELECT sub.client_id, sub.PREVIOUS_DATE, sub.LAST_DATE
FROM
 (select 
   ROW_NUMBER() OVER (PARTITION BY a.client_id order by b.given_date desc,(MAX(b.given_date) - a.given_date)) AS ROW_NUMBER,
   a.client_id,a.given_date AS PREVIOUS_DATE, MAX(b.given_date) - a.given_date AS diff, (b.given_date) AS LAST_DATE
    FROM myTable AS a 
      JOIN myTable AS b
        ON b.client_id = a.client_id 
    WHERE a.given_date <> b.given_date
    group by a.client_id, a.given_date, b.given_date) AS sub
WHERE sub.ROW_NUMBER = 1

#1