U-SQL 加入两者之间以创建每月结果

Question

>编辑： 我一直在研究所需的结果。让我更好地解释一下：我试图达到的是为每个 SomeData 的每个 YearMonth 创建一行，在它的开始和结束日期之间。

例如，SomeData "88888888888888888881"，带有 startDate "2005-12-06 00：00：00.000"和结束日期 2006-03-13 00：00：000"。 我希望行如下所示：

88888888888888888881, 200512
88888888888888888881, 200601
88888888888888888881, 200602
88888888888888888881, 200603

我知道这可能会将结果"爆炸"成一个巨大的文件。

在我的帖子下面：

我正在尝试用U-SQL重写我们之前在T-SQL中做过的东西。

问题是 U-SQL 不允许在联接期间发生 between 。

T-SQL 联接如下所示：

SELECT rf.SomeData AS SomeData,
rd.YearMonth AS YearMonth,
(rf.SomeData + '-' + rd.YearMonth.ToString()) AS MonthlyKey,
rf.SomeKey AS SomeKey
FROM MyTable rf
INNER JOIN dbo.DimDate rd
ON rd.Date >= rf.StartDate
AND rd.Date <= (CASE WHEN rf.EndDate IS NULL THEN GETDATE() ELSE rf.EndDate END)

在 U-SQL 中，我是这样开始的，但是我现在应该如何编写 JOIN？

@EditedTable =
SELECT rf.SomeData AS SomeData,
rd.YearMonth AS YearMonth,
(rf.SomeData + "-" + rd.YearMonth.ToString()) AS MonthlyKey,
rf.SomeKey AS SomeKey
FROM @MyTable AS rf
INNER JOIN
@date AS rd
ON 

重要的是，我们要获取开始日期和结束日期之间的所有数据，并创建一个每月密钥，以便"SomeData"以后可以与另一个表联接。

我尝试使用交叉连接，但在运行时，它卡在 80% 并且似乎永远不会结束。它一直在一个顶点中写入 GB。此外，我实际上不确定这会产生相同的结果。

@EditedTableCROSS =
SELECT rfj.SomeData AS SomeData,
rfj.StartDate AS StartDate,
rfj.EndDate AS EndDate,
(rfj.SomeData + "-" + dtj.YearMonth.ToString()) AS MonthlyKey, 
rfj.SomeKey AS SomeKey
FROM
(
SELECT SomeData AS SomeData,
StartDate AS StartDate,
EndDate AS EndDate,
SomeKey AS SomeKey
FROM @TableA
WHERE SomeData != ""
) AS rfj
CROSS JOIN
(
SELECT DISTINCT
dt.Date AS Date,
dt.YearMonth AS YearMonth,
dt.Month AS Month,
rf.StartDate AS StartDate
FROM @date AS dt INNER JOIN @TableA AS rf ON rf.StartDate == dt.Date
WHERE rf.StartDate >= dt.Date AND
dt.Date <= DateTime.Now
) AS dtj
WHERE rfj.StartDate <= dtj.Date AND
rfj.EndDate >= dtj.Date;

上面代码的问题在于"INNER JOIN @TableA AS rf ON rf。开始日期 == dt。日期"不在唯一键上，某些日期出现不止一次。所以我怀疑这是办法....

请分享您的想法？

编辑： 人们要求提供样本数据，结束日期可能包含：

2006-03-13 10:27:13.000
2016-03-02 18:48:11.000
2016-03-02 18:42:57.000
NULL
2013-09-12 09:19:05.000
NULL
2016-03-02 18:59:37.000
NULL
NULL

开始日期：

2005-12-06 00:00:00.000
2011-03-29 20:57:51.000
2007-11-01 00:00:00.000
2007-11-01 00:00:00.000
2007-11-01 00:00:00.000
2011-02-28 00:00:00.000
2011-02-28 00:00:00.000
2011-02-28 00:00:00.000
2008-01-17 00:00:00.000

DimDate 包含 2000 年至 2018 年的日期。

