Examples

Window functions

Description

row_number() numbers the rows of each partition 1, 2, 3, ... with no ties: John and Janet earn the same but get 3 and 4. Use it for top-N per group or for a per-partition id.

Documentation

Window functions compute over a frame of rows without collapsing them.

Window Functions

Code

<?php

declare(strict_types=1);

use function Flow\ETL\DSL\{data_frame, row_number, from_array, ref, to_output, window};

require __DIR__ . '/vendor/autoload.php';

data_frame()
    ->read(
        from_array([
            ['id' => 1, 'name' => 'Greg', 'department' => 'IT', 'salary' => 6000],
            ['id' => 2, 'name' => 'Michal', 'department' => 'IT', 'salary' => 5000],
            ['id' => 3, 'name' => 'Tomas', 'department' => 'Finances', 'salary' => 11_000],
            ['id' => 4, 'name' => 'John', 'department' => 'Finances', 'salary' => 9000],
            ['id' => 5, 'name' => 'Jane', 'department' => 'Finances', 'salary' => 14_000],
            ['id' => 6, 'name' => 'Janet', 'department' => 'Finances', 'salary' => 9000],
            ['id' => 7, 'name' => 'Ann', 'department' => 'Finances', 'salary' => 8000],
        ])
    )
    ->withColumn('row_num', row_number()->over(window()->partitionBy(ref('department'))->orderBy(ref('salary')->desc())))
    ->sortBy([ref('department'), ref('row_num')])
    ->collect()
    ->write(to_output(truncate: false))
    ->run();

Contributors

Built in the open.

Join us on GitHub