嗨，我需要逐周数据。如果数据不存在一周，则从最近一周开始。 [英] Hi I need to get group by week data. If data not present for one week then get from very latest week.

问题描述

我有这样的数据



输入

I have data like this

Input

Name | Date | Total
-------------------
AA | 2016-06-13 | 4
BB | 2016-06-13 | 3
CC | 2016-06-13 | 2
AA | 2016-06-20 | 6

需要像这样输出

Need Output like this

Name | Date | Total
-------------------
AA | 2016-06-13 | 4
BB | 2016-06-13 | 3
CC | 2016-06-13 | 2
AA | 2016-06-20 | 6
BB | 2016-06-20 | 3
CC | 2016-06-20 | 2

这是一周一周的数据。如果本周没有数据，那么获取最新的一周数据。

这里AA有2016-06-13和2016-06-20周的数据。但对于BB和CC，我们没有2016-06-20周的数据，在这种情况下需要从最近一周获得可用数据的数据（即2016-06-13）



我需要逐周数据。如果数据不存在一周，则从最近一周开始。

It is group by week data. If data is not present for this week then get the very latest week data.
Here AA have data in 2016-06-13 and 2016-06-20 week. But for BB and CC we don't have data for 2016-06-20 week, in this case need to get data from very latest week which data available (i.e 2016-06-13)

I need to get group by week data. If data not present for one week then get from very latest week.

推荐答案

这是一个非常有趣的问题，因为您需要生成缺少的行（日期）每个名称并确定最后一个非空值。后者是一个经典问题，没有明显简单的解决方案。我将调整我在这里找到的解决方案 - Last non NULL | SQL Server Pro中的T-SQL内容 [ ^ ]你可以通过搜索sql last non null value找到其他人



我也选择使用CTE将查询分解为让它更容易看到发生了什么...

This is quite an interesting problem in that you need to generate the missing rows (dates) for each Name AND determine the last non-null value. The latter is a classic problem for which there is no obviously easy solution. I'm going to adapt the solution I found here - Last non NULL | T-SQL content from SQL Server Pro[^] but you can find others by searching "sql last non null value"

I've also chosen to use CTEs to break down the query to make it easier to see what is going on...

;with CTE AS
(
	select [Name], [Date] from
	(select distinct [Name] from myTable) q1
	cross join
	(select distinct [Date] from myTable) q2
), CTE2 AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY q1.[Date],q1.[Name]) rn, q1.[Name], q1.[Date], a.Total
	FROM CTE q1
	LEFT OUTER JOIN myTable a ON q1.Name = a.Name AND q1.Date = a.Date
)
SELECT[Name], [Date],  
CAST(SUBSTRING(MAX( CAST(rn AS BINARY(4)) + CAST(total AS BINARY(4)))
                    OVER( partition by [Name] ORDER BY rn
                    ROWS UNBOUNDED PRECEDING ),
            5, 4) AS INT) AS total
FROM CTE2
ORDER BY [Date], [Name]

第一部分（CTE）生成每个名称的缺失日期 - 使用您提供的样本数据，它返回

The first part (CTE) generates the missing dates for each name - with the sample data you provided it returns

Name    Date
AA	2016-06-13
BB	2016-06-13
CC	2016-06-13
AA	2016-06-20
BB	2016-06-20
CC	2016-06-20

第二部分（CTE2）为每个条目生成一个行号表并尝试从原始表中获取总值。它返回

The 2nd part (CTE2) generates a row number for each entry on the table and attempts to get the total value from the original table. It returns

rn      Name    Date            Total
1	AA	2016-06-13	4
2	BB	2016-06-13	3
3	CC	2016-06-13	2
4	AA	2016-06-20	6
5	BB	2016-06-20	NULL
6	CC	2016-06-20	NULL

最后一位（参见上面的链接以获得更全面的解释）将行号和总值组合为2个二进制值，因此该组合列的MAX值将为保持最新的非空值 - 我们然后解构该列以获得我们正在寻找的值。

并且整个事情返回

The final bit (see the link above for a fuller explanation) combines the row number and total value as 2 binary values, thus the MAX value of that combined column will hold the latest non-null value - we then deconstruct that column to get the value we are looking for.
And the whole thing returns

Name    Date            Total
AA	2016-06-13	4
BB	2016-06-13	3
CC	2016-06-13	2
AA	2016-06-20	6
BB	2016-06-20	3
CC	2016-06-20	2

这篇关于嗨，我需要逐周数据。如果数据不存在一周，则从最近一周开始。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！