SomeDate 和 SomeKey 看起来像这样：

88888888888888888881
88888888888888888882
88888888888888888883
88888888888888888884
88888888888888888885
88888888888888888886
88888888888888888887
88888888888888888888
88888888888888888889

Answer 1

U-SQL 在谓词中不支持 BETWEEN 的原因是没有适用于非等值连接的横向扩展联接算法。即使我们在语法上允许它，它仍然会在计划中进入交叉连接。

您要做的是获取一个可以分区的联接。一种方法是，如果可以在分区键上有一个相等联接，然后在该分区内进行交叉联接。

但是，在您的情况下，我认为您实际上并不需要加入。我认为您要做的是在开始日期和结束日期之间每天生成一行。

我会使用没有比例限制的CROSS APPLY EXPLODE来执行此操作。下面是一个示例：

@MyTable = 
SELECT * 
FROM (VALUES
(81,81,(DateTime?) DateTime.Parse("2005-12-06 00:00:00.000"),(DateTime?) DateTime.Parse("2006-03-13 10:27:13.000")),
(82,82,(DateTime?) DateTime.Parse("2011-03-29 20:57:51.000"),(DateTime?) DateTime.Parse("2016-03-02 18:48:11.000")),
(83,83,(DateTime?) DateTime.Parse("2007-11-01 00:00:00.000"),(DateTime?) DateTime.Parse("2016-03-02 18:42:57.000")),
(84,84,(DateTime?) DateTime.Parse("2007-11-01 00:00:00.000"),(DateTime?) null),
(85,85,(DateTime?) DateTime.Parse("2007-11-01 00:00:00.000"),(DateTime?) DateTime.Parse("2013-09-12 09:19:05.000")),
(86,86,(DateTime?) DateTime.Parse("2011-02-28 00:00:00.000"),(DateTime?) null),
(87,87,(DateTime?) DateTime.Parse("2011-02-28 00:00:00.000"),(DateTime?) DateTime.Parse("2016-03-02 18:59:37.000")),
(88,88,(DateTime?) DateTime.Parse("2011-02-28 00:00:00.000"),(DateTime?) null),
(89,89,(DateTime?) DateTime.Parse("2008-01-17 00:00:00.000"),(DateTime?) null)
) AS T(SomeKey, SomeData, StartDate, EndDate);
@res = 
SELECT SomeKey, SomeData, StartDate, EndDate, DailyDate 
FROM @MyTable 
CROSS APPLY EXPLODE 
(Enumerable.Range(0, 
1 + (EndDate == (DateTime?) null ? DateTime.Now 
: EndDate.Value).Subtract(StartDate.Value).Days)
.Select(offset => StartDate.Value.AddDays(offset))
) AS T(DailyDate);
OUTPUT @res
TO "/output/test.csv"
USING Outputters.Csv(outputHeader : true);

这是一个典型的例子，说明基于场景提问而不是要求翻译更容易回答:)。

Answer 2

我让这个脚本来处理我生成的一些示例数据。

@dateDim =
EXTRACT xdate DateTime,
yearMonth string
FROM "/input/dbo.DimDate.tsv"
USING Extractors.Tsv();
@data =
EXTRACT 
someKey int,
someData string,
startDate DateTime,
endDate DateTime?
FROM "/input/dbo.MyTable.tsv"
USING Extractors.Tsv();
/*
// Use U-SQL ISNULL conditional operator which is ?
@working =
SELECT COUNT( * ) AS records
FROM
(
SELECT *
FROM @dateDim AS dd
CROSS JOIN
@data AS d
WHERE dd.xdate BETWEEN d.startDate AND (d.endDate == (DateTime?)null ? DateTime.Now : d.endDate)
) AS x;
*/
@working =
SELECT COUNT( * ) AS records
FROM
(
SELECT *
FROM @dateDim AS dd
CROSS JOIN
@data AS d
WHERE dd.xdate >= d.startDate
AND dd.xdate <= (d.endDate == (DateTime?)null ? DateTime.Now : d.endDate)
) AS x;