U-SQL Как выбрать первое значение в столбце, которое отличается от текущей строки?

Question

Я борюсь за то, как сделать "многострочную" формулу в U-SQL. Я упорядочил данные по дате, и для каждого из них я хочу найти первое значение "Порт", которое не равно значению текущей строки. Аналогичным образом я хочу найти последнюю строку в значении даты с текущим значением порта, чтобы выяснить, сколько дней судно находилось в порту. Имейте в виду, что это должна быть строка с тем же именем порта, без новых / других портов между ними.

Я загружаю свои данные следующим образом:

@res = SELECT
        Port,
        Date
        FROM @data;

Вот как устроено мое свидание:

Port      |   Date       |
Port A    |   1/1/2017   |
Port A    |   1/1/2017   |
Port A    |   1/2/2017   |
Port B    |   1/4/2017   |
Port B    |   1/4/2017   |
Port B    |   1/4/2017   |
Port B    |   1/5/2017   |
Port B    |   1/6/2017   |
Port C    |   1/9/2017   |
Port C    |   1/10/2017  |
Port C    |   1/11/2017  |
Port A    |   1/14/2017  |
Port A    |   1/15/2017  |

Как я хотел бы, чтобы данные были структурированы:

Port      |   Date       |  Time in Port   | Previous Port
Port A    |   1/1/2017   |      0          |   N/A
Port A    |   1/1/2017   |      0          |   N/A
Port A    |   1/2/2017   |      1          |   N/A
Port B    |   1/4/2017   |      0          |   Port  A
Port B    |   1/4/2017   |      0          |   Port  A
Port B    |   1/4/2017   |      0          |   Port  A
Port B    |   1/5/2017   |      1          |   Port  A
Port B    |   1/6/2017   |      2          |   Port  A
Port C    |   1/9/2017   |      0          |   Port  B
Port C    |   1/10/2017  |      1          |   Port  B
Port C    |   1/11/2017  |      2          |   Port  B
Port A    |   1/14/2017  |      0          |   Port  C
Port A    |   1/15/2017  |      1          |   Port  C

Я новичок в U-SQL, и поэтому у меня возникли некоторые проблемы, связанные с этим. Моим первым инстинктом было бы использовать некоторую комбинацию LEAD()/LAG() и ROW_NUMBER() OVER(PARTITION BY xx ORDER BY Date), но я не уверен, как получить именно тот эффект, который я ищу.

Кто-нибудь может указать мне правильное направление?

Answer 1

Вы можете делать то, что вам нужно, с помощью так называемых функций ранжирования и аналитики, таких как LAG, DENSE_RANK и OVER пункт, хотя это не совсем просто. Эта простая установка работала для ваших тестовых данных, я бы посоветовал провести тщательное тестирование с большим и более сложным набором данных.

// Test data
@input = SELECT *
     FROM (
        VALUES
        ( "Port A", DateTime.Parse("1/1/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port A", DateTime.Parse("1/1/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port A", DateTime.Parse("1/2/2017", new CultureInfo("en-US") ), 1 ),
        ( "Port B", DateTime.Parse("1/4/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port B", DateTime.Parse("1/4/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port B", DateTime.Parse("1/4/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port B", DateTime.Parse("1/5/2017", new CultureInfo("en-US") ), 1 ),
        ( "Port B", DateTime.Parse("1/6/2017", new CultureInfo("en-US") ), 2 ),
        ( "Port C", DateTime.Parse("1/9/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port C", DateTime.Parse("1/10/2017", new CultureInfo("en-US") ), 1 ),
        ( "Port C", DateTime.Parse("1/11/2017", new CultureInfo("en-US") ), 2 ),
        ( "Port A", DateTime.Parse("1/14/2017", new CultureInfo("en-US") ), 0 ),
        ( "Port A", DateTime.Parse("1/15/2017", new CultureInfo("en-US") ), 1 )
     ) AS x ( Port, Date, timeInPort );



// Add a group id to the dataset
@working =
    SELECT Port,
           Date,
           timeInPort,
           DENSE_RANK() OVER(ORDER BY Date) - DENSE_RANK() OVER(PARTITION BY Port ORDER BY Date) AS groupId

    FROM @input;


// Use the group id to work out the datediff with previous row
@working =
    SELECT Port,
           Date,
           timeInPort,
           groupId,
           Date.Date.Subtract((DateTime)(LAG(Date) OVER(PARTITION BY groupId ORDER BY Date) ?? Date)).TotalDays AS diff    // datediff

    FROM @working;


// Work out the previous port, based on group id
@ports =
    SELECT Port, groupId
    FROM @working
    GROUP BY Port, groupId;

@ports =
    SELECT Port, groupId, LAG(Port) OVER( ORDER BY groupId ) AS previousPort
    FROM @ports;


// Prep the final output
@output =
    SELECT w.Port,
           w.Date.ToString("M/d/yyyy") AS Date,
           SUM(w.diff) OVER( PARTITION BY w.groupId ORDER BY w.Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS timeInPort,
           p.previousPort
    FROM @working AS w
         INNER JOIN
             @ports AS p
         ON w.Port == p.Port
            AND w.groupId == p.groupId;


OUTPUT @output TO "/output/output.csv"
ORDER BY Date, Port       
USING Outputters.Csv(quoting:false);

Мои результаты: