寻找最老的最近访问记录。（按最大值分组的最小值）

Question

我试图在一个列表中找到最早的日期，但我只想考虑每个可能“人”的最近条目。

例如，考虑以下数据集（日期是varchar 103，即dd/mm/yyyy）

+--------+------+------------+
| Person | Shop | Date       |
+--------+------+------------+
| A      | X    | 09/01/2017 |
+--------+------+------------+
| A      | X    | 12/01/2017 |
+--------+------+------------+
| A      | Y    | 12/01/2017 |
+--------+------+------------+
| B      | X    | 01/01/2017 |
+--------+------+------------+
| B      | X    | 13/01/2017 |
+--------+------+------------+
| B      | Y    | 11/01/2017 |
+--------+------+------------+
| C      | X    | 14/01/2017 |
+--------+------+------------+
| C      | Y    | 18/01/2017 |
+--------+------+------------+
| C      | Y    | 07/01/2017 |
+--------+------+------------+

如果我想找到每个“商店”的最小日期使用量（即MIN(DATE) GROUP BY Shop），那么我会得到X商店的01/01/2017和Y商店的07/01/2017。但是，在这两次访问中，访问者在稍后的日期来到了商店，因此我不想将这些日期视为“最老的最近”访问的一部分。

明确一下，我想知道每个客户最近访问的时间中，哪一个是最旧的。也就是说，有人最后一次访问商店的日期是什么，但自那以后就再没有回来。

我需要通过提取按商店分组的MAX(Date)的临时表进行操作，还是可以直接从这个数据集中获取X商店的12/01/2017和Y商店的11/01/2017的日期？

我需要以下结果

+------+------------+
| Shop | Date       |
+------+------------+
| X    | 12/01/2017 |
+------+------------+
| Y    | 11/01/2017 |
+------+------------+

任何帮助都将不胜感激。

- ACrazyChemist

---

能否请您添加预期的结果集，以便澄清。 - Olivier Jacot-Descombes

所以您想要最早的访问记录，但仅限于那些仅访问过一次的人？ - GuidoG

你真的应该开始使用日期数据类型来存储日期。这就是它的设计目的。我继续感到震惊的是有多少人认为这样做是可以接受的。这些人永远不会在varchar列中存储整数。 - Sean Lange

添加Olivier和GuidoG：我想知道对于所有客户来说，构成最近一次到店的顾客的最早访问是什么。 - ACrazyChemist

Sean，它被存储为日期时间 - 这只是为了简单起见。 - ACrazyChemist

Answer 1

最简单的方法是使用排名函数ROW_NUMBER：

with cte as
(
   select Person, Shop, [Date],
          rn = ROW_NUMBER() OVER (PARTITION BY Person Order By CONVERT(datetime, t.[Date], 103) DESC)
   fromk dbo.TableName t
)
select Person, Shop, [Date]
from cte
where rn = 1

为什么不把日期存储为 date/datetime 而是 varchar？

- Tim Schmelter

---

我认为这个查询没有返回预期的结果。OP想要获取每个人每个商店最新的日期，然后获取最早的商店值。 - Giorgos Betsos

@GiorgosBetsos：也许我没有仔细阅读问题。但他说他“只想要每个可能的'人'的最新条目”。然而，他可以轻松修改它：PARTITION BY Person, Shop... - Tim Schmelter

Answer 2

您可以尝试在一个选择器内嵌套另一个选择器：

CREATE TABLE #VISIT (Person CHAR(1), Shop CHAR(1), [Date] DATE)

INSERT INTO  #VISIT
SELECT 'A','X','09/01/2017'
UNION ALL SELECT 'A','X','01/12/2017'
UNION ALL SELECT 'A','Y','01/12/2017'
UNION ALL SELECT 'B','X','01/01/2017'
UNION ALL SELECT 'B','X','01/13/2017'
UNION ALL SELECT 'B','Y','01/11/2017'
UNION ALL SELECT 'C','X','01/14/2017'
UNION ALL SELECT 'C','Y','01/18/2017'
UNION ALL SELECT 'C','Y','01/07/2017'

SELECT minDate.Shop
    ,Min(minDate.lastVisit)
FROM (
    SELECT Person
        ,Shop
        ,Max([Date]) lastVisit
    FROM #VISIT
    GROUP BY Person
        ,Shop
    ) AS minDate
GROUP BY minDate.Shop

- Rogério Carvalho Batista

---

你比预定时间早了3分钟！ - Giorgos Betsos