Behavioral analytics functions inspired by ClickHouse (sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, sequence_next_node)
Maintainer(s):
tomtom215
Installing and Loading
INSTALL behavioral FROM community;
LOAD behavioral;
Example
-- Session assignment with 30-minute timeout
SELECT ts, sessionize(ts, INTERVAL '30 minutes') OVER (ORDER BY ts) AS session_id
FROM events;
-- Conversion funnel analysis
SELECT user_id, window_funnel(INTERVAL '1 hour', ts,
event = 'view', event = 'cart', event = 'purchase')
FROM events GROUP BY user_id;
About behavioral
Behavioral analytics functions for DuckDB, providing complete ClickHouse parity:
- sessionize: Window function assigning session IDs based on timestamp gaps
- retention: Cohort retention analysis returning boolean arrays
- window_funnel: Conversion funnel step tracking with 6 composable modes
- window_funnel_events: Timestamps of the best funnel chain as LIST(TIMESTAMP)
- sequence_match: Pattern matching over event sequences (NFA-based)
- sequence_count: Count non-overlapping pattern matches
- sequence_match_events: Return matched condition timestamps
- sequence_next_node: Find the next event value after a pattern match
- behavioral_version: Diagnostic scalar returning the loaded extension version
All aggregate functions support up to 32 boolean event conditions. Invalid configuration (unknown modes, malformed patterns, month-based intervals) raises descriptive SQL errors. Pure Rust implementation with zero unsafe code in business logic. Benchmarked at 830 Melem/s (sessionize) and 95 Melem/s (sequence_match) on commodity hardware.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| behavioral_version | scalar | NULL | NULL | |
| retention | aggregate | NULL | NULL | |
| sequence_count | aggregate | NULL | NULL | |
| sequence_match | aggregate | NULL | NULL | |
| sequence_match_events | aggregate | NULL | NULL | |
| sequence_next_node | aggregate | NULL | NULL | |
| sessionize | aggregate | NULL | NULL | |
| window_funnel | aggregate | NULL | NULL | |
| window_funnel_events | aggregate | NULL | NULL |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
This extension does not add any settings.