主页 / dba / 问题 / 292201
Accepted
hem6d
Asked: 2021-05-25 20:36:38 +0800 CST

两个日期之间的总和日期并按月分组

  • 772

我有一个如下表：

Name  Start Date  End Date
Joe   20/04/2021  20/05/2021
John  01/05/2021  28/05/2021

我正在使用 SQL 表值函数来返回一个包含 2 列的表：月份和计数本月的总日期。

例子：

  • 乔：4月10天，5月20天
  • 约翰：5 月 28 天

最后我会返回一个新表

月 数数
4 10
5 48

我尝试使用 datediff 和 datepart 按月分组，但不知道如何在分组后求和。有没有办法做到这一点？

此外，我想从日期和日期添加过滤器。

sql-server sql-server-2012

3 个回答

  1. 另一种方法：

    CREATE TABLE dbo.DateRanges
(
    [Name] varchar(20) NOT NULL,
    [Start Date] date NOT NULL,
    [End Date] date NOT NULL,

    PRIMARY KEY ([Name], [Start Date]),
    CHECK ([End Date] >= [Start Date])
);

INSERT dbo.DateRanges
    ([Name], [Start Date], [End Date])
VALUES
    (CONVERT(varchar(20), 'Joe'), CONVERT(date, '20/04/2021', 103), CONVERT(date, '20/05/2021', 103)),
    (CONVERT(varchar(20), 'John'), CONVERT(date, '01/05/2021', 103), CONVERT(date, '28/05/2021', 103));

    解决方案：

    SELECT
    [Month] = Months.m,
    [Count] =
        SUM
        (
            -- Number of days in the current month
            1 + DATEDIFF
            (
                DAY, 
                -- Latest of [Start Date] and current month start date
                IIF(DR.[Start Date] <= MonthRange.StartDate, MonthRange.StartDate, DR.[Start Date]),
                -- Earliest of [End Date] and current month end date 
                IIF(DR.[End Date] >= MonthRange.EndDate, MonthRange.EndDate, DR.[End Date])
            )
        )
FROM dbo.DateRanges AS DR
JOIN
(
    -- Month numbers
    VALUES
        (01), (02), (03), (04), (05), (06),
        (07), (08), (09), (10), (11), (12)
) AS Months (m)
    -- Months that overlap the [Start Date], [End Date] range
    ON Months.m BETWEEN MONTH(DR.[Start Date]) AND MONTH(DR.[End Date])
    OR Months.m BETWEEN MONTH(DR.[End Date]) AND MONTH(DR.[Start Date])
CROSS APPLY
(
    -- Start and end day of each month
    VALUES
        (
            DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1),
            EOMONTH(DATEFROMPARTS(YEAR(DR.[Start Date]), Months.m, 1))
        )
) AS MonthRange (StartDate, EndDate)
GROUP BY
    Months.m
ORDER BY
    Months.m;

    输出：

    月 数数
    4 11
    5 48

    在线演示：

    db<>小提琴演示

    • 2
  2. Best Answer
    WITH 
-- Generate months list
cte1 AS ( SELECT MIN(MONTH([Start Date])) [Month], 
                 MAX(MONTH([End Date])) LastMonth, 
                 MIN(YEAR([Start Date])) [Year]
          FROM test
          UNION ALL
          SELECT [Month] + 1, 
                 LastMonth,
                 [Year]
          FROM cte1
          WHERE [Month] < LastMonth ),
-- Convert months list to start-end list
cte2 AS ( SELECT DATEFROMPARTS([Year], [Month], 1) MonthStart,
                 EOMONTH(DATEFROMPARTS([Year], [Month], 1)) MonthEnd,
                 [Month]
          FROM cte1 )
-- Get needed data
SELECT cte2.[Month],
       SUM(1 + DATEDIFF(day, CASE WHEN test.[Start Date] < cte2.MonthStart
                                  THEN cte2.MonthStart
                                  ELSE test.[Start Date] END,
                             CASE WHEN test.[End Date] > cte2.MonthEnd
                                  THEN cte2.MonthEnd
                                 ELSE test.[End Date] END )) [Count]
FROM test
          -- join only overlapped periods
JOIN cte2 ON test.[Start Date] <= cte2.MonthEnd
         AND cte2.MonthStart <= test.[End Date]
