时间戳

首页 > 解决方案 > T SQL - 统计连续 3 个月访问的人数

sql-server - T SQL - 统计连续 3 个月访问的人数

问题描述

使用以下数据：

Declare @t Table 
(
Name        Varchar(1),
VisitDate   Date
)

Insert Into @t select 'A','2017-01-05'
Insert Into @t select 'A','2017-03-05'
Insert Into @t select 'A','2017-04-05'
Insert Into @t select 'A','2017-05-05'
Insert Into @t select 'A','2017-08-05'
Insert Into @t select 'B','2017-03-05'
Insert Into @t select 'C','2017-01-05'
Insert Into @t select 'C','2017-02-05'
Insert Into @t select 'C','2017-04-05'
Insert Into @t select 'D','2017-01-05'
Insert Into @t select 'D','2017-02-05'
Insert Into @t select 'D','2017-03-05'
Insert Into @t select 'D','2017-06-05'
Insert Into @t select 'B','2018-01-05'
Insert Into @t select 'B','2018-02-05'
Insert Into @t select 'B','2018-03-05'
Insert Into @t select 'E','2018-01-05'
Insert Into @t select 'E','2018-02-05'
Insert Into @t select 'E','2018-03-05'
Insert Into @t select 'E','2018-06-05'

我需要编写一个查询，该查询将返回在任何一年的任何连续三个月中具有 VisitDates 的年份和名称。

根据数据，我希望看到：

2017 A
2017 D
2018 B
2018 E

老实说，我不知道从哪里开始使用 SQL。

我会很感激我能得到的任何帮助。

谢谢！！

标签： sql-servertsqldatedatetime

解决方案

您可以使用与 中使用的方法相同的方法来避免连接或多次解析整个数据集gaps-and-islands。

http://rextester.com/SYHJ40676

WITH
  sequenced AS
(
  SELECT
    Name,
    YEAR(VisitDate)         AS VisitYear,
    MONTH(VisitDate)        AS VisitMonth,
    ROW_NUMBER()
      OVER (PARTITION BY Name, YEAR(VisitDate)
                ORDER BY MONTH(VisitDate)
           )
                            AS MonthSequenceID
  FROM
    @t
  GROUP BY
    Name,
    YEAR(VisitDate),
    MONTH(VisitDate)
)
SELECT DISTINCT
  Name,
  VisitYear
FROM
  sequenced
GROUP BY
  Name,
  VisitYear,
  VisitMonth - MonthSequenceID
HAVING
  COUNT(*) >= 3

推荐阅读