主页 / dba / 问题 / 193030
Accepted
Elio Fernandes
Asked: 2017-12-14 08:26:52 +0800 CST

计算给定日期中每一天的总发生次数

  • 772

我有这张表，但没有足够的 SQL 来进行查询以实现下图所示的结果。

CREATE TABLE myDates (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')

查看“startDate”和“endDate”之间的所有日期并计算每天出现的总数：

预期结果 在此处输入图像描述

我刚刚从 Excel 文件中添加了这张图片，以更好地说明我想要实现的目标。我正在从 excel 转向 sql。忽略周末和 22/12 和 25/12。

在此处输入图像描述

Regrads，埃利奥·费尔南德斯

count

2 个回答

  1. 这是一种基于集合的方法，它使用公用表表达式 (CTE) 生成快速日期维度表，然后我们使用 CTE 按日期分组

    答案和@nates一样

    Declare @myDates TABLE  (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into @myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')
;

WITH dates ([Date])
AS (
    SELECT convert(DATE, '2017-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] <= '2020-12-31' -- Put the end date here 
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] >= m.startDate
    AND d.[Date] <= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!
    • 4
  2. Best Answer

    这是一种非常“程序化”的方法，与基于集合的/纯 RDBMS 不同，但它应该有效。基本上，您创建一个“频率表”，它跟踪源表中有多少次包含“日期”的行，每个日期从min到max。按照您的示例代码构建（即您已经创建了myDates表格并用数据填充了它）。

    CREATE TABLE #DateFrequency (theDate date, freq int);
--TRUNCATE TABLE #DateFrequency;

DECLARE @theDate date, @maxDate date;
SELECT @theDate = MIN(startDate), @maxDate = MAX(endDate)
    FROM myDates

WHILE (@theDate <= @maxDate)
BEGIN
    INSERT INTO #DateFrequency (theDate, freq)
    SELECT @theDate, COUNT(id)
    FROM myDates
    WHERE @theDate BETWEEN startDate AND endDate;

    SET @theDate = DATEADD(DAY, 1, @theDate);
END

SELECT * FROM #DateFrequency;
--DROP TABLE #DateFrequency;

    然后对#DateFrequency 中的数据做任何你需要做的事情；如果需要，将其设为永久表（而不是#temporary）。

    PS：阅读Aaron Bertrand 的这篇关于处理日期范围的博文；它并不直接针对您的问题，因为您（正确地）使用了Datetype 而不是DateTime，但如果您正在处理此类数据和此类查询，它仍然是我认为必读的内容。

    • 1

相关问题