GROUP BY cte2.[Month];

    https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c2d6d92223f6516d78550a021cd5c3ce

    该查询假定所有期间都属于同一年。当然也可以简化。从“2021-04-20”到“2021-04-30”（含）的期间长度为 11 天，而不是 10 天。

    我想从日期和日期添加过滤器

    这仅影响cte1（不是根据表数据而是根据所需的期间日期生成日历），并且如果您想部分过滤某个月份（不是从一个月的第一天到最后一天）在cte2. 由于ON子句表达式，主查询将被自动过滤。

    • 1

  3. TL;博士

    我添加了前一年的一些样本数据（见小提琴），并包括跨过除夕的一个时期。我使用的方法假设存在日历表 - 下面讨论使用递归 CTE 执行此操作的方法。通过添加WHERE提供的条款（注释掉），可以限制日期范围和/或在给定月份是否有任何停留。

    SELECT
  DATEPART(mm, ad.tdate)    AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",  -- Not strictly necessary
  DATEPART(yy, ad.tdate)    AS "YEAR",
  SUM
  (
    CASE
      WHEN s.start_date IS NOT NULL THEN 1
      ELSE 0
    END
  ) AS "Stays"
FROM all_dates ad
LEFT JOIN stay s
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
-- WHERE ad.tdate >= '2021-03-01' 
-- AND ad.tdate <= '2021-06-30'       -- Restrict dates of stays  
-- WHERE s.start_date IS NOT NULL     -- Show only months with at least 1 stay
GROUP BY DATEPART(yy, ad.tdate), DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(yy, ad.tdate), DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

    结果（参见小提琴中的额外数据- 这个结果似乎是一致的）：

    Month        Name   YEAR    Stays
    1     January   2020        0
    2    February   2020        0
    3       March   2020        0
    4       April   2020        0
    5         May   2020       12
    6        June   2020       20
    7        July   2020       12
    8      August   2020       20
    9   September   2020        0
   10     October   2020        6
   11    November   2020       11
   12    December   2020       17
    1     January   2021        2
    2    February   2021        2
    3       March   2021        2
    4       April   2021       11
    5         May   2021       48
    6        June   2021        0
    7        July   2021        0
    8      August   2021        0
    9   September   2021        2
   10     October   2021        0
   11    November   2021       11
   12    December   2021        5

    更详细的讨论：

    对于已接受的答案，有两种或三种替代方法可能会起作用。这是需要日历表的经典案例。有两种方法可以做到这一点 - 第一种是使用“经典” SQL - 使用函数 - 以及使用更现代的CTE（通用表表达式）方法，特别是递归 CTE（参见上面参考中的链接）。

    我遇到的第一个宝石是Jon Tavernier 的这篇关于如何在 SQL Server 中创建（物化）日历表的文章 - 我将它缩减到这个问题所需的最低限度，尽管这种表有很多潜在用途，你不妨考虑在 10-20 年期间（仅约 3.5K - 7K 条记录）和更多字段（例如每月的第一天、会计期结束...）进行此操作。

    只是一小点-您的表定义似乎被简化了，但我建议您将其设置为更像这样-增加信息量，服务器使其更容易提出一个好的计划：


    CREATE TABLE stay 
(
  name VARCHAR(255) NOT NULL, 
  start_date DATE NOT NULL, 
  end_date DATE   NOT NULL,
  
  CONSTRAINT n_sd_ed_pk PRIMARY KEY (name, start_date, end_date),
  
  CONSTRAINT n_sd_uq UNIQUE (name, start_date),
  
  CONSTRAINT n_ed_uq UNIQUE (name, end_date),
  
  CONSTRAINT sd_lt_ed_ck CHECK (start_date < end_date),
  
);

    您可能还想确保给定name和它们start_date之间没有重叠end_date- 但我不知道如何在 SQL Server 中执行此操作，除非使用触发器 - 我的 UNIQUE 约束是通过 SQL 进行的尝试 - 但它们不会阻止嵌套间隔...

    这是一篇关于如何使用触发器进行操作的文章。

    第一种方法（日历表 - Tavernier）：

    方法 1 的代码可在此处的小提琴中找到。

    DECLARE @start_dt AS DATE = '2021-01-01';       -- Date from which the calendar table will be created (inclusive).
DECLARE @end_dt   AS DATE = '2022/01/01';       -- Calendar table will be created up to this date (not inclusive).

CREATE TABLE all_dates
(
 tdate DATE PRIMARY KEY
);

WHILE @start_dt < @end_dt
BEGIN
  INSERT INTO all_dates 
  (
    tdate   -- can generate many more useful values - see article by Jon Tavernier
  ) 
  VALUES 
  (
    @start_dt 
  )
  SET @start_dt = DATEADD(DAY, 1, @start_dt)
END;

    然后我检查：

    SELECT * FROM all_dates;

    结果（为简洁起见）：

      tdate
2021-01-01
2021-01-02
2021-01-03
2021-01-04
2021-01-05

    stay接下来，我们根据 OP 的问题（我称之为 table ）设置我们的测试数据：

    name    start_date  end_date
