SQL将月份数据拆分为天数日期行

Question

我在SQL Server中有一个表，其中包含每月目标单位。

select TargetDate, Units
FROM TargetTable;

TargetDate  Units
2020-01-01  62
2020-02-01  87
2020-03-01  104
2020-04-01  109

我想要按日期的单位--总单位除以当月的天数--并具有包含天和日单位的行……如下所示：

TargetDate  Units
2020-01-01  2
2020-01-02  2
2020-01-03  2
2020-01-04  2
    .
    .
    .
2020-02-01  3

我对SQL的理解还处于初学者的水平，如果能得到任何帮助，我将不胜感激。

提亚

Answer 1

一种选择是使用递归查询：

with cte as (
    select 
        targetdate, 
        eomonth(targetdate) enddate, 
        1.0 * units / day(eomonth(targetdate)) units
    from targettable
    union all
    select dateadd(day, 1, targetdate), enddate, units 
    from cte
    where targetdate < enddate
)
select targetdate, units from cte

备注：

• 我假设units并不总是一个月中天数的倍数，所以这会给你一个小数值，而不是一个整数。

• 如果您需要处理超过100天(大约3个月的数据)，则需要在查询的末尾添加option (max recursion 0)。

• eomonth()是在SQL Server 2012中引入的(早期版本中也有替代方案)

Answer 2

SELECT 
  targetDate, 
  DATEADD(day, a, targetDate) as dd, 
  CAST(units as FLOAT) / DAY(EOMONTH(targetdate)) as uu

FROM 
    ( 
        VALUES 
        (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
        (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
        (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30)
    ) a(a)

    CROSS JOIN

    /*
    (SELECT CAST('2020-01-01' as date) targetdate, 62 as units  UNION ALL 
     SELECT CAST('2020-02-01' as date), 87 UNION ALL 
     SELECT CAST('2020-03-01' as date), 104
    )x*/
    targetTable

WHERE 
   DATEADD(day, a, targetdate) <= EOMONTH(targetdate)

它的工作原理：

我们生成一个从0到30的数字块，并将您现有的数据与它们交叉，给出2020-01-01的31个重复，2020-02-01的31个重复，2020-03-01的31个重复，等等。

2020-01-01, 0
2020-01-01, 1
2020-01-01, 2
...

如果我们将数字(作为天数)添加到日期上，它会给出一个日期，该日期将滚动到该月，但有些日期会超时到下一个月：

2020-01-01, 0, 2020-01-01
2020-01-01, 1, 2020-01-02
2020-01-01, 2, 2020-01-03
...
2020-02-01, 28, 2020-02-29
2020-02-01, 29, 2020-03-01 --it's march!
2020-02-01, 30, 2020-03-02 --it's march!

我们使用where子句来删除超调的日期，方法是确保添加的日期小于或等于EOMONTH(targetdate) - EOMONTH返回的日期是传入日期的月份的最后一天

所以我们得到了递增日期，我们只需要每天的单位-除以一个月中的天数(由EOMONTH的日期(Targetdate )给出)。我们将单位转换为浮点数，这样sqlserver将使用小数而不是整数进行除法(否则60/31是1)

如果没有EOMONTH函数，可以使用DATEADD(DAY, -1, DATEADD(MONTH, 1 targetdate))前滚一个月和后退一天