Window functions in Exposed DSL

[dsdolzhenko]

I couldn't find a way to express a query containing a window function via Exposed DSL. Are there any plans to support window functions in Exposed DSL?

I could try to implement it if there are no objections.

[Tapac]

Hi @dsdolzhenko, there are no plans right now to implement window functions, so it would be great if you could provide a PR.

[dsdolzhenko]

Ok. I'll try to keep you posted.

[abishai]

I'm interested in window functions too and they are part of SQL2003 standard. Anybody tried to use them in Exposed, maybe with some custom extension?

[ghost]

I dont know if anyone can find this useful, but this is how I was able to reproduce a window function:

Original query:

SELECT  height,
extract(epoch from LAG(block_timestamp) OVER (order by height desc) ) - extract(epoch from block_timestamp) as block_creation_time
FROM block_cache
order by height desc
limit 10;

basic LAG function (can be customized to whatever types you need):

class Lag(val lag: Expression<DateTime>, val orderBy: Expression<Int>): Function<DateTime>(DateColumnType(true)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("LAG(", lag,") OVER (order by ", orderBy, " desc)") }
}

I also created an extract(epoch...) function for my use:

class ExtractEpoch(val expr: Expression<DateTime>): Function<BigDecimal>(DecimalColumnType(10, 10)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("extract(epoch from ", expr," )") }
}

In action:

fun getBlockCreationInterval(limit: Int) = transaction {
            val lag = Lag(BlockCacheTable.blockTimestamp, BlockCacheTable.height)
            val lagExtract = ExtractEpoch(lag)
            val baseExtract = ExtractEpoch(BlockCacheTable.blockTimestamp)
            val creationTime = lagExtract.minus(baseExtract)

            BlockCacheTable.slice(BlockCacheTable.height, creationTime)
                .selectAll()
                .orderBy(BlockCacheTable.height, SortOrder.DESC)
                .limit(limit)
                .map { Pair(it[BlockCacheTable.height], it[creationTime]) }
        }

[H4kt]

Any updates on this?
I would really like to see something like extract(epoch from ...) being as simple as other operations in exposed

[minxylynx]

@H4kt This is what Ive used

Edit: The comment above yours is mine as well, from a different account.

custom function:

class ExtractHour(val expr: Expression<DateTime>) : Function<Int>(IntegerColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("extract(hour from ", expr, " )") }
}

class ExtractDay(val expr: Expression<DateTime>) : Function<String>(VarCharColumnType(9)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("to_char(", expr, ", 'DAY')") }
}

class ExtractDOW(val expr: Expression<DateTime>) : Function<Int>(IntegerColumnType()) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("extract(dow from ", expr, " )") }
}

usage:

            val blockTimestamp = BlockCacheHourlyTxCountsTable.blockTimestamp
            val dow = ExtractDOW(blockTimestamp)
            val day = ExtractDay(blockTimestamp)
            val hour = ExtractHour(blockTimestamp)
            val txSum = BlockCacheHourlyTxCountsTable.txCount.sum()
            val result = BlockCacheHourlyTxCountsTable
                .slice(dow, day, hour, txSum)
                .selectAll()
                .groupBy(dow)
                .groupBy(day)
                .groupBy(hour)
                .orderBy(dow, SortOrder.ASC)
                .orderBy(hour, SortOrder.ASC)
                .map {
                    TxHeatmapRaw(
                        it[dow],
                        it[day].trim(),
                        it[hour],
                        it[txSum]!!
                    )
                }

Epoch would look like

class ExtractEpoch(val expr: Expression<DateTime>) : Function<BigDecimal>(DecimalColumnType(10, 10)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) =
        queryBuilder { append("extract(epoch from ", expr, " )") }
}