Joe     2021-04-20  2021-05-20
John    2021-05-01  2021-05-28

    然后，我运行了这个查询：

    SELECT 
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "M Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate) 
ORDER BY DATEPART(mm, ad.tdate);

    结果：

    Month     Name  Count
    4    April     11
    5      May     48

    月份名称未包含在规范中，并且可以很容易地删除 - 只是觉得它很高兴。

    然后我turned on Statistics如下：

    SET STATISTICS PROFILE ON;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

    并重新运行查询 - 结果如下所述。

    第二种方法（通过 CTE 使用预定义日期的日历年表）：

    这种方法的代码可在 fiddle here上找到。

    有时不允许顾问创建物理表，而我想要自然的方法是使用 CTE 方法，我偶然发现Creating a date dimension or calendar table in SQL Server了 Aaron Bertand（本教区）的下一个宝石 ( )。

    他以这些圣言开始他的作品：

    我听到的对日历表的最大反对意见之一是人们不想创建表。与使用各种函数来确定每个查询中的日期相关信息相比，我无法强调表在大小和内存使用方面有多便宜，尤其是在底层存储继续更大更快的情况下。存储在表中的二十或三十年的日期最多占用几 MB，压缩时甚至更少，如果您经常使用它们，它们将始终在内存中。

    他接着说：

    这是一个一次性的群体，所以我不担心速度，尽管这种特定的 CTE 方法并没有懈怠。我喜欢将所有列具体化到磁盘，而不是依赖计算列，因为表在初始填充后变为只读。因此，我将在最初的一系列 CTE 期间进行大量此类计算。首先，我将一次显示每个 CTE 的输出。

    因此，即使是第一种方法中的函数也可能是合适的——但是，CTE在 Tavernier 的上述文章之后，尽我所能确定，'s（通用表表达式 - 也请参见内部 RECURSIVE CTE 的链接）被引入 SQL Server。

    我改编了 Bertrand 的第二个 CTE 中的代码，如下所示：

    WITH seq (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, '2021-01-01', '2021-12-31')
),
all_dates (tdate) AS
(
  SELECT DATEADD(DAY, n, '2021-01-01')  FROM seq
)
SELECT tdate FROM all_dates
OPTION (MAXRECURSION 0);  -- Important!!! Fails after 100 recursions if not present

    结果（为简洁起见）：

             tdate
2021-01-01 00:00:00.000
2021-01-02 00:00:00.000
2021-01-03 00:00:00.000

    所以，就我们的日历表而言，我们是金色的......

    然后，我将其放在方法 1 中的原始查询上，如下所示：

    WITH seq (n) AS
(
  SELECT 0
  UNION ALL
  SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, '2021-01-01', '2021-12-31')
),
all_dates (tdate) AS
(
  SELECT DATEADD(DAY, n, '2021-01-01') FROM seq
)
SELECT 
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate) 
ORDER BY DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

    结果（相同）：

    Month    Name   Count
    4   April      11
    5     May      48

    所以，我做了与第一种方法相同的方法并打开了统计 - 下面讨论。

    第三种方法（限制日期）：

    小提琴在这里可用。

    在这里，为了“效率”，我将日历表中的日期限制MIN()为 start_time 和表MAX()中end_time的stay，如下所示：

    WITH stays (min_sd, max_ed, no_days) AS
(
  SELECT 
    MIN(start_date) AS min_sd, 
    MAX(end_date) AS max_ed,
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) AS no_days
  FROM stay
),
seq(n, tdate) AS
(
  SELECT 0, (SELECT min_sd FROM stays)
  UNION ALL
  SELECT n + 1, DATEADD(DAY, n + 1, (SELECT min_sd FROM stays))
  FROM seq
  WHERE n < (SELECT no_days FROM stays)
)
SELECT * FROM seq;

    结果（为简洁起见）：

    n   tdate
0   2021-04-20
1   2021-04-21
2   2021-04-22
(39 rows...)

    所以，现在我们有了最早start_date和最晚之间的每个日期end_date。

    并使用这些日期作为我们的日历表，我们制定了查询：

    WITH stays (min_sd, max_ed, no_days) AS
(
  SELECT 
    MIN(start_date) AS min_sd, 
    MAX(end_date) AS max_ed,
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) AS no_days
  FROM stay
),
all_dates(n, tdate) AS
(
  SELECT 0, (SELECT min_sd FROM stays)
  UNION ALL
  SELECT n + 1, DATEADD(DAY, n + 1, (SELECT min_sd FROM stays))
  FROM all_dates
  WHERE n < (SELECT no_days FROM stays)
)
SELECT 
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate) 
ORDER BY DATEPART(mm, ad.tdate)
OPTION (MAXRECURSION 0);

    结果（相同）：

    Month    Name   Count
    4   April      11
    5     May      48

    我在小提琴结束时有一些性能统计数据。当然，很难从无法控制的服务器上（相对）少量数据的性能分析中得出许多结论 - 我会敦促您使用自己的数据并在自己的负载下进行测试。 .

    • 0

相关问题