英文:
How do I obtain a list of dates not included in a consumption table
问题
我有一个包含每个现有“连接”的每日消耗的表格。
```sql
create table dbo.Consumptiondata
(
ConnectionId nvarchar(125) not null,
StartDateTime datetime not null,
EndDateTime datetime not null,
Value numeric(20, 10) not null
)
我需要编写一个查询，以获取每个连接的没有消耗记录（在表中没有记录的日期）的日期。
我首先编写了一个查询，生成了一个临时表，其中包含给定开始和结束日期之间的所有预期日期：
-- 临时表
declare @DatesRange table (DateValue datetime);
-- 构建日期范围
set @currentDateTime = @startDateTime
while @currentDateTime <= @endDateTime
begin
insert into @DatesRange values (@currentDateTime);
set @currentDateTime = dateadd(day, 1, @currentDateTime);
end
然后我尝试通过与消耗表进行连接（左连接或右连接）来完成任务，但由于连接字段，我无法使其正常工作。
然后我修改了我的临时表，为每个连接添加了所需的日期范围：
-- 日期过滤器
declare @startDateTime varchar(100) = '2023-05-01 00:00:00.000';
declare @endDateTime varchar(100) = '2023-06-01 00:00:00.000'
declare @currentDate datetime;
-- 临时表
declare @ExpectedDatesPerConnectionId table (ExternalConnectionId nvarchar(125), ExpectedDate datetime);
set @currentDate = @StartDateTime;
while @currentDate < @EndDateTime
begin
insert into @ExpectedDatesPerConnectionId
(ConnectionId, ExpectedDate)
(select distinct ConnectionId, @currentDate
from Consumptiondata)
set @currentDate = dateadd(day, 1, @currentDate)
end
然后我只需执行以下查询，即可获得预期结果：
select b.ConnectionId, b.ExpectedDate
from @ExpectedDatesPerConnectionId b
where b.ExpectedDate not in (select DateConsumption from Consumptiondata where ConnectionId = b.ExternalConnectionId)
order by b.ConnectionId, b.ExpectedDate
问题是？运行时间超过8分钟。
因此，我想知道这里是否有人知道获得相同结果的其他方法？
<details>
<summary>英文:</summary>
I have a table that contains the daily consumptions for each existing "connection".
create table dbo.Consumptiondata
(
ConnectionId nvarchar(125) not null,
StartDateTime datetime not null,
EndDateTime datetime not null,
Value numeric(20, 10) not null
)
I need to write a query that yields the days without consumptions (which have no records in the table) for each connection.
I started by writing a query generating a temporary table of all expected dates between a given start and end date:
```Sql
-- Temporary tables
declare @DatesRange table (DateValue datetime);
-- Build date range
set @currentDateTime = @startDateTime
while @currentDateTime <= @endDateTime
begin
insert into @DatesRange values (@currentDateTime);
set @currentDateTime = dateadd(day, 1, @currentDateTime);
end
I tried then to get the job done by linking (left or right join) with the consumption table, but I didn't find a way to make it work due to the connection field.
I then modified my temporary table to add the needed date range for each connection:
-- Date filters
declare @startDateTime varchar(100) = '2023-05-01 00:00:00.000';
declare @endDateTime varchar(100) = '2023-06-01 00:00:00.000'
declare @currentDate datetime;
-- Temporary tables
declare @ExpectedDatesPerConnectionId table (ExternalConnectionId nvarchar(125), ExpectedDate datetime);
set @currentDate = @StartDateTime;
while @currentDate < @EndDateTime
begin
insert into @ExpectedDatesPerConnectionId
(ConnectionId, ExpectedDate)
(select distinct ConnectionId, @currentDate
from Consumptiondata)
set @currentDate = dateadd(day, 1, @currentDate)
end
Then I merely execute the following query, which gives me the expected result:
select b.ConnectionId, b.ExpectedDate
from @ExpectedDatesPerConnectionId b
where b.ExpectedDate not in (select DateConsumption from Consumptiondata where ConnectionId = b.ExternalConnectionId)
order by b.ConnectionId, b.ExpectedDate
The problem? It takes over 8 minutes to run.
So, I'm wondering if anyone here would know of another means of obtaining the same result?
答案1
得分: 1
你可以使用日历列出起始/结束日期之间的所有日期，然后简单地提取在消耗表中未使用的日期。
;with
n as (select ROW_NUMBER() over (order by object_id) -1 n from sys.objects),
cal as (
select *, dateadd(DAY, n, @startDateTime) theday
from n
where n < datediff(DAY, @startDateTime, @endDateTime)
)
select *
from cal c
where not exists (
select null
from Consumptiondata d
where c.theday between d.startDateTime and d.endDateTime
and ConnectionId = @externalConnectionId
)
英文:
You can use a calendar to list all days between your start/end dates, and then simply extrat days that are not used in your consumption table.
;with
n as (select ROW_NUMBER() over (order by object_id) -1 n from sys.objects),
cal as (
select *, dateadd(DAY, n, @startDateTime) theday
from n
where n < datediff(DAY, @startDateTime, @endDateTime)
)
select *
from cal c
where not exists (
select null
from Consumptiondata d
where c.theday between d.startDateTime and d.endDateTime
and ConnectionId = @externalConnectionId
)
P.S.
sys.objects is just a quick and simple solution to build the calendar, you can use a tally table or any other way you like
答案2
得分: 0
以下是翻译好的部分：
只需在其中提取所有日期的地方。通常情况下，用于此目的的是使用某种形式的日历表格。我有一个喜欢的函数来处理这个。
然后，它只是一个提取非匹配项的练习：
DECLARE @Table TABLE (Date DATE);
INSERT INTO @Table (Date) VALUES ('2023-06-01'),('2023-06-03'),('2023-06-04'),('2023-06-09'),('2023-06-12'),('2023-06-13'),('2023-06-14'),('2023-06-15'),('2023-06-28'),('2023-06-29');
SELECT c.Date
FROM Calendar(1,1) c
LEFT OUTER JOIN @Table t
ON c.Date = t.Date
WHERE c.Month = 6
AND c.Year = 2023
AND t.Date IS NULL;
在这里，我们只返回不在 @Table 中的所有行。
如果您需要我提到的函数：
CREATE OR ALTER FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
RETURNS @Calender TABLE (
Date DATE, DateEndTime DATETIME2, Year INT, Month INT, Day INT, Quarter INT, WeekNumber INT, MonthName NVARCHAR(20), DayName NVARCHAR(20), WeekStartDate DATE, WeekEndDate DATE, MonthStartDate DATE, MonthEndDate DATE,
QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT)
AS
BEGIN
WITH CalendarHistory AS (
SELECT CAST(GETUTCDATE() AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,-1,Date)
FROM CalendarHistory
WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
), CalendarFuture AS (
SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,1,Date)
FROM CalendarFuture
WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
), Calendar AS (
SELECT Date
FROM CalendarHistory
UNION ALL
SELECT Date
FROM CalendarFuture
)
INSERT INTO @Calender
SELECT Date,
DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
DATEPART(YEAR,Date) AS Year, DATEPART(MONTH,Date) AS Month, DATEPART(DAY,Date) AS Day, DATEPART(QUARTER,Date) AS Quarter, DATEPART(WEEK,Date) AS WeekNumber,
DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS QuarterStartDate, DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS QuarterEndDate,
DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
CAST(DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS DATETIME2) AS WeekStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date)) AS DATETIME2)) AS WeekEndTime,
CAST(DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS DATETIME2) AS MonthStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS DATETIME2)) AS MonthEndTime,
CAST(DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS DATETIME2) AS QuarterStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS DATETIME2)) AS QuarterEndTime,
CAST(DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS DATETIME2) AS YearStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS DATETIME2)) AS YearEndTime,
CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
FROM Calendar
ORDER BY Date
OPTION (MAXRECURSION 0)
RETURN
END;
它接受两个整数，表示要向前/向后移动的年数。
英文:
You just need somewhere to pull all the dates from. It's pretty common to use a calendar table of some description for this. I have a favored function to handle that for me.
Then, it just becomes an exercise in pulling the non-matches:
DECLARE @Table TABLE (Date DATE);
INSERT INTO @Table (Date) VALUES ('2023-06-01'),('2023-06-03'),('2023-06-04'),('2023-06-09'),('2023-06-12'),('2023-06-13'),('2023-06-14'),('2023-06-15'),('2023-06-28'),('2023-06-29');
SELECT c.Date
FROM Calendar(1,1) c
LEFT OUTER JOIN @Table t
ON c.Date = t.Date
WHERE c.Month = 6
AND c.Year = 2023
AND t.Date IS NULL;
Here we just return all the rows not in the @Table.
If you'd like the function I reference:
CREATE OR ALTER FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
RETURNS @Calender TABLE (
Date DATE, DateEndTime DATETIME2, Year INT, Month INT, Day INT, Quarter INT, WeekNumber INT, MonthName NVARCHAR(20), DayName NVARCHAR(20), WeekStartDate DATE, WeekEndDate DATE, MonthStartDate DATE, MonthEndDate DATE,
QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT)
AS
BEGIN
WITH CalendarHistory AS (
SELECT CAST(GETUTCDATE() AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,-1,Date)
FROM CalendarHistory
WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
), CalendarFuture AS (
SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
UNION ALL
SELECT DATEADD(DAY,1,Date)
FROM CalendarFuture
WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
), Calendar AS (
SELECT Date
FROM CalendarHistory
UNION ALL
SELECT Date
FROM CalendarFuture
)
INSERT INTO @Calender
SELECT Date,
DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
DATEPART(YEAR,Date) AS Year, DATEPART(MONTH,Date) AS Month, DATEPART(DAY,Date) AS Day, DATEPART(QUARTER,Date) AS Quarter, DATEPART(WEEK,Date) AS WeekNumber,
DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS QuarterStartDate, DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS QuarterEndDate,
DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
CAST(DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS DATETIME2) AS WeekStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date)) AS DATETIME2)) AS WeekEndTime,
CAST(DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS DATETIME2) AS MonthStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS DATETIME2)) AS MonthEndTime,
CAST(DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)) AS DATETIME2) AS QuarterStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEADD(MONTH,3,DATEADD(MONTH,(((DATEPART(MONTH,Date)-1)/3))*3,DATEFROMPARTS(DATEPART(YEAR,Date),1,1)))) AS DATETIME2)) AS QuarterEndTime,
CAST(DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS DATETIME2) AS YearStartTime, DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,0,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS DATETIME2)) AS YearEndTime,
CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
FROM Calendar
ORDER BY Date
OPTION (MAXRECURSION 0)
RETURN
END;
It accepts two ints, representing the number of years back/forward you'd like it to go.
答案3
得分: 0
同我的一位同事一起研究了这个问题，他提出了以下解决方案：
-- 日期筛选器
declare @startDateTime datetime = '2021-08-01 00:00:00.000';
declare @endDateTime datetime = '2021-09-01 00:00:00.000';
drop table if exists #daysInRange;
with daysInRange
as (
select @startDateTime as 'StartDateTime', dateadd(day, 1, @startDateTime) as 'EndDateTime'
union all
select dateadd(day, 1, StartDateTime) as 'StartDateTime',
dateadd(day, 1, EndDateTime) as 'EndDateTime'
from daysInRange
where StartDateTime < @endDateTime
)
select *
into #daysInRange
from daysInRange
select conn.ConnectionId,
r.StartDateTime,
r.EndDateTime
from dbo.ConnectionData conn,
cross apply (
select *
from #daysInRange
) as r
left outer join dbo.ConsumptionData cons on cons.ConnectionId = conn.ConnectionId and cons.StartDateTime = r.StartDateTime and cons.EndDateTime = r.EndDateTime
where conn.STARTDATETIME < @endDateTime
and conn.ENDDATETIME > @startDateTime
and conn.Active = 1
and cons.ConnectionMember is null
group by conn.ConnectionId,
r.StartDateTime,
r.EndDateTime
order by r.StartDateTime
go
我最初在帖子中省略了ConnectionData表，因为在我的查询中它只作为ConsumptionData的附加筛选器。但在这里，它是查询的一个不可或缺的部分，所以这次我将它留了下来。
不管怎样，执行时间从8分钟减少到不到30秒。
英文:
Looked into it with one of my colleagues, and he proposed the following solution:
-- Date filters
declare @startDateTime datetime = '2021-08-01 00:00:00.000';
declare @endDateTime datetime = '2021-09-01 00:00:00.000';
drop table if exists #daysInRange;
with daysInRange
as (
select @startDateTime as 'StartDateTime', dateadd(day, 1, @startDateTime) as 'EndDateTime'
union all
select dateadd(day, 1, StartDateTime) as 'StartDateTime',
dateadd(day, 1, EndDateTime) as 'EndDateTime'
from daysInRange
where StartDateTime < @endDateTime
)
select *
into #daysInRange
from daysInRange
select conn.ConnectionId,
r.StartDateTime,
r.EndDateTime
from dbo.ConnectionData conn,
cross apply (
select *
from #daysInRange
) as r
left outer join dbo.ConsumptionData cons on cons.ConnectionId = conn.ConnectionId and cons.StartDateTime = r.StartDateTime and cons.EndDateTime = r.EndDateTime
where conn.STARTDATETIME < @endDateTime
and conn.ENDDATETIME > @startDateTime
and conn.Active = 1
and cons.ConnectionMember is null
group by conn.ConnectionId,
r.StartDateTime,
r.EndDateTime
order by r.StartDateTime
go
I originally ommitted the ConnectionData table from my posting since in my query it only served as an additional filter on the ConsumptionData. Here it's an integral part of the query so I left it in this time.
Regardless, the execution time dropped from 8 minutes to less than 3O seconds.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论