如何获取不包含在消费表中的日期列表。

huangapple
go评论113阅读模式
英文:

How do I obtain a list of dates not included in a consumption table

问题

  1. 我有一个包含每个现有“连接”的每日消耗的表格。
  3. ```sql
  4. create table dbo.Consumptiondata
  5. (
  6.     ConnectionId nvarchar(125) not null,
  7.     StartDateTime datetime not null,
  8.     EndDateTime datetime not null,
  9.     Value numeric(20, 10) not null
  10. )

我需要编写一个查询，以获取每个连接的没有消耗记录（在表中没有记录的日期）的日期。

我首先编写了一个查询，生成了一个临时表，其中包含给定开始和结束日期之间的所有预期日期：

  1. -- 临时表
  2. declare @DatesRange table (DateValue datetime);
  4. -- 构建日期范围
  5. set @currentDateTime = @startDateTime
  6. while @currentDateTime <= @endDateTime
  7. begin
  8.     insert into @DatesRange values (@currentDateTime);
  9.     set @currentDateTime = dateadd(day, 1, @currentDateTime);
  10. end

然后我尝试通过与消耗表进行连接（左连接或右连接）来完成任务，但由于连接字段，我无法使其正常工作。

然后我修改了我的临时表，为每个连接添加了所需的日期范围：

  1. -- 日期过滤器
  2. declare @startDateTime varchar(100) = '2023-05-01 00:00:00.000';
  3. declare @endDateTime varchar(100) = '2023-06-01 00:00:00.000'
  4. declare @currentDate datetime;
  6. -- 临时表
  7. declare @ExpectedDatesPerConnectionId table (ExternalConnectionId nvarchar(125), ExpectedDate datetime);
  9. set @currentDate = @StartDateTime;
  10. while @currentDate < @EndDateTime 
  11. begin
  12.     insert into @ExpectedDatesPerConnectionId
  13.     (ConnectionId, ExpectedDate)
  14.     (select distinct ConnectionId, @currentDate
  15.     from Consumptiondata)
  16.     
  17.     set @currentDate = dateadd(day, 1, @currentDate)
  18. end

然后我只需执行以下查询，即可获得预期结果：

  1. select b.ConnectionId, b.ExpectedDate
  2. from @ExpectedDatesPerConnectionId b
  3. where b.ExpectedDate not in (select DateConsumption from Consumptiondata where ConnectionId = b.ExternalConnectionId)
  4. order by b.ConnectionId, b.ExpectedDate

问题是？运行时间超过8分钟。

因此，我想知道这里是否有人知道获得相同结果的其他方法？

  2. <details>
  3. <summary>英文:</summary>
  5. I have a table that contains the daily consumptions for each existing &quot;connection&quot;.

create table dbo.Consumptiondata
(
ConnectionId nvarchar(125) not null,
StartDateTime datetime not null,
EndDateTime datetime not null,
Value numeric(20, 10) not null
)

  2. I need to write a query that yields the days without consumptions (which have no records in the table) for each connection.
  4. I started by writing a query generating a temporary table of all expected dates between a given start and end date:
  6. ```Sql
  7. --  Temporary tables
  8. declare @DatesRange table (DateValue datetime);
  10. --  Build date range
  11. set @currentDateTime = @startDateTime
  12. while @currentDateTime &lt;= @endDateTime
  13.     begin
  14.         insert into @DatesRange values (@currentDateTime);
  15.         set @currentDateTime = dateadd(day, 1, @currentDateTime);
  16.     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:

  1. -- Date filters
  2. declare @startDateTime varchar(100) = &#39;2023-05-01 00:00:00.000&#39;;
  3. declare @endDateTime varchar(100) = &#39;2023-06-01 00:00:00.000&#39;
  4. declare @currentDate datetime;
  6. --  Temporary tables
  7. declare @ExpectedDatesPerConnectionId table (ExternalConnectionId nvarchar(125), ExpectedDate datetime);
  9. set @currentDate = @StartDateTime;
  10. while @currentDate &lt; @EndDateTime 
  11. begin
  12.     insert into @ExpectedDatesPerConnectionId
  13.     (ConnectionId, ExpectedDate)
  14.     (select distinct ConnectionId, @currentDate
  15.     from Consumptiondata)
  16.     
  17.     set @currentDate = dateadd(day, 1, @currentDate)
  18. end

Then I merely execute the following query, which gives me the expected result:

  1. select b.ConnectionId, b.ExpectedDate
  2. from @ExpectedDatesPerConnectionId b
  3. where b.ExpectedDate not in (select DateConsumption from Consumptiondata where ConnectionId = b.ExternalConnectionId)
  4. 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

