Создание дат в диапазоне дат с помощью U-SQL

Мы всегда рекомендуем разработчикам исследовать сначала использование чистого подхода U-SQL вместо использования UDO C#, вот еще один способ выполнения этой задачи.

Во-первых, подумайте, как бы вы просто получили список чисел в U-SQL.

@numbers_10 = 
    SELECT
        *
    FROM 
    (VALUES
        (0),
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9)
    ) AS T(Value);

Всего 10 чисел — от 0 до 9. Мы можем использовать CROSS JOIN, чтобы расширить список.

@numbers_100 = 
    SELECT (a.Value*10 + b.Value) AS Value
    FROM @numbers_10 AS a 
        CROSS JOIN @numbers_10 AS b;

Теперь у нас есть от 0 до 99. Мы можем использовать CROSS JOIN, чтобы генерировать еще больше чисел.

@numbers_10000 = 
    SELECT (a.Value*100 + b.Value) AS Value
    FROM @numbers_100 AS a CROSS JOIN @numbers_100 AS b;

Затем сгенерируйте список дат из этого.

DECLARE @StartDate = DateTime.Parse("1979-03-31");

...

@result = 
    SELECT 
        Value,
        @StartDate.AddDays( Value ) AS Date
    FROM @numbers_10000;

Полный скрипт выглядит так:

DECLARE @StartDate = DateTime.Parse("1979-03-31");

@numbers_10 = 
    SELECT
        *
    FROM 
    (VALUES
        (0),
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7),
        (8),
        (9)
    ) AS T(Value);

@numbers_100 = 
    SELECT (a.Value*10 + b.Value) AS Value
    FROM @numbers_10 AS a CROSS JOIN @numbers_10 AS b;

@numbers_10000 = 
    SELECT (a.Value*100 + b.Value) AS Value
    FROM @numbers_100 AS a CROSS JOIN @numbers_100 AS b;

@result = 
    SELECT 
        Value,
        @StartDate.AddDays( Value ) AS Date
    FROM @numbers_10000;

OUTPUT @result TO "/res.csv" USING Outputters.Csv(outputHeader:true);

Когда у вас есть список чисел или дат, может быть удобно сохранить его в таблицу U-SQL, чтобы вы могли легко получить список позже.

Самый простой способ сделать это — экспортировать ваше любимое измерение даты из вашего любимого хранилища и импортировать его в таблицу U-SQL.

Вы также можете сделать это, используя собственный код U-SQL, например:

DECLARE @outputFilepath string = "output/output74.csv";

//DECLARE @startDate DateTime = DateTime.Parse("19/7/2017");
//DECLARE @endDate DateTime = DateTime.Parse("21/7/2017");

DECLARE @startDate DateTime = DateTime.Parse("1/1/2000");
DECLARE @endDate DateTime = DateTime.Parse("31/12/2017");


// User-defined appliers
// Take one row and produce 0 to n rows
// Used with OUTER/CROSS APPLY
@output =
    SELECT outputDate
    FROM(
        VALUES ( 1 ) 
        ) AS dummy(x)
        CROSS APPLY new USQLtpch.makeDateRange (@startDate, @endDate) AS properties(outputDate DateTime);


OUTPUT @output
TO @outputFilepath
USING Outputters.Tsv();

Файл кода программной части:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace USQLtpch
{

    [SqlUserDefinedApplier]
    public class makeDateRange : IApplier
    {
        private DateTime startDate;
        private DateTime endDate;

        public makeDateRange(DateTime startDate, DateTime endDate)
        {
            this.startDate = startDate;
            this.endDate = endDate;
        }

        public override IEnumerable<IRow> Apply(IRow input, IUpdatableRow output)
        {

            // Initialise
            DateTime outputDate = this.startDate;


            // Loop until date range has been filled out
            while (outputDate <= endDate)
            {
                output.Set<DateTime>("outputDate", outputDate);

                // Increment date
                outputDate = outputDate.AddDays(1);

                yield return output.AsReadOnly();

            }
        }
    }
}

Я сделал это с помощью пользовательского Applier, который берет 1 строку и преобразует ее в 0 или n.

Шаг 1: Вам нужно иметь детерминированный порядок строк в наборе строк, чтобы это имело логический смысл. Итак, выясните, в каком столбце вы хотите упорядочить строки.

Шаг 2: Получите номер строки, присвоенный каждой строке. Вот пример того, как https://msdn.microsoft.com/en-us/library/azure/mt763822.aspx

Шаг 3. Вы можете использовать номер строки, назначенный каждой строке, в сочетании с выражением C#, чтобы сгенерировать дату, которая должна быть для каждой строки.

Это яркий пример того, как можно эффективно использовать .Net элементы языка U-SQL. В этом случае вы можете explode и Enumerable.Range получить увеличивающийся список значений, которые вы можете применить к своим данным:

DECLARE @startDate DateTime = DateTime.Parse("2000/01/01");
DECLARE @endDate DateTime = DateTime.Parse("2017/12/31");

@dates =
    SELECT d.DateValue
    FROM (VALUES(@startDate)) AS sd(s)
         CROSS APPLY    // EXPLODE creates a rowset from all the values in the given list
             EXPLODE(Enumerable.Range(0
                                     ,(@endDate - @startDate).Days
                                     )
                                     .Select(offset => sd.s.AddDays(offset))
                    ) AS d(DateValue)
    ;