sql - Sql查询以获取基于月份的唯一日期
问题描述
我正在从表中提取一些数据。
declare @SampleData as Table(Id int, ContactId int, Item varchar(25),CreatedOn date)
insert into @SampleData
VALUES(100,2500,'Some item name 1212', '9/5/2020'),
(104,2500,'Some item name 2232', '9/15/2020'),
(109,2500,'Some item name 3434', '9/20/2020'),
(112,3000,'Some item name 5422', '8/1/2020'),
(132,3000,'Some item name 344', '9/5/2020'),
(134,3000,'Some item name 454', '9/15/2020'),
(139,3500,'Some item name 6455', '7/5/2020'),
(146,3500,'Some item name 546', '8/5/2020'),
(142,3500,'Some item name 867', '9/5/2020'),
(149,3500,'Some item name 677', '9/15/2020'),
(150,3500,'Some item name 888', '9/19/2020')
这里的逻辑是让您每个月都可以找到新的联系人 ID(所以逻辑是如果同一联系人在该月 1 日的最后 28 天内没有任何记录,则视为新联系人)
当您有两个日期期间时,这很容易做到,因此您可以排除您想要的记录,如下所示
SELECT *
FROM @SampleData
WHERE CreatedOn> = @FromDate
and CreatedOn <=@Date
and ContactId not in (SELECT ContactId
FROM @SampleData
WHERE CreatedOn >= DateAdd(Day, -28,@FromDate)
AND CreatedOn < @FromDate)
我想要的是在没有参数的情况下预先填充这些数据,以便用户可以使用。
在此示例数据中,我预计 7 月的联系人为 3500,8 月的联系人为 3000,9 月的联系人为 2500&3000。
此外,它只需要显示每个联系人的记录而不是重复的。
DECLARE @From date,
@To date
DECLARE date_cursor CURSOR FOR
select distinct DATEADD(month, DATEDIFF(month, 0, CreatedOn), 0) FromDate,EOMONTH(CreatedOn) ToDate
from @SampleData
OPEN date_cursor
FETCH NEXT FROM date_cursor INTO @From,@To
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT *
FROM (
SELECT DISTINCT ContactId,@From 'From Date', @To 'To Date'
FROM @SampleData D
WHERE D.CreatedOn>= @From AND D.CreatedOn <= @To
AND ContactId NOT IN (SELECT ContactId
FROM @SampleData
WHERE CreatedOn >= DateAdd(Day, -28,@From)
AND CreatedOn < @From)) ContactData
OUTER APPLY (
--pick first row for the contact as per the period
SELECT TOP 1 *
FROM @SampleData D
WHERE D.ContactId = ContactData.ContactId
AND D.CreatedOn >= ContactData.[From Date]
AND D.CreatedOn < ContactData.[To Date]
ORDER BY CreatedOn
) Records
FETCH NEXT FROM date_cursor INTO @From,@To
END
CLOSE date_cursor
DEALLOCATE date_cursor
结果
ContactId From Date To Date Id Item CreatedOn
3500 01/07/2020 31/07/2020 139 Some item name 6455 05/07/2020
3000 01/08/2020 31/08/2020 112 Some item name 5422 01/08/2020
2500 01/09/2020 30/09/2020 100 Some item name 1212 05/09/2020
3000 01/09/2020 30/09/2020 132 Some item name 344 05/09/2020
我想摆脱光标,有没有可能
解决方案
lag()
您可以通过使用和比较行来为联系人分配分组:
select sd.*,
sum(case when prev_createdon > dateadd(day, -28, createdon) then 0 else 1 end) over
(partition by contactid order by createdon) as grouping
from (select sd.*,
lag(createdon) over (partition by contactid order by createdon) as prev_createdon
from SampleData sd
) sd;
如果您只想要一系列相邻记录中的第一行,那么:
select sd.*
from (select sd.*,
lag(createdon) over (partition by contactid order by createdon) as prev_createdon
from SampleData sd
) sd
where prev_createdon < dateadd(day, -28, createdon) or prev_createdon is null;
这是一个 db<>fiddle。
编辑:
根据修改后的问题,您要按组进行总结。您可以使用:
select contactid, min(createdon), max(createdon), min(id),
max(case when seqnum = 1 then item end) as item
from (select sd.*,
row_number() over (partition by contactid, grouping order by createdon) as seqnum
from (select sd.*,
sum(case when prev_createdon > dateadd(day, -28, createdon) then 0 else 1 end) over
(partition by contactid order by createdon) as grouping
from (select sd.*,
lag(createdon) over (partition by contactid order by createdon) as prev_createdon
from SampleData sd
) sd
) sd
) sd
group by contactid, grouping;
我也更新了数据库小提琴以拥有它。
推荐阅读
- angular - Amazon Build 失败并出现构建命令需要在 Angular 项目中运行,但找不到项目定义
- amazon-web-services - 是否可以将标签添加到使用 Packer 创建的包含过滤后的 source_ami 名称的 AWS AMI?
- javascript - jQuery:随机放置的对象应始终完全保留在父对象内
- google-apps-script - 如何使单个脚本在同一工作表中的多个选项卡上工作
- reactjs - 使用 react、express、node mongodb/postgress 实时更改
- html - 圆形边框CSS上的渐变
- firebase - 使用 nvm 在 IntelliJ IDE 中运行 firebase 命令/脚本
- php - 构造 XML 数据时如何使用 FOREACH?
- typescript - Typescript ChildNode:`parentElement`和/或`parentNode`为空?
- express - 将 mqtt 订阅绑定到节点 ejs 和 alpine 状态