Examples

Window functions

Description

rank() numbers the rows of each partition in window order. Tied rows share a rank and the next rank skips ahead: John and Janet are both 3, so Ann is 5.

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, rank, 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('rank', rank()->over(window()->partitionBy(ref('department'))->orderBy(ref('salary')->desc())))
    ->sortBy([ref('department'), ref('rank')])
    ->collect()
    ->write(to_output(truncate: false))
    ->run();

Contributors

Built in the open.

Join us on GitHub