你可以使用日历列出起始/结束日期之间的所有日期，然后简单地提取在消耗表中未使用的日期。

  1. ;with
  2. n as (select ROW_NUMBER() over (order by object_id) -1 n from sys.objects),
  3. cal as (
  4. 	select *, dateadd(DAY, n, @startDateTime) theday
  5. 	from n
  6. 	where n &lt; datediff(DAY, @startDateTime, @endDateTime)
  7. )
  8. select *
  9. from cal c
  10. where not exists (
  11. 	select null
  12. 	from Consumptiondata d
  13. 	where c.theday between d.startDateTime and d.endDateTime
  14. 	and ConnectionId = @externalConnectionId
  15. )
英文:

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.

  1. ;with
  2. n as (select ROW_NUMBER() over (order by object_id) -1 n from sys.objects),
  3. cal as (
  4. 	select *, dateadd(DAY, n, @startDateTime) theday
  5. 	from n
  6. 	where n &lt; datediff(DAY, @startDateTime, @endDateTime)
  7. )
  8. select *
  9. from cal c
  10. where not exists (
  11. 	select null
  12. 	from Consumptiondata d
  13. 	where c.theday between d.startDateTime and d.endDateTime
  14. 	and ConnectionId = @externalConnectionId
  15. )

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

以下是翻译好的部分：

只需在其中提取所有日期的地方。通常情况下，用于此目的的是使用某种形式的日历表格。我有一个喜欢的函数来处理这个。

然后，它只是一个提取非匹配项的练习：

  1. DECLARE @Table TABLE (Date DATE);
  2. 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');
  4. SELECT c.Date
  5.   FROM Calendar(1,1) c
  6.     LEFT OUTER JOIN @Table t
  7. 	  ON c.Date = t.Date
  8.  WHERE c.Month = 6
  9.    AND c.Year = 2023
  10.    AND t.Date IS NULL;

在这里，我们只返回不在 @Table 中的所有行。

