How to aggregate simple statistics with topk results?

[kesmit13]

I'm trying to aggregate various simple statistics such as min, max, and mean with topk results in a single query so that I can use it in a table expression, but I'm not having much luck. I have the following aggregate.

t.aggregate(
    name=ibis.literal('Survived'),
    min=t.Survived.min(), 
    max=t.Survived.max(), 
    mean=t.Survived.mean(),
    top=t.Survived.topk(1).to_aggregation().Survived.max(),  # Just using max here to get a single value, maybe there's a better way
    freq=t.Survived.topk(1).to_aggregation()['count'].max(),
)

This is the error I'm getting.

RelationError: Selection expressions don't fully originate from dependencies of the table expression.

[cpcloud]

Hey @kesmit13, thanks for opening a discussion about this.

I think you'll have to do a join:

t.aggregate(
    name=ibis.literal("Survived"),
    min=_.Survived.min(),
    max=_.Survived.max(),
    mean=_.Survived.mean(),
).cross_join(t.Survived.topk(1).to_aggregation())

I tried this with some public ClickHouse data:

In [65]: con = ibis.connect("clickhouse://[email protected]:9440/?secure=1")

In [66]: hn = con.tables.hackernews
h
In [67]: hn = hn.mutate(n=_.by.length())

In [68]: hn.aggregate(name=ibis.literal('name_length'), min=_.n.min(), max=_.n.max(), mean=_.n.mean()).cross_join(hn.n.topk(1).to_aggregation())
Out[68]:
┏━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━┓
┃ name        ┃ min   ┃ max   ┃ mean              ┃ n     ┃ count   ┃
┡━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━┩
│ string      │ int32 │ int32 │ float64           │ int32 │ int64   │
├─────────────┼───────┼───────┼───────────────────┼───────┼─────────┤
│ name_length │     0 │    15 │ 8.000957326015342 │     8 │ 4034030 │
└─────────────┴───────┴───────┴───────────────────┴───────┴─────────┘

There might be ways we can make this more convenient, like not requiring the to_aggregation() call. Open to API suggestions here!

[kesmit13]

That does get me closer. Is there a way to simply rename the columns created by topk?

[kesmit13]

Part of the issue I'm having is that I'm trying to build something similar to pandas DataFrame.describe which may require casting all values to strings in order for min / max / mean of all types to work in the same column. This causes the names in the topk to be things like 'cast(n, string)' rather than 'top'. The count column would be named 'freq' to match pandas.

[kesmit13]

I found a solution to my last issue. I don't know if this is the best solution, but it works. I hadn't realized that you could use functions as elements in a projection. That's really cool!

t.aggregate(
    name=ibis.literal("Survived"),
    min=_.Survived.min(),
    max=_.Survived.max(),
    mean=_.Survived.mean(),
).cross_join(t.Survived.topk(1).to_aggregation()[lambda x: x.Survived.cast('string').name('top'), lambda x: x['count'].name('freq')])

[kesmit13]

Here's an even easier way for the final step.

t.aggregate(
    name=ibis.literal("Survived"),
    min=_.Survived.min(),
    max=_.Survived.max(),
    mean=_.Survived.mean(),
).cross_join(t.Survived.name('top').topk(1).to_aggregation(metric_name='freq'))

[kesmit13]

@cpcloud I'm really close to getting this working just the way I want, but I run into trouble if I do a projection first, then try to do my aggregations that include the cross_join with topk.

tsub = t[['Name', 'Sex']]
ibis.union(
    tsub.aggregate(min=tsub.Name.min()).cross_join(tsub.Name.name('top').topk(1).to_aggregation(metric_name='freq')),
    tsub.aggregate(min=tsub.Sex.min()).cross_join(tsub.Sex.name('top').topk(1).to_aggregation(metric_name='freq')),
)

This causes a compile error CompileError: Multiple, unrelated CTEs found with the same name: 't0'. The SQL generated for just one of those aggregates is as follows. I guess the extra level of indirection caused by the projection is mucking things up. Any ideas on this one?

WITH t0 AS 
(SELECT t3.`Name` AS `Name`, t3.`Sex` AS `Sex` 
FROM titanic AS t3)
 SELECT t1.min, t2.top, t2.freq 
FROM (SELECT min(t0.`Name`) AS min 
FROM t0) AS t1 INNER JOIN (SELECT t3.top AS top, t3.freq AS freq 
FROM (SELECT t0.`Name` AS top, count(t0.`Name`) AS freq 
FROM t0 GROUP BY t0.`Name`) AS t3 ORDER BY t3.freq DESC 
 LIMIT :param_1) AS t2 ON :param_2 = 1 
 LIMIT :param_3