Relation:project
Description
project name (aggretor) (, name (aggregator)...)
project inline name (aggretor) (, name (aggregator)...)
project rollup name (aggretor) (, name (aggregator)...)
Parameters
- name: must be a valid name.
- aggregator for numbers: count, sum, min, max, avg, median, stddev
- aggregator for text: sum, mins, maxs, avgs, medians
- inline: aggregates and creates a new column, therefore the number of rows stays the same
- rollup: aggregates and creates new lines for the aggregation.
Examples
Using the sample relation films.csv
read "films.csv"
project director
| director |
|---|
| Godard |
| Truffaut |
| Varda |
| von Trotta |
| Kluge |
read "films.csv"
project film count
| film_count |
|---|
| 9 |
read "films.csv"
project director, film count, year min
| director | film_count | year_min |
|---|---|---|
| Godard | 3 | 1960 |
| Truffaut | 2 | 1960 |
| Varda | 2 | 1962 |
| von Trotta | 1 | 1975 |
| Kluge | 1 | 1976 |
read "films.csv"
project director, film count
project film_count avg
| film_count_avg |
|---|
| 1.8 |
read "films.csv"
project inline director, film count
| film | director | year | film_count |
|---|---|---|---|
| A bout de souffle | Godard | 1960 | 3 |
| Tirez sur le pianiste | Truffaut | 1960 | 2 |
| Cléo de 5 à 7 | Varda | 1962 | 2 |
| Jules et Jim | Truffaut | 1962 | 2 |
| Pierrot le fou | Godard | 1965 | 3 |
| Week-End | Godard | 1967 | 3 |
| Die verlorene Ehre der Katharina Blum | von Trotta | 1975 | 1 |
| Der starke Ferdinand | Kluge | 1976 | 1 |
| Sans toi ni loi | Varda | 1985 | 2 |
read "films.csv"
project inline director, film count
select film_count = 3
| film | director | year | film_count |
|---|---|---|---|
| A bout de souffle | Godard | 1960 | 3 |
| Pierrot le fou | Godard | 1965 | 3 |
| Week-End | Godard | 1967 | 3 |
read "films.csv"
project director, film
project rollup director, film count
| director | film |
|---|---|
| Godard | A bout de souffle |
| Truffaut | Tirez sur le pianiste |
| Varda | Cléo de 5 à 7 |
| Truffaut | Jules et Jim |
| Godard | Pierrot le fou |
| Godard | Week-End |
| von Trotta | Die verlorene Ehre der Katharina Blum |
| Kluge | Der starke Ferdinand |
| Varda | Sans toi ni loi |
| Godard | 3 |
| Truffaut | 2 |
| Varda | 2 |
| von Trotta | 1 |
| Kluge | 1 |
| 5 |
Comments
Project (without inline or rollup) reduces in general the number of the rows also, because all tuples must be unique.
With agregation, the columns are automatically renamed
Project inline maybe useful when you have to choose in a second step rows based on an aggregate criteria.
Project rollup creates quickly an overview on a distribution.
Empty values are ignored in aggregations.