News for hackers. Stuff that matters.
Re:Writing Analytics SQL with Common Table Expressions by groovy2shoes on Saturday August 23, 2014 @01:57PM (#8216381)
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!
Re:Writing Analytics SQL with Common Table Expressions by Xophmeister on Saturday August 23, 2014 @04:44PM (#8216820)
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 :)
Re:Writing Analytics SQL with Common Table Expressions by batbomb on Saturday August 23, 2014 @02:19PM (#8216470)
A few others: SQLite, Oracle, PostgreSQL, DB2, HSQLDB.
Re:Writing Analytics SQL with Common Table Expressions by yangyang on Saturday August 23, 2014 @03:56PM (#8216715)
And SQL Server.
Re:Writing Analytics SQL with Common Table Expressions by collyw on Sunday August 24, 2014 @08:57AM (#8218439)
Everything except MySQL?
Re:Writing Analytics SQL with Common Table Expressions by benilov on Saturday August 23, 2014 @03:54PM (#8216705)
DbFit (http://dbfit.github.io/dbfit/) provides a more heavy-weight, but much more scalable alternative to raw SQL for testing CTEs.
Re:Writing Analytics SQL with Common Table Expressions by j_s on Saturday August 23, 2014 @08:49PM (#8217316)
In SQL Server, CTEs eventually slow down then temp tables win because they can have indexes.