Writing Analytics SQL with Common Table Expressions

http://research.neustar.biz/2014/05/19/writing-analytics-sql-with-common-table-expressions/

by chubot • 12 years ago

57 7 12 years ago

research.neustar.biz

Some RDBMSs support recursive CTEs, which come in handy sometimes. SQLite added support earlier this year, and I've written a handful of recursive CTEs to traverse DAGs in the database. I was blown away by how quickly SQLite can handle queries like that!

batbomb• 12 years ago

A few others: SQLite, Oracle, PostgreSQL, DB2, HSQLDB.

yangyang• 12 years ago

And SQL Server.

Xophmeister• 12 years ago

Recursive CTEs are part of the SQL:1999 standard and actually make SQL into Turing complete language. I didn't realise SQLite had implemented CTEs, let alone recursive ones...I'll have to play :)

benilov• 12 years ago

DbFit (http://dbfit.github.io/dbfit/) provides a more heavy-weight, but much more scalable alternative to raw SQL for testing CTEs.

j_s• 12 years ago

In SQL Server, CTEs eventually slow down then temp tables win because they can have indexes.

Writing Analytics SQL with Common Table Expressions

Related Stories