如果您需要我提到的函数：

  1. CREATE OR ALTER FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
  2. RETURNS @Calender TABLE (
  3. 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,
  4.  QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
  5.  YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT) 
  6. AS
  7. BEGIN
  9. WITH CalendarHistory AS (
  10. SELECT CAST(GETUTCDATE() AS DATE) AS Date
  11. UNION ALL
  12. SELECT DATEADD(DAY,-1,Date)
  13.   FROM CalendarHistory
  14.  WHERE DATEADD(DAY,-1,Date) > DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
  15. ), CalendarFuture AS (
  16. SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
  17. UNION ALL
  18. SELECT DATEADD(DAY,1,Date)
  19.   FROM CalendarFuture
  20.  WHERE DATEADD(DAY,1,Date) < DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
  21. ), Calendar AS (
  22. SELECT Date
  23.   FROM CalendarHistory
  24. UNION ALL
  25. SELECT Date
  26.   FROM CalendarFuture
  27. )
  29. INSERT INTO @Calender
  30. SELECT Date, 
  31.        DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
  32.        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,
  33. 	   DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
  34.        DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
  35. 	   DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
  36. 	   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,
  37. 	   DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
  38. 	   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,
  39. 	   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,
  40. 	   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,
  41. 	   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,
  42. 	   CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
  43.   FROM Calendar
  44.  ORDER BY Date
  45.  OPTION (MAXRECURSION 0)
  46.  RETURN
  47. 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:

  1. DECLARE @Table TABLE (Date DATE);
  2. INSERT INTO @Table (Date) VALUES (&#39;2023-06-01&#39;),(&#39;2023-06-03&#39;),(&#39;2023-06-04&#39;),(&#39;2023-06-09&#39;),(&#39;2023-06-12&#39;),(&#39;2023-06-13&#39;),(&#39;2023-06-14&#39;),(&#39;2023-06-15&#39;),(&#39;2023-06-28&#39;),(&#39;2023-06-29&#39;);
  4. SELECT c.Date
  5.   FROM Calendar(1,1) c
  6.     LEFT OUTER JOIN @Table t
  7. 	  ON c.Date = t.Date
  8.  WHERE c.Month = 6
  9.    AND c.Year = 2023
  10.    AND t.Date IS NULL;

Here we just return all the rows not in the @Table.

If you'd like the function I reference:

  1. CREATE OR ALTER FUNCTION [dbo].[Calendar] (@StartYear INT, @EndYear INT)
  2. RETURNS @Calender TABLE (
  3. 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,
  4.  QuarterStartDate DATE, QuarterEndDate DATE, YearStartDate DATE, YearEndDate DATE, WeekStartTime DATETIME2, WeekEndTime DATETIME2, MonthStartTime DATETIME2, MonthEndTime DATETIME2, QuarterStartTime DATETIME2, QuarterEndTime DATETIME2,
  5.  YearStartTime DATETIME2, YearEndTime DATETIME2, IsWeekDay BIT) 
  6. AS
  7. BEGIN
  9. WITH CalendarHistory AS (
  10. SELECT CAST(GETUTCDATE() AS DATE) AS Date
  11. UNION ALL
  12. SELECT DATEADD(DAY,-1,Date)
  13.   FROM CalendarHistory
  14.  WHERE DATEADD(DAY,-1,Date) &gt; DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,-@StartYear,GETUTCDATE())),1,1))
  15. ), CalendarFuture AS (
  16. SELECT CAST(DATEADD(DAY,1,GETUTCDATE()) AS DATE) AS Date
  17. UNION ALL
  18. SELECT DATEADD(DAY,1,Date)
  19.   FROM CalendarFuture
  20.  WHERE DATEADD(DAY,1,Date) &lt; DATEFROMPARTS(DATEPART(YEAR,DATEADD(YEAR,@EndYear,GETUTCDATE())),1,1)
  21. ), Calendar AS (
  22. SELECT Date
  23.   FROM CalendarHistory
  24. UNION ALL
  25. SELECT Date
  26.   FROM CalendarFuture
  27. )
  29. INSERT INTO @Calender
  30. SELECT Date, 
  31.        DATEADD(MICROSECOND,-3,CAST(DATEADD(DAY,1,Date) AS DATETIME2)) AS DateEndTime,
  32.        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,
  33. 	   DATENAME(MONTH,Date) AS MonthName, DATENAME(WEEKDAY,Date) AS DayName,
  34.        DATEADD(DAY,1-DATEPART(WEEKDAY,Date),Date) AS WeekStartDate, DATEADD(DAY,7-DATEPART(WEEKDAY,Date),Date) AS WeekEndDate,
  35. 	   DATEADD(DAY,1-DATEPART(DAY,Date),Date) AS MonthStartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,Date),Date))) AS MonthEndDate,
  36. 	   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,
  37. 	   DATEFROMPARTS(DATEPART(YEAR,Date),1,1) AS YearStartDate, DATEADD(DAY,-1,DATEFROMPARTS(DATEPART(YEAR,Date)+1,1,1)) AS YearEndDate,
  38. 	   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,
  39. 	   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,
  40. 	   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,
  41. 	   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,
  42. 	   CASE WHEN DATEPART(WEEKDAY,Date) IN (1,7) THEN 0 ELSE 1 END AS IsWeekDay
  43.   FROM Calendar
  44.  ORDER BY Date
  45.  OPTION (MAXRECURSION 0)
  46.  RETURN
  47. 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:

  1. -- Date filters
  2. declare @startDateTime datetime = &#39;2021-08-01 00:00:00.000&#39;;
  3. declare @endDateTime datetime = &#39;2021-09-01 00:00:00.000&#39;;
  4. drop table if exists #daysInRange;
  5. with daysInRange
  6. as (
  7. select @startDateTime as &#39;StartDateTime&#39;, dateadd(day, 1, @startDateTime) as &#39;EndDateTime&#39;
  8. union all
  9. select dateadd(day, 1, StartDateTime) as &#39;StartDateTime&#39;,
  10. dateadd(day, 1, EndDateTime) as &#39;EndDateTime&#39;
  11. from daysInRange
  12. where StartDateTime &lt; @endDateTime
  13. )
  14. select *
  15. into #daysInRange
  16. from daysInRange
  17. select conn.ConnectionId,
  18. r.StartDateTime,
  19. r.EndDateTime
  20. from dbo.ConnectionData conn,
  21. cross apply (
  22. select *
  23. from #daysInRange
  24. ) as r
  25. left outer join dbo.ConsumptionData cons on cons.ConnectionId = conn.ConnectionId and cons.StartDateTime = r.StartDateTime and cons.EndDateTime = r.EndDateTime
  26. where conn.STARTDATETIME &lt; @endDateTime
  27. and conn.ENDDATETIME &gt; @startDateTime
  28. and conn.Active = 1
  29. and cons.ConnectionMember is null
  30. group by conn.ConnectionId,
  31. r.StartDateTime,
  32. r.EndDateTime
  33. order by r.StartDateTime
  34. 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.

huangapple
  • 本文由 发表于 2023年6月29日 21:06:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/76581360.html
  • sql
  • sql-server
  • tsql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定